R: Pivot numeric data from columns to rows based on string in variable name

user_15530658 0
Viewed 423

I have a data set that I want to pivot to long format depending on if the variable name contains any of the strings: list_a <- c("a", "b", "c") and list_b <- c("usd", "eur", "gbp"). The data set only contains values in one row. I want the values in list_b to become column names and the values in list_a to become row names in the resulting dataset. Please see the reproducable example data set below.

I currently solve this issue by applying the following R code (once for each value in list_b) resulting in three data frames called "df_usd", "df_eur" and "df_gbp" which I then merge based on the column "name". This is however a bit cumbersome and I would very much appreciate if you could help me with finding a more elegant solution since the variables in list_b change from month to month (list_a stays the same each month) and updating the existing code manually is both time consuming and opens up for manual error.

# Current solution for df_usd:

df_usd <- df %>%
  select(date, contains("usd")) %>% 
  pivot_longer(cols = contains(c("a_", "b_", "c_")),
   names_to = "name", values_to = "usd") %>% mutate(name = case_when(
    str_detect(name, "a_") ~ "a",
    str_detect(name, "b_") ~ "b",
    str_detect(name, "c_") ~ "c")) %>% 
  select(-date)

A screenshot of the starting point in Excel A screenshot of the starting point in Excel

A screenshot of the result I want to acheive in Excel A screenshot of the result I want to acheive in Excel

# Example data to copy and paste into R for easy reproduction of problem:

df <- data.frame (date  = c("2020-12-31"),
                  a_usd  = c(1000),
                  b_usd = c(2000),
                  c_usd = c(3000),
                  a_eur = c(100),
                  b_eur =c(200),
                  c_eur = c(300),
                  a_gbp = c(10),
                  b_gbp = c(20),
                  c_gbp = c(30))
pivot r function
2 Answers

It would be to specify names_sep with names_to in pivot_longer

library(dplyr)
df %>%
   pivot_longer(cols = -date, names_to = c("grp", ".value"), names_sep = "_")

-output

# A tibble: 3 x 5
#  date       grp     usd   eur   gbp
#  <chr>      <chr> <dbl> <dbl> <dbl>
#1 2020-12-31 a      1000   100    10
#2 2020-12-31 b      2000   200    20
#3 2020-12-31 c      3000   300    30
user_37322710

A base R option using reshape

reshape(
  setNames(df, gsub("(\\w+)_(\\w+)", "\\2.\\1", names(df))),
  direction = "long",
  varying = -1
)

gives

          date time  usd eur gbp id
1.a 2020-12-31    a 1000 100  10  1
1.b 2020-12-31    b 2000 200  20  1
1.c 2020-12-31    c 3000 300  30  1
user_121587570
Related