CODEWITHSUNDEEP
pythonpandasdataframedata-cleaning
EmKhan
EmKhan

Reputation: 21

Shift the values in a record by a column.

I have a dataframe, where one of the observations has mismatched columns. Kind of like this : 

  Names    Age   Dept
0  John     21  sales
1   Joe     22     IT
2   Ann     20     IT
3    24  sales    NaN

I want to shift the values, to the next column and assign NaN on the first column which actually has the missing value.

   Names  Age   Dept
0  John   21  sales
1   Joe   22     IT
2   Ann   20     IT
3   NaN   24  sales

I searched a lot, but did not find a method to do this. I feel there should be an inbuilt method to do it in a simple way.

I am a newbie to programming, so sorry if it is a basic question.

Upvotes: 2

Views: 44

Answers (2)

U13-Forward
U13-Forward

Reputation: 71600

Or use a df[...]=df[...] structure:

df[df['Dept'].isnull()]=df[df['Dept'].isnull()].shift(axis=1)

And now:

print(df)

Is:

  Names Age   Dept
0  John  21  sales
1   Joe  22     IT
2   Ann  20     IT
3   NaN  24  sales

shift(axis=1) is shifting row by row, and if you remove axis=1 part, shift() it will shift column by column.

Upvotes: 1

jezrael
jezrael

Reputation: 863156

Create boolean mask by to_numeric with notna or function isnumeric for find problematic rows and then shift only filtered rows:

m = pd.to_numeric(df['Names'], errors='coerce').notna()
#alternative
#m = df['Names'].str.isnumeric()

df[m] = df[m].shift(axis=1)
print (df)
  Names Age   Dept
0  John  21  sales
1   Joe  22     IT
2   Ann  20     IT
3   NaN  24  sales

Upvotes: 1

Related Questions