Spreadsheet transformation data code help


i am looking for code to transform data in a spreadsheet from "BEFORE" view to "AFTER" view.


You can read in your data from Excel using the readxl package.

As you haven't provided a reproducible example, I have mocked up some dummy data. This achieves what you want I believe:


df = tribble(
  ~listing, ~title, ~sku1, ~sku2, ~sku3,
  101, "necklace", "abc123", "abc1234", "abc12345",
  102, "earring", "abc239", "abc293", NA,
  103, "bracelet", "abc398", "abc308", "abc183"

#> # A tibble: 3 x 5
#>   listing title    sku1   sku2    sku3    
#>     <dbl> <chr>    <chr>  <chr>   <chr>   
#> 1     101 necklace abc123 abc1234 abc12345
#> 2     102 earring  abc239 abc293  <NA>    
#> 3     103 bracelet abc398 abc308  abc183

df %>%
  pivot_longer(contains("sku"), values_to = "sku") %>% # make longer
  select(-name) %>% # get rid of the name column
  drop_na(sku) # get rid of missing values
#> # A tibble: 8 x 3
#>   listing title    sku     
#>     <dbl> <chr>    <chr>   
#> 1     101 necklace abc123  
#> 2     101 necklace abc1234 
#> 3     101 necklace abc12345
#> 4     102 earring  abc239  
#> 5     102 earring  abc293  
#> 6     103 bracelet abc398  
#> 7     103 bracelet abc308  
#> 8     103 bracelet abc183

Created on 2022-01-16 by the reprex package (v2.0.1)

Reading into Excel and Writing it back is one part of the issue.
But it sounds like the transformation from wide to long is what you are after
is pivot_longer the answer?

Maybe post a reprex creating the BEFORE so that people can test proposed solutions

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.