# Help needed with DataFrames and Query
**URL:** https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077
**Category:** Data
**Tags:** data
**Created:** [September 26, 2017, 8:55am UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077 "2017-09-26T08:55:21Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@johann.spies](https://discourse.julialang.org/u/johann.spies)
#### Post date: [September 26, 2017, 8:55am UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/1 "2017-09-26T08:55:21Z")
I am learning how to use DataFrames and find am struggling to use it for what I need it for.
My present problem is illustrated by this attempt to use Quey.jl. I cannot understand why there result of q1 has no values.
And I cannot understand why I cannot use @collect DataFrame in this case.
```julia
julia> typeof(bs)
DataFrames.DataFrame
julia> size(bs)
(33067, 7)
julia> bs[1:2,:]
2×7 DataFrames.DataFrame
│ Row │ broadfield │ field │ title │ pubtype │ pubyear │ doctype │ publications │
├─────┼───────────────────────┼────────────────────────┼──────────────────┼───────────┼─────────┼───────────┼──────────────┤
│ 1 │ "Biological Sciences" │ "Anatomy & Morphology" │ "ACTA ZOOLOGICA" │ "Journal" │ 2006 │ "Article" │ 1 │
│ 2 │ "Biological Sciences" │ "Anatomy & Morphology" │ "ACTA ZOOLOGICA" │ "Journal" │ 2007 │ "Article" │ 2 │
julia> names(bs)
7-element Array{Symbol,1}:
:broadfield
:field
:title
:pubtype
:pubyear
:doctype
:publications
julia> bfs = Set(bs[:broadfield])
Set(Nullable{String}["Mathematics", "Statistics", "Geological Sciences", "Biological Sciences", "Physics", "Computer Science", "Chemistry"])
julia> describe(bs)
broadfield
Summary Stats:
Length: 33067
Type: Nullable{String}
Number Unique: 7
Number Missing: 0
% Missing: 0.000000
field
Summary Stats:
Length: 33067
Type: Nullable{WeakRefString{UInt16}}
Number Unique: 55
Number Missing: 0
% Missing: 0.000000
title
Summary Stats:
Length: 33067
Type: Nullable{WeakRefString{UInt16}}
Number Unique: 5229
Number Missing: 0
% Missing: 0.000000
pubtype
Summary Stats:
Length: 33067
Type: Nullable{WeakRefString{UInt16}}
Number Unique: 5
Number Missing: 0
% Missing: 0.000000
pubyear
Summary Stats:
Mean: 2010.479421
Minimum: 2005.000000
1st Quartile: 2008.000000
Median: 2011.000000
3rd Quartile: 2013.000000
Maximum: 2015.000000
Length: 33067
Type: Int16
Number Missing: 0
% Missing: 0.000000
doctype
Summary Stats:
Length: 33067
Type: Nullable{WeakRefString{UInt16}}
Number Unique: 15
Number Missing: 0
% Missing: 0.000000
publications
Summary Stats:
Mean: 2.720930
Minimum: 1.000000
1st Quartile: 1.000000
Median: 1.000000
3rd Quartile: 3.000000
Maximum: 210.000000
Length: 33067
Type: Int64
Number Missing: 0
% Missing: 0.000000
julia> q1 = @from i in bs begin
@where i.broadfield == "Chemistry"
@select i.field
@collect
end
0-element Array{Nullable{WeakRefString{UInt16}},1}
julia> q1 = @from i in bs begin
@where i.broadfield == "Chemistry"
@select i.field
@collect DataFrame
end
ERROR: MethodError: Cannot `convert` an object of type Query.EnumerableSelect{Nullable{WeakRefString{UInt16}},Query.EnumerableWhere{NamedTuples._NT_broadfield_field_title_pubtype_pubyear_doctype_publications{Nullable{String},Nullable{WeakRefString{UInt16}},Nullable{WeakRefString{UInt16}},Nullable{WeakRefString{UInt16}},Nullable{Int16},Nullable{WeakRefString{UInt16}},Nullable{Int64}},Query.EnumerableIterable{NamedTuples._NT_broadfield_field_title_pubtype_pubyear_doctype_publications{Nullable{String},Nullable{WeakRefString{UInt16}},Nullable{WeakRefString{UInt16}},Nullable{WeakRefString{UInt16}},Nullable{Int16},Nullable{WeakRefString{UInt16}},Nullable{Int64}},IterableTables.DataFrameIterator{NamedTuples._NT_broadfield_field_title_pubtype_pubyear_doctype_publications{Nullable{String},Nullable{WeakRefString{UInt16}},Nullable{WeakRefString{UInt16}},Nullable{WeakRefString{UInt16}},Nullable{Int16},Nullable{WeakRefString{UInt16}},Nullable{Int64}},Tuple{NullableArrays.NullableArray{String,1},NullableArrays.NullableArray{WeakRefString{UInt16},1},NullableArrays.NullableArray{WeakRefString{UInt16},1},NullableArrays.NullableArray{WeakRefString{UInt16},1},NullableArrays.NullableArray{Int16,1},NullableArrays.NullableArray{WeakRefString{UInt16},1},NullableArrays.NullableArray{Int64,1}}}},##10#12},##11#13} to an object of type DataFrames.DataFrame
This may have arisen from a call to the constructor DataFrames.DataFrame(...),
since type constructors fall back to convert methods.
```
Regards
Johann
---
### Author:  [@swt30](https://discourse.julialang.org/u/swt30)
#### Post date: [September 26, 2017, 1:35pm UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/2 "2017-09-26T13:35:51Z")
When you do a `@select` in Query and then collect into a DataFrame, you need to provide an expression that constructs a named tuple so that Query knows the column names. In Query there’s a shorthand for doing this that uses curly braces `{}`. Does replacing your select statement with
```julia
@select {i.field}
```
do what you want?
Also, when posting here you can quote your code by putting triple backticks around it
```
like this
```
to make it easier to read.
---
### Author:  [@piever](https://discourse.julialang.org/u/piever)
#### Post date: [September 26, 2017, 2:02pm UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/3 "2017-09-26T14:02:05Z")
```julia
julia> q1 = @from i in bs begin
@where i.broadfield == “Chemistry”
@select i.field
@collect
end
0-element Array{Nullable{WeakRefString{UInt16}},1}
```
This shouldn’t be empty. I’m afraid your DataFrame has Nullable strings rather than normal strings so that the comparison never holds. I’m not sure how Nullables ended up there, maybe some CSV reader that hasn’t updated yet. If you loaded the data using CSV.jl, could you try just loading it with `readtable` and see if that works?
---
### Author:  [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)
#### Post date: [September 27, 2017, 5:33am UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/4 "2017-09-27T05:33:33Z")
If you use CSVFiles.jl to read your CSV files into a DataFrame it should always create the right column types (normal array if there are no missing values in the column, otherwise a DataArray).
---
### Author:  [@johann.spies](https://discourse.julialang.org/u/johann.spies)
#### Post date: [September 27, 2017, 7:27am UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/5 "2017-09-27T07:27:42Z")
> [@piever](#):
>
> q1 = @from i in bs begin
> @where i.broadfield == “Chemistry”
> @select i.field
> @collect
> end
Thanks @swt30, @piever and @davidanthoff for your replies.
I am a relative newcomer to Julia and have yet to get to grips with it’s typing system.
The DataFrame was created using ODBC to query a PostgreSQL-database. (Unfortunately there is not yet a good PostgreSQL-specific interface to do that in Julia).
My next question would then be: How is it possible to convert the type of Nullable strings in a DataFrame to normal strings?
Regards
Johann
---
### Author:  [@swt30](https://discourse.julialang.org/u/swt30)
#### Post date: [September 27, 2017, 8:20am UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/6 "2017-09-27T08:20:31Z")
As yes, PostgreSQL support in Julia is pretty poor at the moment. I have forks of [PostgreSQL.jl](https://github.com/swt30/PostgreSQL.jl) and [DBI.jl](https://github.com/swt30/DBI.jl) that just merge some pending pull requests to get them working on julia 0.6 if you were interested. But I won’t blame you for wanting to stick with something a little more “official”!
In the meantime, the best way I can come up with to manually convert columns from DataArray{Nullable{something}} to DataArray{something} is this:
```julia
using DataFrames
"Unwrap any Nullables inside a DataArray or DataFrame, turning nulls into NAs"
function unwrap(x::DataArray{Nullable{T}})::DataArray{T} where {T <: Any}
get.(x, NA)
end
unwrap(x::DataArray) = x
unwrap(df::DataFrame) = DataFrame(unwrap.(df.columns), names(df))
cleaned_bs = unwrap(bs)
```
That function will unwrap any column of nullables (first method) while leaving ‘normal’ columns alone (second method). The third method is so you can just call `unwrap` on your entire DataFrame. Hope that helps—let us know if you have any questions about the way that types are being used in the above code, as it uses some recent-ish additions to Julia (return type annotation and the `where` syntax).
---
### Author:  [@johann.spies](https://discourse.julialang.org/u/johann.spies)
#### Post date: [September 27, 2017, 10:19am UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/7 "2017-09-27T10:19:53Z")
Thanks @swt30
Your unwrap function did not solve the problem. The types of the columns were more complicated than I (and your code) expected.
So I did clone your forks of PostgreSQL and DBI and could get it working.
Now my Query.jl stuff also works as expected and I can carry on exploring DataFrames.
Regards
Johann
---
### Author:  [@swt30](https://discourse.julialang.org/u/swt30)
#### Post date: [September 27, 2017, 10:35am UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/8 "2017-09-27T10:35:46Z")
It could be something to do with those `WeakRefString`s—I don’t know what those are. Anyway, glad to hear it is working for you.
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [September 27, 2017, 1:01pm UTC](https://discourse.julialang.org/t/help-needed-with-dataframes-and-query/6077/9 "2017-09-27T13:01:26Z")
See this post about how to disable `WeakRefString` (the data ecosystem is quite inconsistent at the moment, but we hope to stabilize it before Julia 1.0):
[https://discourse.julialang.org/t/how-to-change-value-of-nullable-weakrefstring-uint8-using-csv-package](https://discourse.julialang.org/t/how-to-change-value-of-nullable-weakrefstring-uint8-using-csv-package)