Any better equivalent for pandas value_counts in julia dataframes?

user_9038021 0
Viewed 1100

I was searching for an equivalent for the very convenient value_counts in pandas for a series in a dataframe in julia.

Unfortunately I could not find anything here, so my solution for a value_counts in a julia dataframe is as follows. However I didn't like my solution very much, as it is not as convinient compared to pandas with the method .value_counts(). So my question, is there another (more convinient) option than this?

jdf = DataFrame(rand(Int8, (1000000, 3)))

which gives me:

│ Row     │ x1   │ x2   │ x3   │
│         │ Int8 │ Int8 │ Int8 │
├─────────┼──────┼──────┼──────┤
│ 1       │ -97  │ 98   │ 79   │
│ 2       │ -77  │ -118 │ -19  │
⋮
│ 999998  │ -115 │ 17   │ 107  │
│ 999999  │ -43  │ -64  │ 72   │
│ 1000000 │ 40   │ -11  │ 31   │

Value count for the first column would be:

combine(nrow,groupby(jdf,:x1))

which returns:

│ Row │ x1   │ nrow  │
│     │ Int8 │ Int64 │
├─────┼──────┼───────┤
│ 1   │ -97  │ 3942  │
│ 2   │ -77  │ 3986  │
⋮
│ 254 │ 12   │ 3899  │
│ 255 │ -92  │ 3973  │
│ 256 │ -49  │ 3952  │
julia
1 Answers

In DataFrames.jl this is the way to get the result you want. In general the approach in DataFrames.jl is to have a minimal API. If you use combine(nrow,groupby(jdf,:x1)) often then you can just define:

value_counts(df, col) = combine(groupby(df, col), nrow)

in your script.

Alternative ways to achieve what you want are using FreqTables.jl or StatsBase.jl:

julia> freqtable(jdf, :x1)
256-element Named Array{Int64,1}
x1   │
─────┼─────
-128 │ 3875
-127 │ 3931
-126 │ 3924
⋮         ⋮
125  │ 3873
126  │ 3917
127  │ 3975

julia> countmap(jdf.x1)
Dict{Int8,Int64} with 256 entries:
  -98  => 3925
  -74  => 4054
  11   => 3798
  -56  => 3853
  29   => 3765
  -105 => 3918
  ⋮    => ⋮

(the difference is that the output type will differ)

In terms of performance countmap is fastest, and combine is slowest:

julia> using BenchmarkTools

julia> @benchmark countmap($jdf.x1)
BenchmarkTools.Trial:
  memory estimate:  16.80 KiB
  allocs estimate:  14
  --------------
  minimum time:     436.000 μs (0.00% GC)
  median time:      443.200 μs (0.00% GC)
  mean time:        455.244 μs (0.22% GC)
  maximum time:     5.362 ms (91.59% GC)
  --------------
  samples:          10000
  evals/sample:     1

julia> @benchmark freqtable($jdf, :x1)
BenchmarkTools.Trial:
  memory estimate:  37.22 KiB
  allocs estimate:  86
  --------------
  minimum time:     7.972 ms (0.00% GC)
  median time:      8.089 ms (0.00% GC)
  mean time:        8.158 ms (0.00% GC)
  maximum time:     10.016 ms (0.00% GC)
  --------------
  samples:          613
  evals/sample:     1

julia> @benchmark combine(groupby($jdf,:x1), nrow)
BenchmarkTools.Trial:
  memory estimate:  23.28 MiB
  allocs estimate:  183
  --------------
  minimum time:     12.679 ms (0.00% GC)
  median time:      14.572 ms (8.68% GC)
  mean time:        15.239 ms (14.50% GC)
  maximum time:     20.385 ms (21.83% GC)
  --------------
  samples:          328
  evals/sample:     1

Note though that in combine most of the cost is grouping, so if you have the GroupedDataFrame object created already then combine is relatively fast:

julia> gdf = groupby(jdf,:x1);

julia> @benchmark combine($gdf, nrow)
BenchmarkTools.Trial:
  memory estimate:  16.16 KiB
  allocs estimate:  152
  --------------
  minimum time:     680.801 μs (0.00% GC)
  median time:      714.800 μs (0.00% GC)
  mean time:        737.568 μs (0.15% GC)
  maximum time:     4.561 ms (83.47% GC)
  --------------
  samples:          6766
  evals/sample:     1

EDIT

If you want a sorted dict then load DataStructures.jl and then do:

sort!(OrderedDict(countmap(jdf.x1)))

or

 sort!(OrderedDict(countmap(jdf.x1)), byvalue=true)

depending on by what you want to sort the dictionary.

user_12695670
Related