CODEWITHSUNDEEP
pythonpandasdataframe
Simon
Simon

Reputation: 312

Comparing pandas dataframes of different length

I have two dataframes of different lengths both indexed by date. I need both dataframes to have the same dates, ie. delete the extra entries in the longest dataframe.

I have found that I can reset index and make it another another column then call that column as a pandas dataseries and compare to the other data series, giving me a pandas series with only the entries that are also in the shorter dataframe: 

df1 = ...
df2 = ...
dfadj = df1.reset_index(['Date'])
dfstock = dfadj['Date'][dfadj['Date'].isin(dfindex['Date'])]

But then I would need to find the index positions from these values and in another step delete it from the longest dataframe. Am I missing a completely different approch which would be more logical and/or simple?

Upvotes: 3

Views: 19455

Answers (1)

jezrael
jezrael

Reputation: 862396

You can use Index.intersection and then select data in df2 by ix:

idx = df2.index.intersection(df1.index)
print (idx)
DatetimeIndex(['2015-02-24', '2015-02-25', '2015-02-26', '2015-02-27',
               '2015-02-28', '2015-03-01', '2015-03-02', '2015-03-03',
               '2015-03-04', '2015-03-05'],
              dtype='datetime64[ns]', freq='D')

print (df2.ix[idx])
             b
2015-02-24  10
2015-02-25  11
2015-02-26  12
2015-02-27  13
2015-02-28  14
2015-03-01  15
2015-03-02  16
2015-03-03  17
2015-03-04  18
2015-03-05  19

Another solution is use merge with inner join, what is by deafult, so can be omited how='inner':

df = pd.merge(df1,df2, left_index=True, right_index=True)

Sample:

rng1 = pd.date_range(pd.to_datetime('2015-02-24'), periods=10)
df1 = pd.DataFrame({'a': range(10)}, index=rng1)   
print (df1)
            a
2015-02-24  0
2015-02-25  1
2015-02-26  2
2015-02-27  3
2015-02-28  4
2015-03-01  5
2015-03-02  6
2015-03-03  7
2015-03-04  8
2015-03-05  9

rng2 = pd.date_range(pd.to_datetime('2015-02-24'), periods=20)
df2 = pd.DataFrame({'b': range(10,30)}, index=rng2)  
print (df2)
            b
2015-02-24  10
2015-02-25  11
2015-02-26  12
2015-02-27  13
2015-02-28  14
2015-03-01  15
2015-03-02  16
2015-03-03  17
2015-03-04  18
2015-03-05  19
2015-03-06  20
2015-03-07  21
2015-03-08  22
2015-03-09  23
2015-03-10  24
2015-03-11  25
2015-03-12  26
2015-03-13  27
2015-03-14  28
2015-03-15  29

df = pd.merge(df1,df2, left_index=True, right_index=True)
print (df)
            a   b
2015-02-24  0  10
2015-02-25  1  11
2015-02-26  2  12
2015-02-27  3  13
2015-02-28  4  14
2015-03-01  5  15
2015-03-02  6  16
2015-03-03  7  17
2015-03-04  8  18
2015-03-05  9  19

Last if need delete some columns use drop:

print (df.drop(['a'], axis=1))
             b
2015-02-24  10
2015-02-25  11
2015-02-26  12
2015-02-27  13
2015-02-28  14
2015-03-01  15
2015-03-02  16
2015-03-03  17
2015-03-04  18
2015-03-05  19

Upvotes: 10

Related Questions