1

I asked a similar question to this here:

Previous post

Now, my dataset has expanded a little bit so I want to preserve two columns of data in the long format. Sorry, I couldn't figure out how to extend the answers that were provided to this situation.

> id <- c(1000, 1000, 1000, 1001, 1001, 1001)
> type <- c("A", "B", "B", "C", "C", "A")
> zipcode <- c(14201, 32940, 94105, 22020, 94104, 14201)
> dates <- c("10/5/2019", "10/5/2019", "10/5/2019", "9/17/2020", "9/17/2020", "9/17/2020")
> df <- as.data.frame(cbind(id, type, dates, zipcode))
> df
    id type     dates zipcode
1 1000    A 10/5/2019   14201
2 1000    B 10/5/2019   32940
3 1000    B 10/5/2019   94105
4 1001    C 9/17/2020   22020
5 1001    C 9/17/2020   94104
6 1001    A 9/17/2020   14201

I would like df to look something like this (it doesn't have to be exactly the same): enter image description here

user3390169
  • 1,015
  • 1
  • 11
  • 34

3 Answers3

3

You can try reshape like below

reshape(
  transform(
    df,
    q = ave(1:nrow(df),id,dates,FUN = seq_along)
  ),
  direction = "wide",
  idvar = c("id","dates"),
  timevar = "q"
)

which gives

    id     dates type.1 zipcode.1 type.2 zipcode.2 type.3 zipcode.3
1 1000 10/5/2019      A     14201      B     32940      B     94105
4 1001 9/17/2020      C     22020      C     94104      A     14201
ThomasIsCoding
  • 96,636
  • 9
  • 24
  • 81
3

Using data.table

library(data.table)
dcast(setDT(df), id + dates ~ rowid(id, dates), value.var = c('type', 'zipcode'))
#    id     dates type_1 type_2 type_3 zipcode_1 zipcode_2 zipcode_3
#1: 1000 10/5/2019      A      B      B     14201     32940     94105
#2: 1001 9/17/2020      C      C      A     22020     94104     14201
akrun
  • 874,273
  • 37
  • 540
  • 662
2

A tidyverse approach can be:

library(tidyverse)
#Code
df2 <- df %>% pivot_longer(-c(id,dates)) %>%
  group_by(id,name) %>%
  mutate(name=paste0(name,1:n())) %>%
  pivot_wider(names_from = name,values_from=value)

Output:

# A tibble: 2 x 8
# Groups:   id [2]
  id    dates     type1 zipcode1 type2 zipcode2 type3 zipcode3
  <fct> <fct>     <fct> <fct>    <fct> <fct>    <fct> <fct>   
1 1000  10/5/2019 A     14201    B     32940    B     94105   
2 1001  9/17/2020 C     22020    C     94104    A     14201

Update: In case that data types are troublesome, you can set a common format for all variables.

#Code 2
df2 <- df %>% 
  mutate(across(everything(),~as.character(.))) %>%
  pivot_longer(-c(id,dates)) %>%
  group_by(id,name) %>%
  mutate(name=paste0(name,1:n())) %>%
  pivot_wider(names_from = name,values_from=value)
Duck
  • 39,058
  • 13
  • 42
  • 84
  • how does this solution change if, instead of a zip code, that column is a number? I am getting an error for my columns that have non-factor values in them. – user3390169 Oct 09 '20 at 15:04
  • @user3390169 In that case let me add a possible transformation. It looks like the pivots are not working because of different data types. – Duck Oct 09 '20 at 15:10
  • @user3390169 I have added an update, I hope that can be useful for you! – Duck Oct 09 '20 at 15:18