reshape data into multiple columns using pivot_longer

Question

I am using pivot_longer to reshape my data from wide to long format into multiple value columns. I know there are related questions (Pivot_longer 6 columns to 3 columns or Tidy dataset with pivot_longer: Multiple columns into two columns), but I could not find a solution so far, probably because my two columns will be of different class, the first one being POSIXct and the second one is numeric.

Here is a minimal working example:

    structure(list(compid = c("AT9130162999", "AT9090003478", "AT9070005375", 
"AT9130048156"), iso2c = c("AT", "AT", "AT", "AT"), nace4 = c("7010", 
"4211", "2452", "7010"), lastyear = c("2018", "2019", "2019", 
"2019"), `Closing date
                          Last avail. yr` = structure(c(1546214400, 
1577750400, 1585612800, 1577750400), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 1` = structure(c(1514678400, 
1546214400, 1553990400, 1546214400), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 2` = structure(c(NA, 
1514678400, 1522454400, 1514678400), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 3` = structure(c(NA, 
1483142400, 1490918400, 1483142400), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 4` = structure(c(NA, 
1451520000, 1459382400, 1451520000), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 5` = structure(c(NA, 
1419984000, 1427760000, 1419984000), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 6` = structure(c(NA, 
1388448000, 1396224000, 1388448000), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 7` = structure(c(NA, 
1356912000, 1364688000, 1356912000), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 8` = structure(c(NA, 
1325289600, 1333152000, 1325289600), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), `Closing date
                          Year - 9` = structure(c(NA, 
1293753600, 1301529600, 1293753600), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), operatinginc_last = c(NA, 482813, -94300, NA), operatinginc_year1 = c(NA, 
423482, 780400, NA), operatinginc_year2 = c(NA, 404694, 1210300, 
NA), ebit_last = c(1060000, 482813, -94300, 351292), ebit_year1 = c(1501000, 
423482, 780400, 331415), ebit_year2 = c(NA, 404694, 1210300, 
305492), operatingrev_last = c(28463000, 15842418, 13009700, 
11742884), operatingrev_year1 = c(NA, 13734462, 13146300, 10682889
), operatingrev_year2 = c(NA, 13734462, 13146300, 10682889)), row.names = c(NA, 
-4L), class = c("tbl_df", "tbl", "data.frame"))

So far, I have tried this:

df_l <- df %>%  
pivot_longer(., cols = -(starts_with(c("compid","iso2c","nace4","lastyear","Closing"))), 
               values_to = "value", values_drop_na=T, names_sep = "_", names_to = c("variable","year"))

But now I would also like to reshape all the columns that start with Closing. How do I do (preferably in one step with pivot_longer)?

The expected output should then include a variable, year and value column, but also a closingdate and date column:

 compid    iso2c   nace4   lastyear   `closingdate             ~ `date              ~`variable      ~`year       ~ `value
   <chr>  <chr> <chr> <chr>    <dttm>              <dttm>              <dttm>              <dttm>             
 1 AT913~ AT    7010  2018    `Closing date Last avail. yr` 2018-12-31 ebit  last            28463000                 
 2 AT913~ AT    7010  2018    `Closing date Year - 1`       2017-12-31 ebit  year1           15362687  
 2 AT913~ AT    7010  2018    `Closing date Year - 1`       2016-12-31 ebit  year2           404694

score 0 · Answer 1 · answered Apr 23 '21 at 09:14

I have no clue how you would do that in one call to pivot_longer, because you have different variables with different schemes. And you ALSO want to pivot to longer the closing date variable. So here it is in two calls with some cleaning of the closing variable.

library(tidyverse)

df_l <-   pivot_longer(df, cols = starts_with("Closing"), 
                       values_to = "date", values_drop_na=T,  names_to = c("closing")) %>%
  pivot_longer(., cols = contains("_"), 
               values_to = "value", values_drop_na=T, names_sep = '_', names_to = c("variable",'year')) %>%
  mutate(closing = str_remove_all(closing,'Closing date') %>% 
           str_remove_all(.,'[:cntrl:]') %>%
           str_squish() %>%
           str_trim())

reshape data into multiple columns using pivot_longer

1 Answers1