Transform a dataframe

Question

M     Product   Price
-------------------------
2014m1  Pepsi   55
2014m1  Coke    60
2014m2  Pepsi   55
2014m2  Coke    62
2014m3  Pepsi   55
2014m3  Coke    63
2014m4  Pepsi   55
2014m5  Pepsi   55
2014m6  Pepsi   55
2014m8  Pepsi   58
2014m9  Pepsi   58
2014m10 Pepsi   58
2014m11 Pepsi   58
2014m12 Pepsi   58

I have some time series with two products Pepsi and Coke.My intention is to transform this table like table below.

M     Product Price
--------------------------
2014m1  Coke    60
2014m2  Coke    62
2014m3  Coke    63
2014m4  Coke    NA
2014m5  Coke    NA
2014m6  Coke    NA
2014m7  Coke    NA
2014m8  Coke    NA
2014m9  Coke    NA
2014m10 Coke    NA
2014m11 Coke    NA
2014m12 Coke    NA
2014m1  Pepsi   55
2014m2  Pepsi   55
2014m3  Pepsi   55
2014m4  Pepsi   55
2014m5  Pepsi   55
2014m6  Pepsi   55
2014m7  Pepsi   58
2014m8  Pepsi   58
2014m9  Pepsi   58
2014m10 Pepsi   58
2014m11 Pepsi   58
2014m12 Pepsi   58

Namely in this table,every product have appropriate month and price.So can anybody help me to transform this table ?

Your original data.frame doesn't have a value for Pepsi for 2014m7. Is this a typo? — kath, Sep 07 '18 at 06:27
Sorry this was mistake for Pepsi 2014m7.This observation have value.You can see in second table. — j235, Sep 07 '18 at 06:34

score 2 · Answer 1 · answered Sep 07 '18 at 06:17

You can use complete from tidyr for this. First turn M into a factor with all levels you want to have in your data and then use complete to fill the Product.

my_df %>% 
  mutate(M = factor(M, levels = paste0(2014, "m", 1:12))) %>%
  complete(M, Product)

# A tibble: 24 x 3
#    M      Product Price
#    <fct>  <chr>   <int>
#  1 2014m1 Coke       60
#  2 2014m1 Pepsi      55
#  3 2014m2 Coke       62
#  4 2014m2 Pepsi      55
#  5 2014m3 Coke       63
#  6 2014m3 Pepsi      55
#  7 2014m4 Coke       NA
#  8 2014m4 Pepsi      55
#  9 2014m5 Coke       NA
# 10 2014m5 Pepsi      55
# ... with 14 more rows

Data

my_df <- structure(list(M = c("2014m1", "2014m1", "2014m2", "2014m2", "2014m3", "2014m3", 
                     "2014m4", "2014m5", "2014m6", "2014m8", "2014m9", "2014m10", 
                     "2014m11", "2014m12"), 
               Product = c("Pepsi", "Coke", "Pepsi", "Coke", "Pepsi", "Coke", 
                           "Pepsi", "Pepsi", "Pepsi", "Pepsi", "Pepsi", "Pepsi",
                           "Pepsi", "Pepsi"), 
               Price = c(55L, 60L, 55L, 62L, 55L, 63L, 55L, 55L, 55L, 58L, 58L, 
                         58L, 58L, 58L)), 
          class = "data.frame", row.names = c(NA, -14L))

score 1 · Answer 2 · answered Sep 07 '18 at 05:57

One way we could do is create a new data frame with all the possible combinations and then merge them with the original dataframe

new_df <- data.frame(M = paste0(2014, "m", seq(12)), 
         Product = rep(unique(df$Product), each = 12))

merge(new_df, df, all.x = TRUE)


#         M  Product Price
#1   2014m1    Coke    60
#2   2014m1   Pepsi    55
#3   2014m10   Coke    NA
#4   2014m10  Pepsi    58
#5   2014m11   Coke    NA
#6   2014m11  Pepsi    58
#7   2014m12   Coke    NA
#8   2014m12  Pepsi    58
#9   2014m2    Coke    62
#10  2014m2   Pepsi    55
......

Here df is your original dataframe.

score 1 · Accepted Answer · answered Sep 07 '18 at 07:20

Here is a more flexible solution via tidyr::expand. You do not have to specify the number of rows to add (in your case 12) since we take care of that with sub.

library(tidyverse)

my_df %>% 
 mutate(val = max(as.integer(sub('.*m', '', M)))) %>% 
 group_by(Product) %>% 
 expand(M = paste0('2014m', seq(val[1]))) %>% 
 left_join(., my_df)

which gives,

# A tibble: 24 x 3
# Groups:   Product [?]
   Product M       Price
   <chr>   <chr>   <int>
 1 Coke    2014m1     60
 2 Coke    2014m10    NA
 3 Coke    2014m11    NA
 4 Coke    2014m12    NA
 5 Coke    2014m2     62
 6 Coke    2014m3     63
 7 Coke    2014m4     NA
 8 Coke    2014m5     NA
 9 Coke    2014m6     NA
10 Coke    2014m7     NA
# ... with 14 more rows

Thank you this really works! I have some additional to ask.How to expand this code to work to different years (e.g 2015,2016,2017) not only for 2014? — j235, Sep 07 '18 at 09:45

Transform a dataframe

3 Answers3