0

Its my first time in the forum so apologies for the way i am articulating the question! I use tidyverse in R and I have a tbl_df like this:

Month Price
1 10
1 111
1 19
2 2
2 49
3 80
3 560

I want to convert it so that there are three columns named 1, 2 , 3 for the months and each column lists the prices corresponding to that month. Something like:

1 2 3
10 2 80
111 49 560
19

Any help on how this can be done is much appreciated!

Michael Kalogerakis
  • 1
  • Hi Michael! Welcome to SO! Is your data have a unique id column to identify that the price of month 1, month 2, month 3 is for that one unique id. Without that id it hard to convert them from current format (long) to the column format (wide) as you want. – Sinh Nguyen Jan 30 '21 at 00:53
  • `df %>% group_by(Month) %>% mutate(row = row_number()) %>% pivot_wider(names_from = Month, values_from = Price)` – Ronak Shah Jan 30 '21 at 03:55

1 Answers1

0

This is very awkward, but you can do something like this:

# Creating the data:
month <- c(1,1,1,2,2,3,3)
price <- c(10, 111, 19, 2, 49, 80, 560)

table <- tibble::tibble(month = month, price = price)

# making it wide
vecs <- list() 
for (i in 1:3) { # 3 = number of unique months
  vecs[[i]] <- table$price[which(table$month == i)]
}

max_length = 3 # maximum number of observations for a month
vecs <- lapply(vecs, function(x) c(x, rep(NA, max_length - length(x))))
wide_table <- as.data.frame(do.call(cbind, vecs))
names(wide_table) <- as.character(1:3)

#     1  2   3
# 1  10  2  80
# 2 111 49 560
# 3  19 NA  NA
Jacob Goldsmith
  • 1
  • 2
  • This answer do the job for the question though It is not a scalable. – Sinh Nguyen Jan 30 '21 at 01:37
  • @SinhNguyen sure, like I said it's awkward, though it should be pretty easy to adapt to similar situations. But the problem is unusual and I'm not sure there's a more natural way to do it. – Jacob Goldsmith Jan 30 '21 at 01:45