Transpose some columns in lines

I need to transpose repeated columns in rows. Indeed, I have one line by client and in this line there is as much columns as number of sales. I want to transpose the sales informations to have one row for each sales for one client.
If a client has only one sales, I want only one line. If it's not possible it doesn't matter, I will deal with it in an other step with sql in R.

To illustrate I have something like that :
Id/name/idSale1 / saleAmount1 /idSale2 / saleAmount2
42/james/698 /30 /1002 / 100
102/cathy/574 /120 /708 / 40
14/david /450 /70 / NA / NA

And I want something like that
Id/name / idSale / saleAmount /
42/james /698 /30
42/james /1002 / 100
102/cathy /574 /120
102/cathy /708 / 40
14/david /450 /70

Thank you for your time

Here is code that performs the transformation you described. Please note how I provide runnable code, including the starting data. use of dput() and or datapasta package supports this as per the reprex guide.

df1 <- data.frame(
  stringsAsFactors = FALSE,
  Id = c(42, 102, 14),
  name = c("james", "cathy", "david"),
  idSale1 = c(698, 574, 450),
  saleAmount1 = c(30, 120, 70),
  idSale2 = c(1002, 708, NA),
  saleAmount2 = c(100, 40, NA)

pivot_longer(df1 %>% rename(clientname=name),
             cols = contains("ale"),
             names_pattern = "([A-Za-z]*)(\\d)",
             names_to = c("text","num"),
             names_repair = "unique") %>% 
  pivot_wider(id_cols = c("Id","clientname","num"),
              names_from = "text",
              values_from = "value") %>% select(-num) %>% 

I don't understand the line

names_pattern = "([A-Za-z]*)(\\d)"

Its called a "Regular Expression" which is a term you can research easily i think. In this case the first bracket identifies text characters and the second one numbers in text, because this is the pattern of names you have.

This topic was automatically closed 7 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.