I have a dataset like this (yes, with double titles, as these are mandatory from my BI tool):
library(readxl)
df = read_excel("df.xlsx")
View(df)
Firm November November December December etc etc
Firm On-time Pieces On-time Pieces etc etc
A 37% 60 50% 10
B 60% 50 10% 55
Which I would much prefer in following format:
Firm Month On-time Pieces
A December 50% 10
A November 37% 60
Etc
etc
I tried:
df %>%
gather(month, ot, -firm) %>%
filter(firm != "firm") %>%
arrange(firm)
But it did not give me what I wanted. Above function gave me on-time and pieces in one column, mixed up.
I couldn't find this anywhere, but if you find it, please comment and I will of course delete the post.
Thanks in advance
Assuming you can reduce your double row of headings to a single row so that you have a dataframe of the following form...
df
Firm Nov-OnTime Nov-Pieces Dec-OnTime Dec-Pieces
1 A 37% 60 50% 10
2 B 60% 50 10% 55
then you can use a gather - separate - spread sequence to produce what you want...
library (tidyr)
df2 <- df %>% gather(key=key, value=value, -Firm) %>% #gather all columns except Firm
separate(key, into=c("Month", "Type"), remove=TRUE) %>% #split into month and type
spread(key=Type, value=value) #spread by Type (keeping month)
df2
Firm Month OnTime Pieces
1 A Dec 50% 10
2 A Nov 37% 60
3 B Dec 10% 55
4 B Nov 60% 50