英文:
How to interpolate monthly frequency sample data's missing values with interp1d(x, y) from scipy
问题
我已经创建了名为data的每月样本数据，其中某些月份存在缺失值，我希望使用interp1d()方法来填充它们。我已经用以下代码实现了它，但结果仍然为空，我不知道问题出在哪里。请问如何修改代码？非常感谢。
import pandas as pdimport numpy as npfrom scipy.interpolate import interp1d# 创建一个示例DataFramedata = pd.DataFrame({'value': [1.0, 1.2, np.nan, 1.4, 1.6, np.nan, 1.8, 2.0, np.nan, 2.2, 2.4, np.nan]}, index=pd.date_range('2000-01-01', periods=12, freq='M'))# 将索引转换为DateTimeIndexdata.index = pd.to_datetime(data.index)# 将DateTimeIndex转换为具有月度频率的PeriodIndexx = data.index.to_period('M')# 将周期索引转换为整数x = x.astype(int)# 将'y'列转换为numpy数组y = data['value'].values# 创建插值函数f = interp1d(x, y, kind='linear', fill_value="extrapolate")# 创建一个布尔掩码，选择'value'列中的缺失值mask = np.isnan(data['value'])# 创建一个包含'y'缺失的'x'值的数组x_new = pd.date_range(start=data.index.min(), end=data.index.max(), freq='M')[mask]# 将'x_new'值转换为具有月度频率的日期x_new_dates = pd.date_range(start=x_new.min(), end=x_new.max(), freq='M')# 插值缺失的'y'值y_new = f(x_new_dates.astype(int))# 创建一个新列'value_interpolated'，并用原始数据填充它# 将插值的'y'值插入新列data.loc[x_new_dates, 'value_interpolated'] = y_new# 打印DataFrameprint(data)
输出：
value value_interpolated2000-01-31 1.0 NaN2000-02-29 1.2 NaN2000-03-31 NaN NaN2000-04-30 1.4 NaN2000-05-31 1.6 NaN2000-06-30 NaN NaN2000-07-31 1.8 NaN2000-08-31 2.0 NaN2000-09-30 NaN NaN2000-10-31 2.2 NaN2000-11-30 2.4 NaN2000-12-31 NaN NaN
英文:
I have created monthly sample data data, in which there are missing values in some months, and I hope to fill them in by interp1d() method. I have implemented it with the following code, but the result is still empty, and I don’t know where the problem lies. May I ask how to modify the code? Many thanks.
import pandas as pdimport numpy as npfrom scipy.interpolate import interp1d# Create an example DataFramedata = pd.DataFrame({'value': [1.0, 1.2, np.nan, 1.4, 1.6, np.nan, 1.8, 2.0, np.nan, 2.2, 2.4, np.nan]}, index=pd.date_range('2000-01-01', periods=12, freq='M'))# Convert the index to a DateTimeIndexdata.index = pd.to_datetime(data.index)# Convert the DateTimeIndex to a PeriodIndex with monthly frequencyx = data.index.to_period('M')# Convert the period index to integersx = x.astype(int)# Convert the 'y' column to a numpy arrayy = data['value'].values# Create the interpolation functionf = interp1d(x, y, kind='linear', fill_value="extrapolate")# Create a boolean mask that selects the missing values in the 'value' columnmask = np.isnan(data['value'])# Create an array with the 'x' values where 'y' is missingx_new = pd.date_range(start=data.index.min(), end=data.index.max(), freq='M')[mask]# Convert the 'x_new' values to dates with monthly frequencyx_new_dates = pd.date_range(start=x_new.min(), end=x_new.max(), freq='M')# Interpolate the missing 'y' valuesy_new = f(x_new_dates. astype(int))# Create a new column 'value_c' and fill it with the original data# Insert the interpolated 'y' values into the new columndata.loc[x_new_dates, 'value_interpolated'] = y_new# Print the DataFrameprint(data)
Out:
value value_interpolated2000-01-31 1.0 NaN2000-02-29 1.2 NaN2000-03-31 NaN NaN2000-04-30 1.4 NaN2000-05-31 1.6 NaN2000-06-30 NaN NaN2000-07-31 1.8 NaN2000-08-31 2.0 NaN2000-09-30 NaN NaN2000-10-31 2.2 NaN2000-11-30 2.4 NaN2000-12-31 NaN NaN
答案1
得分: 1
以下是您要翻译的内容：
您可以使用与某个参考时间的秒数进行插值，如下所示的此答案中所示。由于存在大量缺失数据，我无法保证这些结果的准确性。
import pandas as pdimport numpy as npfrom scipy.interpolate import interp1ddata = pd.DataFrame({"value": [1.0, 1.2, np.nan, 1.4, 1.6, np.nan, 1.8, 2.0, np.nan, 2.2, 2.4, np.nan]}, index=pd.date_range("2000-01-01", periods=12, freq="M"))data.index = pd.to_datetime(data.index)mask = ~np.isnan(data["value"]) # mask out the missing valuesdref = data.index[0]x = (data.index-dref).total_seconds()[mask]y = data["value"][mask].to_numpy()f = interp1d(x, y, fill_value="extrapolate")y_new = f((data.index - dref).total_seconds())data["value_interpolated"] = y_new
输出：
value value_interpolated2000-01-31 1.0 1.0000002000-02-29 1.2 1.2000002000-03-31 NaN 1.3016392000-04-30 1.4 1.4000002000-05-31 1.6 1.6000002000-06-30 NaN 1.6983612000-07-31 1.8 1.8000002000-08-31 2.0 2.0000002000-09-30 NaN 2.0983612000-10-31 2.2 2.2000002000-11-30 2.4 2.4000002000-12-31 NaN 2.606667
英文:
You can interpolate the values using the seconds from some reference time (below I used the first date) as shown in this answer. I can't guarantee the accuracy of these results since there is a lot of missing data to interpolate.
import pandas as pdimport numpy as npfrom scipy.interpolate import interp1ddata = pd.DataFrame({"value": [1.0, 1.2, np.nan, 1.4, 1.6, np.nan, 1.8, 2.0, np.nan, 2.2, 2.4, np.nan]}, index=pd.date_range("2000-01-01", periods=12, freq="M"))data.index = pd.to_datetime(data.index)mask = ~np.isnan(data["value"]) # mask out the missing valuesdref = data.index[0]x = (data.index-dref).total_seconds()[mask]y = data["value"][mask].to_numpy()f = interp1d(x, y, fill_value="extrapolate")y_new = f((data.index - dref).total_seconds())data["value_interpolated"] = y_new
Out:
value value_interpolated2000-01-31 1.0 1.0000002000-02-29 1.2 1.2000002000-03-31 NaN 1.3016392000-04-30 1.4 1.4000002000-05-31 1.6 1.6000002000-06-30 NaN 1.6983612000-07-31 1.8 1.8000002000-08-31 2.0 2.0000002000-09-30 NaN 2.0983612000-10-31 2.2 2.2000002000-11-30 2.4 2.4000002000-12-31 NaN 2.606667
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论