填充python dataframe中缺少的日期列
填充python dataframe中缺少的日期列
提问于 2021-10-14 15:11:31
需要帮助填补df_1中缺少的月份开始日期的空白(例如: 01、02、05和07到11)，我需要有一个连续的月份(即12)。
In: df_1 = pd.DataFrame([['2021-03-01', 'Supp_1', 'Product_1', '1'],
['2021-04-01', 'Supp_1', 'Product_1', 1],
['2021-06-01','Supp_1', 'Product_1', 1],
['2021-12-01', 'Supp_1', 'Product_1', 1.25]],
columns=['Date','Supplier','Product','Cost'])
Out:
Date Supplier Product Cost
0 2021-03-01 Supp_1 Product_1 1
1 2021-04-01 Supp_1 Product_1 1
2 2021-06-01 Supp_1 Product_1 1
3 2021-12-01 Supp_1 Product_1 1.25预期的结果是，
Date Supplier Product Cost
0 2021-01-01 Supp_1 Product_1 1
1 2021-02-01 Supp_1 Product_1 1
2 2021-03-01 Supp_1 Product_1
3 2021-04-01 Supp_1 Product_1
4 2021-05-01 Supp_1 Product_1
5 2021-06-01 Supp_1 Product_1 1
6 2021-07-01 Supp_1 Product_1
7 2021-08-01 Supp_1 Product_1
8 2021-09-01 Supp_1 Product_1
9 2021-10-01 Supp_1 Product_1
10 2021-11-01 Supp_1 Product_1
11 2021-12-01 Supp_1 Product_1 1.25一旦我们有了成本，我就可以使用fill ()和bfill()来填补‘df_2’的空白
回答 4
Stack Overflow用户
发布于 2021-10-14 21:18:09
如果我没弄错的话，您希望根据一年中所有月份以及Supplier和Product的组合来显示缺少的行，然后向前/向后填充Cost列。
也许来自pyjanitor的complete函数可以提供帮助：
# pip git+https://github.com/pyjanitor-devs/pyjanitor.git
import pandas as pd
import janitor as jn
year = df.Date.dt.year.at[0]
months = pd.date_range(f"{year}-01-01", f"{year}-12-01", freq="MS")
months = dict(Date = months)
df.complete(months, 'Supplier', 'Product', sort = True)
Date Supplier Product Cost
0 2021-01-01 Supp_1 Product_1 NaN
1 2021-02-01 Supp_1 Product_1 NaN
2 2021-03-01 Supp_1 Product_1 1
3 2021-04-01 Supp_1 Product_1 1
4 2021-05-01 Supp_1 Product_1 NaN
5 2021-06-01 Supp_1 Product_1 1
6 2021-07-01 Supp_1 Product_1 NaN
7 2021-08-01 Supp_1 Product_1 NaN
8 2021-09-01 Supp_1 Product_1 NaN
9 2021-10-01 Supp_1 Product_1 NaN
10 2021-11-01 Supp_1 Product_1 NaN
11 2021-12-01 Supp_1 Product_1 1.25然后，您可以在Cost列上填充或向下填充。
Stack Overflow用户
发布于 2021-10-14 15:25:57
您可以使用resample
print (df_1.assign(Date=pd.to_datetime(df_1["Date"]))
.set_index("Date")
.resample("MS").asfreq()
.reset_index())
Date Supplier Product Cost
0 2021-01-01 Supp_1 Product_1 1
1 2021-02-01 Supp_1 Product_1 1
2 2021-03-01 NaN NaN NaN
3 2021-04-01 NaN NaN NaN
4 2021-05-01 NaN NaN NaN
5 2021-06-01 Supp_1 Product_1 1
6 2021-07-01 NaN NaN NaN
7 2021-08-01 NaN NaN NaN
8 2021-09-01 NaN NaN NaN
9 2021-10-01 NaN NaN NaN
10 2021-11-01 NaN NaN NaN
11 2021-12-01 Supp_1 Product_1 1.25Stack Overflow用户
发布于 2021-10-14 15:30:14
您可以使用此管道。关键的一步是set_index Date和使用asfreq
(df1.assign(Date=pd.to_datetime(df1['Date']))
.set_index('Date')
.asfreq('MS')
.assign(Supplier=lambda d: d['Supplier'].ffill(),
Product=lambda d: d['Product'].ffill()
)
.reset_index()
)输出：
Date Supplier Product Cost
0 2021-01-01 Supp_1 Product_1 1
1 2021-02-01 Supp_1 Product_1 1
2 2021-03-01 Supp_1 Product_1 NaN
3 2021-04-01 Supp_1 Product_1 NaN
4 2021-05-01 Supp_1 Product_1 NaN
5 2021-06-01 Supp_1 Product_1 1
6 2021-07-01 Supp_1 Product_1 NaN
7 2021-08-01 Supp_1 Product_1 NaN
8 2021-09-01 Supp_1 Product_1 NaN
9 2021-10-01 Supp_1 Product_1 NaN
10 2021-11-01 Supp_1 Product_1 NaN
11 2021-12-01 Supp_1 Product_1 1.25页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69573153
复制相关文章
相似问题
腾讯云开发者
