Spreadsheet transformation data code help

[patchkek]

Hello,

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

Thanks!!

[JackDavison]

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

Read Excel Files

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

library(tidyverse)

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

df
#> # 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)}

[mikecrobp]

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