Reshaping Deduping long data to wide in R

0 votes

I've been attempting unsuccessfully to restructure this lengthy data into a broad format by ID in Excel. I attempted to use dcast, but it did not produce the outcomes I was hoping for.

I've included a csv file with the data's current formatting in cells (a1:c10) and my preferred formatting in cells (f1:n4). I initially tried with Excel, but since I've never used a power query, I figured reshape2 or dcast should be able to accomplish the same thing.

csv screenshot

In R I did:

olddata_wide$ID <- factor(olddata_wide$ID)

widedf <- dcast(df1, ID  ~ paydate, value.var="Type")

This just gave me an output of dates.

Feb 26 in Others by narikkadan
• 63,180 points
137 views

1 answer to this question.

0 votes

Using pivot_wider and rename

library(dplyr)
library(tidyr)

repl <- c("1st_transaction" = "type_1", "2nd_transaction" = "type_2", 
  "3rd_transaction" = "type_3", "4th_transaction" = "type_4")

df %>% 
  mutate(n = row_number(), .by = ID) %>% 
  pivot_wider(names_from = n, values_from = c(type, paydate)) %>% 
  rename(all_of(repl))
# A tibble: 3 × 9
  ID     1st_transacti…¹ 2nd_t…² 3rd_t…³ 4th_t…⁴ payda…⁵ payda…⁶ payda…⁷ payda…⁸
  <chr>  <chr>           <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>  
1 AAL100 H               H       B       NA      8/28/2… 8/28/2… 8/28/2… NA     
2 AAC926 H               H       NA      NA      8/28/2… 8/28/2… NA      NA     
3 ABR765 V               H       H       B       8/17/2… 8/28/2… 8/28/2… 8/28/2…
# … with abbreviated variable names ¹​`1st_transaction`, ²​`2nd_transaction`,
#   ³​`3rd_transaction`, ⁴​`4th_transaction`, ⁵​paydate_1, ⁶​paydate_2, ⁷​paydate_3,
#   ⁸​paydate_4

Data

df <- structure(list(ID = c("AAL100", "AAL100", "AAL100", "AAC926", 
"AAC926", "ABR765", "ABR765", "ABR765", "ABR765"), paydate = c("8/28/2019", 
"8/28/2020", "8/28/2021", "8/28/2017", "8/28/2018", "8/17/2016", 
"8/28/2020", "8/28/2021", "8/28/2022"), type = c("H", "H", "B", 
"H", "H", "V", "H", "H", "B")), class = "data.frame", row.names = c(NA, 
-9L))
answered Mar 17 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to set a column as a range variable as long as there is data in it?

Try this: Dim rng As Range Set rng = ...READ MORE

answered Feb 16 in Others by narikkadan
• 63,180 points
280 views
0 votes
0 answers

How to handle large http response data from observer in Angular application to avoid browser crash?

Suppose we have a angular application which ...READ MORE

Apr 19, 2019 in Others by Hemant Gajbe
2,292 views
0 votes
1 answer

Reshape data from long to wide format

You can use the reshape function reshape(data, idvar ...READ MORE

answered Apr 17, 2018 in Data Analytics by nirvana
• 3,130 points
469 views
0 votes
1 answer

Get list of rownames as values after aggregating a dataframe

You can use reshape2 library: library(reshape2) data <- read.table(text="type ...READ MORE

answered Apr 17, 2018 in Data Analytics by Sahiti
• 6,360 points
360 views
0 votes
1 answer

Reshape data from long to wide format in R

Use reshape function: reshape(dat1, idvar = "name", timevar = ...READ MORE

answered Jun 14, 2018 in Data Analytics by CodingByHeart77
• 3,740 points
2,455 views
0 votes
1 answer

Reshape dataframe without “timevar” from long to wide format in R

Assuming that the data is in the ...READ MORE

answered Jun 14, 2018 in Data Analytics by CodingByHeart77
• 3,740 points
725 views
0 votes
1 answer
0 votes
1 answer

Select data that meet criteria from a table, adding it to a combobox in userform VBA Excel

Fill Combo Box With Matches Sub GetSourceAcc() ...READ MORE

answered Mar 26 in Others by Kithuzzz
• 38,010 points
346 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP