CODEWITHSUNDEEP
pythonpandas
user9410826
user9410826

Reputation:

Shift rows in a pandas df

I am trying to shift values in a pandas df. Specifically, rows that follow a specific value. For the df below I want to shift the row to the left when the above value is == 'X'.

d = ({
    'A' : ['Foo','','Foo',''],           
    'B' : ['X','Bar','X','Bar'],
    'C' : ['','Y','','Y'],
    })

df = pd.DataFrame(data=d)

Out:

     A    B  C
0  Foo    X   
1       Bar  Y
2  Foo    X   
3       Bar  Y

I have tried this:

b = ['X']
x = df.B=='X'
df.loc[x, b] = df.loc[x, b].apply(lambda x: x.shift(-1), axis=1)

Intended Output:

     A  B C
0  Foo  X  
1  Bar  Y  
2  Foo  X  
3  Bar  Y

Whilst these strings are identical my dataset varies. Foo will be the same but the string that follows X will vary. So I can't just select Bar and shift row left. I need something that finds the row below X and shifts left

Upvotes: 1

Views: 1861

Answers (3)

jpp
jpp

Reputation: 164623

If you just want to get rid of empty values, you can use a list comprehension and then feed to the pd.DataFrame constructor:

res = pd.DataFrame([list(filter(None, row)) for row in df.values.tolist()],
                   columns=df.columns[:2])

print(res)

     A  B
0  Foo  X
1  Bar  Y
2  Foo  X
3  Bar  Y

Upvotes: 1

BENY
BENY

Reputation: 323226

You can do 

df.replace('',np.nan).apply(lambda x: sorted(x, key=pd.isnull), 1).fillna('')
Out[538]: 
     A  B C
0  Foo  X  
1  Bar  Y  
2  Foo  X  
3  Bar  Y

Upvotes: 1

rafaelc
rafaelc

Reputation: 59274

IIUC

s = (df == 'X').any(1)
idx_to_change = s[s].index+1
df.loc[idx_to_change,:] = df.loc[idx_to_change].shift(-1,axis=1)

df.fillna('')

    A   B   C
0   Foo X   
1   Bar Y
2   Foo X   
3   Bar Y

Upvotes: 2

Related Questions