CODEWITHSUNDEEP
pythonpandas
ulitosCoder
ulitosCoder

Reputation: 2049

Combine two pandas DataFrame into one new

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]
    })

Upvotes: 0

Views: 686

Answers (3)

ashish trehan
ashish trehan

Reputation: 433

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:

enter image description here

Upvotes: -1

rojeeer
rojeeer

Reputation: 2011

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

Upvotes: 0

Zero
Zero

Reputation: 76957

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

Upvotes: 5

Related Questions