Merge two tabel

Question

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

`df1.merge(df2, left_on=['cx1'], right_on='cx2', how='left')`? — Quang Hoang, Jun 01 '20 at 16:56
Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Umar.H, Jun 01 '20 at 16:58
I tried this code now but I am getting cx1 and cx2. I need to get only one column for cx which I mention in final out table — Shulaz Shan, Jun 01 '20 at 17:01
need to join the group (left - cx1, Date and right - cx2, Date) — Shulaz Shan, Jun 01 '20 at 17:09
@ShulazShan Shouldn't the output in row 2 will be `1 12/1/2019 200 0` instead of `1 12/1/2019 0 300` — Shubham Sharma, Jun 01 '20 at 17:23

Shubham Sharma · Accepted Answer · 2020-06-01T17:49:00.497

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

score 0 · Answer 2 · answered Jun 01 '20 at 17:05

0

How about the concat() method?

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

Output:

dtype: object

answered Jun 01 '20 at 17:05

Red

26,798
7
36
58

no plz look at my final output, that is i am expecting as final table – Shulaz Shan Jun 01 '20 at 17:10
Can you post the sources of the dataframes, e.g. lists, text, etc? – Red Jun 01 '20 at 17:12
I have two excel sheet one is customer cash-in and second customer cash-out table. the cash-in table has customer_num, Date, Amount, and second, the cash-out table has customer_num1, Date, Amount. I need to merge those two tables. – Shulaz Shan Jun 01 '20 at 17:19
see my final table, that what I am expecting – Shulaz Shan Jun 01 '20 at 17:23

Merge two tabel

2 Answers2