Kelsey - 2 days ago 5
R Question

# Summing Values based on Hour and Month and Re-arranging Summed Time Series

I am trying to aggregate (sum) values across months and hours and re-arrange the summed data so that hour and month are on different "axes". I would like the hour to be column headers and the month to be row headers with summed values in each cell. Here's what I mean, through a dummy data example (obviously 12 months are present and 24 hours in the real data):

``````Month <- c(1,1,2,2,3,3,3,4,4,4,5,5,5,5,6,7,8,9,10,11,12)
Hour <- c(4,1,3,2,5,5,1,4,3,6,0,0,2,3,1,2,3,4,5,6,2)
Value <- c(0.1,0.4,0.02,0.1,0.1,0.2,0.02,0.01,0.01,0.02,0.1,0.3,0.2,0.1,0.2, 0.1,0.3,0.1,0.01,0.01,0.1)

z <- data.frame(Month, Hour, Value)
Month Hour Value
1    4  0.10
1    1  0.40
2    3  0.02
2    2  0.10
3    5  0.10
3    5  0.20
``````

My desired output, Hour = column headers (there will be 24 total, this just shows first 6 hours), Month = row headers (there will be 12 total)

``````z

0    1    2    3    4    5    6
1  0.3  0.2  0.1  0.7  0.1  1.1  0.7
2  0.1  0.1  0.8  1.7  0.2  0.1  0.6
3  0.2  0.7  0.1  0.4  2.1  1.3  0.1
4  0.1  0.2  0.2  0.1  3.1  0.1  0.7
5  0.7  0.8  1.2  0.2  0.4  0.1  0.2
6  0.5  0.2  3.0  0.8  0.2  5.1  1.2
7  0.5  0.2  3.0  0.8  0.2  5.1  1.2
8  0.5  0.2  3.0  0.8  0.2  5.1  1.2
9  0.5  0.2  3.0  0.8  0.2  5.1  1.2
10  0.5  0.2  3.0  0.8  0.2  5.1  1.2
11  0.5  0.2  3.0  0.8  0.2  5.1  1.2
12  0.5  0.2  3.0  0.8  0.2  5.1  1.2
``````

We can use `xtabs` to create a contingency table
``````xtabs(Value~Month+Hour)