Finding sequential pairs of rows in pandas DataFrame with value matching in two columns

Question

Given a DataFrame like this:

n     name  number  time  
0     foo    0       .1
1     foo    3       .15
2     bar    0       .2
3     bar    2       .3
4     foo    1       .4
5     foo    5       .45
6     bar    3       .5
7     bar    4       .55
8     bar    5       .6
9     bar    1       .7

Make this DataFrame:

n     name  number  time    n     name  number  time
0     foo    0       .1     2     bar    0       .2
1     foo    3       .15    6     bar    3       .5
4     foo    1       .4     9     bar    1       .7
5     foo    5       .45    8     bar    5       .6

I've hacked together a solution using using shift that works if the data appears like this:

n     name  number  time  
0     foo    0       .1
1     bar    0       .15
2     foo    1       .2
3     bar    2       .3
4     foo    3       .4
5     bar    5       .5

But I can't guarantee that the original data interleaves 'foo' and 'bar.' I need to be able to get pairs any distance apart.

BENY · Accepted Answer · 2020-03-11 15:30:28Z

1

IIUC still need groupby then concat

df=pd.concat([y.reset_index(drop=True).set_index('number') for x , y in df.groupby('name')],axis=1, join='inner').reset_index()
Out[322]: 
   number  n name  time  n name  time
0       0  2  bar   0.2  0  foo  0.10
1       3  6  bar   0.5  1  foo  0.15
2       5  8  bar   0.6  5  foo  0.45
3       1  9  bar   0.7  4  foo  0.40

answered Mar 11, 2020 at 15:30

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

Finding sequential pairs of rows in pandas DataFrame with value matching in two columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related