Creating a new column based on two columns in a data frame

Question

I have a reproducible df where there are repeat samplings from an individual. For each sample I track the Order that the samples were taken and the Media used (A or B).

dat <- data.frame(IndID = factor(c(1,1,2,2,2,3,3)),
          Order = c(1,2,1,2,3,1,2),
          Media = factor(c("A", "B", "B","A","B","A", "A")))
dat
  IndID Order Media
1     1     1     A
2     1     2     B
3     2     1     B
4     2     2     A
5     2     3     B
6     3     1     A
7     3     2     A

I want to make a new column that contains the Media and a number that signifies the sampling Order within each level of Media. Said differently, grouping by IndID and Media, I want to make a new column that orders the samples. For each individual, if there are only two samples in two different Media (like IndID 1), the new value would be "A1" and "B1". If there are two samples from the same Media, the new values need to be "B1" and "B2" in an order that follows the sampling Order.

Given the above data, I am trying to create the following column

dat$WantThis <- c("A1", "B1","B1", "A1","B2", "A1", "A2")

  IndID Order Media WantThis
1     1     1     A       A1
2     1     2     B       B1
3     2     1     B       B1
4     2     2     A       A1
5     2     3     B       B2
6     3     1     A       A1
7     3     2     A       A2

I have been trying to use the dplyr package but cannot connect the dots on what should be included as the 2nd argument to paste.

dat2 <- as.data.frame(dat %>% group_by(IndID, Media) %>% mutate(MediaOrder = paste0(Media, ???? )))

Thanks in advance. I welcome any suggestions.

`dat %>% group_by(IndID, Media) %>% mutate(WantThis = paste0(Media, row_number()))` probably — David Arenburg, May 03 '16 at 15:13
`dat2 <- dat %>% group_by(IndID, Media) %>% mutate(WantThis = paste0(Media, row_number()))` generates `Error in rank(x, ties.method = "first", na.last = "keep") : argument "x" is missing, with no default` — B. Davis, May 03 '16 at 15:26
Which `dplyr` version you have? It works fine foe me. You could try `mutate(WantThis = paste0(Media, 1:n()))` instead maybe — David Arenburg, May 03 '16 at 15:34
I updated `dplyr` and it works fine. Thanks. Post as answer? — B. Davis, May 03 '16 at 15:41
It was already posted by some dude. And it's a dupe anyway, so can't post anymore — David Arenburg, May 03 '16 at 15:42

score 2 · Accepted Answer · answered May 03 '16 at 15:34

If you use the row number of the grouping it should get you what you want.

library(dplyr)

dat <- data.frame(IndID = factor(c(1,1,2,2,2,3,3)),
                  Order = c(1,2,1,2,3,1,2),
                  Media = factor(c("A", "B", "B","A","B","A", "A")))

res <- dat %>%
  group_by(IndID, Media) %>%
  mutate(count = row_number(),
         WantThis = paste(Media,count,sep=""))

#Source: local data frame [7 x 5]
#Groups: IndID, Media [5]
#
#   IndID Order  Media count WantThis
#  (fctr) (dbl) (fctr) (int)    (chr)
#1      1     1      A     1       A1
#2      1     2      B     1       B1
#3      2     1      B     1       B1
#4      2     2      A     1       A1
#5      2     3      B     2       B2
#6      3     1      A     1       A1
#7      3     2      A     2       A2

Creating a new column based on two columns in a data frame

1 Answers1