CODEWITHSUNDEEP
pythonpandasdataframe
Shulaz Shan
Shulaz Shan

Reputation: 175

Merge two tabel

I have two tables. I need to merge two tables with cx1, cx2 and Date keys. How is it?

fist table
cx1   Date       Amount
1     11/1/2019   400
1     12/1/2019   200
2     15/1/2019   400
3     16/1/2019   500

Second Table
cx2    Date        Amount
1      11/1/2019   300
2      16/1/2019   200
3      16/1/2019   400

Final output
cx     Date        Amount_1    Amount_2
1      11/1/2019   400         300
1      12/1/2019   0           300
2      15/1/2019   400         0
2      16/1/2019   0           200
3      16/1/2019   500         400

Upvotes: 1

Views: 47

Answers (2)

Shubham Sharma
Shubham Sharma

Reputation: 71689

Use, the combination of pd.merge, Series.fillna, Series.sort_values, Series.rename:

df = pd.merge(
    df1.rename(columns={'cx1': 'cx'}), df2.rename(columns={'cx2': 'cx'}),
    on=['cx', 'Date'], how='outer', suffixes=('_1', '_2'))

df = df.sort_values(by='cx').fillna(0)
df[['cx', 'Amount_1', 'Amount_2']] = df[['cx', 'Amount_1', 'Amount_2']].astype(int)

Result:

   cx       Date  Amount_1  Amount_2
0   1  11/1/2019       400       300
1   1  12/1/2019       200         0
2   2  15/1/2019       400         0
4   2  16/1/2019         0       200
3   3  16/1/2019       500       400

Upvotes: 1

Red
Red

Reputation: 27567

How about the concat() method?

>>> import pandas as pd
>>> s1 = pd.Series(['a', 'b'])
>>> s2 = pd.Series(['c', 'd'])
>>> pd.concat([s1, s2])

Output:

0    a
1    b
0    c
1    d

dtype: object

Upvotes: 0

Related Questions