How to efficiently grab data based on string value of a row

user_11171682 0
Viewed 37

I have the data set pictured here: enter image description here

I built a simple linear regression model for one specific county, and now I want to test its accuracy on different counties. Currently, I know that I can create a subset of the data for one county using the following: tompkins = tax[strip.(tax.County, [' ']) .== "Tompkins",:]. However, it would be quite cumbersome to manually create a subset of the data for each county this way. Is there a more efficient way where I can gather all county data subsets so that I can run my linear model on them?

julia dataframe
1 Answers

What you are looking for is groupby.

Suppose that you have the following DataFrame:

julia> df = DataFrame(Country=rand([:A,:B,:C],7), year=rand(2000:2020,7), tax=rand(7))
7×3 DataFrame
│ Row │ Country │ year  │ tax      │
│     │ Symbol  │ Int64 │ Float64  │
├─────┼─────────┼───────┼──────────┤
│ 1   │ A       │ 2014  │ 0.913118 │
│ 2   │ C       │ 2003  │ 0.894182 │
│ 3   │ A       │ 2018  │ 0.917585 │
│ 4   │ C       │ 2011  │ 0.869531 │
│ 5   │ A       │ 2011  │ 0.45841  │
│ 6   │ B       │ 2001  │ 0.808954 │
│ 7   │ B       │ 2008  │ 0.969813 │

You can collect information by each country:

dfg = groupby(df, :Country);

and now:

julia> dfg[1]
3×3 SubDataFrame
│ Row │ Country │ year  │ tax      │
│     │ Symbol  │ Int64 │ Float64  │
├─────┼─────────┼───────┼──────────┤
│ 1   │ A       │ 2014  │ 0.913118 │
│ 2   │ A       │ 2018  │ 0.917585 │
│ 3   │ A       │ 2011  │ 0.45841  │

julia> dfg[2]
2×3 SubDataFrame
│ Row │ Country │ year  │ tax      │
│     │ Symbol  │ Int64 │ Float64  │
├─────┼─────────┼───────┼──────────┤
│ 1   │ C       │ 2003  │ 0.894182 │
│ 2   │ C       │ 2011  │ 0.869531 │

julia> dfg[3]
2×3 SubDataFrame
│ Row │ Country │ year  │ tax      │
│     │ Symbol  │ Int64 │ Float64  │
├─────┼─────────┼───────┼──────────┤
│ 1   │ B       │ 2001  │ 0.808954 │
│ 2   │ B       │ 2008  │ 0.969813 │

Note that for faster search it is better to use Symbols than string. You can always use vectorized Symbol.() constructor to convert any column of Strings.

user_99577100
Related