CODEWITHSUNDEEP
rmeltspread
AustinMiller
AustinMiller

Reputation: 1

How to change a R table from long to wide with dates as well?

I have a dataframe of purchasing events and ages of the individual. 

Name     Item     Date
PersonA   Apple    1/1/14
PersonA   Banana   1/1/13
PersonA   Pear     1/1/12
PersonB   Orange   1/1/15
PersonC   Kiwi     1/1/17
PersonC   Grapes   1/1/12
PersonD   Lemon    1/1/16

I want to rearrange it long to wide, but while keeping the dates for each of the purchasing events i.e

Name     Item.x    Date.x     Item.y     Date.y     Item.z     Item.z
PersonA   Pear      1/1/12     Banana     1/1/13     Apple      1/1/14
PersonB   Orange    1/1/15     NA         NA         NA         NA       
PersonC   Grapes    1/1/12     Kiwi       1/1/17     NA         NA  
PersonD   Lemon     1/1/16

I see lots of questions of generic long to wide, but not sure how to pull it into a wide format when two columns are involved(the item plus the date).

Upvotes: 0

Views: 90

Answers (2)

Onyambu
Onyambu

Reputation: 79318

in base R you could do:

df2 <- transform(df1, time = ave(Item, Name, FUN = function(x)c("x", "y", "z")[seq(x)]))

reshape(df2, idvar = "Name", dir = "wide")

     Name Item.x Date.x Item.y Date.y Item.z Date.z
1 PersonA  Apple 1/1/14 Banana 1/1/13   Pear 1/1/12
4 PersonB Orange 1/1/15   <NA>   <NA>   <NA>   <NA>
5 PersonC   Kiwi 1/1/17 Grapes 1/1/12   <NA>   <NA>
7 PersonD  Lemon 1/1/16   <NA>   <NA>   <NA>   <NA>

if you do not care about x,y,z you could do:

reshape(transform(df1,time = ave(Item,Name,FUN = seq_along)),idvar = "Name",dir="wide")

     Name Item.1 Date.1 Item.2 Date.2 Item.3 Date.3
1 PersonA  Apple 1/1/14 Banana 1/1/13   Pear 1/1/12
4 PersonB Orange 1/1/15   <NA>   <NA>   <NA>   <NA>
5 PersonC   Kiwi 1/1/17 Grapes 1/1/12   <NA>   <NA>
7 PersonD  Lemon 1/1/16   <NA>   <NA>   <NA>   <NA>

Upvotes: 0

akrun
akrun

Reputation: 887691

We can use pivot_wider

library(dplyr)
library(tidyr)
library(lubridate)
df1 %>%
    arrange(Name, mdy(Date)) %>%
    group_by(Name) %>% 
    mutate(rn = c('x', 'y', 'z')[row_number()]) %>% 
    ungroup %>%
    pivot_wider(names_from = rn, values_from = c(Item, Date), names_sep=".")%>% 
    select(Name, ends_with('x'), ends_with('y'), ends_with('z'))
# A tibble: 4 x 7
#  Name    Item.x Date.x Item.y Date.y Item.z Date.z
#  <chr>   <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
#1 PersonA Pear   1/1/12 Banana 1/1/13 Apple  1/1/14
#2 PersonB Orange 1/1/15 <NA>   <NA>   <NA>   <NA>  
#3 PersonC Grapes 1/1/12 Kiwi   1/1/17 <NA>   <NA>  
#4 PersonD Lemon  1/1/16 <NA>   <NA>   <NA>   <NA>

data

df1 <- structure(list(Name = c("PersonA", "PersonA", "PersonA", "PersonB", 
"PersonC", "PersonC", "PersonD"), Item = c("Apple", "Banana", 
"Pear", "Orange", "Kiwi", "Grapes", "Lemon"), Date = c("1/1/14", 
"1/1/13", "1/1/12", "1/1/15", "1/1/17", "1/1/12", "1/1/16")), 
class = "data.frame", row.names = c(NA, 
-7L))

Upvotes: 1

Related Questions