summarising and aggregating column values as rows in R

Question

My data frame contains mostly catagorical columns and one numerical column, The df looks like this(simplified):

**Home_type**     **Garden_type**       **NaighbourhoOd**    **Rent** 
Vila                big                  brooklyn             5000
Vila                small                bronx                7000
Condo               shared               Sillicon valley      2000 
Appartment          none                 brooklyn             500
Condo               none                 bronx                1700
Appartment          none                 Sillicon Valley      800

For every catagorical column, I want to display all its distinct values, frequency and sum of rent associated with it.

The result should look like this:

**Variable**     **Distinct_values**      **No_of-Occurences**     **SUM_RENT**
  Home_type        Vila                     2                        12000
  Home_type        Condo                    2                        3700
  Home_type        Appartment               2                        1300
  Garden_type      big                      1                        5000
  Garden_type      small                    1                        7000
  Garden_type      shared                   1                        2000 
  Garden_type      none                     3                        3000 
  Naighbourhood    brooklyn                 2                        5500
  Naighbourhood    Bronx                    2                        8700 
  Naighbourhood    Sillicon Valley          2                        2800

I'm new to R and have tried to do this using melt in reshape2 but havent had much success, any help will be much appreciated.

You may want to look at [this overview for asking good R questions](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example), particularly at the portions that give ways to easily read your data in. It is a lot easier to help if we don't have to fight to read your data into R. — Mark Peterson, Nov 04 '16 at 12:39
Thank you for pointing it out to me Mark, I will definitely be more careful in the future and will edit this post later on. — ljourney, Nov 04 '16 at 15:29

score 2 · Accepted Answer · answered Nov 04 '16 at 12:36

I tend to prefer tidyr to reshape2 of late, though that is mostly because the syntax is more similar to dplyr -- which will make this task even easier as well due to loading the magrittr pipe (%>%) and it's data summary tools.

First, we gather (from tidyr) all of the non-Rent columns into long form (run just those two lines to see the result). Then group_by the columns you want to cluster together. Finally, summarise within each group to get the metrics you want.

df %>%
  gather(Variable, Distinct_Values, -Rent) %>%
  group_by(Variable, Distinct_Values) %>%
  summarise(
    `No_of-Occurences` = n()
    , SUM_RENT = sum(Rent)
  )

gives:

        Variable Distinct_Values `No_of-Occurences` SUM_RENT
           <chr>           <chr>              <int>    <int>
1    Garden_type             big                  1     5000
2    Garden_type            none                  3     3000
3    Garden_type          shared                  1     2000
4    Garden_type           small                  1     7000
5      Home_type      Appartment                  2     1300
6      Home_type           Condo                  2     3700
7      Home_type            Vila                  2    12000
8  NaighbourhoOd           bronx                  2     8700
9  NaighbourhoOd        brooklyn                  2     5500
10 NaighbourhoOd Sillicon valley                  1     2000
11 NaighbourhoOd Sillicon Valley                  1      800

(Note, your data has "V" and "v" for "Silicon Valley" causing the two separate lines.)

Works great, thankyou. I wasn't aware about tidyr, will definitely be using more of it. — ljourney, Nov 04 '16 at 15:31

akrun · Answer 2 · 2016-11-04T12:44:26.443

We can use data.table. Convert the 'data.frame' to 'data.table' (setDT(df1)), melt from 'wide' to 'long' format, grouped by 'variable', 'value' (columns created from the melt), we create two columns 'No_of_occur', 'SUM_RENT' as the number of rows (.N) and sum of 'Rent' column, and then grouped by 'variable', 'No_of_occur' and 'SUM_RENT', get the unique elements of 'value' column ('Distinct_values')

library(data.table)
melt(setDT(df1), id.var=c('Rent'))[, c("No_of_occur", "SUM_RENT") :=
      .(.N, sum(Rent)) ,.(variable, value)][,
    .(Distinct_values = unique(value)) , .(variable, No_of_occur, SUM_RENT)]
 #         variable No_of_occur SUM_RENT Distinct_values
 #1:     Home_type           2    12000            Vila
 #2:     Home_type           2     3700           Condo
 #3:     Home_type           2     1300      Appartment
 #4:   Garden_type           1     5000             big
 #5:   Garden_type           1     7000           small
 #6:   Garden_type           1     2000          shared
 #7:   Garden_type           3     3000            none
 #8: NaighbourhoOd           2     5500        brooklyn
 #9: NaighbourhoOd           2     8700           bronx
 #10:NaighbourhoOd           2     2800 Sillicon Valley

summarising and aggregating column values as rows in R

2 Answers2