How to concatenate rows if certain columns match, and certain columns are different

user_11598050 0
Viewed 58

So I have a dataframe that looks like this


time    event   nflId_WR    position_WR team_WR x,y_WR    
0   2018-09-07T01:07:18.099Z    pass_forward    2495454.0   WR  away    (80.69, 44.91)    
1   2018-09-07T01:07:18.099Z    pass_forward    2533040.0   WR  away    (82.65, 34.56)
2   2018-09-07T01:07:18.099Z    pass_forward    2552689.0   CB  home    (79.51, 20.0)
3   2018-09-07T01:07:18.099Z    pass_forward    2555383.0   CB  home    (76.53, 44.93)
4   2018-09-07T01:07:19.200Z    pass_arrived    2495454.0   WR  away    (81.11, 47.87)

I am trying to bring the rows where the "position" column is different, to the rows where the "time" column is the same and the "event" is the same.

time    event_WR    nflId_WR    position_WR team_WR x,y_WR    nflId_CB    position_CB    team_CB    x,y_CB
0   2018-09-07T01:07:18.099Z    pass_forward    2495454.0   WR  away    (80.69, 44.91)    2552689.0   CB  home    (79.51, 20.0)
1   2018-09-07T01:07:18.099Z    pass_forward    2533040.0   WR  away    (82.65, 34.56)    2495454.0 WR  away    (81.11, 47.87)

Something like this (Sorry that the column headers don't line up. Not too sure how to format that correctly in here).

Any recommendations on how to do this?

Also, if you know any way to filter before doing this stacking, to see which pairing of CB and WR have the closest "y" points, so that they pair up together, that would be great.

pandas python
1 Answers

I am trying to bring the rows where the "position" column is different, to the rows where the "time" column is the same and the "event" is the same.

You can try something like this:

merged = df.join(df, on=['time', 'event'], rsuffix='_other')
merged = merged[merged.position != merged.position_other]

This will first merge on the two columns, then drop the rows where the positions are the same.

user_35107360
Related