Reshape by id and keep other variables fixed

Question

What I'm trying to do is reorder the data where the data is in columns, but the rest of the variables are maintained

       c1<- c("ID","Location", "Year","Gender", "MoneySpent", "MoneyWithCreditCard")
         c2<- c(1,"EEUU",2007,"M",1500,400)
         c3<- c(1,"EEUU",2008,"M",3900,0)
         c4<- c(1,"EEUU",2009,"M",0,100)
         c5<- c(2,"Germany",2007,"F",0,1000)
         c6<- c(2,"Germany",2008,"F",4000,500)
         c7<- c(2,"Germany",2009,"F",700,0)
         c8<- c(2,"Germany",2010,"F",0,50)
         Df<-data.frame(rbind(c2,c3,c4,c5,c6,c7,c8))
         colnames(Df)<-c1   

#   ID Location Year Gender MoneySpent   MoneyWithCreditCard   TypeofHome
#c2  1     EEUU 2007      M       1500        400                House
#c3  1     EEUU 2008      M       3900         0                 House
#c4  1     EEUU 2009      M          0        100                House
#c5  2  Germany 2007      F          0        1000               Department
#c6  2  Germany 2008      F       4000        500                Department
#c7  2  Germany 2009      F        700         0                 Department
#c8  2  Germany 2010      F          0         50                Department

The result I need is this one:

# ID Location Gender TypeofHome MS.2007 MS.2008 MS.2009 MS.2010 MWC.2007 MWC.2008 MWC.2009 MWC.2010
# 1   EEUU      M      House     1500    3900      0      NA      400       0        100     NA
# 2   Germany   F      Department 0      4000     700      0     1000      500       0       50

Which one is the better solution? Thanks btw!

Answer 1

This renames the columns in the original data so only a single pivot is needed:

library(dplyr)
library(tidyr)

Df %>%
  rename(MS = MoneySpent, MWC = CreditCard) %>%
  pivot_wider(names_from = c("Year"),
              values_from = c("MS", "MWC"))
# # A tibble: 2 x 11
#   ID    Location Gender MS_2007 MS_2008 MS_2009 MS_2010 MWC_2007 MWC_2008 MWC_2009 MWC_2010
#   <fct> <fct>    <fct>  <fct>   <fct>   <fct>   <fct>   <fct>    <fct>    <fct>    <fct>   
# 1 1     EEUU     M      1500    3900    0       NA      400      0        100      NA      
# 2 2     Germany  F      0       4000    700     0       1000     500      0        50 

Answer 2

Here is an option with pivot_longer and pivot_wider. We first reshape to 'long' format with pivot_longer on the 'Money' columns, change the 'Year' by appending 'MC' or 'MWC' based on the column names, and do a pivot_wider to 'wide' format

library(dplyr)
library(tidyr)
library(stringr)
Df %>% 
    pivot_longer(cols = starts_with("Money")) %>% 
    mutate(Year = case_when(name == "MoneySpent" ~ str_c("MS.", Year),
                            TRUE ~ str_c("MWC.", Year))) %>% 
    select(-name) %>%
    pivot_wider(names_from = Year, values_from = value)
#ID Location Gender TypeofHome MS.2007 MWC.2007 MS.2008 MWC.2008 MS.2009 MWC.2009 MS.2010 MWC.2010
#1  1     EEUU      M      House    1500      400    3900        0       0      100      NA       NA
#2  2  Germany      F Department       0     1000    4000      500     700        0       0       50

---

Or using rename_at

Df %>% 
   rename_at(vars(matches("Money")), ~ str_remove_all(., "[a-z]+")) %>% 
   pivot_wider(names_from = Year, values_from = starts_with("M"))
# ID Location Gender MS_2007 MS_2008 MS_2009 MS_2010 MWCC_2007 MWCC_2008 MWCC_2009 MWCC_2010
#1  1     EEUU      M    1500    3900       0    <NA>       400         0       100      <NA>
#2  2  Germany      F       0    4000     700       0      1000       500         0        50