help with reshaping dataframe in R

Im working with a dataframe like this:

df <- data.frame(name = c("Hello", "Hello"),
                code = c("He", "He"),
                sum = c("Sum_AG", "Sum_FZ"),
                "2017" = c(222, 2),
                "2018" = c(333, 3),
                "2019" = c(444, 4),
                name.1 = c("Hoi", "Hoi"),
                code.1 = c("Ho", "Ho"),
                value.1 = c("Sum_AG", "Sum_FZ"),
                "2017.1" = c(666, 6),
                "2018.1" = c(777, 7),
                "2019.1" = c(888, 8))

And I want to reshape it into this dataframe:

df2 <- data.frame(name = c("Hello", "Hello","Hello", "Hello","Hello", "Hello", "Hoi", "Hoi", "Hoi", "Hoi", "Hoi", "Hoi"),
                 code = c("He", "He","He", "He","He", "He", "Ho", "Ho", "Ho", "Ho", "Ho", "Ho"),
                 sum = c("Sum_AG", "Sum_FZ", "Sum_AG", "Sum_FZ","Sum_AG", "Sum_FZ", "Sum_AG", "Sum_FZ","Sum_AG", "Sum_FZ","Sum_AG", "Sum_FZ"),
                 value = c(222, 2, 333, 3, 444, 4, 666, 6, 777, 7, 888, 8), 
                 year = c(2017, 2017, 2018, 2018, 2019, 2019,2017, 2017, 2018, 2018, 2019, 2019))

I would do it in steps.

df1 <- df %>%
  rename(sum.1 = value.1)

bind_rows(select(df1, 1:6),
          select(df1, ends_with(".1")) %>%
            rename_with(~str_remove(.x, "\\.1$"))) %>%
  pivot_longer(cols = starts_with("X"),
               names_to="year") %>%
  mutate(year = str_remove(year, "^X"))

The first command is to make sum and value.1 consistent. Then we select the first half and the second half and bind them together:

# select first half
select(df1, 1:6)

# select second half and remove the ".1" at the end (`$`) to have the same column names as the first half
select(df1, ends_with(".1")) %>%
            rename_with(~str_remove(.x, "\\.1$"))

Second step, now that we have a consistent wide data.frame, we can simply pivot it longer:

pivot_longer(cols = starts_with("X"),
               names_to="year")

And for aesthetics, remove that "X" that appeared in the years:

mutate(year = str_remove(year, "^X"))

Thank you!! It works good!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.