Queryverse queries lose type information?

I’m working on a tutorial and have the following code:

dflong2 = DataFrame(dflong |> @mutate(year = tryparse(Int,String(_.variable)[end-3:end])))

obviously I’m modifying the year column to be the result of trying to parse the last 4 characters of the variable column into an integer because someone at the Census decided that a wide form table with POPESTIMATE2010, POPESTIMATE2011,… was a good idea.

EDIT: That actually works… But then when I go to strip the year off the symbols in dflong2 I get:

pop = dflong2 |> @filter(! (_.year isa Nothing))|> @map(variable = Symbol(String(_.variable)[1:end-3]))|>DataFrame

It doesn’t work… Why? Because although each column in dflong has a particular type, after converting the query pipeline to a DataFrame == dflong2, the fields have type DataValue{Any} and although the variable field is always a symbol, I can’t convert a symbol to a string when Julia doesn’t know what type it is:

MethodError: no method matching String(::DataValue{Any})
Closest candidates are:
  String(!Matched::String) at boot.jl:321
  String(!Matched::Array{UInt8,1}) at strings/string.jl:39
  String(!Matched::Base.CodeUnits{UInt8,String}) at strings/string.jl:77
  ...

The complete loss of all type information seems to be a non-starter for Query! I can’t believe that’s correct, so what am I doing wrong?

Not sure I understand - are POPESTIMATE2010 column names? And you’ve already stacked the dataframe so now you have a column that holds all the former column names?

I’d probably just do

parse.(Int, replace.(string.(df.variable), "POPESTIMATE" => ""))

(I can’t really comment on the Query solution as I find base DataFrames flexible enough for my needs, but I’m sure someone will be along to solve that problem as well shortly!)

Yes, that’s correct. I had an error in my original post and made some edits, please see that… The parse that I did in the first query pipeline did work… but then when I go to modify the resulting data frame, I want to take the symbols POPESTIMATE2010 and soforth and strip off all the digits at the end, so that I have POPESTIMATE as the key, and the year is already a separate column… and it doesn’t work because I can’t convert an Any to a String and in the first pipeline it lost all type info!

Also, to give some more background. As is typical for data put out by Govt entities, they make it easy for Excel users, and HARD for the real pros. In fact, there are about 5 or 6 variables each of which has a series of 10 columns for the data 2010 through 2019. So I can’t just strip out “POPESTIMATE” because there is also “BIRTHS” and “DEATHS” and “MIGRATIONOUT” or whatever… So the first thing I did was run through the column stripping off the last 4 digits, and converting it to a year. so each row has an associated year… and then I decided I wanted to drop the year from the end of the symbols… and it was at that point that I discovered all the type info was lost.

You can retrain the first part of the information by splitting:

julia> using DataFrames 

julia> df = DataFrame(variable = ["POPESTIMATE2018", "POPESTIMATE2019", "BIRTHS2018", "BIRTHS2019"])
4×1 DataFrame
│ Row │ variable        │
│     │ String          │
├─────┼─────────────────┤
│ 1   │ POPESTIMATE2018 │
│ 2   │ POPESTIMATE2019 │
│ 3   │ BIRTHS2018      │
│ 4   │ BIRTHS2019      │

julia> using Dates

julia> df[!, "year"] = Date.(last.(split.(df.variable, "20"))) .+ Year(2000);

julia> df[!, "variable"] = first.(split.(df.variable, "20"));

julia> df
4×2 DataFrame
│ Row │ variable    │ year       │
│     │ SubString…  │ Date       │
├─────┼─────────────┼────────────┤
│ 1   │ POPESTIMATE │ 2018-01-01 │
│ 2   │ POPESTIMATE │ 2019-01-01 │
│ 3   │ BIRTHS      │ 2018-01-01 │
│ 4   │ BIRTHS      │ 2019-01-01 │

That’s a nice method, and it solves the problem of how to do this particular thing, sort of… undoubtedly someone is going to do POP18-20_2010, POP18-20_2011 because they are always out to get you. :wink: The only guarantee I have is that if the last 4 characters of the string are digits, then they’re the year.

The real question here though is, why when I run the Query pipeline do I lose all my type information? Because in the long run I want to use Query pipelines, but not if they result in information that has no type and can’t be modified or computed with.

I see that the simplest case doesn’t lose type info:

 df
3×2 DataFrame
│ Row │ a     │ b      │
│     │ Int64 │ Symbol │
├─────┼───────┼────────┤
│ 1   │ 1     │ foo    │
│ 2   │ 2     │ bar    │
│ 3   │ 3     │ baz    │

julia>  df |> @mutate(b=String(_.b)) |> DataFrame
3×2 DataFrame
│ Row │ a     │ b      │
│     │ Int64 │ String │
├─────┼───────┼────────┤
│ 1   │ 1     │ foo    │
│ 2   │ 2     │ bar    │
│ 3   │ 3     │ baz    │

But something destroyed the types in my more complex query. Sigh…

I only just see that you’re using String - this is a type constructor, for conversion of a wider range of inputs into a String object you should use the string function (lowercase, which by convention in Julia is a function name, as compared to uppercase which is conventionally use to denote types)

1 Like

Here’s where things went wrong:

dflong.year=fill(-1,nrow(dflong))
display(first(dflong,10))
dflong2 = DataFrame(dflong |> @mutate(year = tryparse(Int,string(_.variable)[end-3:end])))
display(first(dflong2,10))

The first display shows a table with data types (copied and pasted from Jupyter, sorry if the formatting is not great)

variable	value	SUMLEV	REGION	DIVISION	STATE	COUNTY	STNAME	CTYNAME
	Symbol	Float64	Int64	Int64	Int64	Int64	Int64	String	String
1	CENSUS2010POP	4.77974e6	40	3	6	1	0	Alabama	Alabama
2	CENSUS2010POP	54571.0	50	3	6	1	1	Alabama	Autauga County
3	CENSUS2010POP	182265.0	50	3	6	1	3	Alabama	Baldwin County
4	CENSUS2010POP	27457.0	50	3	6	1	5	Alabama	Barbour County
5	CENSUS2010POP	22915.0	50	3	6	1	7	Alabama	Bibb County
6	CENSUS2010POP	57322.0	50	3	6	1	9	Alabama	Blount County
7	CENSUS2010POP	10914.0	50	3	6	1	11	Alabama	Bullock County
8	CENSUS2010POP	20947.0	50	3	6	1	13	Alabama	Butler County
9	CENSUS2010POP	118572.0	50	3	6	1	15	Alabama	Calhoun County
10	CENSUS2010POP	34215.0	50	3	6	1	17	Alabama	Chambers County

The second display shows a table with no datatypes in any column:

variable	value	SUMLEV	REGION	DIVISION	STATE	COUNTY	STNAME	CTYNAME
	Any	Any	Any	Any	Any	Any	Any	Any	Any
1	CENSUS2010POP	4.77974e6	40	3	6	1	0	Alabama	Alabama
2	CENSUS2010POP	54571.0	50	3	6	1	1	Alabama	Autauga County
3	CENSUS2010POP	182265.0	50	3	6	1	3	Alabama	Baldwin County
4	CENSUS2010POP	27457.0	50	3	6	1	5	Alabama	Barbour County
5	CENSUS2010POP	22915.0	50	3	6	1	7	Alabama	Bibb County
6	CENSUS2010POP	57322.0	50	3	6	1	9	Alabama	Blount County
7	CENSUS2010POP	10914.0	50	3	6	1	11	Alabama	Bullock County
8	CENSUS2010POP	20947.0	50	3	6	1	13	Alabama	Butler County
9	CENSUS2010POP	118572.0	50	3	6	1	15	Alabama	Calhoun County
10	CENSUS2010POP	34215.0	50	3	6	1	17	Alabama	Chambers County

As soon as everything is an “Any” then as you pointed out, String won’t construct a string from a Symbol stored in an Any…

As you pointed out lower case string does do the conversion but it doesn’t do it correctly. Instead of giving the characters that make up the symbol name, it gives the string “DataValue{Any}(:SYMBOLHERE)” which then gets stripped by my conversion, incorrectly…

pop = dflong2 |> @filter(! (_.year isa Nothing))|> @mutate(variable = Symbol(string(_.variable)[1:end-3]))|>DataFrame
display(first(pop,3))

	variable	value	SUMLEV	REGION	DIVISION	STATE	COUNTY
	Symbol	Any	Any	Any	Any	Any	Any
1	DataValue{Any}(:CENSUS2010P	4.77974e6	40	3	6	1	0
2	DataValue{Any}(:CENSUS2010P	54571.0	50	3	6	1	1
3	DataValue{Any}(:CENSUS2010P	182265.0	50	3	6	1	3

So the data becomes fairly useless as soon as everything becomes an Any

I never got around to figuring out this whole DataValue business in the queryverse - we might have to summon @davidanthoff to shed light on this :slight_smile:

I have a suspicion that tryparse, which can return all kinds of data types, might be forcing a conversion to DataValue{Any}. I’ll see if I can make that type-stable and see what results.

learning … learning…

Indeed:

dflong2 = DataFrame(dflong |> @mutate(year = something(tryparse(Int,string(_.variable)[end-3:end]),0)))
display(first(dflong2,10))

gives a table with proper data types.

In this case it’s OK to encode meaningless data as 0. But I find it weird that I lose ALL type info rather than just the particular column “year”.