Use DataFrame values inside .loc[]

user_15649322 0
Viewed 63

I want to filter a DataFrame in this way, base on dictionary values.

dict = { 
           101 : 2500,
           102 : 2700,
           103 : 2000,
}

And a DataFrame in this way:

idx match_id type_name second
1 101 Pass 2400
2 101 Shot 2450
3 101 Match_end 2500
4 102 Pass 2700
5 102 Match_end 2600
6 103 Match_end 2000

I want a line of code that returns me the match_id values where type_name==Match_end and second is equal to the value inside the dictionary where the key has the same value of the match_id.

In this case I'd like to returns this list : [101,103]

Because, inside the DataFrame, the 3 and 6 rows respects the conditions (5 no because its second value is not the same of dict.get(102)).

I tried to use this code but without success because with loc I'm not able to use the relative index:

list = list(
           df.loc[
               (df["type_name"]=="Match_end")
               & (df["second"] == dict.get(df["match_id"]))
           ]["match_id"].values
       )

I need something inside the second condition which help me to use the dictionary based on match_id values of each row.

Does someone has some suggests to do this thing (with or without loc)?

NB I KNOW HOW TO DO THIS USING A FOR CICLE OVER "match_id" BUT I'M LOOKING FOR A METHOD TO DO THIS WITHOUT USE A FOR LOOP.

Thanks

pandas computer-science coding-style optimization python
2 Answers

Use Series.map and compare by second column:

d = {  101 : 2500,102 : 2700, 103 : 2000}

print (df['match_id'].map(d))
0    2500
1    2500
2    2500
3    2700
4    2700
5    2000
Name: match_id, dtype: int64

L = df.loc[df['match_id'].map(d).eq(df['second']) & 
           (df["type_name"]=="Match_end"), 'match_id'].tolist()
print (L)
[101, 103]
user_29010020

If you make your dictionary as a dataframe (df1), you can manage by joining the dataframes with merge().

df1 = pd.DataFrame({'match_id':[101, 102, 103], 'second':[2500, 2700, 2000]})
df2 = pd.DataFrame({'match_id':[101, 101, 101, 102, 102, 103], 'type_name': ['Pass', 'Shot', 'Match_end', 'Pass', 'Match_end', 'Match_end'], 'second':[2400, 2450, 2500, 2700, 2600, 2000]})

Take only the rows from df2 where type_name == 'Match_end' as you are not interested of the others. Then drop that type_name column as it is not needed later.

df2 = df2.loc[df2.type_name == 'Match_end']
df2.drop('type_name', axis=1, inplace=True)

Merge the two dataframes and you get the list that you want.

df = df1.merge(df2)
print(df.match_id.tolist())

[101, 103]
user_157379180
Related