Reshape based on repeating rows in first column in R

Question

I just want to reshape the following data set:

Here is the code to generate the following df:

data.frame(Variable = c("Date","Location_1","Location_2","Date","Location_1","Location_2"),

           Monday = c("7/1/20","1","2","7/3/20","1","2"),

           Tuesday = c("7/2/20","5","2","7/4/20","5","2"))

Variable    Monday Tuesday 

Date        7/1/20  7/2/20     

Location_1     1       5                 

Location_2     2       2      

Date         7/3/20  7/4/20  

Location_1     1       5                

Location_2     2       2     

The shape of the data frame should look like: 

Date      Location_1 Location_2

7/1/20        1          2          

7/2/20        5          2

7/3/20        1          2

7/4/20        5          2          

Kindly help...

Answer 1

You can get the data in the long format, create an identifier column and back to wide format.

library(dplyr)

library(tidyr)

df %>%

  pivot_longer(cols = -Variable) %>%

  select(-name) %>%

  group_by(Variable) %>%

  mutate(row = row_number()) %>%

  pivot_wider(names_from = Variable, values_from = value) %>%

  select(-row)

#  Date   Location_1 Location_2

#  <chr>  <chr>      <chr>     

#1 7/1/20 1          2         

#2 7/2/20 5          2         

#3 7/3/20 1          2         

#4 7/4/20 5          2         

In data.table using melt + dcast

library(data.table)

dcast(melt(setDT(df), id.vars = 'Variable'), rowid(Variable)~Variable,

      value.var = 'value')

If you are a beginner and want to know about R then do check out the R programming tutorial.