Pandas shift column data upon condition

Question

I have dataframe which look like this.

    Name    Val Rating  
0   ABC     123 B + 
1   DEF     234 B + 
2   567     B-  NaN
3   GHI     890 D

but instead I want to shift the data by checking (col['Name']) to next column (col['Val']) and successively shifting. Also if the shifting happens change the row index value. I want the following dataframe as output.

    Name    Val Rating  
0   ABC     123 B + 
1   DEF     234 B + 
    NaN     567 B - 
2   GHI     890 D

Anybody know how to do this?

Answer 1

You can shift rows by boolean mask:

mask = pd.to_numeric(df['Name'], errors='coerce').notnull()
df[mask] = df[mask].shift(axis=1)
print (df)
  Name  Val Rating
0  ABC  123    B +
1  DEF  234    B +
2  NaN  567     B-
3  GHI  890      D

Detail:

print (pd.to_numeric(df['Name'], errors='coerce'))
0      NaN
1      NaN
2    567.0
3      NaN
Name: Name, dtype: float64

If really need replace index values to empty strings is possible create helper Series and reindex.

But this is not recommended because performance problem and possible some function with this index should failed.

i = df.index[~mask]
df.index = pd.Series(range(len(i)), index=i).reindex(df.index, fill_value='')
print (df)
  Name  Val Rating
0  ABC  123    B +
1  DEF  234    B +
   NaN  567     B-
2  GHI  890      D

Answer 2

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

Output:

  Name  Val Rating
0  ABC  123    B +
1  DEF  234    B +
2  NaN  567     B-
3  GHI  890      D

Edit:

df[df['Rating'].isnull()|df['Name'].isnull()]=df[df['Rating'].isnull()|df['Name'].isnull()].shift(axis=1)
print(df)