dagg3r dagg3r - 1 year ago 86
Python Question

Pandas swap columns based on condition

I have a pandas dataframe like the following:

Col1 Col2 Col3
0 A 7 NaN
1 B 16 NaN
1 B 16 15

What I want to do is to swap Col2 with Col3 where the value of Col3 is
. Based on other posts and answers on SO, I have this code so far:

df[['Col2', 'Col3']] = df[['Col3', 'Col2']].where(df[['Col3']].isnull())

But this does not seem to be working properly and gives me the following:

Col1 Col2 Col3
0 A NaN NaN
1 B NaN NaN
1 B NaN NaN

Is there something that I might be missing here?

Update: My desired output looks like:

Col1 Col2 Col3
0 A NaN 7
1 B NaN 16
1 B 16 15


Answer Source

You can use loc to do the swap:

df.loc[df['Col3'].isnull(), ['Col2', 'Col3']] = df.loc[df['Col3'].isnull(), ['Col3', 'Col2']].values

Note that .values is required to make sure the swap is done properly, otherwise Pandas would try to align based on index and column names, and no swap would occur.

You can also just reassign each row individually, if you feel the code is cleaner:

null_idx = df['Col3'].isnull()
df.loc[null_idx, 'Col3'] = df['Col2']
df.loc[null_idx, 'Col2'] = np.nan

The resulting output:

  Col1  Col2  Col3
0    A   NaN   7.0
1    B   NaN  16.0
2    B  16.0  15.0