In R, pivot duplicate row values into column values

Question

My problem is similar to this one, but I am having trouble making the code work for me: Pivot dataframe to keep column headings and sub-headings in R

My data looks like this:

prod1<-c(1000,2000,1400,1340)
prod2<-c(5000,5400,3400,5400)
partner<-c("World","World","Turkey","Turkey")
year<-c("2017","2018","2017","2018")
type<-c("credit","credit","debit","debit")

s<-as.data.frame(rbind(partner,year,type,prod1,prod2)

But I need to convert all the rows into individual variables so that it my columns are:

column.names<-c("products","partner","year","type","value")

I've been trying the code below:

#fix partners
colnames(s)[seq(2, 7, 1)] <- colnames(s)[2] #seq(start,end,increment)
colnames(s)[seq(9, ncol(s), 1)] <- colnames(s)[8]

colnames(s) <-
  c(s[1, 1], paste(sep = '_', colnames(s)[2:ncol(s)], as.character(unlist(s[1, 2:ncol(s)]))))

test<-s[-1,]
s <- rename(s, category=1)

test<- s %>%
  slice(-1) %>%
  pivot_longer(-1,
               names_to = c("partner", ".value"),
               names_sep = "_") %>%
  arrange(partner, `Service item`) %>%
  mutate(partner = as.character(partner))

But it keeps saying I can't have duplicate column names. Can someone please help? The initial data is submitted in this format so I need to get it in the right shape.

Did you try: s %>% pivot_longer(cols = starts_with("product"), names_to = "product", values_to = "value") — Ammar Gamal, Jun 28 '22 at 15:34
Hi! That could work for those columns, but for all the others I'm pretty stumped. — melange164, Jun 28 '22 at 15:37
Not sure how you want the final shape of the data, but in that case I describes you'd have the col names you mentioned in the question — Ammar Gamal, Jun 28 '22 at 15:41

score 1 · Accepted Answer · answered Jun 28 '22 at 15:59

    s <- rownames_to_column(s)
    s %>% pivot_longer(starts_with("V")) %>% 
      pivot_wider(names_from = rowname,values_from = value) %>%
      select(-name) %>% pivot_longer(starts_with("prod"), names_to = "product",
                                     values_to = "value")

# A tibble: 8 × 5
  partner year  type   product value
  <chr>   <chr> <chr>  <chr>   <chr>
1 World   2017  credit prod1   1000 
2 World   2017  credit prod2   5000 
3 World   2018  credit prod1   2000 
4 World   2018  credit prod2   5400 
5 Turkey  2017  debit  prod1   1400 
6 Turkey  2017  debit  prod2   3400 
7 Turkey  2018  debit  prod1   1340 
8 Turkey  2018  debit  prod2   5400

sorry misread the question at the beginning, is that what you look for ?

I had to fiddle a bit because my original dataset was not exactly the same but this works perfectly thanks!!!! — melange164, Jun 29 '22 at 09:29

In R, pivot duplicate row values into column values

1 Answers1