CODEWITHSUNDEEP
pythonpandasdataframe
Learner
Learner

Reputation: 682

Shifting cells to up in Pandas dataframe

I have a csv file with some messy data.

I have following dataframe in pandas

Name Age Sex Salary Status
John 32 Nan NaN NaN
Nan Male 4000 Single NaN
May 20 Female 5000 Married
teresa 45

Desired output:

   Name   Age     Sex Salary  Status
0  John   32    Male   4000  Single
1   May   20  Female   5000  Married
2  teresa 45

So Does anyone know how to do it with Pandas?

Upvotes: 2

Views: 217

Answers (2)

U13-Forward
U13-Forward

Reputation: 71620

Try groupby:

>>> df.groupby(df['Name'].notna().cumsum()).apply(lambda x: x.apply(lambda x: next(iter(x.dropna()), np.nan))).reset_index(drop=True)
   Name Age     Sex  Salary   Status
0  John  32    4000  Single      NaN
1   May  20  Female    5000  Married
>>>

Upvotes: 0

mozway
mozway

Reputation: 262594

You can use a bit of numpy magic to drop the NaNs and reshape the underlying array:

a = df.replace({'Nan': float('nan')}).values.flatten()
pd.DataFrame(a[~pd.isna(a)].reshape(-1, len(df.columns)),
             columns=df.columns)

output:

   Name Age     Sex Salary   Status
0  John  32    Male   4000   Single
1   May  20  Female   5000  Married

Upvotes: 1

Related Questions