Shift rows in pandas df where equal to specific value

Question

I'm not sure which is the most efficient process to achieve this question so I'll be pretty broad. I want to shift and combine a row where == to a specific value. For the df below I want to shift rows up where value is == to X. But I want to combine it with the string above, not overwrite it.

Note: The row I want to shift up is every 14th row. So it may be easier to select every nth row and shift up?

df = pd.DataFrame({
    'Value' : ['Foo','X','00:00','00:00','29:00','30:00','00:00','02:00','15:00','20:00','10:00','15:00','20:00','25:00'],                 
    'Number' : [00,0,1,2,3,4,5,6,7,8,9,10,11,12],                      
    })

val = ['X']

a = df[df.isin(val)].shift(-1)

df[df.isin(val)] = np.nan

out_df = a.combine_first(df)

Out:

    Value  Number
0       X     0.0
1     NaN     0.0
2   00:00     1.0
3   00:00     2.0
4   29:00     3.0
5   30:00     4.0
6   00:00     5.0
7   02:00     6.0
8   15:00     7.0
9   20:00     8.0
10  10:00     9.0
11  15:00    10.0
12  20:00    11.0
13  25:00    12.0

Intended Output:

    Value  Number
0   Foo X     0.0
2   00:00     1.0
3   00:00     2.0
4   29:00     3.0
5   30:00     4.0
6   00:00     5.0
7   02:00     6.0
8   15:00     7.0
9   20:00     8.0
10  10:00     9.0
11  15:00    10.0
12  20:00    11.0
13  25:00    12.0

Scott Boston · Accepted Answer · 2019-09-10 02:07:49Z

2

You can try something like this:

df.groupby((df['Value'] != val[0]).cumsum())[['Value','Number']].agg({'Value':' '.join, 'Number':'sum'})

Output:

       Value  Number
Value               
1      Foo X       0
2      00:00       1
3      00:00       2
4      29:00       3
5      30:00       4
6      00:00       5
7      02:00       6
8      15:00       7
9      20:00       8
10     10:00       9
11     15:00      10
12     20:00      11
13     25:00      12

answered Sep 10, 2019 at 2:07

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

5 Comments

moys Over a year ago

wow! wonder ful. Can you explain what is happening here .agg({'Value':' '.join, 'Number':'sum'})? what is this aggregate function doing?

Scott Boston Over a year ago

Using string join with a space character to aggregate strings. Try this. ' '.join(['A','B','C'])

moys Over a year ago

got it. I printed out the group object & it is much clear now. Thanks.

Chopin Over a year ago

Thanks @ScottBoston. Is it also possible to pass more strings into val? For e.g. [X,Y,Z]

Scott Boston Over a year ago

Yes. Use isin and the invert operator like this: df.groupby((~df['Value'].isin(['X','y'])).cumsum())[['Value','Number']].agg({'Value':' '.join, 'Number':'sum'})

Collectives™ on Stack Overflow

Shift rows in pandas df where equal to specific value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related