how can i restructure the data frame in R

Question

I have the following df:

>df
      cluster_no days_to_port             channel
1          1          2.8                 MMR
2          2          4.1 Spark Retail Stores
3          3          2.4 Spark Retail Stores
4          4          3.0 Spark Retail Stores
5          5          3.5 Spark Retail Stores
             channel_outlet_name             region
1 PIN Distributor Ltd (Activata) Auckland - Central
2   Spark - Auckland Int Airport            Mangere
3   Spark - Auckland Int Airport            Mangere
4   Spark - Auckland Int Airport            Mangere
5   Spark - Auckland Int Airport            Mangere

I am trying to reshape/pivot this into the below format so that i could do some further conditional formatting and analysis: (so columns are cluster numbers and rows are the variables)

cluster_no           1    2     3    4   5
days_to_port                    
channel                 
channel_outlet_name                 
region

I could achieve using pivot in excel:

I would like to achieve the same using R.(BTW numeric values in df are mean values and categorical values are mode)

t(df) although this will not maintain the class of the variables — Onyambu, Jan 08 '18 at 08:50

score 1 · Accepted Answer · answered Jan 08 '18 at 20:29

df <- data.frame(cluster=1:5,
                 days_to_port=c(2.8,4.1,2.4,3.0,3.5),
                 channel=c('MMR','Spark Retail Stores','Spark Retail Stores','Spark Retail Stores','Spark Retail Stores'),
                 channel_outlet_name=c('PIN Distributor Ltd (Activata)','Spark - Auckland Int Airport','Spark - Auckland Int Airport','Spark - Auckland Int Airport','Spark - Auckland Int Airport'),
                 region=c('Auckland - Central','Mangere','Mangere','Mangere','Mangere'))

library(tidyr)
out <- df %>% gather(key=key,value=value,-cluster) %>%
  spread(cluster,value)

out
                  key                              1                            2                            3                            4                            5
1             channel                            MMR          Spark Retail Stores          Spark Retail Stores          Spark Retail Stores          Spark Retail Stores
2 channel_outlet_name PIN Distributor Ltd (Activata) Spark - Auckland Int Airport Spark - Auckland Int Airport Spark - Auckland Int Airport Spark - Auckland Int Airport
3        days_to_port                            2.8                          4.1                          2.4                            3                          3.5
4              region             Auckland - Central                      Mangere                      Mangere                      Mangere                      Mangere

Fantastic..This is what i was looking for. – Nishant Jan 09 '18 at 07:05 — Nishant, Jan 09 '18 at 07:05

how can i restructure the data frame in R

1 Answers1