Create a dataframe based in common timestamps of multiple dataframes

user_11380243 0
Viewed 385

I am looking for a elegant solution to selecting common timestamps from multiple dataframes. I know that something like this could work supposing the dataframe of common timestamps to be df:

df = df1[df1['Timestamp'].isin(df2['Timestamp'])]

However, if I have several other dataframes, this solution becomes quite unelegant. Therefore, I have been wondering if there is an easier approach to achieve my goal when working with multiple dataframes.

So, let's say for example that I have:


date1 = pd.date_range(start='1/1/2018', end='1/02/2018', freq='H')
date2 = pd.date_range(start='1/1/2018', end='1/02/2018', freq='15min')
date3 = pd.date_range(start='1/1/2018', end='1/02/2018', freq='45min')
date4 = pd.date_range(start='1/1/2018', end='1/02/2018', freq='30min')

data1 = np.random.randn(len(date1))
data2 = np.random.randn(len(date2))
data3 = np.random.randn(len(date3))
data4 = np.random.randn(len(date4))

df1 = pd.DataFrame(data = {'date1' : date1, 'data1' : data1})
df2 = pd.DataFrame(data = {'date2' : date2, 'data2' : data2})
df3 = pd.DataFrame(data = {'date3' : date3, 'data3' : data3})
df4 = pd.DataFrame(data = {'date4' : date4, 'data4' : data4})

I would like as an output a dataframe containing the common timestamps of the four dataframes as well as the respective data column out of each of them, for example (just to illustrate what I mean, it doesn't reflect on the result):

    commom Timestamp     data1    data2    data3    data4
0  2018-01-01 00:00:00 -1.129439   1.2312   1.11    -0.83
1  2018-01-01 01:00:00  0.853421   0.423    0.241   0.123
2  2018-01-01 02:00:00 -1.606047   1.001   -0.005   -0.12
3  2018-01-01 03:00:00 -0.668267   0.98     1.11    -0.23
[...]
pandas dataframe python
2 Answers

You can use reduce from functools to perform the complete inner merge. We'll need to rename the columns just so the merge is a bit easier.

from functools import reduce

lst = [df1.rename(columns={'date1': 'Timestamp'}), df2.rename(columns={'date2': 'Timestamp'}), 
       df3.rename(columns={'date3': 'Timestamp'}), df4.rename(columns={'date4': 'Timestamp'})]

reduce(lambda l,r: l.merge(r, on='Timestamp'), lst)

            Timestamp     data1     data2     data3     data4
0 2018-01-01 00:00:00 -0.971201 -0.978107  1.163339  0.048824
1 2018-01-01 03:00:00 -1.063810  0.125318 -0.818835 -0.777500
2 2018-01-01 06:00:00  0.862549 -0.671529  1.902272  0.011490
3 2018-01-01 09:00:00  1.030826 -1.306481  0.438610 -1.817053
4 2018-01-01 12:00:00 -1.191646 -1.700694  1.007190 -1.932421
5 2018-01-01 15:00:00 -1.803248  0.415256  0.690243  1.387650
6 2018-01-01 18:00:00 -0.304502  0.514616  0.974318 -0.062800
7 2018-01-01 21:00:00 -0.668874 -1.262635 -0.504298 -0.043383
8 2018-01-02 00:00:00 -0.943615  1.010958  1.343095  0.119853

Alternatively concat with an 'inner' join and setting the Timestamp to the index

pd.concat([x.set_index('Timestamp') for x in lst], axis=1, join='inner')
user_43333590

If it would be acceptable to name every timestamp column in the same way (date for example), something like this could work:

def common_stamps(*args): # *args lets you feed it any number of dataframes
    
    df = pd.concat([df_i.set_index('date') for df_i in args], axis=1)\
           .dropna()\ # this removes all rows with `uncommon stamps`
           .reset_index()
    
    return df
    
df = common_stamps(df1, df2, df3, df4)

print(df)

Output:

                 date     data1     data2     data3     data4
0 2018-01-01 00:00:00 -0.667090  0.487676 -1.001807 -0.200328
1 2018-01-01 03:00:00 -1.639815  2.320734 -0.396013 -1.838732
2 2018-01-01 06:00:00  0.469890  0.626428  0.040004 -2.063454
3 2018-01-01 09:00:00 -0.916928 -0.260329 -0.598313  0.383281
4 2018-01-01 12:00:00  0.132670  1.771344 -0.441651  0.664980
5 2018-01-01 15:00:00 -0.761542  0.255955  1.378836 -1.235562
6 2018-01-01 18:00:00 -0.120083  0.243652 -1.261733  1.045454
7 2018-01-01 21:00:00  0.339921 -0.901171  1.492577 -0.797161
8 2018-01-02 00:00:00 -1.397864 -0.173818 -0.581590 -0.402472
user_51077620
Related