Column rows concatenation based on other columns of the dataframe

0
votes

I want to concatenate the column row values separated by ',' looking at a column whose row value should not be zero.

Example in steps:

  1. group_by date, column name x whose row values are not zero, concatenate values of column name 'colname' separated by ','
  2. group_by date, column name y whose row values are not zero, concatenate values of column name 'colname' separated by ','
  3. Using both of these concatenated values in step 1 and step 2, concatenate them separated by '/' creating a new column colname1
  4. Expand the final concatenated values in colname1 to all the row items of group_by date

The dataframe:

enter image description here

Output dataframe:

enter image description here

1
rdataframestring-concatenationmutate

1 Answers

0
votes

We group by 'date', subset the 'colname', based on the values of 'x' and 'y' that are not 0, paste (toString), separately and then convert into a single string with str_c

library(dplyr)
library(stringr)
df1 %>%
  group_by(date) %>%
  mutate(colname1 = str_c(toString(colname[x != 0]), 
                           toString(colname[y != 0]), sep = "/")) %>%
  ungroup

-output

# A tibble: 5 x 5
#  date       colname     x     y colname1              
#  <chr>      <chr>   <dbl> <dbl> <chr>                 
#1 01-01-2021 a01       1     2   a01, d01/a01, b01, d01
#2 01-01-2021 b01       0     4   a01, d01/a01, b01, d01
#3 01-01-2021 d01       3     4   a01, d01/a01, b01, d01
#4 02-01-2021 b01       3.1   1.1 b01, c01/b01, c01     
#5 02-01-2021 c01       4.5   6.2 b01, c01/b01, c01

data

df1 <- structure(list(date = c("01-01-2021", "01-01-2021", "01-01-2021", 
"02-01-2021", "02-01-2021"), colname = c("a01", "b01", "d01", 
"b01", "c01"), x = c(1, 0, 3, 3.1, 4.5), y = c(2, 4, 4, 1.1, 
6.2)), class = "data.frame", row.names = c(NA, -5L))