What is the best way to write a function to calculate row elements in panda?
I have a base table like this :
col1 is a list of independent values, and col2 is an aggregation based on the combination of Country and Type. I want to calculate the columns col3 to col5: using the following logic
- col3: The proportion of an element in col1 to the sum of col1
- col4: The ratio of an element in col1 to the corresponding element in col2
- col5: The natural exponent of the product of the elements in col3 and col4
I wrote a function like the following to achieve this:
def calculate(df):
for i in range(len(df)):
df['col3'].loc[i] = df['col1'].loc[i]/sum(df['col1'])
df['col4'].loc[i] = df['col1'].loc[i]/df['col2'].loc[i]
df['col5'].loc[i] = np.exp(df['col3'].loc[i]*df['col4'].loc[i])
return df
This function executes and gives the expected result, but the notebook also throws a warning:
SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame
See the caveats in the documentation:
http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
I’m not sure if the features I’m writing about here are the best. Any help would be appreciated! Thank you.
Solution
I think it’s
better to avoid using apply
and loops in pandas, so it’s better and faster to use a vectorized solution :
df = pd. DataFrame({'col1':[4,5,4,5,5,4],
'col2':[7,8,9,4,2,3],
'col3':[1,3,5,7,1,0],
'col4':[5,3,6,9,2,4],
'col5':[1,4,3,4,0,4]})
print (df)
col1 col2 col3 col4 col5
0 4 7 1 5 1
1 5 8 3 3 4
2 4 9 5 6 3
3 5 4 7 9 4
4 5 2 1 2 0
5 4 3 0 4 4
df['col3'] = df['col1']/(df['col1']).sum()
df['col4'] = df['col1']/df['col2']
df['col5'] = np.exp(df['col3']*df['col4'])
print (df)
col1 col2 col3 col4 col5
0 4 7 0.148148 0.571429 1.088343
1 5 8 0.185185 0.625000 1.122705
2 4 9 0.148148 0.444444 1.068060
3 5 4 0.185185 1.250000 1.260466
4 5 2 0.185185 2.500000 1.588774
5 4 3 0.148148 1.333333 1.218391
Time:
df = pd. DataFrame({'col1':[4,5,4,5,5,4],
'col2':[7,8,9,4,2,3],
'col3':[1,3,5,7,1,0],
'col4':[5,3,6,9,2,4],
'col5':[1,4,3,4,0,4]})
#print (df)
#6000 rows
df = pd.concat([df] * 1000, ignore_index=True)
In [211]: %%timeit
...: df['col3'] = df['col1']/(df['col1']).sum()
...: df['col4'] = df['col1']/df['col2']
...: df['col5'] = np.exp(df['col3']*df['col4'])
...:
1.49 ms ± 104 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
Unfortunately, the circular solution for this example is really slow, so it was only tested in a 60-row
DataFrame:
#60 rows
df = pd.concat([df] * 10, ignore_index=True)
In [3]: %%timeit
...: (calculate(df))
...:
C:\Anaconda3\lib\site-packages\pandas\core\indexing.py:194: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame
See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
self._setitem_with_indexer(indexer, value)
10.2 s ± 410 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)