How to add variables, make the it as equally dataframe

September 14, 2023

There is dataframe raw_data_2 , I want to add variables e_2022 b_2023,the result as dataframe raw_data_2_fixed .How to add them by easy code ? (In actual, i have to add so many variables, type in one by one will NOT be good choice).

library(tidyverse)
raw_data_2 <- data.frame(category=c('A','B','C'),
                         
                       a_2022=c(1,2,3),
                       b_2022=c(0.1,0.4,0.3),
                       
                       a_2023=c(1.5,3,4),
                       e_2023=c(0.3,0.7,0.5)
                       
                       )

The wished dataframe as below (added variable e_2022 b_2023)

raw_data_2_fixed <- data.frame(category=c('A','B','C'),
                               
                               a_2022=c(1,2,3),
                               b_2022=c(0.1,0.4,0.3),
                               **e_2022=c(NA,NA,NA),**
                               
                               a_2023=c(1.5,3,4),
                               *b_2023=c(NA,NA,NA),*
                               e_2023=c(0.3,0.7,0.5)
                               )

>Solution :

You can pivot, separate, complete and pivot back:

library(tidyr)

raw_data_2 |> 
  pivot_longer(-category) |> 
  separate(name, into = c("prefix", "year")) |> 
  complete(category, prefix, year) |> 
  pivot_wider(names_from = c("prefix", "year"))

output

# A tibble: 3 × 7
  category a_2022 a_2023 b_2022 b_2023 e_2022 e_2023
  <chr>     <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1 A             1    1.5    0.1     NA     NA    0.3
2 B             2    3      0.4     NA     NA    0.7
3 C             3    4      0.3     NA     NA    0.5