Rearrangement columns of a table in R-CodePudding

I have the following table that I want to modify

Debt2017  Debt2018  Debt2019  Cash2017   Cash2018   Cash2019  Year  Other
    2         4         3         5          6          7     2018    x
    3         8         9         7          9          9     2017    y

So that the result is the following

  Debt    Cash     FLAG After   Other
   2       5          0           x
   3       7          1           x
   8       9          1           y
   9       9          1           y|

Basically, I want to change the data so that I have the different years in different rows, eliminating the values for the year indicated in the column "Year" and adding a FLAG that tells me whether the data indicated in the row is from a previous (0) or following (1) year (with respect to the year indicated in the column "Year"). Furthermore, I also want to keep the column "Other".

Does anybody know how to do it in R?

CodePudding user response：

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(Debt2017:Cash2019,
               names_to = c(".value", "Year2"),
               names_pattern = "(\\D )(\\d )") %>%
  filter(Year != Year2) %>%
  mutate(flag =  (Year2 > Year))

# # A tibble: 4 × 6
#    Year Other Year2  Debt  Cash  flag
#   <int> <chr> <chr> <int> <int> <int>
# 1  2018 x     2017      2     5     0
# 2  2018 x     2019      3     7     1
# 3  2017 y     2018      8     9     1
# 4  2017 y     2019      9     9     1

Data

df <- structure(list(Debt2017 = 2:3, Debt2018 = c(4L, 8L), Debt2019 = c(3L, 9L),
Cash2017 = c(5L, 7L), Cash2018 = c(6L, 9L), Cash2019 = c(7L, 9L),
Year = 2018:2017, Other = c("x", "y")), class = "data.frame", row.names = c(NA, -2L))