CODEWITHSUNDEEP
pythonpandas
user6016731
user6016731

Reputation: 382

Subset data in python using another dataframe

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 ?

Upvotes: 1

Views: 47

Answers (1)

Serge Ballesta
Serge Ballesta

Reputation: 148890

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

Upvotes: 1

Related Questions