CODEWITHSUNDEEP
pythonpandasdataframe
Mahendra Gaur
Mahendra Gaur

Reputation: 390

Merging two DataFrame with different number of rows

How can I merge below two DataFrames with the different number of rows but have one common column in Pandas?

DataFrame1:

CName   PName   Col1    Col2
A1      XX1     34      22
A2      XX2     23      44
A1      XX3     11      12
A2      XX4     23      43
A1      XX5     42      76
A3      XX6     15      56
A4      XX7     33      45
A5      XX8     223     87
A5      XX9     12      56
A5      XX10    87      34
A5      XX11    6       23
A4      XX12    55      33

DataFrame2:

CName   read    unread
A1      12      43
A2      24      78
A3      1       65
A4      2       16
A5      5       6

so that the resultant DataFrame must as below:

CName   PName   Col1    Col2    SumOfReadAndUnRead
A1      XX1     34      22      55
A2      XX2     23      44      102
A1      XX3     11      12      55
A2      XX4     23      43      102
A1      XX5     42      76      55
A3      XX6     15      56      66
A4      XX7     33      45      18
A5      XX8     223     87      11
A5      XX9     12      56      11
A5      XX10    87      34      11
A5      XX11    6       23      11
A4      XX12    55      33      18

Upvotes: 4

Views: 943

Answers (2)

piRSquared
piRSquared

Reputation: 294218

It looks as though 'CName' is unique in the second dataframe. I'd use map. It should be faster.

df1.assign(
    SumOfReadAndUnRead=df1.CName.map(df2.set_index('CName').sum(1))
)

   CName PName  Col1  Col2  SumOfReadAndUnRead
0     A1   XX1    34    22                  55
1     A2   XX2    23    44                 102
2     A1   XX3    11    12                  55
3     A2   XX4    23    43                 102
4     A1   XX5    42    76                  55
5     A3   XX6    15    56                  66
6     A4   XX7    33    45                  18
7     A5   XX8   223    87                  11
8     A5   XX9    12    56                  11
9     A5  XX10    87    34                  11
10    A5  XX11     6    23                  11
11    A4  XX12    55    33                  18

Upvotes: 4

Alexander
Alexander

Reputation: 109528

Sum the read and unread columns in df2, then join it to df1.

>>> df1.join(df2.set_index('CName').sum(axis=1).to_frame('SumOfReadAndUnRead'), on='CName')
   CName PName  Col1  Col2  SumOfReadAndUnRead
0     A1   XX1    34    22   55
1     A2   XX2    23    44  102
2     A1   XX3    11    12   55
3     A2   XX4    23    43  102
4     A1   XX5    42    76   55
5     A3   XX6    15    56   66
6     A4   XX7    33    45   18
7     A5   XX8   223    87   11
8     A5   XX9    12    56   11
9     A5  XX10    87    34   11
10    A5  XX11     6    23   11
11    A4  XX12    55    33   18

Upvotes: 4

Related Questions