Subset data in python using another dataframe

Question

I have a dataframe

 x_date    country1 country2    data
2020-01-01  France  Denmark 993.740
2020-01-01  France  Sweden  692.716
2020-01-01  Germany Denmark 2297.989
2020-01-01  Germany Sweden  1610.735
2020-01-02  France  Denmark 942.228
2020-01-02  France  Sweden  818.425
2020-01-02  Germany Denmark 1998.913

I want to select only a few combination of country1 & country2 . Having said that I have prepared a mapping dataframe

Map = pd.DataFrame({
"country1": ["France","France","France","France","France","France",

                    "Germany","Germany","Germany","Germany","Germany","Germany"],
"country2": [None,None,"Denmark",None,None,None , #France

               None,None,None,"Sweden",None,None]}) #Germany

I want to select France(country1) & Denmark(country2) & Germany & Sweden. In order to do so I used the following code :

data_Jan20_Apr20_V2=data_Jan20_Apr20_V2.loc[data_Jan20_Apr20_V2.country1.isin(Map['country1']) & data_Jan20_Apr20_V2.country2.isin(Map['country2']) ]

But this gives me both Sweden & Denmark for France & Germany which defeats the purpose. Can someone help with the right code to achieve the same ?

can you print your expected output if pos ?

Umar.H
– Umar.H

2020-04-22 08:59:34 +00:00
Commented Apr 22, 2020 at 8:59 — Umar.H
– Umar.H, Commented Apr 22, 2020 at 8:59

Serge Ballesta · Accepted Answer · 2020-04-22 09:12:14Z

1

I think that what you want is a merge:

data_Jan20_Apr20_V2.merge(Map, on=['country1', 'country2'])

which gives:

       x_date country1 country2      data
0  2020-01-01   France  Denmark   993.740
1  2020-01-02   France  Denmark   942.228
2  2020-01-01  Germany   Sweden  1610.735

answered Apr 22, 2020 at 9:12

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

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

Collectives™ on Stack Overflow

Subset data in python using another dataframe

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