Concatenating two DataFrames with different number of rows
How can I combine below two DataFrames with 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 resulting DataFrame should look like this:
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
+3
source to share
2 answers
Seems to be 'CName'
unique in the second data frame. I would 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
+4
source to share
Keep the read and unread columns in df2
, then join them 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
+4
source to share