Selection using Base functions and possibly missing values

[tcovert]

Suppose I have a DataFrame with two fields: idx and date. The date field has missing values (in the DataFrames sense) and is currently stored in the DataFrame as a string. Is there a query statement that I can write which parses the string into a date? I tried something like this:

df2 = @from i in df begin
       @select {i.idx, date = Date.(i.date, "mm/dd/yyyy")}
       @collect DataFrame
       end

but got an error like this:

ERROR: type UnionAll has no field parameters
Stacktrace:
 [1] column_types at /Users/tcovert/.julia/v0.6/IterableTables/src/utilities.jl:20 [inlined]
 [2] _DataFrame(::Query.EnumerableSelect{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},_} where _,Query.EnumerableIterable{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##11#13}) at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:105
 [3] DataFrames.DataFrame(::Query.EnumerableSelect{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},_} where _,Query.EnumerableIterable{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##11#13}) at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:128
 [4] collect(::Query.EnumerableSelect{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},_} where _,Query.EnumerableIterable{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##11#13}, ::Type{DataFrames.DataFrame}) at /Users/tcovert/.julia/v0.6/Query/src/sinks/sink_type.jl:2

I also tried a version with no dot-broadcasting:

df2 = @from i in df begin
       @select {i.idx, date = Date(i.date, "mm/dd/yyyy")}
       @collect DataFrame
       end

and got this error:

ERROR: MethodError: Cannot `convert` an object of type DataValues.DataValue{String} to an object of type Int64
This may have arisen from a call to the constructor Int64(...),
since type constructors fall back to convert methods.
Stacktrace:
 [1] next at /Users/tcovert/.julia/v0.6/Query/src/enumerable/enumerable_select.jl:41 [inlined]
 [2] macro expansion at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:91 [inlined]
 [3] _filldf(::Tuple{DataArrays.DataArray{Int64,1},Array{Date,1}}, ::Query.EnumerableSelect{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},Date},Query.EnumerableIterable{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##15#16}) at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:79
 [4] _DataFrame(::Query.EnumerableSelect{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},Date},Query.EnumerableIterable{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##15#16}) at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:119
 [5] DataFrames.DataFrame(::Query.EnumerableSelect{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},Date},Query.EnumerableIterable{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##15#16}) at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:128
 [6] collect(::Query.EnumerableSelect{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},Date},Query.EnumerableIterable{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_idx_date{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##15#16}, ::Type{DataFrames.DataFrame}) at /Users/tcovert/.julia/v0.6/Query/src/sinks/sink_type.jl:2

is what I am trying to do possible? if so, what am I doing wrong?

thanks in advance for any suggestions you can offer.

here is some example data to apply the code to above: https://www.dropbox.com/s/kgiicawhegmtavc/query_example.csv?dl=0

[davidanthoff]

I'll have to investigate why the . lifting doesn't work in that context. I'm also going to add a lifted version for the Date constructor in the DataValues.jl package that should make your second attempt work.

In the meantime, you can always handle the lifting manually by hand:

@select {i.idx, date = isnull(i.date) ? ?Date() : DataValue{Date}(Date(get(i.date), "mm/dd/yyyy"))}

Yes, cumbersome, but you don't have to wait for me to finish these fixes ;)

Also, if you use the new FileIO.jl integration for loading files that I showed in my juliacon talk, you get a proper Date column in the DataFrame without any manipulation, the TextParse.jl package seems to autodetect the type of the column. To get started with that approach, do a Pkg.clone("https://github.com/davidanthoff/Dataverse.jl.git"), then using Dataverse. After that the following should work:

df = DataFrame(load("filename.csv"))

or alternatively you can use a pipe syntax:

df = load("filensame.csv") |> DataFrame

[tcovert]

Thanks for taking a look.

The FileIO integration is cool, but in my experience, the old school readtable from DataFrames is a much more reliable CSV parser than TextParse or CSV. For example, neither TextParse nor CSV are able to read the actual data I am working with (I've included just a subset of the columns in the MWE above), while readable works fine, aside from the type issues.

[davidanthoff]

Ah, interesting. Can you open issues about those problems, maybe even in TextParse.jl and CSV.jl? I think the maintainers of DataFrames.jl and DataTables.jl plan to do away with readtable, so it would be quite crucial that the replacement parsers are able to handle the kind of data you are using.

[davidanthoff]

Alright, the . lifting doesn't work because some of the methods in base are not type stable. It might be enough to add a return type annotation here, but not sure.

queryverse/DataValues.jl#15 has a new method for the Date constructor, once that is merged and tagged, the non-dot-broadcasting version should just work.

[tcovert]

Yes, I have opened issues for CSV parsing already:
JuliaData/CSV.jl#86
queryverse/TextParse.jl#19

[tcovert]

here is a related issue, perhaps with the handling of the split function on DataValues:

julia> df = DataFrame(id = [1, 1, 2, 2], val = @data(["a,b", NA, "c,d", "e,f"]))
julia> @from i in df begin
       @where !isnull(i.val)
       @select {i.id, names = split(i.val, ",")} into i
       @from j in i.names
       @select {i.id, newval = j}
       @collect DataFrame
       end
ERROR: type UnionAll has no field parameters
Stacktrace:
 [1] select_many(::Query.EnumerableSelect{NamedTuples._NT_id_names{DataValues.DataValue{Int64},_} where _,Query.EnumerableWhere{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Query.EnumerableIterable{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##49#53},##50#54}, ::##51#55, ::Expr, ::Function, ::Expr) at /Users/tcovert/.julia/v0.6/Query/src/enumerable/enumerable_selectmany.jl:51

julia> @from i in df begin
       @where !isnull(i.val)
       @select {i.id, names = split.(i.val, ",")} into i
       @from j in i.names
       @select {i.id, newval = j}
       @collect DataFrame
       end
ERROR: 
Stacktrace:
 [1] query(::DataValues.DataValue{Array{SubString{String},1}}) at /Users/tcovert/.julia/v0.6/Query/src/sources/source_iterable.jl:6
 [2] start(::Query.EnumerableSelectMany{NamedTuples._NT_id_newval{DataValues.DataValue{Int64},Array{SubString{String},1}},Query.EnumerableSelect{NamedTuples._NT_id_names{DataValues.DataValue{Int64},DataValues.DataValue{Array{SubString{String},1}}},Query.EnumerableWhere{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Query.EnumerableIterable{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##57#62},##58#63},##60#65,##61#66}) at /Users/tcovert/.julia/v0.6/Query/src/enumerable/enumerable_selectmany.jl:67
 [3] macro expansion at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:91 [inlined]
 [4] _filldf(::Tuple{DataArrays.DataArray{Int64,1},Array{Array{SubString{String},1},1}}, ::Query.EnumerableSelectMany{NamedTuples._NT_id_newval{DataValues.DataValue{Int64},Array{SubString{String},1}},Query.EnumerableSelect{NamedTuples._NT_id_names{DataValues.DataValue{Int64},DataValues.DataValue{Array{SubString{String},1}}},Query.EnumerableWhere{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Query.EnumerableIterable{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##57#62},##58#63},##60#65,##61#66}) at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:79
 [5] _DataFrame(::Query.EnumerableSelectMany{NamedTuples._NT_id_newval{DataValues.DataValue{Int64},Array{SubString{String},1}},Query.EnumerableSelect{NamedTuples._NT_id_names{DataValues.DataValue{Int64},DataValues.DataValue{Array{SubString{String},1}}},Query.EnumerableWhere{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Query.EnumerableIterable{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##57#62},##58#63},##60#65,##61#66}) at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:119
 [6] DataFrames.DataFrame(::Query.EnumerableSelectMany{NamedTuples._NT_id_newval{DataValues.DataValue{Int64},Array{SubString{String},1}},Query.EnumerableSelect{NamedTuples._NT_id_names{DataValues.DataValue{Int64},DataValues.DataValue{Array{SubString{String},1}}},Query.EnumerableWhere{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Query.EnumerableIterable{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##57#62},##58#63},##60#65,##61#66}) at /Users/tcovert/.julia/v0.6/IterableTables/src/integrations/dataframes.jl:128
 [7] collect(::Query.EnumerableSelectMany{NamedTuples._NT_id_newval{DataValues.DataValue{Int64},Array{SubString{String},1}},Query.EnumerableSelect{NamedTuples._NT_id_names{DataValues.DataValue{Int64},DataValues.DataValue{Array{SubString{String},1}}},Query.EnumerableWhere{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Query.EnumerableIterable{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},IterableTables.DataFrameIterator{NamedTuples._NT_id_val{DataValues.DataValue{Int64},DataValues.DataValue{String}},Tuple{DataArrays.DataArray{Int64,1},DataArrays.DataArray{String,1}}}},##57#62},##58#63},##60#65,##61#66}, ::Type{DataFrames.DataFrame}) at /Users/tcovert/.julia/v0.6/Query/src/sinks/sink_type.jl:2

as you might guess, this is related to the question I posed over at IndexedTables here: JuliaData/IndexedTables.jl#70

edit: apparently this works though:

julia> @from i in df begin
       @where !isnull(i.val)
       @select {i.id, names = split.(get(i.val), ",")} into i
       @from j in i.names
       @select {i.id, newval = j}
       @collect DataFrame
       end
6×2 DataFrames.DataFrame
│ Row │ id │ newval │
├─────┼────┼────────┤
│ 1   │ 1  │ "a"    │
│ 2   │ 1  │ "b"    │
│ 3   │ 2  │ "c"    │
│ 4   │ 2  │ "d"    │
│ 5   │ 2  │ "e"    │
│ 6   │ 2  │ "f"    │

why does this query need both array broadcasting and a get call to work?

edit 2: ok now I see that this works as well:

julia> @from i in df begin
       @where !isnull(i.val)
       @select {i.id, names = split(get(i.val), ",")} into i
       @from j in i.names
       @select {i.id, newval = j}
       @collect DataFrame
       end
6×2 DataFrames.DataFrame
│ Row │ id │ newval │
├─────┼────┼────────┤
│ 1   │ 1  │ "a"    │
│ 2   │ 1  │ "b"    │
│ 3   │ 2  │ "c"    │
│ 4   │ 2  │ "d"    │
│ 5   │ 2  │ "e"    │
│ 6   │ 2  │ "f"    │

so to answer my own question, no array broadcasting is needed, just get

[davidanthoff]

@tcovert I can close this, right?

I have one idea for the split story here queryverse/DataValues.jl#34. Probably a really bad idea, if you have an opinion, I'd be interested to hear it over there.

If I missed some open issue here, just let me know and I'll reopen.