I am struggling to find a code that works for my purpose of calculating the average temperature each week for three cities X, Y and Z combined. I have data from 01.01.2017 to 31.12.2020 on a daily basis, and there are two values for each day, for each city. My data looks like this:
ID | City | Date | Value | Week |
---|---|---|---|---|
1 | X | 01-01-2017 | 1.7 | 2016-52 |
1 | X | 01-01-2017 | 2.3 | 2016-52 |
2 | Y | 01-01-2017 | 3.9 | 2016-52 |
2 | Y | 01-01-2017 | 2.6 | 2016-52 |
3 | Z | 01-01-2017 | 0.9 | 2016-52 |
3 | Z | 01-01-2017 | 1.6. | 2016-52 |
1 | X | 02-01-2017 | 1.9 | 2017-01 |
1 | X | 02-01-2017 | 2.0 | 2017-01 |
2 | Y | 02-01-2017 | 4.9 | 2017-01 |
2 | Y | 02-01-2017 | 3.6 | 2017-01 |
3 | Z | 02-01-2017 | 1.9 | 2017-01 |
3 | Z | 02-01-2017 | 1.8. | 2017-01 |
.. | .. | .......... | ..... | ....... |
1 | X | 31-12-2020 | 0.7 | 2020-53 |
1 | X | 31-12-2020 | 0.3 | 2020-53 |
2 | Y | 31-12-2020 | 0.2 | 2020-53 |
2 | Y | 31-12-2020 | 1.1 | 2020-53 |
3 | Z | 31-12-2020 | 0.9 | 2020-53 |
3 | Z | 31-12-2020 | 0.4 | 2020-53 |
I therefore need to make a code that gives the daily average of both values for each city X, Y and Z. In example, take the average of the daily City X values 1.7 and 2.3 and combining them in a table, for all cities X, Y and Z for each day in the period.
Further, I need to summarize the new daily values (that now are only one observation per city per day), and now calculate the average of all three cities combined on a weekly basis. The reason I need them to be weekly is because they are going to be merged with another dataset that consists of weekly observations later on.
I was thinking about calculating the weekly average temperature for all cities X, Y and Z by making a code that summarize it in groups by the week-variable for the entire dataset, but I am open for other suggestions. The most important thing with the task is to get the weekly average temperature for the cities combined for the entire period.
Would be very helpful if someone could share their thoughts and/or suggestions of code to use for this purpose!
aggregate
e.g.:aggregate(Value ~ Week + ID, YourDataset, mean)
– GKi