Average of two time series in pandas

user_7388106 0
Viewed 1783

Ultimate goal description

My goal is to compute the average of two times series (red and green) stored in pandas DataFrames. However, while both time series have the same columns, they differ in precise time points. What I want to implement is a function average which computes average time series from the two given series such that if a value is missing for particular time point, it should be interpolated. For example:

import pandas as pd
green_df = pd.DataFrame({'A': [4, 2, 5], 'B': [1, 2, 3]}, index=[1, 3, 6])
red_df = pd.DataFrame({'A': [4, 2.5, 8, 2, 4], 'B': [4, 2, 2, 4, 1]}, index=[1, 2, 4, 5, 6])

average_grey_df = pd.DataFrame({'A': [4, 2.7, 3.75, 5.5, 3, 4.5], 'B': [...]}, index= [1, 2, 3, 4, 5, 6])

assert average_grey_df == average(green_df, red_df)

It is obvious when displayed graphically (values shown for column A, but the same should be done with all columns; precise values are just illustrative):

enter image description here

Approach

So far I was not able to find a completely working solution. I was thinking about dividing it to three steps:

(1) extend both time series by time points from the other time series such that missing data are nan

                    A  | ...                    A | ...
                -------                     -------
                1 | 4 |                     1 | 4 |
                2 |nan|                     2 |2.5|
    red:        3 | 2 |         green:      3 |nan|
                4 |nan|                     4 | 8 |
                5 |nan|                     5 | 2 |
                6 | 5 |                     6 | 4 |

(2) fill the missing data by interpolating both dataframes (direct usage of dataframe interpolate method) (3) finally compute average of these two time series as following:

averages = (green_df.stack() + red_df.stack()) / 2
average_grey_df = averages.unstack()

Additionally, method dropna can be used to drop created nans. Moreover, maybe there is a better method I haven't discovered.

Question

I was not able to figure out how to compute part (1) at all. I checked methods like join, merge and concat with its various examples, but none of them seems to do the job. Any suggestions? I am also open to other approaches.

Thank you

pandas time-series python
2 Answers

You can merge the two dfs. From there, you can interpolate the NA values

green_df = pd.DataFrame({'A': [4, 2, 5], 'B': [1, 2, 3]}, index=[1, 3, 6])
red_df = pd.DataFrame({'A': [4, 2.5, 8, 2, 4], 'B': [4, 2, 2, 4, 1]}, index=[1, 2, 4, 5, 6])

combined_df = pd.merge(green_df, red_df, suffixes=('_green', '_red'), left_index=True, right_index=True, how='outer')
combined_df = combined_df.interpolate()
combined_df['A_avg'] = combined_df[["A_green", "A_red"]].mean(axis=1)
combined_df['B_avg'] = combined_df[["B_green", "B_red"]].mean(axis=1)

These can then be plotted using .plot():

combined_df[['A_green', 'A_red', 'A_avg']].plot(color=['green', 'red', 'gray'])

enter image description here

user_67806770

To perform the task 1) you can do this:

 #union of the indexes
 union_idx = green_df.index.union(red_df.index)


 #reindex with the union
 green_df= green_df.reindex(union_idx)
 red_df= red_df.reindex(union_idx)

 # the interpolation
 green_df = green_df.interpolate(method='linear', limit_direction='forward', axis=0)
 red_df = red_df.interpolate(method='linear', limit_direction='forward', axis=0)


 grey_df= pd.concat([green_df,red_df])
 grey_df= grey_df.groupby(level=0).mean()

I get (i didn't pay attention to displaying the correct colors) my result

user_41310180
Related