From R to Python: define multiple columns from multiple columns in a pandas dataframe

Question

As an R user, I can manipulate columns in a data.table to derive a set of new columns, what is the best way to achieve this with pandas datafframes?

Here is a reproducible example (I am using R 3.2.5 and Python 3.6):

R code:

library(data.table)

df = data.table(iris)
df[,.(ratio1 = Sepal.Length/Sepal.Width, ratio2 = Petal.Length/Petal.Width)]


df[,.(ratio1 = Sepal.Length/Sepal.Width, ratio2 = Petal.Length/Petal.Width)]

The last command will return:

> df[,.(ratio1 = Sepal.Length/Sepal.Width, ratio2 = Petal.Length/Petal.Width)]
       ratio1   ratio2
  1: 1.457143 7.000000
  2: 1.633333 7.000000
  3: 1.468750 6.500000
  4: 1.483871 7.500000
  5: 1.388889 7.000000
 ---                  
146: 2.233333 2.260870
147: 2.520000 2.631579
148: 2.166667 2.600000
149: 1.823529 2.347826
150: 1.966667 2.833333

Python code:

import pandas as pd
from sklearn.datasets import load_iris

iris = load_iris()
df = pd.DataFrame(iris.data, columns=iris.feature_names)

pd.DataFrame(list(df.apply(lambda x: {'ratio1':x['sepal length (cm)']/x['sepal width (cm)'], 'ratio2':x['petal length (cm)']/x['petal width (cm)']}, axis=1)))

The last command will return:

In[6]: pd.DataFrame(list(df.apply(lambda x: {'ratio1':x['sepal length (cm)']/x['sepal width (cm)'], 'ratio2':x['petal length (cm)']/x['petal width (cm)']}, axis=1)))
Out[6]: 
       ratio1     ratio2
0    1.457143   7.000000
1    1.633333   7.000000
2    1.468750   6.500000
3    1.483871   7.500000
4    1.388889   7.000000
5    1.384615   4.250000

Here is my question: My Python implementation strikes me as inefficient. I am computing a series of dictionaries, projecting it to a list and then calling the DataFrame constructor. In other words, it's not a direct manipulation from dataframes to dataframes. This translates in verbose code: the last line of the R snippet is 76 characters, the last line of the Python one is 158.

Is there a better way to do this? Thanks!

P.S. Note that I don't want to add permanently the derived columns (ratio1, ratio2 in the example) to the original dataset. I want to compute something on the fly and plot it or aggregate it without mutating the data.

score 2 · Answer 1 · answered Apr 30 '19 at 15:41

You don't need the list() or apply() methods:

import pandas as pd
from sklearn.datasets import load_iris

iris = load_iris()
df = pd.DataFrame(iris.data, columns=iris.feature_names)

pd.DataFrame({"ratio1": df['sepal length (cm)']/df['sepal width (cm)'], "ratio2": df['petal length (cm)']/df['petal width (cm)']})

If you instead wanted the variables added to the original dataset, you could use the assign() method.

score 0 · Accepted Answer · answered May 03 '19 at 16:26

Just a little known gem to get stuff like this done, try out the following:

import pandas as pd
from sklearn.datasets import load_iris

iris = load_iris()
df = pd.DataFrame(iris.data, columns=iris.feature_names)

# replace spaces and parentheses in column names:
df.columns = [col.replace(" (cm)", "").replace(" ", "_") for col in df.columns]

Now, use the multiline evaluation provided by pandas eval method:

df.eval("""ratio1 = sepal_length/sepal_width
           ratio2 = petal_length/petal_width""")

See the documentation here and here.

From R to Python: define multiple columns from multiple columns in a pandas dataframe

2 Answers2