Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is correct syntax to swap column values for selected rows in a pandas data frame using just one line?

Tags:

python

pandas

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?

like image 704
stachyra Avatar asked Sep 11 '14 16:09

stachyra


People also ask

How do I swap rows and columns in pandas?

Pandas DataFrame. transpose() is a library function that transpose index and columns. The transpose reflects the DataFrame over its main diagonal by writing rows as columns and vice-versa. Use the T attribute or the transpose() method to swap (= transpose) the rows and columns of DataFrame.

How do I change the value of a column in pandas DataFrame?

In order to replace a value in Pandas DataFrame, use the replace() method with the column the from and to values.


3 Answers

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
like image 161
DSM Avatar answered Oct 16 '22 21:10

DSM


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
like image 11
JohnE Avatar answered Oct 16 '22 19:10

JohnE


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
like image 7
Bharath Avatar answered Oct 16 '22 21:10

Bharath