R - Cast dataframe on unique rows - reshape2

Question

I have a dataframe that shows election results by constituency and party. I need to find the party with the most votes for each constituency.

My df looks like this

#    gss        party         votes     
1    W07000049  Labour        22662     
2    W07000049  Conservative  5901     
3    W07000049  LibDem        941           
3    W07000058  Labour        5951
3    W07000058  LibDem        1741
3    W07000058  Conservative  852

I would like to Cast it so the unique party names become my column names, like this

#    gss        Labour   Conservative  LibDem
1    W07000049  22662    5901          941
2    W07000058  5951     1741          941

On this dataframe I could then use which.max like so

 x$win <- colnames(df)[apply(df, 1, function(x) which.max(x)[1])]

I've tried using dcast from reshape2 http://seananderson.ca/2013/10/19/reshape.html but am unable to apply it. How can I find the winning party of each constituency?

P.S. I'm a beginner so please let me know if I can explain this better

In base R, you could use `reshape`: `reshape(df, direction="wide", idvar="gss", timevar="party")` or with `xtabs`: `xtabs(votes ~ gss + party, df)`. — lmo, Oct 20 '17 at 12:19

score 1 · Accepted Answer · answered Oct 20 '17 at 11:10

Here is a reshape2::dcast solution:

dcast(df, df[, 2] ~ df[, 3])

# Output
# 1 W07000049         5901  22662    941
# 2 W07000058          852   5951   1741

This is assuming the following structure of df

str(df)
#'data.frame':  6 obs. of  4 variables:
# $ V1: int  1 2 3 3 3 3
# $ V2: Factor w/ 2 levels "W07000049","W07000058": 1 1 1 2 2 2
# $ V3: Factor w/ 3 levels "Conservative",..: 2 1 3 2 3 1
# $ V4: int  22662 5901 941 5951 1741 852

That works nicely, thank you very much! – user3821345 Oct 20 '17 at 11:18 — user3821345, Oct 20 '17 at 11:18

score 0 · Answer 2 · answered Oct 20 '17 at 11:18

Another reshape2::dcast solution.

library(reshape2)

molten <- melt(df)
dcast(molten, gss ~ party, id.vars = "gss", value.var = "value")
#        gss Conservative Labour LibDem
#1 W07000049         5901  22662    941
#2 W07000058          852   5951   1741

Note that the first step is necessary but you can skip creating the intermediate data frame molten and simply do the one-liner dcast(melt(...)...).

DATA.

df <-
structure(list(gss = structure(c(1L, 1L, 1L, 2L, 2L, 2L), .Label = c("W07000049", 
"W07000058"), class = "factor"), party = structure(c(2L, 1L, 
3L, 2L, 3L, 1L), .Label = c("Conservative", "Labour", "LibDem"
), class = "factor"), votes = c(22662L, 5901L, 941L, 5951L, 1741L, 
852L)), .Names = c("gss", "party", "votes"), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))

R - Cast dataframe on unique rows - reshape2

2 Answers2