pandas certain rows dataframe merging

Question

print(ScimEn.head(20))
    Rank             Country
0      1               China
1      2       United States
2      3               Japan
3      4      United Kingdom
4      5  Russian Federation
5      6              Canada
6      7             Germany
7      8               India
8      9              France
9     10         South Korea
10    11               Italy
11    12               Spain
12    13                Iran
13    14           Australia
14    15              Brazil
15    16              Taiwan
16    17              Turkey
17    18              Norway
18    19         Netherlands
19    20              Sweden

I would like to merge this DF to another but only rows which are ranked [0:15]. If I do regular:

dfs = [ScimEn, energy, GDP[year].reset_index()]
newdf = reduce(lambda left, right: pd.merge(left, right, on='Country'), dfs)
newdf = newdf.set_index('Country')
print(len(newdf))

I have a DF of 140 entries, but I would like to have a DF of 15 entries per ranking mentioned above. I had also tried:

 dfs = [ScimEn.where(ScimEn['Rank'] < 15), energy, GDP[year].reset_index()]
newdf = reduce(lambda left, right: pd.merge(left, right, on='Country'), dfs)
newdf = newdf.set_index('Country')
print(len(newdf))

But it returned 4 entries only.

You could slice the ScimEn dataframe to what you need first then merge normally. ScimEn[ScimEn['Rank'] < 15] will return your needed df. — Phung Duy Phong
– Phung Duy Phong, Commented May 12, 2017 at 9:07

Community · Accepted Answer · 2017-05-23 12:34:25Z

1

I think you can filter by query:

dfs = [ScimEn.query("Rank < 15"), energy, GDP[year].reset_index()]
newdf = reduce(lambda left, right: pd.merge(left, right, on='Country'), dfs)
newdf = newdf.set_index('Country')
print(len(newdf))

Or by boolean indexing as mentioned Phong Phung comment:

dfs = [ScimEn[ScimEn['Rank'] < 15], energy, GDP[year].reset_index()]
newdf = reduce(lambda left, right: pd.merge(left, right, on='Country'), dfs)
newdf = newdf.set_index('Country')
print(len(newdf))

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered May 12, 2017 at 9:14

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pandas certain rows dataframe merging

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related