CODEWITHSUNDEEP
python-3.xpandas
Zephyr
Zephyr

Reputation: 1352

Working with two data frames with different size in python

I am working with two data frames. The sample data is as follow:

DF = ['A','B','C','D','E','A','C','B','B']

DF1 = pd.DataFrame({'Team':DF})

DF2 = pd.DataFrame({'Team':['A','B','C','D','E'],'Rating':[1,2,3,4,5]})

i want to add a new column to DF1 as follow:

Team       Rating
A             1
B             2
C             3
D             4
E             5
A             1
C             3
B             2
B             2

How can I add a new column? I used

DF1['Rating']= np.where(DF1['Team']== DF2['Team'],DF2['Rating'],0)

Error : ValueError: Can only compare identically-labeled Series objects Thanks ZEP

Upvotes: 2

Views: 274

Answers (3)

宋殊同
宋殊同

Reputation: 91

i think you can use pd.merge

DF1=pd.merge(DF1,DF2,how='left',on='Team')
DF1

  Team Rating
0   A   1
1   B   2
2   C   3
3   D   4
4   E   5
5   A   1
6   C   3
7   B   2
8   B   2

Upvotes: 1

llllllllll
llllllllll

Reputation: 16424

You can use:

In [54]: DF1['new_col'] = DF1.Team.map(DF2.set_index('Team').Rating)

In [55]: DF1
Out[55]: 
  Team  new_col
0    A        1
1    B        2
2    C        3
3    D        4
4    E        5
5    A        1
6    C        3
7    B        2
8    B        2

Upvotes: 1

jezrael
jezrael

Reputation: 863166

I think need map by Series created with set_index and if not match get NaNs, so fillna was added for replace to 0:

DF1['Rating']= DF1['Team'].map(DF2.set_index('Team')['Rating']).fillna(0)
print (DF1)
  Team  Rating
0    A       1
1    B       2
2    C       3
3    D       4
4    E       5
5    A       1
6    C       3
7    B       2
8    B       2

DF = ['A','B','C','D','E','A','C','B','B', 'G']
DF1 = pd.DataFrame({'Team':DF})

DF2 = pd.DataFrame({'Team':['A','B','C','D','E'],'Rating':[1,2,3,4,5]})

DF1['Rating']= DF1['Team'].map(DF2.set_index('Team')['Rating']).fillna(0)
print (DF1)
  Team  Rating
0    A     1.0
1    B     2.0
2    C     3.0
3    D     4.0
4    E     5.0
5    A     1.0
6    C     3.0
7    B     2.0
8    B     2.0
9    G     0.0 <- G not in DF2['Team']

Detail:

print (DF1['Team'].map(DF2.set_index('Team')['Rating']))
0    1.0
1    2.0
2    3.0
3    4.0
4    5.0
5    1.0
6    3.0
7    2.0
8    2.0
9    NaN
Name: Team, dtype: float64

Upvotes: 2

Related Questions