CODEWITHSUNDEEP
rdataframemergedplyr
Pryore
Pryore

Reputation: 520

R - Combining rows and renaming values

I'm trying to merge rows while recoding the values in the following data frame:

     Days    Object   Frequency
1    1       Fruit    20
2    2       Fruit    21
3    3       Fruit    41
4    4       Fruit    12
5    5       Fruit    1   
6    6       Fruit    9
8    8       Fruit    1
9    9       Fruit    14

Essentially, I'd like to group the days into categorical variables like this:

    Days    Object    Frequency
1   1-2     Fruit     41
2   3-4     Fruit     43
3   5+      Fruit     25

Is there any way to merge while creating new values for the Days column?

Apologies if this is a silly question

Upvotes: 1

Views: 960

Answers (2)

lmo
lmo

Reputation: 38500

In base R, you can combine cut and aggregate. Here, cut produces the day groups, and provides labels to those groups. This is fed in a list with Object to aggregate to perform the full grouping. aggregate takes Frequency as its first argument and applies sum.

aggregate(dat$Frequency, list(Days=cut(dat$Days, c(-Inf, 2, 4, Inf),
                                       labels=c("1-2", "2-4", "5+")),
                              object=dat$Object),
          sum)

this returns

  Days object  x
1  1-2  Fruit 41
2  2-4  Fruit 53
3   5+  Fruit 25

to rename the x variable, you could wrap this in setNames, or just use names<- in a second line.

The data.table equivalent to this is

library(data.table)
setDT(dat)[, sum(Frequency),
           by=list(Days=cut(dat$Days, c(-Inf, 2, 4, Inf), labels=c("1-2", "2-4", "5+")),
                   object=dat$Object)]
   Days object V1
1:  1-2  Fruit 41
2:  2-4  Fruit 53
3:   5+  Fruit 25

Upvotes: 3

akuiper
akuiper

Reputation: 214957

You can create the group variable in group_by dynamically, and then do summarize (assume you'd like to group by Object as well):

df %>% 
    group_by(Days = if_else(Days %in% c(1,2), "1-2", if_else(Days %in% c(3,4), "3-4", "5+")), 
             Object) %>% 
    summarise(Frequency = sum(Frequency))

# A tibble: 3 x 3
# Groups:   Days [?]
#   Days Object Frequency
#  <chr> <fctr>     <int>
#1   1-2  Fruit        41
#2   3-4  Fruit        53
#3    5+  Fruit        25

Upvotes: 3

Related Questions