Plotting "Historical Averages" in R

Question

I am trying to create a graph of the average hourly power input from a solar PV panel. I need to visually compare the solar power input against a facility's hourly power load, but I'm having trouble figuring out how to create a graph of the daily average solar power.

Year Month Day   Hour AC.Power..W.
1   1997     1   1  01:00            0
2   1997     1   1  02:00            0
3   1997     1   1  03:00            0
4   1997     1   1  04:00            0
5   1997     1   1  05:00            0
6   1997     1   1  06:00            0
7   1997     1   1  07:00            0
8   1997     1   1  08:00          378
9   1997     1   1  09:00         1145
10  1997     1   1  10:00         1822
11  1997     1   1  11:00         1520
12  1997     1   1  12:00         2639
13  1997     1   1  13:00         2465
14  1997     1   1  14:00         1401
15  1997     1   1  15:00         1716
16  1997     1   1  16:00         1949
17  1997     1   1  17:00         1142
18  1997     1   1  18:00          113
19  1997     1   1  19:00            2
20  1997     1   1  20:00            0
21  1997     1   1  21:00            0
22  1997     1   1  22:00            0
23  1997     1   1  23:00            0
24  1997     1   1  24:00            0
25  1997     1   2  01:00            0
26  1997     1   2  02:00            0
27  1997     1   2  03:00            0
28  1997     1   2  04:00            0
29  1997     1   2  05:00            0
30  1997     1   2  06:00            0
31  1997     1   2  07:00            0
32  1997     1   2  08:00          249
33  1997     1   2  09:00          887
34  1997     1   2  10:00         1758
35  1997     1   2  11:00         2171
36  1997     1   2  12:00         2584
37  1997     1   2  13:00         2017
38  1997     1   2  14:00         2435
39  1997     1   2  15:00         2259
40  1997     1   2  16:00         1382
41  1997     1   2  17:00          592
42  1997     1   2  18:00          575
43  1997     1   2  19:00            0
44  1997     1   2  20:00            0
45  1997     1   2  21:00            0
46  1997     1   2  22:00            0
47  1997     1   2  23:00            0
48  1997     1   2  24:00            0
49  1997     1   3  01:00            0
50  1997     1   3  02:00            0
51  1997     1   3  03:00            0
52  1997     1   3  04:00            0
53  1997     1   3  05:00            0
54  1997     1   3  06:00            0
55  1997     1   3  07:00            0
56  1997     1   3  08:00          134
57  1997     1   3  09:00         1114
58  1997     1   3  10:00         1394
59  1997     1   3  11:00         1721
60  1997     1   3  12:00         2301
61  1997     1   3  13:00         2703
62  1997     1   3  14:00         1933
63  1997     1   3  15:00         1781
64  1997     1   3  16:00         1231
65  1997     1   3  17:00          741
66  1997     1   3  18:00          123
67  1997     1   3  19:00            0
68  1997     1   3  20:00            0
69  1997     1   3  21:00            0
70  1997     1   3  22:00            0
71  1997     1   3  23:00            0
72  1997     1   3  24:00            0
73  1997     1   4  01:00            0
74  1997     1   4  02:00            0
75  1997     1   4  03:00            0
76  1997     1   4  04:00            0
77  1997     1   4  05:00            0
78  1997     1   4  06:00            0
79  1997     1   4  07:00            0
80  1997     1   4  08:00           82
81  1997     1   4  09:00         1008
82  1997     1   4  10:00         1611
83  1997     1   4  11:00         2305
84  1997     1   4  12:00         2727
85  1997     1   4  13:00         2224
86  1997     1   4  14:00         2623
87  1997     1   4  15:00         1202
88  1997     1   4  16:00         1555
89  1997     1   4  17:00         1025
90  1997     1   4  18:00          542
91  1997     1   4  19:00            0
92  1997     1   4  20:00            0
93  1997     1   4  21:00            0
94  1997     1   4  22:00            0
95  1997     1   4  23:00            0
96  1997     1   4  24:00            0
97  1997     1   5  01:00            0
98  1997     1   5  02:00            0
99  1997     1   5  03:00            0
100 1997     1   5  04:00            0

This continues, hours 01:00-24:00, every day, for several years. So far all I have is a graph of every single day overlaid on each other, to get a rough idea of the range of values.

solardata <- read.csv("C:/Users/Simon/Documents/R/SolarPowerNakuru.csv")
plot(solardata[6049:6072,5], type="l")


dim(solardata) -> solardatadim
solardatadim[1]

n = solardatadim[1]/24
for (i in 1:n) {
  lines(solardata[((i*24+1):((i+1)*24)),5], type="l")
}

My approach for the "Historical Averages" plot is to isolate every hour one at a time, getting the AC Power values for that hour every day over the years, calculate the average for each hour, and then plot all the averages as a single curve.

I'm having trouble isolating the hours. hours <- subset(solardata, Hour = 12:00, select=AC.Power..W.) doesn't work.

The problem is that I can't use the time values like normal values:

> solardata[4,4]
[1]  04:00
24 Levels:  01:00  02:00 ...  24:00

What does levels mean? I can't get the Hours column to work like a normal column. Can I use chron() to fix this? If so, how?

I'm also wondering if there's a simpler approach to this.

This can be done very conveniently with ggplot2, but showing you how would be easier if you [provided](http://stackoverflow.com/a/5963610/1412059) representative example data (i.e., data with more than one value for the same hour). (Your `Hour` variable is a `factor`.) — Roland, Aug 07 '14 at 12:39
OK. How do I put code in my post without having to indent every line? — Andrew Hutner, Aug 07 '14 at 12:51
in subset you have to use `==`instead than just `=`, indeed `==`is the logical operator while `=`is an assignment operator — BBrill, Aug 07 '14 at 13:00
Roland - I added some more data. I would love to know how to do this in ggplot2 — Andrew Hutner, Aug 07 '14 at 13:30

Roland · Accepted Answer · 2014-08-07T14:11:31.117

2

#parse the hours
DF$numHour <- as.POSIXlt(DF$Hour, format="%H:%M", tz="GMT")$hour

#plot
library(ggplot2)
ggplot(DF, aes(x=numHour, y=AC.Power..W.)) +
  geom_line(aes(colour=paste(Year, Month, Day, sep="-"))) +
  stat_summary(fun.y=mean, geom="line", size=1)

enter image description here

Study ggplot2 tutorials and documentation to customize the plot further.

edited Aug 07 '14 at 14:11

answered Aug 07 '14 at 13:44

Roland

127,288
10
191
288

Carl Witthoft · Answer 2 · 2014-08-07T13:04:44.293

Convert Hour to numerical -- but beware of the infamous factors trap: Convert Hour to a date/time class -- but beware of the infamous factors trap: strptime(as.character(data),"%H:%M") is one way to get a date/time -class value out of your factors.

I think the package lubridate may help you here as well. Then once you've converted, use subset or just the [ operator to select all rows with the desired hour-minute value.

ETA: if you're comfortable working in numeric-land :-), you could just do as.numeric(as.character(data)) , where 12:00 will turn out to be 1407429000 <-- at least today, Aug 7, 2014 :-) . The point is that all your "hours" will be converted in a common manner and you can subset on numeric values.

Plotting "Historical Averages" in R

2 Answers2