Combine several row variables

Question

Given data that looks like this:

Year<-c(1,1,1,1,2,2,2,2,3,3,3,3)
Tax<-c('A','B','C','D','A','B','C','D','A','B','C','D')
Count<-c(1,2,1,2,1,2,1,1,1,2,1,1)
Dummy<-data.frame(Year,Tax,Count)

 Dummy
  Year  Tax Count
1   1   A     1
2   1   B     2
3   1   C     1
4   1   D     2
5   2   A     1
6   2   B     2
7   2   C     1
8   2   D     1
9   3   A     1
10  3   B     2
11  3   C     1
12  3   D     1

How would I go about combining some of the "Tax" elements- for instance if I wanted to combine A,B,C into a new variable "ABC". My end result should look like this

Year Tax Count
  1   ABC  4
  1    D   2
  2   ABC  4
  2    D   1
  3   ABC  4
  3    D   1

ABC is called a variable?

Metrics
– Metrics

2013-07-16 00:45:49 +00:00
Commented Jul 16, 2013 at 0:45 — Metrics
– Metrics, Commented Jul 16, 2013 at 0:45

Hong Ooi · Accepted Answer · 2013-07-16 06:34:13Z

3

Another plyr solution. Just redefine your Tax variable and do a normal summary.

ddply(within(Dummy, {
    Tax <- ifelse(Tax %in% c('A','B','C'), 'ABC', 'D')
}), .(Year, Tax), summarise, Count=sum(Count))

If you don't have plyr (or don't like it (!)), this problem is simple enough to handle in base R in a straightforward way.

aggregate(Count ~ Year + Tax, within(Dummy, {
    Tax <- ifelse(Tax %in% c('A','B','C'), 'ABC', 'D')
}), sum)

edited Jul 16, 2013 at 6:34

answered Jul 16, 2013 at 1:49

Hong Ooi

57.9k13 gold badges145 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

agstudy · Accepted Answer · 2013-07-16 01:14:43Z

1

Here an option using ddply

ddply(Dummy,.(Year),summarise,
           Tax=c(Reduce(paste0,head(Tax,-1)),as.character(tail(Tax,1))),
           Count=c(sum(head(Count,-1)),tail(Count,1)))

 Year Tax Count
1    1 ABC     4
2    1   D     2
3    2 ABC     4
4    2   D     1
5    3 ABC     4
6    3   D     1

answered Jul 16, 2013 at 1:14

agstudy

122k18 gold badges205 silver badges265 bronze badges

Comments

Marius · Accepted Answer · 2013-07-16 01:49:19Z

1

Alright, here is a much better solution than my original one. No empty dataframes, no rbinding, but it can still deal with arbitrary groups:

groups_list = list(c("A", "B", "C"), "D")
Dummy$TaxGroup = sapply(Dummy$Tax, function(tax_value) {
  group_search = sapply(groups_list, function(group) tax_value %in% group)
  group_num = which(group_search)
})
combined = ddply(
  Dummy,
  .(Year, TaxGroup),
  summarize,
  GroupName=paste(groups_list[[TaxGroup[1]]], sep="", collapse=""),
  CombinedCount=sum(Count)
)

answered Jul 16, 2013 at 1:49

Marius

60.6k16 gold badges115 silver badges108 bronze badges

Collectives™ on Stack Overflow

Combine several row variables

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related