CODEWITHSUNDEEP
rdplyrreshapetidyr
Sharath
Sharath

Reputation: 2267

Reshape several hour columns into key and value

I have a dataframe like this 

ID <- c("A","A","A","A","A","A","B","B","B","B","B","B")
Day <- c("Mon","Mon","Mon","Fri","Fri","Fri","Tue","Tue","Tue","Wed","Wed","Wed")
minute <- c(49,32,15,38,18,16,06,16,26,31,33,38)
second <- c(12,22,08,16,21,42,41,48,32,21,26,18)
hour0 <- c(0,0,0,60,0,0,0,0,0,0,0,0)
hour1 <- c(0,100,0,0,0,0,68,0,0,0,0,0)
hour2 <- c(0,0,0,0,0,0,0,92,0,0,0,72)
hour3 <- c(0,0,92,0,62,0,0,0,81,0,0,0)
hour4 <- c(110,0,0,0,0,0,0,0,0,93,0,0)
hour5 <- c(0,0,0,0,0,112,0,0,0,0,0,0)
hour6 <- c(0,0,0,0,0,0,0,0,0,0,105,0)

df <- data.frame(ID,Day,minute,second,hour0,hour1,hour2,hour3,hour4,hour5,hour6,
                 stringsAsFactors=FALSE)

I am trying to convert the several hour columns in 2 columns with the hour and its unit.

My desired output

   ID Day minute second hour unit
1   A Mon     49     12    4  110
2   A Mon     32     22    1  100
3   A Mon     15      8    3   92
4   A Fri     38     16    0   60
5   A Fri     18     21    3   62
6   A Fri     16     42    5  112
7   B Tue      6     41    1   68
8   B Tue     16     48    2   92
9   B Tue     26     32    3   81
10  B Wed     31     21    4   93
11  B Wed     33     26    6  105
12  B Wed     38     18    2   72

I am trying to do it this way but it's not exactly how I want it to be

library(tidyr)
df1 <- gather(df, key = "Hour", value = "Unit" ,
        hour0, hour1, hour2, hour3, hour4, hour5, hour6 )

Can someone point me in the right direction?

Upvotes: 1

Views: 39

Answers (1)

akrun
akrun

Reputation: 887531

Instead of specifying 'hour0', 'hour1', etc, it can be matched with starts_with or matches, then filter out the rows where the 'unit' is 0 and extract the numeric part from 'hour' (parse_number)

library(tidyverse)
df %>% 
  gather(hour, unit, starts_with("hour")) %>% 
  filter(unit != 0) %>% 
  mutate(hour = readr::parse_number(hour)) %>%
  arrange(ID, 
       factor(Day, levels = c("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")))

Upvotes: 1

Related Questions