Divide two pandas columns of lists by each other

user_9392446 0
Viewed 514

I have a df like this:

col1        col2
[1,3,4,5]   [3,3,6,2]
[1,4,5,5]   [3,8,4,3]
[1,3,4,8]   [8,3,7,2]

Trying to divide the elements in the lists in col1 and col2 together to get what's in the result column:

col1        col2        result
[1,3,4,5]   [3,3,6,2]   [.33,1,.66,2.5]
[1,4,5,5]   [3,8,4,3]   [.33,.5,1.25,1.66]
[1,3,4,8]   [8,3,7,2]   [.33,1,.57,4]

Tried a lot of different approaches - but always get an error.

Attempts:

#attempt1
df['col1'].div(df['col2'], axis=0)

#attempt2
from operator import truediv

for i in df.col1:
     a = np.array(df['col1'])
     for t in df.col2:
         b = np.array(df['col2'])
         x = a/b
         print(x)


#attempt3
for i in df.index:
    a = col1
    b = col2
    x = map(truediv, a, b)

#attempt4
a = col1
b = col2
result = [x/y for x, y in zip(a, b)]
#then apply to df

#attempt5
a = col1
b = col2
result = a/b
print(percent_matched)
#then #apply to df

>>>TypeError: unsupported operand type(s) for /: 'list' and 'list'

Any ideas?

pandas python
4 Answers
  • Use .applymap to convert the columns to np.arrays
  • Then use .div to divide the columns
  • If result must be rounded, tack on .apply(lambda x: np.round(x, 3)), when calculating that column.
    • np.round()
    • df['result'] = df.col1.div(df.col2).apply(lambda x: np.round(x, 3))
import numpy as np
import pandas as pd

data = {'col1': [[1,3,4,5], [1,4,5,5], [1,3,4,8]], 'col2': [[3,3,6,2], [3,8,4,3], [8,3,7,2]]}

df = pd.DataFrame(data)

# convert columns to arrays
df = df.applymap(np.array)

# divide the columns
df['result'] = df.col1.div(df.col2)
user_77588040

You can use list comprehension with apply, this is conditional on both the lists being of same length

df['result'] = df.apply(lambda x: [np.round(x['col1'][i]/x['col2'][i], 2) for i in range(len(x['col1']))], axis = 1)

    col1            col2            result
0   [1, 3, 4, 5]    [3, 3, 6, 2]    [0.33, 1.0, 0.67, 2.5]
1   [1, 4, 5, 5]    [3, 8, 4, 3]    [0.33, 0.5, 1.25, 1.67]
2   [1, 3, 4, 8]    [8, 3, 7, 2]    [0.12, 1.0, 0.57, 4.0]

Edit: As @TrentonMcKinney suggested, this can be done without using LC. This solution capitalized on Numpy's vectorized operations,

df.apply(lambda x: np.round(np.array(x[0]) / np.array(x[1]), 3), axis=1)
user_62873080 
df=df.apply(pd.Series.explode)#
df['result']=(df.col1.div(df.col2))
df.groupby(level=0)['result'].agg(list).reset_index()
user_89869750

If these are large, you're far better off converting these to np.arrays and then doing the divide:

df["col1"] = df["col1"].apply(np.array)
df["col2"] = df["col2"].apply(np.array)

df["output"] = df.col1/df.col2
user_50329410
Related