re-shape table from wide to long

[tjcnnl1]

I have a table named wide and want to reshape to long. The data like
wide <- data.frame(
stringsAsFactors = FALSE,
ID = c("10", "12"),
Year = c(2020L, 2020L),
C_9_Rate = c("15.89", "N/A"),
C_9_Footnote = c(NA, "5"),
C_14_Rate = c("0.31", "0.41"),
C_14_Footnote = c(NA, NA)
)

The result table with columns and values like
ID, year, topic, rate, footnote
10 2020 c9 15.85 NA
10 2020 c14 0.31 NA
12 2020 c9 NA 5
12 2020 c14 0.41 NA

How to get it? Thanks in advance.

[GreyMerchant]

Hi,

Please make use of a reprex in future. It is just cleaner to work with. As you can see below I created an example for you on how I would do it. You will have to change your columns slightly more if you want that exact requirement as you specified. Your topic doesn't preexist as that is actually "derived" from the column names as a value that should be there.

A typical wide to long will look like the below:

library(tidyr)
#> Warning: package 'tidyr' was built under R version 4.0.5

wide <- data.frame(
  stringsAsFactors = FALSE,
  ID = c("10", "12"),
  Year = c("2020", "2020"),
  C_9_Rate = c("15.89", "N/A"),
  C_9_Footnote = c(NA, "5"),
  C_14_Rate = c("0.31", "0.41"),
  C_14_Footnote = c(NA, NA)
)


wide %>% 
  tidyr::pivot_longer(!ID:Year, names_to = "value", values_to = "sum")
#> # A tibble: 8 x 4
#>   ID    Year  value         sum  
#>   <chr> <chr> <chr>         <chr>
#> 1 10    2020  C_9_Rate      15.89
#> 2 10    2020  C_9_Footnote  <NA> 
#> 3 10    2020  C_14_Rate     0.31 
#> 4 10    2020  C_14_Footnote <NA> 
#> 5 12    2020  C_9_Rate      N/A  
#> 6 12    2020  C_9_Footnote  5    
#> 7 12    2020  C_14_Rate     0.41 
#> 8 12    2020  C_14_Footnote <NA>

The data in the above format is probably more usable as well than what you asked for.

^{Created on 2022-01-31 by the reprex package (v2.0.0)}

[FJCC]

I would do this.

wide <- data.frame(
   stringsAsFactors = FALSE,
   ID = c("10", "12"),
   Year = c(2020L, 2020L),
   C_9_Rate = c("15.89", "N/A"),
   C_9_Footnote = c(NA, "5"),
   C_14_Rate = c("0.31", "0.41"),
   C_14_Footnote = c(NA, NA)
 )
library(tidyr)
pivot_longer(wide, cols=3:6, 
              names_pattern = "(C_\\d+)_(Rate|Footnote)",
              names_to = c("topic",".value"))
# A tibble: 4 x 5
  ID     Year topic Rate  Footnote
  <chr> <int> <chr> <chr> <chr>   
1 10     2020 C_9   15.89 NA      
2 10     2020 C_14  0.31  NA      
3 12     2020 C_9   N/A   5       
4 12     2020 C_14  0.41  NA  

[tjcnnl1]

@FJCC

wide <- data.frame(
   stringsAsFactors = FALSE,
   ID = c("10", "12"),
   Year = c(2020L, 2020L),
   C_9_Rate = c("15.89", "N/A"),
   C_9_Footnote = c(NA, "5"),
   C_14_Rate = c("0.31", "0.41"),
   C_14_Footnote = c(NA, NA)
 )
library(tidyr)
pivot_longer(wide, cols=3:6, 
              names_pattern = "(C_\\d+)_(Rate|Footnote)",
              names_to = c("topic",".value"))
# A tibble: 4 x 5
  ID     Year topic Rate  Footnote
  <chr> <int> <chr> <chr> <chr>   
1 10     2020 C_9   15.89 NA      
2 10     2020 C_14  0.31  NA      
3 12     2020 C_9   N/A   5       
4 12     2020 C_14  0.41  NA  

Hi FJCC, it works exactly as what I expected. Thank you