Cfrgtkky

I have a CSV file of game history for my badminton club. I would like to be able to find out information about games that contain a given player (e.g. who did "Bill" play most with?). Here's an example of what two rounds of three games might look like:

import pandas as pd

player_data = player_data = pd.DataFrame(data=[

('2018-06-12', 1, 1, 1, 'Adam'),

 ('2018-06-12', 1, 1, 2, 'Bill'),

 ('2018-06-12', 1, 1, 3, 'Cindy'),

 ('2018-06-12', 1, 1, 4, 'Derek'),

 ('2018-06-12', 1, 2, 1, 'Edward'),

 ('2018-06-12', 1, 2, 2, 'Fred'),

 ('2018-06-12', 1, 2, 3, 'George'),

 ('2018-06-12', 1, 2, 4, 'Harry'),

 ('2018-06-12', 1, 3, 1, 'Ian'),

 ('2018-06-12', 1, 3, 2, 'Jack'),

 ('2018-06-12', 1, 3, 3, 'Karl'),

 ('2018-06-12', 1, 3, 4, 'Laura'),

 ('2018-06-12', 2, 1, 1, 'Karl'),

 ('2018-06-12', 2, 1, 2, 'Cindy'),

 ('2018-06-12', 2, 1, 3, 'Bill'),

 ('2018-06-12', 2, 1, 4, 'Derek'),

 ('2018-06-12', 2, 2, 1, 'Max'),

 ('2018-06-12', 2, 2, 2, 'George'),

('2018-06-12', 2, 2, 3, 'Fred'),

 ('2018-06-12', 2, 2, 4, 'Ian'),

 ('2018-06-12', 2, 3, 1, 'Nigel'),

 ('2018-06-12', 3, 3, 2, 'Edward'),

 ('2018-06-12', 3, 3, 3, 'Harry'),

 ('2018-06-12', 3, 3, 4, 'Adam')],   

columns=['Date', 'Round #', 'Court #', 'Space', 'Name'])

However, as each row is an individual player's entry, simply locating by name, e.g.

player_data.loc[player_data['Name'] == 'Bill']

is just going to return only Bill's individual entries, like so:

    Date    Round # Court # Space   Name



    1 2018-06-12    1   1   2   Bill

    14 2018-06-12   2   1   3   Bill

... when what I want is a new dataframe that contains ALL entries of games that Bill has played in, such that in this case it would display as:

Date    Round # Court # Space   Name

0   2018-06-12  1   1   1   Adam

1   2018-06-12  1   1   2   Bill

2   2018-06-12  1   1   3   Cindy

3   2018-06-12  1   1   4   Derek

12  2018-06-12  2   1   1   Karl

13  2018-06-12  2   1   2   Cindy

14  2018-06-12  2   1   3   Bill

15  2018-06-12  2   1   4   Derek

I'm thinking it might be easier to convert the original dataframe to one where each entry is a separate game with all the player names for that game listed in a tuple, so then it'd be relatively simple to check "if name in Names"? e.g.

Date    Round # Court # Names

    0   2018-06-12  1   1   (Adam, Bill, Cindy, Derek)

... but maybe that'd cause other problems.

After the filter using merge

s1=player_data.loc[player_data['Name'] == 'Bill',['Date','Round #','Court #']]

s2=s1.merge(player_data,how='left')

s2

Out[12]: 

         Date  Round #  Court #  Space   Name

0  2018-06-12        1        1      1   Adam

1  2018-06-12        1        1      2   Bill

2  2018-06-12        1        1      3  Cindy

3  2018-06-12        1        1      4  Derek

4  2018-06-12        2        1      1   Karl

5  2018-06-12        2        1      2  Cindy

6  2018-06-12        2        1      3   Bill

7  2018-06-12        2        1      4  Derek

My method for this is :

bill_player_data = player_data.loc[player_data['Name'] == 'Bill']

ro = bill_player_data['Round #']

co = bill_player_data['Court #']

bill = player_data.loc[player_data['Round #'].isin(ro)]

bill = bill.loc[bill['Court #'].isin(co)]

bill