How do I make a table tidy?

Question

I have the following table, where I've been logging my daily activity. It each column represents one hour of the day, and the numbers correspond to an activity.

I want to bring this into R for analysis but I think what would be more useful is a 'tidy' format, where I could have the hours in a single column and the relevant activity in a column alongside that. Something like so:

| Date      | Hour  | Activity |
|-----------|-------|----------|
| 1/12/2021 | 00:00 | 23       |
| 1/12/2021 | 01:00 | 23       |

I've tried pivoting the table in Excel but that doesn't give me the required result.

How do I go about this?

dput result:

> dput(data)
structure(list(Day = c("01-01-2021", "01-02-2021", "01-03-2021", 
"01-04-2021", "01-05-2021", "01-06-2021", "01-07-2021", "01-08-2021", 
"01-09-2021", "01-10-2021", "01-11-2021", "01-12-2021", "01-13-2021", 
"01-14-2021", "01-15-2021"), X00.00 = c(1L, 1L, 1L, 7L, 1L, 23L, 
1L, 1L, 1L, 1L, 1L, 17L, 1L, 1L, 1L), X01.00 = c(1L, 1L, 1L, 
1L, 1L, 4L, 1L, 1L, 1L, 1L, 1L, 17L, 1L, 1L, 1L), X02.00 = c(1L, 
1L, 1L, 1L, 1L, 4L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), X03.00 = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), X04.00 = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), X05.00 = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), X06.00 = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), X07.00 = c(1L, 
10L, 1L, 15L, 15L, 15L, 15L, 1L, 1L, 1L, 1L, 1L, 1L, 19L, 1L), 
    X08.00 = c(23, 23, 1, 15, 15, 15, 15, 7.5, 22, 15, 1, 2, 
    1, 19, 2), X09.00 = c(2, 10, 23, 15, 15, 23, 14, 7.5, 22, 
    15, 5, 10, 5, 19, 19), X10.00 = c(23, 23, 23, 23, 23, 23, 
    14, 7.5, 7.5, 15, 5, 10, 5, 19, 5), X11.00 = c(23, 23, 23, 
    23, 23, 23, 14, 7.5, 7.5, 15, 16, 5, 5, 19, 5), X12.00 = c(23, 
    23, 23, 23, 23, 23, 14, 7.5, 7.5, 15, 16, 5, 5, 15, 2), X13.00 = c(23, 
    23, 23, 23, 23, 23, 14, 7.5, 7.5, 2, 16, 5, 2, 2, 5), X14.00 = c(12, 
    23, 23, 23, 23, 8, 11, 22, 7.5, 15, 1, 5, 5, 1, 5), X15.00 = c(12L, 
    12L, 12L, 23L, 23L, 8L, 11L, 22L, 16L, 16L, 1L, 15L, 5L, 
    1L, 5L), X16.00 = c(15, 12, 4, 7.7, 23, 8, 11, 22, 12, 19, 
    1, 15, 22, 1, 5), X17.00 = c(15, 23, 4, 7.7, 15, 8, 11, 22, 
    12, 19, 1, 23, 22, 7.5, 16), X18.00 = c(23, 23, 15, 7.7, 
    15, 15, 15, 22, 15, 19, 15, 23, 22, 7.5, 16), X19.00 = c(23, 
    23, 15, 7.7, 23, 2, 15, 7.5, 15, 19, 17, 23, 22, 7.5, 7.5
    ), X20.00 = c(23, 23, 15, 2, 17.1, 15, 15, 7.5, 2, 2, 17, 
    23, 2, 2, 2), X21.00 = c(2, 23, 2, 15, 17.1, 15, 15, 7.5, 
    2, 15, 12, 23, 17, 23, 23), X22.00 = c(23, 23, 2, 15, 23, 
    23, 15, 7.5, 7.5, 15, 12, 23, 17, 23, 23), X23.00 = c(23, 
    7, 23, 15, 23, 1, 1, 1, 7.5, 15, 17, 16, 17, 1, 23)), row.names = c(NA, 
15L), class = "data.frame", na.action = structure(16:41, .Names = c("16", 
"17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", 
"28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", 
"39", "40", "41"), class = "omit"))

score 0 · Accepted Answer · answered Jan 16 '21 at 06:27

You can use the pivot_longer() function from tidyverse.

Given that your dataframe is called data, you would do something like this:


data <- data %>% pivot_longer(
  !Day, #Every Column Except Day Will Be Pivoted
  names_to = "Hour", #What do we call the new column?
  values_to = "Activity" #Where will the activities go?
  
)

How do I make a table tidy?

1 Answers1