R data frame convert column names to rows based on dates, the column names which has a common string and there non 0 values to 1 row item

Question

I want to convert the R data frame column names to rows by looking at conditions like,

1. If two column names are having partial common name separated by '_' like x_a01, y_a01 convert it to 1 row item with common name as a01 based on date. Ex: x_a01, y_a01 -> a01, x_b01, y_b01 -> b01
2. These converted column names to row values should have non zero values. Ex: x_c01, y_c01 have 0 values in 1st row these should be ignored while converting to row items

The dataframe:

Convert the above dataframe to:

Answer 1

We can use pivot_longer to reshape the data into 'long' format and then with filter remove any rows having both x and y values as 0

library(dplyr)
library(dplyr)
df1 %>%
    pivot_longer(cols = -date, names_to = c(".value", "colname"), 
        names_sep = "_", values_drop_na = TRUE)%>% 
    filter(if_any(c(x, y), ~ . > 0))

-output

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

data

df1 <- structure(list(date = c("01-01-2021", "02-01-2021"), x_a01 = c(1, 
0), y_a01 = c(2, 0), x_b01 = c(0, 3.1), y_b01 = c(4, 1.1), x_c01 = c(0, 
4.5), y_c01 = c(0, 6.2), x_d01 = c(3, 0), y_d01 = c(4, 0)),
class = "data.frame", row.names = c(NA, 
-2L))