R aggregating a column values into rows

Question

I am trying to get some help with summarizing a dataframe.

My example df looks like this:

ID           Zoo            Last_date
ABC-DEF     DENVER          Oct_2017
ABC-DEF     DENVER          Oct_2017
ABC-DEF     DENVER          Nov_2017  
ABC-DEF     DENVER          Nov_2017  
ABC-DEF     DENVER          Nov_2017  
HG-IJK      MEMPHIS         Jan_2018
JK-LMO      MEMPHIS         Feb_2018

The result should be

ID          ZOO          Oct_2017  Nov_2017 Jan_2019  Feb_2018
ABC-DEF    DENVER          2         3         0           0
HG-IJK      MEMPHIS        0         0         1           0
JK-LMO      MEMPHIS        0         0         0           1

I tried looking at reshape2 using the melt and cast functions but I couldn't achieve the desired result. I melted the data into long form

tempMelt <- melt(temp, id.vars = 1:2, na.rm = FALSE)

and cast using

casttemp <- dcast(aqm, "ID" + "Zoo" ~ value)

Check group_by and summarise of the dplyr package – M.Punt Jun 19 '18 at 19:28 — M.Punt, Jun 19 '18 at 19:28

score 2 · Accepted Answer · answered Jun 19 '18 at 19:28

library(reshape2) # or you could use data.table's dcast function
dcast(df, ID + Zoo ~ Last_date)

#        ID     Zoo Feb_2018 Jan_2018 Nov_2017 Oct_2017
# 1 ABC-DEF  DENVER        0        0        3        2
# 2  HG-IJK MEMPHIS        0        1        0        0
# 3  JK-LMO MEMPHIS        1        0        0        0

This gives a warning about not specifying the value var or aggregation function. You can be a little more verbose to avoid the warning

dcast(df, ID + Zoo ~ Last_date, value.var = 'Last_date', length)

Data used

df <- data.table::fread("
ID           Zoo            Last_date
ABC-DEF     DENVER          Oct_2017
ABC-DEF     DENVER          Oct_2017
ABC-DEF     DENVER          Nov_2017  
ABC-DEF     DENVER          Nov_2017  
ABC-DEF     DENVER          Nov_2017  
HG-IJK      MEMPHIS         Jan_2018
JK-LMO      MEMPHIS         Feb_2018
")

R aggregating a column values into rows

1 Answers1