Pandas Shift Column Data Upon Condition

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 dat

Solution 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

Solution 2:

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

Output:

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

Edit:

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

Solution 3:

Using isdigit:

df[df['Name'].str.isdigit()] = df[df['Name'].str.isdigit()].shift(axis=1)

Output:

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

Solution 4:

first define a function:

import numpy as np

def f1(row):
    if not row.rating:
         row.Rating = row.val
         row.val = row.Name
         row.Name = np.NaN

then use pandas.DataFrame.apply:

df.apply(f1,axis=1)