使用python中的移动平均线添加行

发布于 2025-01-20 14:05:48 字数 1526 浏览 0 评论 0原文

我有以下问题：我有一个表，客户编号，日期和销售存储。客户交易可在每个月的第一个月提供。客户可能没有每月下订单。 The table looks like this:

ID	Date	Revenues
1	2021-05-01	100
1	2021-07-01	200
1	2021-08-01	100
1	2021-10-01	200
2	2021-12-01	300
2	2022-01-01	400

Now I want to add a certain number of rows to each group whose date is from today for a certain number of months in the future. ID应保持不变，日期应增加一个月，而离职列应用移动平均方法填充。
The table should look like this:

ID	Date	Revenues
1	2021-05-01	100
1	2021-07-01	200
1	2021-08-01	100
1	2021-10-01	200
1	2022-04-01	150
1	2022-05-01	150
2	2021-12-01	300
2	2022-01-01	400
2	2022-04-01	350
2	2022-05-01	350

How can I solve this problem? 感谢您的帮助：）

原文

I have the following problem:
I have a table in which the customer number, the date and the sales are stored. The customer transactions are available on the first of each month. It may happen that a customer has not placed an order every month. The table looks like this:

ID	Date	Revenues
1	2021-05-01	100
1	2021-07-01	200
1	2021-08-01	100
1	2021-10-01	200
2	2021-12-01	300
2	2022-01-01	400

Now I want to add a certain number of rows to each group whose date is from today for a certain number of months in the future. The ID should remain the same, the date should be increased by one month and the turnover column should be filled with the moving average method.
The table should look like this:

ID	Date	Revenues
1	2021-05-01	100
1	2021-07-01	200
1	2021-08-01	100
1	2021-10-01	200
1	2022-04-01	150
1	2022-05-01	150
2	2021-12-01	300
2	2022-01-01	400
2	2022-04-01	350
2	2022-05-01	350

How can I solve this problem?
Thank you for your help :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉宸 2025-01-27 14:05:48

如果我正确理解您：

df["Date"] = pd.to_datetime(df["Date"])


def reindex(x):
    min_date = x["Date"].min()
    r = pd.date_range(min_date, min_date + pd.DateOffset(months=3), freq="MS")
    x = x.set_index("Date").reindex(r)
    x["ID"] = x["ID"].ffill().bfill()
    x["Revenues"] = x["Revenues"].fillna(x["Revenues"].mean())
    return x


x = df.groupby("ID", as_index=False).apply(reindex).droplevel(0).reset_index()
x = x.rename(columns={"index": "Date"})
print(x.to_markdown())

打印：

	日期	ID	收入
0	2021-12-01 00:00:00	1	100
1 100 1	2022-01-01 00:00：	00 ：00 1	200
2	2022-02-02-01 00:00	：00：00 1	150
3	2022-03-03-01 00： 00:00	1	150
4	2021-12-01 00:00:00	2	300
5	2022-01-01-01 00:00:00	2	400 2 400
6	2022-02-02-01 00:00：00	2	350
7	2022-03-03-03-01 00:00:00	2	350

If I understand you correctly:

df["Date"] = pd.to_datetime(df["Date"])


def reindex(x):
    min_date = x["Date"].min()
    r = pd.date_range(min_date, min_date + pd.DateOffset(months=3), freq="MS")
    x = x.set_index("Date").reindex(r)
    x["ID"] = x["ID"].ffill().bfill()
    x["Revenues"] = x["Revenues"].fillna(x["Revenues"].mean())
    return x


x = df.groupby("ID", as_index=False).apply(reindex).droplevel(0).reset_index()
x = x.rename(columns={"index": "Date"})
print(x.to_markdown())

Prints:

	Date	ID	Revenues
0	2021-12-01 00:00:00	1	100
1	2022-01-01 00:00:00	1	200
2	2022-02-01 00:00:00	1	150
3	2022-03-01 00:00:00	1	150
4	2021-12-01 00:00:00	2	300
5	2022-01-01 00:00:00	2	400
6	2022-02-01 00:00:00	2	350
7	2022-03-01 00:00:00	2	350