Groupby multiple columns in R

Question

I am trying to create a table with multiple variable

I used group_by from the dplyr package but it's not giving me what I want.

The example in excel pivot table gives me exactly what I want.

R is not grouping them like excel pivot table instead return per observation.

Here is the code I used to group_by then summarise from dplyr package.

Update_Cc_X2 %>%
  group_by(Merchant )%>%
  summarise(Transaction_count = n(), Face_value = sum(FaceValue))

That Excel pivot table isn't grouped by merchant, it's grouped by whatever the `Row Labels` column is named. That's what you need to put in `group_by()`. — Gregor Thomas, Apr 11 '21 at 22:35
`dplyr` provides the ability to group_by multiple columns - please reference the question I linked. You just need to provide multiple columns into the `group_by(col_1, col_2, col_3)` https://stackoverflow.com/questions/34487641/dplyr-groupby-on-multiple-columns-using-variable-names — Sinh Nguyen, Apr 12 '21 at 02:57
Does this answer your question? [dplyr group by colnames described as vector of strings](https://stackoverflow.com/questions/47912107/dplyr-group-by-colnames-described-as-vector-of-strings) — Sinh Nguyen, Apr 12 '21 at 02:59

score 0 · Answer 1 · answered Apr 11 '21 at 23:08

Since you did not provide a reproducible example, it is difficult to know for sure, but let's say that your data has columns Merchant, TypeOfMerchant and TypeOfTransaction. Then with data.table you can do:

library(data.table)

setDT(Update_Cc_X2) # convert data.frame to data.table

Update_Cc_X2[, .(Transaction_count = .N, Face_value = sum(FaceValue)), by = .(Merchant, TypeOfMerchant, TypeOfTransaction)]

The last line groups and summarizes by the tree variables and creates new summarized variables Transaction_count equal to the number of lines (special .N) and the sum of the variable FaceValue.

Groupby multiple columns in R

1 Answers1