CODEWITHSUNDEEP
r
user3390169
user3390169

Reputation: 1045

R: How to reshape to wide based on multiple columns

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

Upvotes: 1

Views: 63

Answers (3)

Duck
Duck

Reputation: 39595

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)

Upvotes: 2

akrun
akrun

Reputation: 887118

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

Upvotes: 2

ThomasIsCoding
ThomasIsCoding

Reputation: 101343

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

Upvotes: 2

Related Questions