Dates and time series

In short: A time series is data ordered by date; in pandas text becomes a date with to_datetime, resample gathers it by day, week and month, and rolling gives a moving average.

Problem

“How is the year going?”

The shop has the orders for a whole year: from November 2025 to the end of October 2026. The manager asks which month is the strongest, which day of the week sells the most, and how the takings change from month to month.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
print(len(orders))
print(orders.dtypes)
orders.head(3)

import pandas as pd orders = pd.read_csv('year_orders.csv') print(len(orders)) print(orders.dtypes) orders.head(3)

▶ RunRestore the code

There are 11,116 orders. But the ordered_at column is str: to pandas, “2025-11-01 07:20” is only text. You cannot take the month from it:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
orders['ordered_at'].dt.month

import pandas as pd orders = pd.read_csv('year_orders.csv') orders['ordered_at'].dt.month

▶ RunRestore the code

The idea

The text becomes a date

parse_dates tells read_csv which columns to read as dates. pd.to_datetime(orders['ordered_at']) does the same on an existing column, as you saw in the lesson on types:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
print(orders['ordered_at'].dtype)
first = orders['ordered_at'].min()
last = orders['ordered_at'].max()
print(first, last)
print(last - first)

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) print(orders['ordered_at'].dtype) first = orders['ordered_at'].min() last = orders['ordered_at'].max() print(first, last) print(last - first)

▶ RunRestore the code

The type is datetime64: a moment in time, with a date and a time. With it you can compare, subtract and take parts. The difference between the last and the first order is 364 days, 15 hours and 41 minutes. It is a time difference (Timedelta), not a number.

A table in which every row is a moment or a period, and the rows follow time, is called a time series.

The idea

Parts of the date: .dt

.dt gives the parts of every date in the column: year, month, day, hour, dayofweek (0 is Monday), day_name(). When during the day do people order the most?

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
when = orders['ordered_at']
print(when.dt.month.head(3).tolist())
print(when.dt.day_name().head(3).tolist())
when.dt.hour.value_counts().sort_index()

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) when = orders['ordered_at'] print(when.dt.month.head(3).tolist()) print(when.dt.day_name().head(3).tolist()) when.dt.hour.value_counts().sort_index()

▶ RunRestore the code

There are two peaks: at lunchtime (between 12 and 14, about 800 orders an hour) and in the evening (between 19 and 21, about 1,200 an hour). In the morning and late in the evening there are fewer than half as many.

day_name() gives the names in English (“Saturday”). For names in another language you will add your own dictionary, a little further on.

The idea

Filtering by date

A date column can be compared with text like “2025-12-01”: pandas turns it into a date. The orders in December:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
december = orders[(orders['ordered_at'] >= '2025-12-01') & (orders['ordered_at'] < '2026-01-01')]
print(len(december))
short = orders[(orders['ordered_at'] >= '2025-12-01') & (orders['ordered_at'] <= '2025-12-31')]
print(len(short))
print(short['ordered_at'].max())

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) december = orders[(orders['ordered_at'] >= '2025-12-01') & (orders['ordered_at'] < '2026-01-01')] print(len(december)) short = orders[(orders['ordered_at'] >= '2025-12-01') & (orders['ordered_at'] <= '2025-12-31')] print(len(short)) print(short['ordered_at'].max())

▶ RunRestore the code

The right answer is 1285. With <= '2025-12-31' you get 1250, and the last order among them is from 30 December. The reason is the time: “2025-12-31” is 31 December at 00:00, and every order on the 31st comes after midnight.

So for a period you write “from the first day, included, to the first day of the next period, excluded”: >= '2025-12-01' and < '2026-01-01'.

Try it

The week before Christmas

How many orders are there from 22 to 28 December 2025 inclusive? Store those orders in week.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
week = orders[...]
print(len(week))

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) week = orders[...] print(len(week))

Check▶ Run

The idea

By month: resample

For the takings by month you first need the revenue of each order. It is the sum of its lines: groupby on order_id, then merge with the orders. Cancelled orders are not revenue, so sold leaves them out.

Then resample gathers the rows into periods of time. It is a groupby whose groups are periods: days ('D'), weeks ('W'), months ('MS'). It works on an index of dates, so first set_index('ordered_at'):

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
items = pd.read_csv('year_items.csv')
items['revenue'] = items['quantity'] * items['unit_price']
per_order = items.groupby('order_id', as_index=False)['revenue'].sum()
orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one')
# Revenue leaves out the cancelled orders.
sold = orders[orders['status'] != 'cancelled']
monthly = sold.set_index('ordered_at')['revenue'].resample('MS').sum()
monthly.round(2)

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) items = pd.read_csv('year_items.csv') items['revenue'] = items['quantity'] * items['unit_price'] per_order = items.groupby('order_id', as_index=False)['revenue'].sum() orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one') # Revenue leaves out the cancelled orders. sold = orders[orders['status'] != 'cancelled'] monthly = sold.set_index('ordered_at')['revenue'].resample('MS').sum() monthly.round(2)

▶ RunRestore the code

December is the strongest month: €15,915.90. January (€7,228.20) and February (€7,622.30) are weak, as often happens after the holidays. The weakest, though, is September: €7,128.80. Why, we will find out in the project at the end of the course.

More precisely, if you are curious

'MS' (month start) labels each month with its first day, and 'ME' (month end) with its last. The sums are the same.

'W' labels each week with the Sunday it ends on. The first week here is only 1 and 2 November (Saturday and Sunday), so it is much smaller than the others. The first and the last period are often incomplete: check them before you compare them.

If there are no orders on a day, resample('D').sum() still gives the row, with 0. In this data every day has orders.

Try it

The strongest week

Which week brought in the most revenue? Store the revenue by week (without the cancelled orders) in weekly, and the date of the strongest week in best_week.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
items = pd.read_csv('year_items.csv')
items['revenue'] = items['quantity'] * items['unit_price']
per_order = items.groupby('order_id', as_index=False)['revenue'].sum()
orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one')
# Revenue leaves out the cancelled orders.
sold = orders[orders['status'] != 'cancelled']
weekly = ...
best_week = ...
print(best_week)

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) items = pd.read_csv('year_items.csv') items['revenue'] = items['quantity'] * items['unit_price'] per_order = items.groupby('order_id', as_index=False)['revenue'].sum() orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one') # Revenue leaves out the cancelled orders. sold = orders[orders['status'] != 'cancelled'] weekly = ... best_week = ... print(best_week)

Check▶ Run

The idea

Moving average: rolling

The daily takings jump up and down: high on Fridays, low on Sundays. Behind this noise it is hard to see whether things are going up. A moving average works out, for every day, the average of the last 7 days, including that day:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
items = pd.read_csv('year_items.csv')
items['revenue'] = items['quantity'] * items['unit_price']
per_order = items.groupby('order_id', as_index=False)['revenue'].sum()
orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one')
# Revenue leaves out the cancelled orders.
sold = orders[orders['status'] != 'cancelled']
daily = sold.set_index('ordered_at')['revenue'].resample('D').sum()
week_avg = daily.rolling(7).mean()
pd.DataFrame({'daily': daily, 'week_avg': week_avg}).head(10).round(2)

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) items = pd.read_csv('year_items.csv') items['revenue'] = items['quantity'] * items['unit_price'] per_order = items.groupby('order_id', as_index=False)['revenue'].sum() orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one') # Revenue leaves out the cancelled orders. sold = orders[orders['status'] != 'cancelled'] daily = sold.set_index('ordered_at')['revenue'].resample('D').sum() week_avg = daily.rolling(7).mean() pd.DataFrame({'daily': daily, 'week_avg': week_avg}).head(10).round(2)

▶ RunRestore the code

rolling(7) is a window of 7 rows that slides down one row at a time. The first 6 values are NaN: before 7 November there are not 7 days back. On 7 November the average of the first seven days is €374.23. After that, each day one new day comes in and the oldest goes out, so the line changes smoothly.

The idea

Day of the week

What are the average takings on a Monday, on a Tuesday and so on? You group the daily takings by the number of the day from the index, daily.index.dayofweek, and take the mean. You group by the number rather than by day_name(), because names sort alphabetically and Friday would come first. Then a dictionary (and map) gives the numbers their names:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
items = pd.read_csv('year_items.csv')
items['revenue'] = items['quantity'] * items['unit_price']
per_order = items.groupby('order_id', as_index=False)['revenue'].sum()
orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one')
# Revenue leaves out the cancelled orders.
sold = orders[orders['status'] != 'cancelled']
daily = sold.set_index('ordered_at')['revenue'].resample('D').sum()
names = {0: 'Monday', 1: 'Tuesday', 2: 'Wednesday', 3: 'Thursday', 4: 'Friday', 5: 'Saturday', 6: 'Sunday'}
by_day = daily.groupby(daily.index.dayofweek).mean().round(2)
by_day.index = by_day.index.map(names)
by_day

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) items = pd.read_csv('year_items.csv') items['revenue'] = items['quantity'] * items['unit_price'] per_order = items.groupby('order_id', as_index=False)['revenue'].sum() orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one') # Revenue leaves out the cancelled orders. sold = orders[orders['status'] != 'cancelled'] daily = sold.set_index('ordered_at')['revenue'].resample('D').sum() names = {0: 'Monday', 1: 'Tuesday', 2: 'Wednesday', 3: 'Thursday', 4: 'Friday', 5: 'Saturday', 6: 'Sunday'} by_day = daily.groupby(daily.index.dayofweek).mean().round(2) by_day.index = by_day.index.map(names) by_day

▶ RunRestore the code

Friday is the strongest: €350.79 on average. Sunday is the weakest: €250.85. This is the weekly rhythm that rolling(7) smooths out: a 7-day window holds every day of the week exactly once.

Here the index is already made of dates, so dayofweek is right on the index. For a column you write .dt.dayofweek.

1. Why does the moving average of the daily takings use a window of 7, not 5 or 10?

Seven days contain every day of the week exactly once, so the strong Friday and the weak Sunday balance outBecause the week starts on MondayBecause rolling only works with 7

Check

The idea

Month on month: pct_change

The manager does not ask “how much”, but “how much more or less than last month”. pct_change() divides each value by the one before and subtracts 1: it gives the change as a share. Times 100, it is a percentage:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
items = pd.read_csv('year_items.csv')
items['revenue'] = items['quantity'] * items['unit_price']
per_order = items.groupby('order_id', as_index=False)['revenue'].sum()
orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one')
# Revenue leaves out the cancelled orders.
sold = orders[orders['status'] != 'cancelled']
monthly = sold.set_index('ordered_at')['revenue'].resample('MS').sum()
change = (monthly.pct_change() * 100).round(1)
change

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) items = pd.read_csv('year_items.csv') items['revenue'] = items['quantity'] * items['unit_price'] per_order = items.groupby('order_id', as_index=False)['revenue'].sum() orders = orders.merge(per_order, left_on='id', right_on='order_id', how='left', validate='one_to_one') # Revenue leaves out the cancelled orders. sold = orders[orders['status'] != 'cancelled'] monthly = sold.set_index('ordered_at')['revenue'].resample('MS').sum() change = (monthly.pct_change() * 100).round(1) change

▶ RunRestore the code

November is NaN: there is no month before it. December is 71.2% above November, and January is 54.6% below December. September is 19.1% below August, and October comes back up by 39.2%.

Whether the drop in September is real or just noise, and what caused it, is the question of the project. For now you know that it is there and how big it is.

Exercise

When did coffee get dearer?

Coffee beans (product 17) got dearer at some point in the year. Store in price_by_month the mean price of coffee for every month (in the order of the months in time), and in first_new the date and time of the first sale at the new price of 14.9.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at'])
items = pd.read_csv('year_items.csv')
coffee = items[items['product_id'] == 17]
coffee = coffee.merge(orders[['id', 'ordered_at']], left_on='order_id', right_on='id', validate='many_to_one')
price_by_month = ...
first_new = ...
print(price_by_month)
print(first_new)

import pandas as pd orders = pd.read_csv('year_orders.csv', parse_dates=['ordered_at']) items = pd.read_csv('year_items.csv') coffee = items[items['product_id'] == 17] coffee = coffee.merge(orders[['id', 'ordered_at']], left_on='order_id', right_on='id', validate='many_to_one') price_by_month = ... first_new = ... print(price_by_month) print(first_new)

Check▶ Run

Test

Check yourself

1. The ordered_at column holds a date and a time. Why do you write < "2026-01-01" for the December orders, not <= "2025-12-31"?

Because pandas does not accept <= with datesThere is no difference: both filters give the same"2025-12-31" is midnight at the start of 31 December, so the orders during that day fall outside the filter

2. What does the table need for resample("MS") to work?

An index of dates: for example set_index("ordered_at")Rows sorted by revenueA column called month

3. Why are the first 6 values of daily.rolling(7).mean() NaN?

Before the seventh day there are not 7 days to averageBecause there are no orders in the first daysBecause rolling drops the first week

4. The takings are €8,812.70 in August and €7,128.80 in September. What does pct_change() give for September, times 100 and rounded?

-1683.9-19.119.1

Check

Start the lesson over

Related lessons

• Builds on: New columns · Types and missing values · Groups: groupby · Joining: merge
• Continues in: Which chart for which question · Honest charts · Testing a hypothesis · Correlation and cause
• Tasks on this topic: Wrangling data