pandas.concat using only the index from one table

user_756589 0
Viewed 536

I'm looking for a more efficient way to concatenate two dataframes that have a DatetimeIndex. Considering I want only the dates from the first, and all data from the second:

from io import StringIO
import pandas as pd

a = StringIO('''
Time,No,Value
2017-10-17 04:00:00,1,10
2017-10-17 04:01:00,2,10
2017-10-17 04:02:00,3,10
2017-10-17 04:03:00,4,10
2017-10-17 04:04:00,5,10
''')

b = StringIO('''
Time,Str
2017-10-17 04:00:00,a
2017-10-17 04:02:00,b
''')

df1 = pd.read_csv(a).set_index('Time')
df2 = pd.read_csv(b).set_index('Time')

What I currently do is include one useless column, and then delete it:

>>> df = pd.concat([df1.No, df2], axis=1)
>>> print(df)
                    No  Str
Time                        
2017-10-17 04:00:00   1    a
2017-10-17 04:01:00   2  NaN
2017-10-17 04:02:00   3    b
2017-10-17 04:03:00   4  NaN
2017-10-17 04:04:00   5  NaN

>>> del df['No']
>>> print(df)
                    Str
Time                    
2017-10-17 04:00:00    a
2017-10-17 04:01:00  NaN
2017-10-17 04:02:00    b
2017-10-17 04:03:00  NaN
2017-10-17 04:04:00  NaN

Expected command (this doesn't work):

>>> pd.concat([df1.index, df2], axis=1)
                    Str
Time                    
2017-10-17 04:00:00    a
2017-10-17 04:01:00  NaN
2017-10-17 04:02:00    b
2017-10-17 04:03:00  NaN
2017-10-17 04:04:00  NaN
pandas dataframe python
3 Answers

This is what you want in a simpler form:

df2.reindex(df1.index)
user_79926000

Either reindex, as suggested in the first answer, or this:

pd.concat([df1.No, df2], axis=1)['Str']
user_44929320

You can use df.join, which joins two dataframes on index by default:

In [180]: df2.join(df1, how='outer')['Str']
Out[180]: 
Time
2017-10-17 04:00:00      a
2017-10-17 04:01:00    NaN
2017-10-17 04:02:00      b
2017-10-17 04:03:00    NaN
2017-10-17 04:04:00    NaN
Name: Str, dtype: object
user_58208140
Related