15

This has been the bane of my life for the past couple of days. I have numerous Pandas Dataframes that contain time series data with irregular frequencies. I try to align these into a single dataframe.

Below is some code, with representative dataframes, df1, df2, and df3 ( I actually have n=5, and would appreciate a solution that would work for all n>2):

# df1, df2, df3 are given at the bottom
import pandas as pd
import datetime

# I can align df1 to df2 easily
df1aligned, df2aligned = df1.align(df2)
# And then concatenate into a single dataframe
combined_1_n_2 = pd.concat([df1aligned, df2aligned], axis =1 )
# Since I don't know any better, I then try to align df3 to combined_1_n_2  manually:
combined_1_n_2.align(df3)
error: Reindexing only valid with uniquely valued Index objects

I have an idea why I get this error, so I get rid of the duplicate indices in combined_1_n_2 and try again:

combined_1_n_2 = combined_1_n_2.groupby(combined_1_n_2.index).first()
combined_1_n_2.align(df3) # But stll get the same error
error: Reindexing only valid with uniquely valued Index objects

Why am I getting this error? Even if this worked, it is completely manual and ugly. How can I align >2 time series and combine them in a single dataframe?

Data:

df1 = pd.DataFrame( {'price' : [62.1250,62.2500,62.2375,61.9250,61.9125 ]}, 
                     index = [pd.DatetimeIndex([datetime.datetime.strptime(s, '%Y-%m-%d %H:%M:%S.%f')])[0] 
                     for s in ['2008-06-01 06:03:59.614000', '2008-06-01 06:03:59.692000', 
                     '2008-06-01 06:15:42.004000', '2008-06-01 06:15:42.083000','2008-06-01 06:17:01.654000' ] ])   

df2 = pd.DataFrame({'price': [241.0625, 241.5000, 241.3750, 241.2500, 241.3750 ]},
                    index = [pd.DatetimeIndex([datetime.datetime.strptime(s, '%Y-%m-%d %H:%M:%S.%f')])[0] 
                     for s in ['2008-06-01 06:13:34.524000', '2008-06-01 06:13:34.602000', 
                     '2008-06-01 06:15:05.399000', '2008-06-01 06:15:05.399000','2008-06-01 06:15:42.082000' ] ])   

df3 = pd.DataFrame({'price': [67.656, 67.875, 67.8125, 67.75, 67.6875 ]},
                    index = [pd.DatetimeIndex([datetime.datetime.strptime(s, '%Y-%m-%d %H:%M:%S.%f')])[0] 
                     for s in ['2008-06-01 06:03:52.281000', '2008-06-01 06:03:52.359000', 
                     '2008-06-01 06:13:34.848000', '2008-06-01 06:13:34.926000','2008-06-01 06:15:05.321000' ] ])
Zhubarb
  • 11,432
  • 18
  • 75
  • 114

1 Answers1

10

Your specific error is due the column names of combined_1_n_2 having duplicates (both columns will be named 'price'). You could rename the columns and the second align would work.

One alternative way would be to chain the join operator, which merges frames on the index, as below.

In [23]: df1.join(df2, how='outer', rsuffix='_1').join(df3, how='outer', rsuffix='_2')
Out[23]: 
                              price   price_1  price_2
2008-06-01 06:03:52.281000      NaN       NaN  67.6560
2008-06-01 06:03:52.359000      NaN       NaN  67.8750
2008-06-01 06:03:59.614000  62.1250       NaN      NaN
2008-06-01 06:03:59.692000  62.2500       NaN      NaN
2008-06-01 06:13:34.524000      NaN  241.0625      NaN
2008-06-01 06:13:34.602000      NaN  241.5000      NaN
2008-06-01 06:13:34.848000      NaN       NaN  67.8125
2008-06-01 06:13:34.926000      NaN       NaN  67.7500
2008-06-01 06:15:05.321000      NaN       NaN  67.6875
2008-06-01 06:15:05.399000      NaN  241.3750      NaN
2008-06-01 06:15:05.399000      NaN  241.2500      NaN
2008-06-01 06:15:42.004000  62.2375       NaN      NaN
2008-06-01 06:15:42.082000      NaN  241.3750      NaN
2008-06-01 06:15:42.083000  61.9250       NaN      NaN
2008-06-01 06:17:01.654000  61.9125       NaN      NaN
chrisb
  • 49,833
  • 8
  • 70
  • 70
  • Thank you, that is suprising; so the `align()` is unnecessary if I do the chain joins? – Zhubarb Oct 14 '14 at 20:12
  • 1
    Correct, `join` handles the index logic for you. – chrisb Oct 14 '14 at 20:12
  • Thank you, out of curiosity: could you just briefly elaborate on when one may need to use the align() then? Because to me it seems join() handles both alignment and concatenation in one go. – Zhubarb Oct 14 '14 at 20:14
  • There aren't too many practical cases I can think of, but if for some reason you need to only align the index, you'd save a little time by using `align`. – chrisb Oct 14 '14 at 21:51