Reformat Dataframe to start a new row at a certain column-CodePudding

I have a dataframe that looks like this:

ID x.2019 x.2020 
1  10     20 
2  20     30 
3  30     40 
4  40     50 
5  50     60

and I would like to reformat it to look like this:

ID time  x 
1  2019  10
1  2020  20 
2  2019  20 
2  2020  30
3  2019  40 
3  2020  50
4  2019  60 
4  2020  70
5  2019  70 
5  2020  80

Any idea how to achieve this?

CodePudding user response：

This is a rather simple task which you can probably find in other answers. Though, you can achieve what you want with data.table as follows:

library(data.table)

df = data.table( ID = 1:5, 
                 x.2019 = seq(10, 50, by = 10),
                 x.2020 = seq(20, 60, by = 10)
)

# change column names conveniently
setnames(df, c("x.2019", "x.2020"), c("2019", "2020"))

# transform the dataset from wide to long format
out = melt(df, id.vars = "ID", variable.name = "time", value.name = "x", variable.factor = FALSE)

# cast time to integer
out[ , time := as.integer(time)]

# reorder by ID
setorder(out, ID)

out
#>     ID time  x
#>  1:  1 2019 10
#>  2:  1 2020 20
#>  3:  2 2019 20
#>  4:  2 2020 30
#>  5:  3 2019 30
#>  6:  3 2020 40
#>  7:  4 2019 40
#>  8:  4 2020 50
#>  9:  5 2019 50
#> 10:  5 2020 60

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

CodePudding user response：

You can use pivot_longer:

library(dplyr)
library(tidyr)

df = data.frame(ID=1:5, 
                x.2019=c(10, 20, 30, 40, 50), 
                x.2020=c(20, 30, 40, 50, 60))

df %>% 
  pivot_longer(cols = c(2, 3), names_to = 'time', values_to = 'x') %>%
  mutate(time = as.integer(stringr::str_replace(time, 'x.', '')))

Result:

# A tibble: 10 x 3
      ID  time     x
   <int> <int> <dbl>
 1     1  2019    10
 2     1  2020    20
 3     2  2019    20
 4     2  2020    30
 5     3  2019    30
 6     3  2020    40
 7     4  2019    40
 8     4  2020    50
 9     5  2019    50
10     5  2020    60