I am using pandas version 0.14.1 with Python 2.7.5, and I have a data frame with three columns, e.g.:
import pandas as pd
d = {'L': ['left', 'right', 'left', 'right', 'left', 'right'],
'R': ['right', 'left', 'right', 'left', 'right', 'left'],
'VALUE': [-1, 1, -1, 1, -1, 1]}
df = pd.DataFrame(d)
idx = (df['VALUE'] == 1)
results in a data frame which looks like this:
L R VALUE 0 left right -1 1 right left 1 2 left right -1 3 right left 1 4 left right -1 5 right left 1
For rows where VALUE == 1, I would like to swap the contents of the left and right columns, so that all of the “left” values will end up under the “L” column, and the “right” values end up under the “R” column.
Having already defined the idx variable above, I can easily do this in just three more lines, by using a temporary variable as follows:
tmp = df.loc[idx,'L'] df.loc[idx,'L'] = df.loc[idx,'R'] df.loc[idx,'R'] = tmp
however this seems like really clunky and inelegant syntax to me; surely pandas supports something more succinct? I’ve noticed that if I swap the column order in the input to the data frame .loc attribute, then I get the following swapped output:
In [2]: print(df.loc[idx,['R','L']])
R L
1 left right
3 left right
5 left right
This suggests to me that I should be able to implement the same swap as above, by using just the following single line:
df.loc[idx,['L','R']] = df.loc[idx,['R','L']]
However when I actually try this, nothing happens–the columns remain unswapped. It’s as if pandas automatically recognizes that I’ve put the columns in the wrong order on the right hand side of the assignment statement, and it automatically corrects for the problem. Is there a way that I can disable this “column order autocorrection” in pandas assignment statements, in order to implement the swap without creating unnecessary temporary variables?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
One way you could avoid alignment on column names would be to drop down to the underlying array via .values:
In [33]: df
Out[33]:
L R VALUE
0 left right -1
1 right left 1
2 left right -1
3 right left 1
4 left right -1
5 right left 1
In [34]: df.loc[idx,['L','R']] = df.loc[idx,['R','L']].values
In [35]: df
Out[35]:
L R VALUE
0 left right -1
1 left right 1
2 left right -1
3 left right 1
4 left right -1
5 left right 1
Method 2
The key thing to note here is that pandas attempts to automatically align rows and columns using the index and column names. Hence, you need to somehow tell pandas to ignore the column names here. One way is as @DSM does, by converting to a numpy array. Another way is to rename the columns:
>>> df.loc[idx] = df.loc[idx].rename(columns={'R':'L','L':'R'})
L R VALUE
0 left right -1
1 left right 1
2 left right -1
3 left right 1
4 left right -1
5 left right 1
Method 3
You can also do this with np.select and df.where i.e
Option 1: np.select
df[['L','R']] = pd.np.select(df['VALUE'] == 1, df[['R','L']].values, df[['L','R']].values)
Option 2: df.where
df[['L','R']] = df[['R','L']].where(df['VALUE'] == 1, df[['L','R']].values)
Option 3: df.mask
df[['L','R']] = df[['L','R']].mask( df['VALUE'] == 1, df[['R','L']].values)
Output:
L R VALUE 0 left right -1 1 left right 1 2 left right -1 3 left right 1 4 left right -1 5 left right 1
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0