CODEWITHSUNDEEP
pythonpandas
Mehul Jangir
Mehul Jangir

Reputation: 46

Merging two dataframes with different indexes (time as index)

I wanted to know how I would merge two Data Frames that use time values as the index. However, not all indexes are the same.

say I want to merge these Data Frames

index         value
2019-07-12    4.0
2019-08-21    4.5
2019-08-22    4.4
2019-09-01    7.2

index          value
2019-07-14     5.6
2019-07-19     9.2
2019-08-21     4.0
2019-09-02     3.2

And then get this result:

index          value1    value2
2019-07-12     4.0       NaN
2019-07-14     NaN       5.6
2019-07-19     NaN       9.2
2019-08-21     4.5       4.0
2019-08-22     4.4       NaN
2019-09-01     7.2       NaN
2019-09-02     NaN       3.2

I went through the pandas.concat documentation, but either couldn't understand or find something that would enable me to do this.

And yeah, I'm planning to sort out the NaN values by interpolating those specific columns.

Upvotes: 2

Views: 1662

Answers (1)

Chris Adams
Chris Adams

Reputation: 18647

pandas.concat aligns on index and column values, so you would need to rename one of your DataFrame's value column.

# Setup
from pandas import Timestamp

df1 = pd.DataFrame({'value': {Timestamp('2019-07-12 00:00:00'): 4.0, Timestamp('2019-08-21 00:00:00'): 4.5, Timestamp('2019-08-22 00:00:00'): 4.4, Timestamp('2019-09-01 00:00:00'): 7.2}})
df2 = pd.DataFrame({'value': {Timestamp('2019-07-14 00:00:00'): 5.6, Timestamp('2019-07-19 00:00:00'): 9.2, Timestamp('2019-08-21 00:00:00'): 4.0, Timestamp('2019-09-02 00:00:00'): 3.2}})

pd.concat([df1, df2.rename({'value':'value2'}, axis=1)]).sort_index()

[out]

            value  value2
2019-07-12    4.0     NaN
2019-07-14    NaN     5.6
2019-07-19    NaN     9.2
2019-08-21    4.5     NaN
2019-08-21    NaN     4.0
2019-08-22    4.4     NaN
2019-09-01    7.2     NaN
2019-09-02    NaN     3.2

Alternatively, you could use DataFrame.merge:

df1.merge(df2, how='outer', left_index=True, right_index=True, suffixes=('1', '2'))

[out]

            value1  value2
2019-07-12     4.0     NaN
2019-07-14     NaN     5.6
2019-07-19     NaN     9.2
2019-08-21     4.5     4.0
2019-08-22     4.4     NaN
2019-09-01     7.2     NaN
2019-09-02     NaN     3.2

Upvotes: 3

Related Questions