Please consider the following df:
import pandas as pd
data = {'year': [2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011],
'bfsId': [1, 1, 1, 1, 2, 2, 2, 2, 1, 1, 1, 1, 2, 2, 2, 2],
'income': [15000, 20000, 25000, 30000, 15000, 20000, 25000, 30000, 15000, 20000, 25000, 30000, 15000, 20000, 25000, 30000],
'taxes_perc': [0.74, 1.715, 3.192, 4.09, 0.813333333, 1.905, 3.548, 4.543333333, 0.753333333, 1.775, 3.308, 4.183333333, 0.813333333, 1.94, 3.608, 4.563333333],
'perc_inc': [17375, 23625, 33875, 33875, 17375, 23625, 33875, 33875, 17375, 23500, 33625, 33625, 17375, 23500, 33625, 33625]
}
df = pd.DataFrame(data)
I want to apply scipy.interpolate.interp1d
for each year and bfsId separately.
I came up with a loop which does what I intend.
Unfortunately, performance seems to be rather poor.
The problem is that in my real data I have more than 20 years and more than 2000 bfsIds (moreover, I have about 20 datasets).
This is my loop:
import scipy.interpolate
df_interpol = pd.DataFrame()
for j in range(2010, 2012):
df_jahr = df[(df.year == j)]
for i in df_jahr.bfsId.unique():
df_jahr_gem = df_jahr[df_jahr.bfsId == i].copy()
y = df_jahr_gem.taxes_perc
x = df_jahr_gem.income
y_interp = scipy.interpolate.interp1d(x, y, fill_value="extrapolate")
df_jahr_gem['tax_rate_interpol'] = pd.Series(y_interp(df_jahr_gem.perc_inc)).values
df_interpol = df_interpol.append(df_jahr_gem)
Any ideas how to rewrite the code (perhaps with groupby
and by using a function, but I was not able to implement it).
CodePudding user response:
You can use groupby
and apply
methods.
Code:
import pandas as pd
import scipy.interpolate
# Create a sample dataframe
df = pd.DataFrame({'year': [2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011], 'bfsId': [1, 1, 1, 1, 2, 2, 2, 2, 1, 1, 1, 1, 2, 2, 2, 2], 'income': [15000, 20000, 25000, 30000, 15000, 20000, 25000, 30000, 15000, 20000, 25000, 30000, 15000, 20000, 25000, 30000], 'taxes_perc': [0.74, 1.715, 3.192, 4.09, 0.813333333, 1.905, 3.548, 4.543333333, 0.753333333, 1.775, 3.308, 4.183333333, 0.813333333, 1.94, 3.608, 4.563333333], 'perc_inc': [17375, 23625, 33875, 33875, 17375, 23625, 33875, 33875, 17375, 23500, 33625, 33625, 17375, 23500, 33625, 33625] })
# Ensure that the dataframe is sorted by the group keys
df = df.sort_values(['year', 'bfsId'])
# Define the interpolating function
def f(df):
y_interp = scipy.interpolate.interp1d(df.income, df.taxes_perc, fill_value="extrapolate")
return y_interp(df.perc_inc)
# Carry out the interpolation on each group
df['tax_rate_interpol'] = df.groupby(['year', 'bfsId']).apply(f).apply(pd.Series).stack().values
print(df)
Output:
year | bfsId | income | taxes_perc | perc_inc | tax_rate_interpol | |
---|---|---|---|---|---|---|
0 | 2010 | 1 | 15000 | 0.74 | 17375 | 1.20312 |
1 | 2010 | 1 | 20000 | 1.715 | 23625 | 2.78582 |
2 | 2010 | 1 | 25000 | 3.192 | 33875 | 4.78595 |
3 | 2010 | 1 | 30000 | 4.09 | 33875 | 4.78595 |
4 | 2010 | 2 | 15000 | 0.813333 | 17375 | 1.33187 |
5 | 2010 | 2 | 20000 | 1.905 | 23625 | 3.09618 |
6 | 2010 | 2 | 25000 | 3.548 | 33875 | 5.31472 |
7 | 2010 | 2 | 30000 | 4.54333 | 33875 | 5.31472 |
8 | 2011 | 1 | 15000 | 0.753333 | 17375 | 1.23862 |
9 | 2011 | 1 | 20000 | 1.775 | 23500 | 2.8481 |
10 | 2011 | 1 | 25000 | 3.308 | 33625 | 4.81795 |
11 | 2011 | 1 | 30000 | 4.18333 | 33625 | 4.81795 |
12 | 2011 | 2 | 15000 | 0.813333 | 17375 | 1.3485 |
13 | 2011 | 2 | 20000 | 1.94 | 23500 | 3.1076 |
14 | 2011 | 2 | 25000 | 3.608 | 33625 | 5.25595 |
15 | 2011 | 2 | 30000 | 4.56333 | 33625 | 5.25595 |