Wide a dataframe and insert missing columns

Viewed 202

EDIT: more data was added with dput() format.

I have the following products dataframe, with the column having the pattern promo, marca, descripción, cantidad, precio. Sometimes the promo is missing.

I need the dataframe in a wider form (like with pivot_wider), but where doesn't find a column, insert a NA value.

 # A tibble: 18 x 2
   text                                              column     
   <chr>                                             <chr>      
 1 2 X$39.990Ahorro:$14.990                          promo      
 2 Pampers                                           marca      
 3 Pañales Desechables Premium Care XXG 112 Unidades descripción
 4 112 Un                                            cantidad   
 5 $27.490                                           precio     
 6 2 X$39.990Ahorro:$14.990                          promo      
 7 Pampers                                           marca      
 8 Pañales Desechables Premium Care XG 112 Unidades  descripción
 9 112 Un                                            cantidad   
10 $27.490                                           precio     
11 Babysec                                           marca      
12 Toalla Húmeda Premium X 140/6                     descripción
13 1 Un                                              cantidad   
14 $2.590                                            precio     
15 Emuwipes                                          marca      
16 Toallitas Húmedas Premium 85 Un c/u Bolsa         descripción
17 2 Un                                              cantidad   
18 $2.650                                            precio    

This is the output from df %>% mutate(id = row_number()) %>% pivot_wider(...). Is there a way to shrink this output?

# A tibble: 18 x 6
      id promo               marca    descripción                            cantidad precio
   <int> <chr>               <chr>    <chr>                                  <chr>    <chr> 
 1     1 2 X$39.990Ahorro:$~ NA       NA                                     NA       NA    
 2     2 NA                  Pampers  NA                                     NA       NA    
 3     3 NA                  NA       Pañales Desechables Premium Care XXG ~ NA       NA    
 4     4 NA                  NA       NA                                     112 Un   NA    
 5     5 NA                  NA       NA                                     NA       $27.4~
 6     6 2 X$39.990Ahorro:$~ NA       NA                                     NA       NA    
 7     7 NA                  Pampers  NA                                     NA       NA    
 8     8 NA                  NA       Pañales Desechables Premium Care XG 1~ NA       NA    
 9     9 NA                  NA       NA                                     112 Un   NA    
10    10 NA                  NA       NA                                     NA       $27.4~
11    11 NA                  Babysec  NA                                     NA       NA    
12    12 NA                  NA       Toalla Húmeda Premium X 140/6          NA       NA    
13    13 NA                  NA       NA                                     1 Un     NA    
14    14 NA                  NA       NA                                     NA       $2.590
15    15 NA                  Emuwipes NA                                     NA       NA    
16    16 NA                  NA       Toallitas Húmedas Premium 85 Un c/u B~ NA       NA    
17    17 NA                  NA       NA                                     2 Un     NA    
18    18 NA                  NA       NA                                     NA       $2.650

The data:

text = c("2 X$39.990Ahorro:$14.990", "Pampers", 
"Pañales Desechables Premium Care XXG 112 Unidades", "112 Un", 
"$27.490", "2 X$39.990Ahorro:$14.990", "Pampers", "Pañales Desechables Premium Care XG 112 Unidades", 
"112 Un", "$27.490", "Babysec", "Toalla Húmeda Premium X 140/6", 
"1 Un", "$2.590", "Emuwipes", "Toallitas Húmedas Premium 85 Un c/u Bolsa", 
"2 Un", "$2.650", "Parent's Choice", "Toallitas Húmedas Ultra Soft con Aceite de Emu 160 Un", 
"160 Un", "$2.550", "Emuwipes", "Toallitas Húmedas sin Alcohol (2 Bolsas de 80 Un c/u) Bolsa 2 Un", 
"$1.990", "3 X$45.990Ahorro:$13.980", "Babysec", "Pañal Super Premium XXG68", 
"1 Un", "$19.990", "Parent's Choice", "Toallitas Húmedas Ultra Soft con Aceite de Emu y sin Aroma (2 Un de 80 Un c/u)", 
"160 Un", "$2.550", "2 X$39.990Ahorro:$14.990", "Pampers", "Pañales Desechables Premium Care G 124 Unidades", 
"124 Un", "$27.490", "Huggies")

column = c("promo", "marca", 
"descripción", "cantidad", "precio", "promo", "marca", "descripción", 
"cantidad", "precio", "marca", "descripción", "cantidad", "precio", 
"marca", "descripción", "cantidad", "precio", "marca", "descripción", 
"cantidad", "precio", "marca", "descripción", "precio", "promo", 
"marca", "descripción", "cantidad", "precio", "marca", "descripción", 
"cantidad", "precio", "promo", "marca", "descripción", "cantidad", 
"precio", "marca")
3 Answers

If there is a precio for every item, we could use the code below, which count how many precio entries have occurred in the prior rows. lag(column, default = "") is used to avoid having "NA" for the first item, which would break the cumulative count.

df %>%
  mutate(row = cumsum(lag(column, default = "") == "precio")) %>% 
  pivot_wider(names_from = column, values_from = text)


# A tibble: 4 x 6
    row promo                    marca    descripción                                       cantidad precio 
  <int> <chr>                    <chr>    <chr>                                             <chr>    <chr>  
1     0 2 X$39.990Ahorro:$14.990 Pampers  Pañales Desechables Premium Care XXG 112 Unidades 112 Un   $27.490
2     1 2 X$39.990Ahorro:$14.990 Pampers  Pañales Desechables Premium Care XG 112 Unidades  112 Un   $27.490
3     2 NA                       Babysec  Toalla Húmeda Premium X 140/6                     1 Un     $2.590 
4     3 NA                       Emuwipes Toallitas Húmedas Premium 85 Un c/u Bolsa         2 Un     $2.650 

EDIT: Alternate approach using marca, but where promo is assigned to the next grouping (since it is assumed to always precede marca whenever it appears). Same output for the sample data.

df %>%
  mutate(row = cumsum(column == "marca") + if_else(column == "promo", 1, 0)) %>%
  pivot_wider(names_from = column, values_from = text)

Source data

df <- data.frame(
  stringsAsFactors = FALSE,
                    text = c("2 X$39.990Ahorro:$14.990","Pampers",
                             "Pañales Desechables Premium Care XXG 112 Unidades","112 Un","$27.490",
                             "2 X$39.990Ahorro:$14.990","Pampers",
                             "Pañales Desechables Premium Care XG 112 Unidades","112 Un","$27.490",
                             "Babysec","Toalla Húmeda Premium X 140/6",
                             "1 Un","$2.590","Emuwipes",
                             "Toallitas Húmedas Premium 85 Un c/u Bolsa","2 Un","$2.650"),
                  column = c("promo","marca",
                             "descripción","cantidad","precio","promo",
                             "marca","descripción","cantidad","precio","marca",
                             "descripción","cantidad","precio","marca",
                             "descripción","cantidad","precio")
      )

Using dcast from data.table. Convert the 'data.frame' to 'data.table' (setDT), create a formula with cumulative sum (based on taking the lag of 'column' (shift), check if the values are equal to 'precio', get the cumulative sum (cumsum)) with the 'column', and specify the value.var as 'text' in dcast to reshape from 'long' to 'wide' format

library(data.table)
 dcast(setDT(df), cumsum(shift(column, fill = "") == "precio" )~ column, value.var = 'text')

Update: to concerns raised by OP and others with new provided data and with the knowledge gained here: Recognize a given pattern in a vector and add the lacking elements to get the repitition of the given pattern

library(tidyverse)
df1 %>% 
    group_by(column) %>% 
    mutate(row = row_number()) %>%
    pivot_wider(
        names_from = column,
        values_from = text
    )

Gives:

     row promo                    marca           descripción                                                                    cantidad precio 
   <int> <chr>                    <chr>           <chr>                                                                          <chr>    <chr>  
 1     1 2 X$39.990Ahorro:$14.990 Pampers         Pañales Desechables Premium Care XXG 112 Unidades                              112 Un   $27.490
 2     2 2 X$39.990Ahorro:$14.990 Pampers         Pañales Desechables Premium Care XG 112 Unidades                               112 Un   $27.490
 3     3 3 X$45.990Ahorro:$13.980 Babysec         Toalla Húmeda Premium X 140/6                                                  1 Un     $2.590 
 4     4 2 X$39.990Ahorro:$14.990 Emuwipes        Toallitas Húmedas Premium 85 Un c/u Bolsa                                      2 Un     $2.650 
 5     5 NA                       Parent's Choice Toallitas Húmedas Ultra Soft con Aceite de Emu 160 Un                          160 Un   $2.550 
 6     6 NA                       Emuwipes        Toallitas Húmedas sin Alcohol (2 Bolsas de 80 Un c/u) Bolsa 2 Un               1 Un     $1.990 
 7     7 NA                       Babysec         Pañal Super Premium XXG68                                                      160 Un   $19.990
 8     8 NA                       Parent's Choice Toallitas Húmedas Ultra Soft con Aceite de Emu y sin Aroma (2 Un de 80 Un c/u) 124 Un   $2.550 
 9     9 NA                       Pampers         Pañales Desechables Premium Care G 124 Unidades                                NA       $27.490
10    10 NA                       Huggies         NA                                                                             NA       NA     

First answer:

We could create a unique identifier row for each column and then use pivot_wider

library(tidyverse)
df %>% 
    group_by(column) %>% 
    mutate(row = row_number()) %>%
    pivot_wider(
        names_from = column,
        values_from = text
    ) 
    row promo                    marca    descripción                                       cantidad precio 
  <int> <chr>                    <chr>    <chr>                                             <chr>    <chr>  
1     1 2 X$39.990Ahorro:$14.990 Pampers  Pañales Desechables Premium Care XXG 112 Unidades 112 Un   $27.490
2     2 2 X$39.990Ahorro:$14.990 Pampers  Pañales Desechables Premium Care XG 112 Unidades  112 Un   $27.490
3     3 NA                       Babysec  Toalla Húmeda Premium X 140/6                     1 Un     $2.590 
4     4 NA                       Emuwipes Toallitas Húmedas Premium 85 Un c/u Bolsa         2 Un     $2.650
Related