Selection using Base functions and possibly missing values #134

Activity

1. davidanthoff commented on Jun 30, 2017

   

   davidanthoff

   on Jun 30, 2017

   Member

   More actions

   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("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/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
2. tcovert commented on Jun 30, 2017

   

   tcovert

   on Jun 30, 2017

   Author

   More actions

   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.
3. davidanthoff commented on Jun 30, 2017

   

   davidanthoff

   on Jun 30, 2017

   Member

   More actions

   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.
4. davidanthoff commented on Jun 30, 2017

   

   davidanthoff

   on Jun 30, 2017

   Member

   More actions

   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.
5. tcovert commented on Jun 30, 2017

   

   tcovert

   on Jun 30, 2017

   Author

   More actions

   Yes, I have opened issues for CSV parsing already:
   JuliaData/CSV.jl#86
   queryverse/TextParse.jl#19
6. tcovert commented on Aug 4, 2017

   

   tcovert

   on Aug 4, 2017

   Last edited by tcovert

   Author

   More actions

   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
7. davidanthoff commented on Nov 24, 2017

   

   davidanthoff

   on Nov 24, 2017

   Member

   More actions

   @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.
8. davidanthoff

   closed this as completedon Nov 24, 2017