These are the two dataframes I am trying to merge together.
df1
Year Month Debit
0 2020 December 6500.0
1 2020 December 1566.0
2 2020 December 200.0
3 2020 December 100.0
4 2020 December 40.0
5 2021 January 8000.0
6 2021 January 2000.0
7 2021 January 2000.0
8 2021 January 1975.0
9 2021 January 1635.0
df2
Year Month Credit
0 2020 December 1560.0
1 2020 December 198.0
2 2020 December 130.0
3 2021 January 10000.0
4 2021 January 8000.0
5 2021 January 5000.0
6 2021 January 5000.0
7 2021 January 4000.0
8 2021 February 2000.0
9 2021 February 150.0
When I merge them together, the result I expect is for the rows of Debit and Credit columns to return a value for those specific Year and Month if present, otherwise returning NaN.
Expected Result-
Year Month Debit Credit
0 2020 December 6500.0 1560.0
1 2020 December 1566.0 198.0
2 2020 December 200.0 130.0
3 2020 December 100.0 NaN
4 2020 December 40.0 NaN
5 2021 January 8000.0 10000.0
6 2021 January 2000.0 8000.0
7 2021 January 2000.0 5000.0
8 2021 January 1975.0 5000.0
9 2021 January 1635.0 4000.0
10 2021 February NaN 2000.0
11 2021 February NaN 150.0
But the result I get is all the permutations of the values of Debit and Credit.
Observed Result-
Year Month Debit Credit
0 2020 December 6500.0 1560.0
1 2020 December 6500.0 198.0
2 2020 December 6500.0 130.0
3 2020 December 1566.0 1560.0
4 2020 December 1566.0 198.0
5 2020 December 1566.0 130.0
6 2020 December 200.0 1560.0
7 2020 December 200.0 198.0
8 2020 December 200.0 130.0
9 2020 December 100.0 1560.0
10 2020 December 100.0 198.0
11 2020 December 100.0 130.0
12 2020 December 40.0 1560.0
13 2020 December 40.0 198.0
14 2020 December 40.0 130.0
15 2021 January 8000.0 10000.0
16 2021 January 8000.0 8000.0
17 2021 January 8000.0 5000.0
18 2021 January 8000.0 5000.0
19 2021 January 8000.0 4000.0
20 2021 January 2000.0 10000.0
21 2021 January 2000.0 8000.0
22 2021 January 2000.0 5000.0
23 2021 January 2000.0 5000.0
24 2021 January 2000.0 4000.0
25 2021 January 2000.0 10000.0
26 2021 January 2000.0 8000.0
27 2021 January 2000.0 5000.0
28 2021 January 2000.0 5000.0
29 2021 January 2000.0 4000.0
30 2021 January 1975.0 10000.0
31 2021 January 1975.0 8000.0
32 2021 January 1975.0 5000.0
33 2021 January 1975.0 5000.0
34 2021 January 1975.0 4000.0
35 2021 January 1635.0 10000.0
36 2021 January 1635.0 8000.0
37 2021 January 1635.0 5000.0
38 2021 January 1635.0 5000.0
39 2021 January 1635.0 4000.0
Here is the code to reproduce the example-
import pandas as pd
df1 = pd.DataFrame({'Year': {0: 2020,
1: 2020,
2: 2020,
3: 2020,
4: 2020,
5: 2021,
6: 2021,
7: 2021,
8: 2021,
9: 2021},
'Month': {0: 'December',
1: 'December',
2: 'December',
3: 'December',
4: 'December',
5: 'January',
6: 'January',
7: 'January',
8: 'January',
9: 'January'},
'Debit': {0: 6500.0,
1: 1566.0,
2: 200.0,
3: 100.0,
4: 40.0,
5: 8000.0,
6: 2000.0,
7: 2000.0,
8: 1975.0,
9: 1635.0}})
df2 = pd.DataFrame({'Year': {0: 2020,
1: 2020,
2: 2020,
3: 2021,
4: 2021,
5: 2021,
6: 2021,
7: 2021,
8: 2021,
9: 2021},
'Month': {0: 'December',
1: 'December',
2: 'December',
3: 'January',
4: 'January',
5: 'January',
6: 'January',
7: 'January',
8: 'February',
9: 'February'},
'Credit': {0: 1560.0,
1: 198.0,
2: 130.0,
3: 10000.0,
4: 8000.0,
5: 5000.0,
6: 5000.0,
7: 4000.0,
8: 2000.0,
9: 150.0}})
new_df = pd.merge(df1, df2, on=['Year','Month'])
new_df