Combine two pandas DataFrame into one new

Question

I have two Pandas DataFrames whose data from different sources, but both DataFrames have the same column names. When combined only one column will keep the name.

Like this:

speed_df = pd.DataFrame.from_dict({
    'ts':  [0,1,3,4],
    'val': [5,4,2,1]
    })

temp_df = pd.DataFrame.from_dict({
    'ts':  [0,1,2],
    'val': [9,8,7]
    })

And I need to have a result like this:

final_df = pd.DataFrame.from_dict({
    'ts':    [0,1,2,3,4],
    'speed': [5,4,NaN,1],
    'temp':  [9,8,7,NaN,NaN]
    })

Later I will deal with empty cells (here filled with NaN) by copying the values of the previous valid value. And get something like this:

final_df = pd.DataFrame.from_dict({
    'ts':    [0,1,2,3,4],
    'speed': [5,4,4,1],
    'temp':  [9,8,7,7,7]
    })

score 5 · Accepted Answer · answered Aug 30 '17 at 21:22

Use pd.merge

In [406]: (pd.merge(speed_df, temp_df, how='outer', on='ts')
             .rename(columns={'val_x': 'speed','val_y': 'temp'})
             .sort_values(by='ts'))
Out[406]:
   ts  speed  temp
0   0    5.0   9.0
1   1    4.0   8.0
4   2    NaN   7.0
2   3    2.0   NaN
3   4    1.0   NaN

In [407]: (pd.merge(speed_df, temp_df, how='outer', on='ts')
             .rename(columns={'val_x': 'speed', 'val_y': 'temp'})
             .sort_values(by='ts').ffill())
Out[407]:
   ts  speed  temp
0   0    5.0   9.0
1   1    4.0   8.0
4   2    4.0   7.0
2   3    2.0   7.0
3   4    1.0   7.0

Thanks! This is exactly what needed. – ulitosCoder Aug 31 '17 at 13:37 — ulitosCoder, Aug 31 '17 at 13:37

score 0 · Answer 2 · answered Aug 30 '17 at 21:27

0

Two main DataFrame options, one is pd.merge and the other is pd.fillna. Here is the code:

df = speed_df.merge(temp_df, how='outer', on='ts')
df = df.rename(columns=dict(val_x='speed', val_y='temp'))
df = df.sort_values('ts')
df.fillna(method='ffill')

Hope this would be helpful.

Thanks

answered Aug 30 '17 at 21:27

rojeeer

1,991
1
11
13

This is step by step identical to @JohnGalt's answer – piRSquared Aug 30 '17 at 21:40

score -1 · Answer 3 · answered Aug 30 '17 at 21:50

You need to do a left outer join using pandas.merge function

d = pd.merge(speed_df,temp_df,on='ts',how='outer').rename(columns=\
{'val_x':'speed','val_y':'temp'})
d = d.sort_values('ts')
d['speed']=d['speed'].fillna(4)
d['temp']=d['temp'].fillna(7)

That should return you this:

Combine two pandas DataFrame into one new

3 Answers3

Linked