CODEWITHSUNDEEP
rdplyr
user3206440
user3206440

Reputation: 5069

Add column across rows condtionally

with df like below

df <- data.frame(
  name = rep(c("A", "B", "C"),2),
  type = c("10", "10", "10","20", "20", "20"),
  val = c(1,2,3,4,5,6)
)
> df
  name type val
1    A   10   1
2    B   10   2
3    C   10   3
4    A   20   4
5    B   20   5
6    C   20   6
>

the expected output is

I need to add val of all records with name C to val of records with name A for the corresponding type with a new name AC. Need an output keeping name C and without it.

output1

  name type val
1    A   10   1
2    B   10   2
3    C   10   3
4    AC  10   4
5    A   20   4
6    B   20   5
7    C   20   6
8    AC  20   10

output2

  name type val
1    AC  10   4
2    B   10   2
4    AC  20   10
5    B   20   5
>

prefer dplyr based solution

Upvotes: 0

Views: 49

Answers (3)

akrun
akrun

Reputation: 887961

Here is one option using data.table

library(data.table)
rbindlist(list(df, setDT(df)[, .(name = "AC", val = sum(val[as.character(name) %chin% 
     c("A", "C")])) , .(type)][, names(df), with = FALSE]))[order(type, name)]
#    name type val
#1:    A   10   1
#2:    B   10   2
#3:    C   10   3
#4:   AC   10   4
#5:    A   20   4
#6:    B   20   5
#7:    C   20   6
#8:   AC   20  10

Or with dplyr

library(dplyr)
df %>% 
   filter(name %in% c("A", "C")) %>% 
   group_by(type) %>% 
   summarise(name = 'AC', val = sum(val)) %>% 
   full_join(df, ., on = 'type') %>% 
   arrange(type, val)
#    name type val
#1    A   10   1
#2    B   10   2
#3    C   10   3
#4   AC   10   4
#5    A   20   4
#6    B   20   5
#7    C   20   6
#8   AC   20  10

Upvotes: 1

Sotos
Sotos

Reputation: 51612

Here is one way,

library(dplyr)

df %>% 
  mutate(new = as.integer(name %in% c('A', 'C'))) %>% 
  group_by(type, new) %>% 
  summarise(name = paste0(name, collapse = ''), val = sum(val)) %>% 
  ungroup() %>% 
  select(-new)

# A tibble: 4 × 3
#    type  name   val
#  <fctr> <chr> <dbl>
#1     10     B     2
#2     10    AC     4
#3     20     B     5
#4     20    AC    10

To get the other output then,

df %>% 
  mutate(new = as.integer(name %in% c('A', 'C'))) %>% 
  group_by(type, new) %>% 
  summarise(name = paste0(name, collapse = ''), val = sum(val)) %>%
  ungroup() %>% 
  select(-new) %>% 
  filter(nchar(name) > 1) %>% 
  bind_rows( df) %>% 
  arrange(val)

# A tibble: 8 × 3
#    type  name   val
#  <fctr> <chr> <dbl>
#1     10     A     1
#2     10     B     2
#3     10     C     3
#4     10    AC     4
#5     20     A     4
#6     20     B     5
#7     20     C     6
#8     20    AC    10

Upvotes: 3

Andrew Gustar
Andrew Gustar

Reputation: 18435

Here is another (requires tidyr as well as dplyr) 

df1 <- df %>% group_by(type) %>% 
              summarise(AC=sum(val[name %in% c("A","C")]),B=val[name=="B"]) %>% 
              gather(key=name,value=val,-type) %>% 
              arrange(type)

Upvotes: 1

Related Questions