How to reshape dataframe to turn Columns into Rows using R?-CodePudding

Sorry in case the title is unclear I'm not 100% how to phrase my problem. I have a Dataframe that looks something like this:

date	Location A Variable 1	Location A Variable 2	Location B Variable 1	Location B Variable 2
Jan 20	2	2	2	2
Feb 20	2	2	2	2
...	...	2	2	2
Dec 20	2	2	2	2

and I would like it to look like this:

date	Location	Variable 1	Variable 2	Variable...
Jan 20	A	2	2	.
Jan 20	B	2	2	.
Feb 20	A	2	2	.
Feb 20	B	2	2	.
...	...	2	2	.
Dec 20	A	2	2	.
Dec 20	B	2	2	.

CodePudding user response：

If we have spaces in the column name

library(tidyr)
pivot_longer(df1, cols = -date, names_to = c("Location", ".value"), 
     names_pattern = "\\w \\s (\\w )\\s (.*)")

-output

# A tibble: 4 × 4
  date   Location `Variable 1` `Variable 2`
  <chr>  <chr>           <int>        <int>
1 Jan 20 A                   2            2
2 Jan 20 B                   2            2
3 Feb 20 A                   2            2
4 Feb 20 B                   2            2

data

df1 <- structure(list(date = c("Jan 20", "Feb 20"), `Location A Variable 1` = c(2L, 
2L), `Location A Variable 2` = c(2L, 2L), `Location B Variable 1` = c(2L, 
2L), `Location B Variable 2` = c(2L, 2L)), class = "data.frame", row.names = c(NA, 
-2L))

CodePudding user response：

Another way could be first with renaming:

library(dplyr) 
library(tidyr) 
library(stringr) 

df1  %>% 
  rename_with(., ~str_replace_all(., 'Variable', '_Variable')) %>% 
  pivot_longer(
    cols = -date,
    names_to = c("Location", ".value"), 
    names_sep = '_') %>% 
  mutate(Location = str_squish(str_remove(Location, "Location")))

 date   Location `Variable 1` `Variable 2`
  <chr>  <chr>           <int>        <int>
1 Jan 20 A                   2            2
2 Jan 20 B                   2            2
3 Feb 20 A                   2            2
4 Feb 20 B                   2            2