I need to reshape a wide-format dataframe into a long-format.
I am familiar with the melt() function and have used it multiple times.
df <- data.frame( Weekday = c( "Mon", "Tues", "Wed", "Thurs", "Fri" ),
Q1 = c( 9.9 , 4.9 , 8.8 , 12.2 , 12.2 ),
Q2 = c( 5.4 , 9.7 , 11.1 , 10.2 , 8.1 ),
Q3 = c( 8.8 , 7.9 , 10.2 , 9.2 , 7.9 ),
Q4 = c( 6.9 , 5 , 9.3 , 9.7 , 5.6 ) )
df_melt <- melt(df, id.vars=c("Weekday"),
variable.name="Quarter",
value.name="Delay")
The above function will give the following output:
Weekday Quarter Delay
Mon Q1 9.9
Tues Q1 4.9
Wed Q1 8.8
Thurs Q1 12.2
Fri Q1 12.2
Mon Q2 5.4
Tues Q2 9.7
Wed Q2 11.1
... ... ...
However, I would like my long-format to look like this:
Weekday Quarter Delay
Mon Q1 9.9
Mon Q2 5.4
Mon Q3 8.8
Mon Q4 6.9
Tues Q1 4.9
Tues Q2 9.7
... ... ...
Are there functions in R that allow me to do this?
You can match and order the output with a vector of the order you want.
order_vec <- c('Mon', 'Tues', 'Wed', 'Thurs', 'Fri')
df_melt[order(match(df_melt$Weekday, order_vec)), ]
# Weekday Quarter Delay
#1 Mon Q1 9.9
#6 Mon Q2 5.4
#11 Mon Q3 8.8
#16 Mon Q4 6.9
#2 Tues Q1 4.9
#7 Tues Q2 9.7
#...
#...
Instead of melt if you use tidyr::pivot_longer it will give you the order you want directly.
tidyr::pivot_longer(df, cols = -Weekday)
# Weekday name value
# <chr> <chr> <dbl>
# 1 Mon Q1 9.9
# 2 Mon Q2 5.4
# 3 Mon Q3 8.8
# 4 Mon Q4 6.9
# 5 Tues Q1 4.9
# 6 Tues Q2 9.7
# 7 Tues Q3 7.9
#...
#...