在 Pandas DataFrame 中外推值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22491628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extrapolate values in Pandas DataFrame
提问by Jimmy C
It's very easy to interpolate NaN cells in a Pandas DataFrame:
在 Pandas DataFrame 中插入 NaN 单元非常容易：
In [98]: df
Out[98]:
neg neu pos avg
250 0.508475 0.527027 0.641292 0.558931
500 NaN NaN NaN NaN
1000 0.650000 0.571429 0.653983 0.625137
2000 NaN NaN NaN NaN
3000 0.619718 0.663158 0.665468 0.649448
4000 NaN NaN NaN NaN
6000 NaN NaN NaN NaN
8000 NaN NaN NaN NaN
10000 NaN NaN NaN NaN
20000 NaN NaN NaN NaN
30000 NaN NaN NaN NaN
50000 NaN NaN NaN NaN
[12 rows x 4 columns]
In [99]: df.interpolate(method='nearest', axis=0)
Out[99]:
neg neu pos avg
250 0.508475 0.527027 0.641292 0.558931
500 0.508475 0.527027 0.641292 0.558931
1000 0.650000 0.571429 0.653983 0.625137
2000 0.650000 0.571429 0.653983 0.625137
3000 0.619718 0.663158 0.665468 0.649448
4000 NaN NaN NaN NaN
6000 NaN NaN NaN NaN
8000 NaN NaN NaN NaN
10000 NaN NaN NaN NaN
20000 NaN NaN NaN NaN
30000 NaN NaN NaN NaN
50000 NaN NaN NaN NaN
[12 rows x 4 columns]
I would also want it to extrapolate the NaN values that are outside of the interpolation scope, using the given method. How could I best do this?
我还希望它使用给定的方法推断插值范围之外的 NaN 值。我怎样才能最好地做到这一点？
回答by tmthydvnprt
Extrapolating Pandas DataFrames
推断PandasDataFrame小号
DataFrames maybe be extrapolated, however, there is not a simple method call within pandas and requires another library (e.g. scipy.optimize).
DataFrames 可能是外推的，但是，pandas 中没有简单的方法调用，需要另一个库（例如scipy.optimize）。
Extrapolating
外推
Extrapolating, in general, requires one to make certain assumptions about the databeing extrapolated. One way is by curve fittingsome general parameterized equation to the data to find parameter values that best describe the existing data, which is then used to calculate values that extend beyond the range of this data. The difficult and limiting issue with this approach is that some assumption about trendmust be made when the parameterized equation is selected. This can be found thru trial and error with different equations to give the desired result or it can sometimes be inferred from the source of the data. The data provided in the question is really not large enough of a dataset to obtain a well fit curve; however, it is good enough for illustration.
一般来说，外推需要对被外推的数据做出某些假设。一种方法是通过对数据进行一些通用参数化方程的曲线拟合，以找到最能描述现有数据的参数值，然后将其用于计算超出该数据范围的值。这种方法的困难和限制问题是，关于趋势的一些假设必须在选择参数化方程时进行。这可以通过对不同方程的反复试验来找到，以获得所需的结果，或者有时可以从数据源中推断出来。问题中提供的数据确实不够大，无法获得拟合良好的曲线；然而，它足以说明。
The following is an example of extrapolating the DataFramewith a 3rdorder polynomial
下面是外推的一个例子DataFrame用3次多项式
f(x) = ax3+ bx2+ cx+ d(Eq. 1)
f( x) = a x 3+ b x 2+ c x+ d （等式 1）
This generic function (func()) is curve fit onto each column to obtain unique column specific parameters (i.e. a, b, c, d). Then these parameterized equations are used to extrapolate the data in each column for all the indexes with NaNs.
这个通用函数 ( func()) 是曲线拟合到每一列以获得独特的列特定参数（即a、b、c、d）。然后，这些参数化方程用于为所有带有NaNs的索引外推每列中的数据。
import pandas as pd
from cStringIO import StringIO
from scipy.optimize import curve_fit
df = pd.read_table(StringIO('''
neg neu pos avg
0 NaN NaN NaN NaN
250 0.508475 0.527027 0.641292 0.558931
500 NaN NaN NaN NaN
1000 0.650000 0.571429 0.653983 0.625137
2000 NaN NaN NaN NaN
3000 0.619718 0.663158 0.665468 0.649448
4000 NaN NaN NaN NaN
6000 NaN NaN NaN NaN
8000 NaN NaN NaN NaN
10000 NaN NaN NaN NaN
20000 NaN NaN NaN NaN
30000 NaN NaN NaN NaN
50000 NaN NaN NaN NaN'''), sep='\s+')
# Do the original interpolation
df.interpolate(method='nearest', xis=0, inplace=True)
# Display result
print ('Interpolated data:')
print (df)
print ()
# Function to curve fit to the data
def func(x, a, b, c, d):
return a * (x ** 3) + b * (x ** 2) + c * x + d
# Initial parameter guess, just to kick off the optimization
guess = (0.5, 0.5, 0.5, 0.5)
# Create copy of data to remove NaNs for curve fitting
fit_df = df.dropna()
# Place to store function parameters for each column
col_params = {}
# Curve fit each column
for col in fit_df.columns:
# Get x & y
x = fit_df.index.astype(float).values
y = fit_df[col].values
# Curve fit column and get curve parameters
params = curve_fit(func, x, y, guess)
# Store optimized parameters
col_params[col] = params[0]
# Extrapolate each column
for col in df.columns:
# Get the index values for NaNs in the column
x = df[pd.isnull(df[col])].index.astype(float).values
# Extrapolate those points with the fitted function
df[col][x] = func(x, *col_params[col])
# Display result
print ('Extrapolated data:')
print (df)
print ()
print ('Data was extrapolated with these column functions:')
for col in col_params:
print ('f_{}(x) = {:0.3e} x^3 + {:0.3e} x^2 + {:0.4f} x + {:0.4f}'.format(col, *col_params[col]))
Extrapolating Results
推断结果
Interpolated data:
neg neu pos avg
0 NaN NaN NaN NaN
250 0.508475 0.527027 0.641292 0.558931
500 0.508475 0.527027 0.641292 0.558931
1000 0.650000 0.571429 0.653983 0.625137
2000 0.650000 0.571429 0.653983 0.625137
3000 0.619718 0.663158 0.665468 0.649448
4000 NaN NaN NaN NaN
6000 NaN NaN NaN NaN
8000 NaN NaN NaN NaN
10000 NaN NaN NaN NaN
20000 NaN NaN NaN NaN
30000 NaN NaN NaN NaN
50000 NaN NaN NaN NaN
Extrapolated data:
neg neu pos avg
0 0.411206 0.486983 0.631233 0.509807
250 0.508475 0.527027 0.641292 0.558931
500 0.508475 0.527027 0.641292 0.558931
1000 0.650000 0.571429 0.653983 0.625137
2000 0.650000 0.571429 0.653983 0.625137
3000 0.619718 0.663158 0.665468 0.649448
4000 0.621036 0.969232 0.708464 0.766245
6000 1.197762 2.799529 0.991552 1.662954
8000 3.281869 7.191776 1.702860 4.058855
10000 7.767992 15.272849 3.041316 8.694096
20000 97.540944 150.451269 26.103320 91.365599
30000 381.559069 546.881749 94.683310 341.042883
50000 1979.646859 2686.936912 467.861511 1711.489069
Data was extrapolated with these column functions:
f_neg(x) = 1.864e-11 x^3 + -1.471e-07 x^2 + 0.0003 x + 0.4112
f_neu(x) = 2.348e-11 x^3 + -1.023e-07 x^2 + 0.0002 x + 0.4870
f_avg(x) = 1.542e-11 x^3 + -9.016e-08 x^2 + 0.0002 x + 0.5098
f_pos(x) = 4.144e-12 x^3 + -2.107e-08 x^2 + 0.0000 x + 0.6312
Plot for avgcolumn
avg列的绘图
Without a larger dataset or knowing the source of the data, this result maybe completely wrong, but should exemplify the process to extrapolate a DataFrame. The assumed equation in func()would probably need to be playedwith to get the correct extrapolation. Also, no attempt to make the code efficient was made.
如果没有更大的数据集或不知道数据的来源，这个结果可能完全错误，但应该举例说明推断DataFrame. 在假设的公式func()很可能需要被打与以获得正确的推断。此外，也没有尝试使代码高效。
Update:
更新：
If your index is non-numeric, like a DatetimeIndex, see this answerfor how to extrapolate them.
如果您的索引是非数字的，例如 a DatetimeIndex，请参阅此答案以了解如何推断它们。
回答by unutbu
import pandas as pd
try:
# for Python2
from cStringIO import StringIO
except ImportError:
# for Python3
from io import StringIO
df = pd.read_table(StringIO('''
neg neu pos avg
0 NaN NaN NaN NaN
250 0.508475 0.527027 0.641292 0.558931
999 NaN NaN NaN NaN
1000 0.650000 0.571429 0.653983 0.625137
2000 NaN NaN NaN NaN
3000 0.619718 0.663158 0.665468 0.649448
4000 NaN NaN NaN NaN
6000 NaN NaN NaN NaN
8000 NaN NaN NaN NaN
10000 NaN NaN NaN NaN
20000 NaN NaN NaN NaN
30000 NaN NaN NaN NaN
50000 NaN NaN NaN NaN'''), sep='\s+')
print(df.interpolate(method='nearest', axis=0).ffill().bfill())
yields
产量
neg neu pos avg
0 0.508475 0.527027 0.641292 0.558931
250 0.508475 0.527027 0.641292 0.558931
999 0.650000 0.571429 0.653983 0.625137
1000 0.650000 0.571429 0.653983 0.625137
2000 0.650000 0.571429 0.653983 0.625137
3000 0.619718 0.663158 0.665468 0.649448
4000 0.619718 0.663158 0.665468 0.649448
6000 0.619718 0.663158 0.665468 0.649448
8000 0.619718 0.663158 0.665468 0.649448
10000 0.619718 0.663158 0.665468 0.649448
20000 0.619718 0.663158 0.665468 0.649448
30000 0.619718 0.663158 0.665468 0.649448
50000 0.619718 0.663158 0.665468 0.649448
Note: I changed your dfa little to show how interpolating with nearestis different than doing a df.fillna. (See the row with index 999.)
注意：我对你做df了一些改动，以展示插值 with 与nearest执行 a 的不同之处df.fillna。（请参阅索引为 999 的行。）
I also added a row of NaNs with index 0 to show that bfill()may also be necessary.
我还添加了一行索引为 0 的 NaN，以表明这bfill()也可能是必要的。
