Python / Pandas: Dataframe multiple filters

Question

I have the following dataframe which is list of races and results.

      Date       R   H   Fin  Win
0     11182017   1   1   2     0 
1     11182017   1   2   1     5   
2     11182017   1   3   3     0   
3     11182017   2   1   2     0   
4     11182017   2   2   1     10   
5     11182017   3   1   1     6    
6     11182017   3   2   2     0

I want to be able to filter first by race (R) then by horse (H) and return the results.

For the dataset above when R = 1 I want to see only H 1 & 2 (ie filter out H 3 for R 1). For R = 2 I want to to see only H 1 (ie filter out H 2 for R 2) and for R =3 I only want to see H 2 (ie filter out H 1 for R 3). I would accomplish this by setting a variable preferably via a prompt. Essentially I am trying to "scratch" certain horses from a particular race

Results Example below

      Date       R   H   Fin  Win
0     11182017   1   1   2     0 
1     11182017   1   2   1     5    
3     11182017   2   1   2     0   
6     11182017   3   2   2     0

I assume R is race. But what is Horse? Couldn't see the why # 1 and 2 would be returned for R=1 and #1 for R=2 — rafaelc
– rafaelc, Commented Apr 29, 2018 at 13:08
Updated my question and clarified a bit, hope that helps! Thanks — Derek
– Derek, Commented Apr 29, 2018 at 13:15
Well I am looking to be dynamic, ie the change the variable set or prompt result and filter on the criteria. But yes I know but also want to change it. — Derek
– Derek, Commented Apr 29, 2018 at 13:19
So you want the user to input a value for R and for H and you'd return the new data frame? — rafaelc
– rafaelc, Commented Apr 29, 2018 at 13:22
Essentially yes for a set of races, I want to be able to remove Horses BY Race. So i want to be able to say for Race 1 remove horse 3. So first question is what race are you interested and and the for that race what do you want to remove. Don't need to do this by prompt per say but by variables that could be edited before executed the script. — Derek
– Derek, Commented Apr 29, 2018 at 13:32

DJK · Accepted Answer · 2018-04-29 17:46:51Z

1

A dictionary is an option for storing races and horse in that you would not need to create unnecessary variables every time you run a new filtering setup, here I use a dictionary with keys = races and values = list of horses

d = {1:[1,2],2:[1]}

Now you can use the query function, which accepts a query string. This can be made by iterating over the dictionary

query_str = ' | '.join(['((R == {x}) and (H in {y}))'.format(x=x,y=y) for x,y in d.items()])

The string generated with list comp would look like this in this example

((R == 1) and (H in [1, 2])) | ((R == 2) and (H in [1]))

Now you can run

df.query(query_str)

and get

       Date  Fin  H  R  Win
0  11182017    2  1  1    0
1  11182017    1  2  1    5
3  11182017    2  1  2    0

Additional Notes on User input to create dictionary

Code

d = {}
x = input('Add races ')
for i in x:
    d[i] = list(input('Add Horses for Race: {} '.format(i)))
print d

Sample Run

Add races 1,2,3
Add Horses for Race: 1 1,2
Add Horses for Race: 2 2,3
Add Horses for Race: 3 3,4
{1: [1, 2], 2: [2, 3], 3: [3, 4]}

edited Apr 29, 2018 at 17:46

answered Apr 29, 2018 at 15:26

DJK

9,3424 gold badges28 silver badges41 bronze badges

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

2 Comments

Derek Over a year ago

I do like this option, thank you for this. This may be a different question but is there a way to build the dictionary via the user prompts as in the answer by @RafaelC below?

DJK Over a year ago

Sure, I added the info to the question. I did not cover it because you had stated in the comments that you did not need that. If this is something you are just running yourself I would avoid user inputs, to avoid writing code for exception handling. Also I felt that @RafaelC covered inputs sufficiently in his answer

rafaelc · Accepted Answer · 2018-04-29 13:44:14Z

0

IIUC, you want someone to input R and H to get values... You can use input in python3.

races = input("Enter races: ").split(",")
ind = []
for race in races:
    sub = df[df["R"] == int(race)]
    horses = input("Enter horses to show for race {0}: ".format(race)).split(",")
    rows = sub.H.isin(horses)
    ind.extend(rows[rows].index.values)

print(df[df.index.isin(ind)])

Example:

Enter races: 1,2
Enter horses for race 1: 1,2
Enter horses for race 2: 2
   R  H
0  1  1
1  1  2
4  2  2

answered Apr 29, 2018 at 13:44

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

Collectives™ on Stack Overflow

Python / Pandas: Dataframe multiple filters

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related