Julia Dataframe group by and pivot tables function

Question

How do you do group by and pivot tables with Julia Dataframes?

Lets say I have Dataframe

using DataFrames

df =DataFrame(Location = [ "NY", "SF", "NY", "NY", "SF", "SF", "TX", "TX", "TX", "DC"],
                 Class = ["H","L","H","L","L","H", "H","L","L","M"],
                 Address = ["12 Silver","10 Fak","12 Silver","1 North","10 Fak","2 Fake", "1 Red","1 Dog","2 Fake","1 White"],
                 Score = ["4","5","3","2","1","5","4","3","2","1"])

and I want to do the following:

1) a pivot table with Location and Class which should output

Class     H  L  M
Location         
DC        0  0  1
NY        2  1  0
SF        1  2  0
TX        1  2  0

2) group by "Location" and a count on the number of records in that group, which should output

   Pop  
DC  1   
NY  3  
SF  3  
TX  3 

Answer 1

For part 2 of your question, you can use an anonymous function and return a DataFrame, in order to name the new column, for example as count:

julia> by(df, :Location, d -> DataFrame(count=nrow(d)))
4x2 DataFrames.DataFrame
| Row | Location | count |
|-----|----------|-------|
| 1   | "DC"     | 1     |
| 2   | "NY"     | 3     |
| 3   | "SF"     | 3     |
| 4   | "TX"     | 3     |

Answer 2

You can use unstack to get you most of the way (DataFrames don't have an index so Class has to remain a column, rather than in pandas where it would be an Index), this seems to be DataFrames.jl's answer to pivot_table:

julia> unstack(df, :Location, :Class, :Score)
WARNING: Duplicate entries in unstack.
4x4 DataFrames.DataFrame
| Row | Class | H   | L   | M   |
|-----|-------|-----|-----|-----|
| 1   | "DC"  | NA  | NA  | "1" |
| 2   | "NY"  | "3" | "2" | NA  |
| 3   | "SF"  | "5" | "1" | NA  |
| 4   | "TX"  | "4" | "2" | NA  |

I'm not sure how you fillna here (unstack doesn't have this option)...

You can do the groupby using by with the nrows (number of rows) method:

julia> by(df, :Location, nrow)
4x2 DataFrames.DataFrame
| Row | Location | x1 |
|-----|----------|----|
| 1   | "DC"     | 1  |
| 2   | "NY"     | 3  |
| 3   | "SF"     | 3  |
| 4   | "TX"     | 3  |

Answer 3

Using the pivot (df, rowFields, colField, valuesField; <keyword arguments>) function developed for this SO question you could do:

julia> df =DataFrame(Location = [ "NY", "SF", "NY", "NY", "SF", "SF", "TX", "TX", "TX", "DC"],
                        Class = ["H","L","H","L","L","H", "H","L","L","M"],
                        Address = ["12 Silver","10 Fak","12 Silver","1 North","10 Fak","2 Fake", "1 Red","1 Dog","2 Fake","1 White"],
                        Score = ["4","5","3","2","1","5","4","3","2","1"])

First question:

julia> df_piv = pivot(df,[:Location],:Class,:Score,ops=length)
julia> [df_piv[isna(df_piv[i]), i] = 0 for i in names(df_piv)] # remove NA values across whole df
julia> df_piv
4×4 DataFrames.DataFrame
│ Row │ Location │ H │ L │ M │
├─────┼──────────┼───┼───┼───┤
│ 1   │ "DC"     │ 0 │ 0 │ 1 │
│ 2   │ "NY"     │ 2 │ 1 │ 0 │
│ 3   │ "SF"     │ 1 │ 2 │ 0 │
│ 4   │ "TX"     │ 1 │ 2 │ 0 │

Second question:

julia> df[:pop]="Pop" # add a dummy column with constant values
julia> pivot(df,[:Location],:pop,:Score,ops=length)
4×2 DataFrames.DataFrame
│ Row │ Location │ Pop │
├─────┼──────────┼─────┤
│ 1   │ "DC"     │ 1   │
│ 2   │ "NY"     │ 3   │
│ 3   │ "SF"     │ 3   │
│ 4   │ "TX"     │ 3   │

Answer 4

Package FreqTable.jl solves this:

>using FreqTables
>show(freqtable(df,:Location,:Class))

4×3 Named Array{Int64,2}
Location ╲ Class │ H  L  M
─────────────────┼────────
DC               │ 0  0  1
NY               │ 2  1  0
SF               │ 1  2  0
TX               │ 1  2  0

Answer 5

(1) Here is my attempt to create a pivot table. I use by() to group by one column and then count the frequency of second column factor in a function.

# Create pivot table from DataFrame.
#  - df : DataFrame object
#  - column1 : Column symbol used for row labels.
#  - column2 : Column symbol used for column labels.
function pivot_table(df, column1, column2)
    # For given DataArray and factor list, create single row DataFrame:
    #  ----------------------------------------
    #  |    factor1    |    factor2    | ...
    #  ----------------------------------------
    #  |freq of factor1|freq of factor1| ...
    #  ----------------------------------------
    function frequency(data, factors)
        # Convert factors to symbols.
        factor_symbols::Vector{Symbol} = map(factor -> symbol(factor), factors)

        # Convert frequency to fit the DataFrame constructor parameter type.
        frequencies::Vector{Any} = map(frequency->[frequency], map(factor -> sum(data .== factor), factors))

        DataFrame(frequencies, factor_symbols)
    end

    factors = sort(unique(df[column2]))
    by(df, column1, x -> frequency(x[column2], factors))
end

Example:

julia> pivot_table(df, :Location, :Class)
4x4 DataFrames.DataFrame
| Row | Location | H | L | M |
|-----|----------|---|---|---|
| 1   | "DC"     | 0 | 0 | 1 |
| 2   | "NY"     | 2 | 1 | 0 |
| 3   | "SF"     | 1 | 2 | 0 |
| 4   | "TX"     | 1 | 2 | 0 |

(2) You can use by and nrow.

julia> by(df, :Location, nrow)
4x2 DataFrames.DataFrame
| Row | Location | x1 |
|-----|----------|----|
| 1   | "DC"     | 1  |
| 2   | "NY"     | 3  |
| 3   | "SF"     | 3  |
| 4   | "TX"     | 3  |