How to shift rows up in Pandas Dataframe based on specific column

Question

How do I shift up all the values in a row for one specific column without affecting the order of the other columns?

For example, let's say i have the following code:

import pandas as pd
data= {'ColA':["A","B","C"],
        'ColB':[0,1,2],
        'ColC':["First","Second","Third"]}

df = pd.DataFrame(data)

print(df)

I would see the following output:

  ColA  ColB    ColC
0    A     0   First
1    B     1  Second
2    C     2   Third

In my case I want to verify that Column B does not have any 0s and if so, it is removed and all the other values below it get pushed up, and the order of the other columns are not affected. Presumably, I would then see the following:

  ColA  ColB    ColC
0    A   1     First
1    B   2    Second
2    C   NaN   Third

I can't figure out how to do this using either the drop() or shift() methods.

Thank you

BENY · Accepted Answer · 2019-11-18 17:59:57Z

2

Let us do simple sorted

invalid=0
df['ColX']=sorted(df.ColB,key=lambda x : x==invalid)
df.ColX=df.ColX.mask(df.ColX==invalid)
df
Out[351]: 
  ColA  ColB    ColC  ColX
0    A     0   First   1.0
1    B     1  Second   2.0
2    C     2   Third   NaN

answered Nov 18, 2019 at 17:59

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

yatu · Accepted Answer · 2019-11-18 17:46:29Z

1

The way I'd do this IIUC is to filter out the values in ColB which are not 0, and fill the column with these values according to the length of the obtained valid values:

m = df.loc[~df.ColB.eq(0), 'ColB'].values
df['ColB'] = float('nan')
df.loc[:m.size-1, 'ColB'] = m

print(df)

 ColA  ColB    ColC
0    A   1.0   First
1    B   2.0  Second
2    C   NaN   Third

answered Nov 18, 2019 at 17:46

yatu

88.7k12 gold badges93 silver badges148 bronze badges

Comments

Arn · Accepted Answer · 2019-11-18 17:47:19Z

0

You can swap 0s for nans and then move up the rest of the values:

import numpy as np
df.ColB.replace(0, np.nan, inplace=True)
df.assign(ColB=df.ColB.shift(df.ColB.count() - len(df.ColB)))

answered Nov 18, 2019 at 17:47

Arn

2,01516 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to shift rows up in Pandas Dataframe based on specific column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related