Getting Started

Upsample with an average in Pandas

Align inconsistently reported data for your machine learning

Many models input cumulative variables like rainfalls in a few hours or company revenue over some months. Unfortunately, the data sources are often not aligned in time. One sensor feeds the data every odd hour, another one in even hours. One company provides information in May and the other in June.

If you don’t want to wait for all your data sources to collect the inputs or when it doesn’t suit to feed the model with data coming from different time frames, you have to spread your measurement to the same periods of time. Pandas can natively do part of the job, but in this article, we’ll explore how to upsample with an average, which requires a little bit of extra coding.

You can run all the example from this tutorial through the notebook shared on Github — Upsample_to_average.ipynb

Resampling

Downsample

Resampling in python’s Pandas allows you to turn more frequent values to less frequent — downsample, e.g. hourly data to daily sum, count and average, or daily to monthly values.

# downsample:
CAT|DATE|VALUE
abc|0101|10
abc|0102|20
abc|0103|15

# downsample
[IN]: df.groupby("CAT").resample("W", on="DATE").agg({"VALUE":["sum","count","mean","first","last"]})

[OUT]:
CAT|DATE|SUM|COUNT|MEAN|MIN|MAX|FIRST|LAST
abc|0107|45 |   3 | 15 | 10| 20|  10 | 15

It’s called downsample because the number of data-rows decreases. You can apply sum, count, mean (for average), median, min, max, first or last. Based on daily inputs you can resample to weeks, months, quarters, years, but also to semi-months — see the complete list of resample options in pandas documentation. You can also resample to multiplies, e.g. 5H for groups of 5 hours.

Upsample

Resampling to more frequent timestamps is called upsampling. You can turn days into hours or months into days. When upsampling you must first set the DateTime column as an index of your data frame (.set_index), and only then you can upsample.

# error in case you try upsample using .resample("D", on="col")
ValueError: Upsampling from level= or on= selection is not supported, use .set_index(...) to explicitly set index to datetime-like

Besides the functions above, the upsampling supports backfill/bfill, ffill/pad and nearest. Though if you think that applying mean would split the daily value to an average per hour, you would be wrong. All the added up-samples are filled with NaN.

Why do we want to upsample into average

Let’s say you have a portfolio of shops and try to estimate the sales. You input the revenue on Jan-1, Jan-2 and Jan-3 and perform a regression to guess the target value on Jan-4. You train on all your shops and use the model to predict future sales.

But what if for some shops provide data only for Jan-2 and Jan-3. And others only onJan-1 and Jan-3. You can downsample and model using sum or average of these 3 days, but in such a case you lose valuable input information like Friday peeks or low Monday takings.

Contrary to common belief, this scenario is typical. When analyzing companies’ fundamental data in order to predict future stock prices, some companies report them quarterly, while others twice a year. Some do so in November, while others in August. If you would wait until everyone provides their data, you might come up with a precise prediction, but at that time all the lucrative stock would be sold out.

Usually 80% of the inputs hold the same pattern and 20% including some imporant scenarios have unusual structure you have to recalculate.

To work around these data leaks, you can upsample to average.

Upsample with an average

I’m talking about the option to smooth the inconsistently reported data, so that you can

[IN]:
data="""
CAT|DATE|VALUE
abc|0101|10
abc|0103|20
abc|0105|15
efg|0102|40
efg|0105|30"""

[OUT]:
CAT|DATE|VALUE
abc|0102|10
abc|0103|10   # 20 on 0103 covers interval 0102-0103 (2 days)
abc|0104|7.5
abc|0105|7.5  # 15 on 0105 covers interval 0104-0105 (2 days)
efg|0103|10
efg|0104|10
efg|0105|10   # 30 on 0105 covers interval 0103-0105 (3 days)

The example above is quite simple, but imagine you have thousands of categories. Some reported daily, others bi-daily in odd or even days, the rest every three days or irregularly.

How to do the average magic

In order to calculate the average, you need two things — the sum divided by the count. You know the sum, because it was reported, but the number of days (months, weeks …) since the last value is unknown. Luckily, pandas resampling contains a parameter .group which exposes, how many groups were created when upsampled.

Unfortunately, it doesn’t work natively with groupby so we’ll look on a single category first.

data="""
CAT|DATE|VALUE
abc|0101|10
abc|0103|20
abc|0106|15"""

The resample method has two attributes:

• indices — showing where the input data appeared
• groups — indexing each DateTime value with its group index.

# indices
[In]: df.set_index("DATE").resample("D").indices
[Out]: 
defaultdict(list,
            {Timestamp('1900-01-01 00:00:00', freq='D'): [0],
             Timestamp('1900-01-03 00:00:00', freq='D'): [1],
             Timestamp('1900-01-06 00:00:00', freq='D'): [2]})

# groups
[In]: df.set_index("DATE").resample("D").groups
[Out]:
{Timestamp('1900-01-01 00:00:00', freq='D'): 1,
 Timestamp('1900-01-02 00:00:00', freq='D'): 1,
 Timestamp('1900-01-03 00:00:00', freq='D'): 2,
 Timestamp('1900-01-04 00:00:00', freq='D'): 2,
 Timestamp('1900-01-05 00:00:00', freq='D'): 2,
 Timestamp('1900-01-06 00:00:00', freq='D'): 3}

January first and second forms the first group, third to fifth are the second and the last data sample falls into the last group. Mostly the data are reported that the value on the reporting date is included — 20 reported on Jan-3 covers Jan-2 and Jan-3. In that case, you use label parameter and set it to right.

# groups
[In]: df.set_index("DATE").resample("D", label="right).groups
[Out]:
{
 Timestamp('1900-01-02 00:00:00', freq='D'): 1,
 Timestamp('1900-01-03 00:00:00', freq='D'): 1,
 Timestamp('1900-01-04 00:00:00', freq='D'): 2,
 Timestamp('1900-01-05 00:00:00', freq='D'): 2,
 Timestamp('1900-01-06 00:00:00', freq='D'): 2,
 Timestamp('1900-01-07 00:00:00', freq='D'): 3,}

Alternatively you can turn the groups into a dataframe and shift the values

df_groups = pd.DataFrame(df.set_index("DATE").resample("D").groups, index=["group"]).T.shift(1)

Then you count the occurrences in each group.

s = df_groups.groupby("group").size()
s.name = "count"

And join the counts with the dates and backfilled values. It results into a dataframe having a sum for the period and number of occurrences in this period which is all you need to calculate the average.

If you shout with excitement now, hold on, we’re not at the finish line yet. We have calculated the average for a single group, but in case we want to groupby multiple categories, e.g. when we have many stores, companies, or sensors to work with, we must calculate the average for each group separately.

It’s not complicated, but it can be time-consuming in case you have many inputs. See the code below:

You have smoothed the data to average, but often you don’t need each monthly average. You only needed to cover different reporting periods. For example in financial analysis reviewing the performance of publicly traded companies, most of them report the data at the end of the quarter. But some do it in different months, or even not quarterly. If you want to model the behavior of a whole bunch of companies, you first smooth to average those which doesn’t report quarterly and the use rolling function limit the inputs to quarters only.

More inputs mean more model parameters and longer computing time

Let’s look on the stock market analysis example:

Once again the coding gets us surprised. When you apply .resample("D", label="right") resampling to days the results are the same as .resample("D", label="right", closed="left"). If you do the same while resampling to months, the label="right" matches label="right", closed="right". For this reason, you must specify label="right", closed="left" for the monthly calculation or use the workaround with shift mentioned above.

Conclusion

I hope that you have learned something not only about resampling but how to use alternative approaches to align inconsistent data when feeding your machine learning models.

The images were with canva.com (affiliate link, when you click on it and purchase a product, you won't pay more, but I can receive a small reward; you can always write canva.com to your browser to avoid this). Canva offer some free templates and graphics too. 
Other articles:
* Everything you wanted to know about Kfold train-test split
* Why it's worth considering another file types than csv
* How to turn a list of addreses into a map

Great thanks to Towards data science

Full Code available on Github —Upsample_to_average.ipynb .