Generating New Dataframe columns from existing rows

Question

I am looking into some basketball data, where I have some dataframe that would look like (for one team only...baby steps)

df = pd.DataFrame({'PlayId':[1,1,1,1,1],'Player':['A','B','C','D','E'],'Ball':[0,0,1,0,0],'Pos':[1, 4, 10, 15, 20 ],'Speed':[1,2,3,4,5]})

I create a column for distance from Ball=1 (generalized to many PlayId) :

df['DistanceToBall'] = np.abs(df.Pos-df.Pos[df.groupby('PlayId')['Ball'].transform('idxmax')].reset_index(drop=True))

Next I want to make this into a single row that contains the information of Ball = 1

newdf = df.loc[df.Ball==1,:]

Now I want to add columns about the information of Pos and Speed based on DistanceToBall. My new columns would be closest1,closest2,closest3,closest4 which would have values of their position, so in order(15,4,1,20). I am unsure of how to do that, especially in the case where I have many different 'PlayId'.

EDIT: Expected Output:

 PlayId Player  Ball    Pos Speed   DistanceToBall  closest1    closest2    closest3    closest4    speed1  speed2  speed3  speed4
2   1   C   1   10  3   0   15  4   1   20  4   2   1   5

I edited my answer based on your edit. Let me know if it works. — Nicolas Gervais
– Nicolas Gervais, Commented Nov 19, 2019 at 15:16

Nicolas Gervais · Accepted Answer · 2019-11-19 15:11:37Z

1

This will append to newdf the 4 closest players' Pos, sorted by DistanceToBall:

for i in range(4):
    newdf.loc[:, 'closest{}'.format(i+1)] = \
        df.sort_values(by='DistanceToBall')['Pos'].values[i]
for i in range(4):
    newdf.loc[:, 'speed{}'.format(i + 1)] = \
        df.sort_values(by='DistanceToBall')['Speed'].values[i]

Out[22]: 
   PlayId Player  Ball  Pos  Speed  ...  closest4  speed1  speed2  speed3  speed4
2       1      C     1   10      3  ...       1.0     3.0     4.0     2.0     1.0

edited Nov 19, 2019 at 15:11

answered Nov 19, 2019 at 15:01

Nicolas Gervais

36.9k23 gold badges123 silver badges160 bronze badges

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

1 Comment

yankeefan11 Over a year ago

So this definitely works for this case. (You could also just use 1 loop I believe). I am curious how to implement groupby (without looping for time) in the sort_values part

Collectives™ on Stack Overflow

Generating New Dataframe columns from existing rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related