6  resampling

We can only really understand how to calculate monthly means if we do it ourselves.

First, let’s import a bunch of packages we need to use. No need to understand every line of the next cell.

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
from matplotlib.dates import DateFormatter
import matplotlib.dates as mdates
import seaborn as sns
sns.set_theme(style="ticks", font_scale=1.5)  # white graphs, with large and legible letters

Now we load the csv file for Jerusalem (2019), provided by the IMS.

discussion

We will go to the IMS website together and see what are the options available and how to download. If you just need the csv right away, download it here.

Careful: the IMS lets you download the data with the winter clock or with the summer clock (the column name says which one), so take super care to know which one you have, because in the summer months the timestamps are not the same. 
filename = "../archive/data/jerusalem2019.csv"
df = pd.read_csv(filename, na_values=['-'])
df = df.rename(columns={'Temperature (°C)': 'temperature',
                        'Rainfall (mm)': 'rain'})
df['date'] = pd.to_datetime(df['Date & Time (Winter)'], dayfirst=True)
df = df.set_index('date')
df
Station Date & Time (Winter) Diffused radiation (W/m^2) Global radiation (W/m^2) Direct radiation (W/m^2) Relative humidity (%) temperature Maximum temperature (°C) Minimum temperature (°C) Wind direction (°) Gust wind direction (°) Wind speed (m/s) Maximum 1 minute wind speed (m/s) Maximum 10 minutes wind speed (m/s) Time ending maximum 10 minutes wind speed (hhmm) Gust wind speed (m/s) Standard deviation wind direction (°) rain
date
2019-01-01 00:00:00 Jerusalem Givat Ram 01/01/2019 00:00 0.0 0.0 0.0 80.0 8.7 8.8 8.6 75.0 84.0 3.3 4.3 3.5 23:58 6.0 15.6 0.0
2019-01-01 00:10:00 Jerusalem Givat Ram 01/01/2019 00:10 0.0 0.0 0.0 79.0 8.7 8.8 8.7 74.0 82.0 3.3 4.1 3.3 00:01 4.9 14.3 0.0
2019-01-01 00:20:00 Jerusalem Givat Ram 01/01/2019 00:20 0.0 0.0 0.0 79.0 8.7 8.8 8.7 76.0 82.0 3.2 4.1 3.3 00:19 4.9 9.9 0.0
2019-01-01 00:30:00 Jerusalem Givat Ram 01/01/2019 00:30 0.0 0.0 0.0 79.0 8.7 8.7 8.6 78.0 73.0 3.6 4.2 3.6 00:30 5.2 11.7 0.0
2019-01-01 00:40:00 Jerusalem Givat Ram 01/01/2019 00:40 0.0 0.0 0.0 79.0 8.6 8.7 8.5 80.0 74.0 3.6 4.4 3.8 00:35 5.4 10.5 0.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2019-12-31 22:20:00 Jerusalem Givat Ram 31/12/2019 22:20 0.0 0.0 1.0 81.0 7.4 7.6 7.3 222.0 255.0 0.5 0.9 1.0 22:11 1.0 47.9 0.0
2019-12-31 22:30:00 Jerusalem Givat Ram 31/12/2019 22:30 0.0 0.0 1.0 83.0 7.3 7.4 7.3 266.0 259.0 0.6 0.8 0.6 22:28 1.1 22.8 0.0
2019-12-31 22:40:00 Jerusalem Givat Ram 31/12/2019 22:40 0.0 0.0 1.0 83.0 7.5 7.6 7.3 331.0 317.0 0.5 0.8 0.6 22:35 1.0 31.6 0.0
2019-12-31 22:50:00 Jerusalem Givat Ram 31/12/2019 22:50 0.0 0.0 1.0 83.0 7.5 7.6 7.4 312.0 285.0 0.6 1.0 0.6 22:50 1.4 31.3 0.0
2019-12-31 23:00:00 Jerusalem Givat Ram 31/12/2019 23:00 0.0 0.0 1.0 83.0 7.6 7.7 7.4 315.0 321.0 0.7 1.0 0.8 22:54 1.3 23.5 0.0

52554 rows × 18 columns

With resample it’s easy to compute monthly averages. Resample by itself only divides the data into buckets (in this case monthly buckets), and waits for a further instruction. Here, the next instruction is mean.

df_month = df['temperature'].resample('ME').mean()
df_month
date
2019-01-31     9.119937
2019-02-28     9.629812
2019-03-31    10.731571
2019-04-30    14.514329
2019-05-31    22.916894
2019-06-30    23.587361
2019-07-31    24.019403
2019-08-31    24.050822
2019-09-30    22.313287
2019-10-31    20.641868
2019-11-30    17.257153
2019-12-31    11.224131
Freq: ME, Name: temperature, dtype: float64

Notice that each monthly mean is labeled with the last day of the month, because we used ME (month end). With MS (month start) it would be labeled with the first day.

Instead of ME for month, which other options do I have? The full list can be found here, but the most commonly used are:

ME        month end frequency
MS        month start frequency
YE        year end frequency
YS        year start frequency
D         calendar day frequency
h         hourly frequency
min       minutely frequency
s         secondly frequency

The results we got for the monthly means were given as a pandas series, not dataframe. Let’s turn it into a dataframe, since we are about to add more columns to it:

df_month = (df['temperature'].resample('ME')        # resample by month
                             .mean()                # take the mean
                             .to_frame('mean temp') # make output a dataframe
           )
df_month
mean temp
date
2019-01-31 9.119937
2019-02-28 9.629812
2019-03-31 10.731571
2019-04-30 14.514329
2019-05-31 22.916894
2019-06-30 23.587361
2019-07-31 24.019403
2019-08-31 24.050822
2019-09-30 22.313287
2019-10-31 20.641868
2019-11-30 17.257153
2019-12-31 11.224131
hot tip
Sometimes, a line of code can get too long and messy. In the code above, we broke line for every step, which makes the process so much cleaner. We highly advise you to do the same. Attention: This trick works as long as all the elements are inside the same parenthesis.

Now it’s time to plot!

fig, ax = plt.subplots()
ax.plot(df_month['mean temp'], color='black')
ax.set(ylabel='Temperature (°C)',
       yticks=np.arange(5,35,5),
       title="Jerusalem, 2019");

The dates in the horizontal axis are not great. An easy fix is to use the month numbers instead of dates.

fig, ax = plt.subplots()
ax.plot(df_month.index.month, df_month['mean temp'], color='black')
ax.set(xlabel="month",
       ylabel='Temperature (°C)',
       yticks=np.arange(5,35,5),
       title="Jerusalem, 2019");

discussion

When you give plot a pandas series, you don’t need to give it two arguments, date and values. You can just tell plot to use the column you want, the function will take the dates from the index by itself.

What does this line mean?
df_month.index.month

Print on the screen the following, and see yourself what each thing is:

  • df_month
  • df_month.index
  • df_month.index.month
  • df_month.index.day

That’s it for the monthly mean, congratulations :)

Now we need to calculate the average minimum/maximum daily temperatures. We start by creating an empty dataframe.

df_day = pd.DataFrame()

Now resample data by day (D), and take the min/max of each day.

df_day['min temp'] = df['temperature'].resample('D').min()
df_day['max temp'] = df['temperature'].resample('D').max()
df_day

# There is more than one way to do the same thing! The three lines above (and the empty dataframe
# of the previous cell) could be replaced by a single line, with the columns called 'min' and 'max':
# df_day = df['temperature'].resample('D').agg(['min', 'max'])
min temp max temp
date
2019-01-01 7.5 14.1
2019-01-02 6.6 11.5
2019-01-03 6.3 10.7
2019-01-04 6.6 14.6
2019-01-05 7.0 11.4
... ... ...
2019-12-27 4.4 7.4
2019-12-28 6.6 10.3
2019-12-29 8.1 12.5
2019-12-30 6.9 13.0
2019-12-31 5.2 13.3

365 rows × 2 columns

The next step is to calculate the average minimum/maximum for each month. This is similar to what we did above.

df_month['min temp'] = df_day['min temp'].resample('ME').mean()
df_month['max temp'] = df_day['max temp'].resample('ME').mean()
df_month
mean temp min temp max temp
date
2019-01-31 9.119937 5.922581 12.470968
2019-02-28 9.629812 6.825000 13.089286
2019-03-31 10.731571 7.532258 14.661290
2019-04-30 14.514329 10.866667 19.113333
2019-05-31 22.916894 17.296774 29.038710
2019-06-30 23.587361 19.163333 28.860000
2019-07-31 24.019403 19.367742 29.564516
2019-08-31 24.050822 19.903226 29.767742
2019-09-30 22.313287 18.430000 28.456667
2019-10-31 20.641868 16.945161 26.190323
2019-11-30 17.257153 14.066667 21.436667
2019-12-31 11.224131 8.806452 14.448387
discussion

Why did we need two steps? Why not simply resample by month and take the maximum, df['temperature'].resample('ME').max()? Compare the two in the table below, and think about what each number means.

# the hottest moment of each month, compared with the average of the daily maximums
pd.DataFrame({'hottest moment': df['temperature'].resample('ME').max(),
              'mean of the daily max': df_month['max temp']})
hottest moment mean of the daily max
date
2019-01-31 19.1 12.470968
2019-02-28 20.5 13.089286
2019-03-31 21.0 14.661290
2019-04-30 28.6 19.113333
2019-05-31 37.6 29.038710
2019-06-30 34.4 28.860000
2019-07-31 35.1 29.564516
2019-08-31 33.8 29.767742
2019-09-30 33.6 28.456667
2019-10-31 32.7 26.190323
2019-11-30 26.5 21.436667
2019-12-31 18.2 14.448387

Let’s plot…

# a monthly mean describes the whole month, so we draw it in the middle of the month
# (resample labels it with the last day of the month, so we go back 15 days)
x = df_month.index - pd.Timedelta(days=15)

fig, ax = plt.subplots()
ax.plot(x, df_month['max temp'], color='tab:red', label='max')
ax.plot(x, df_month['mean temp'], color='black', label='mean')
ax.plot(x, df_month['min temp'], color='tab:blue', label='min')
ax.set(ylabel='Temperature (°C)',
       yticks=np.arange(10,35,5),
       title="Jerusalem, 2019")
ax.xaxis.set_major_locator(mdates.MonthLocator(range(1, 13, 2), bymonthday=15))
date_form = DateFormatter("%b")
ax.xaxis.set_major_formatter(date_form)
ax.legend(fontsize=12, frameon=False);

Voilà! You made a beautiful graph!

discussion

This time we did not put month numbers in the horizontal axis, we now have month names. How did we do this black magic, you ask? See lines 12–14 above. Matplotlib gives you absolute power over what to put in the axis, if you can only know how to tell it to… Wanna know more? Click here.