python - 根据日期和以前的值填写缺失的数据

Question

在此先感谢您的帮助!
这是我试图解决的谜语的一个简单数据框:

import pandas as pd

data = {"Company ID": ["111", "111", "111", "111", "111", "111",],
        "Company Name": ["xyz", "xyz", "xyz", "xyz", "xyz", "xyz",],
        "Month": ["Jan", "Feb", "Mar", "Apr", "May", "Jun",],
        "Value": [100, 100, 0, 0, 0, 100,],
       }

df = pd.DataFrame(data)

df

有没有办法可以将 0 值更改为 100 如果 公司 ID 从正值变为 0，然后返回正值 ~6 个月内 ?我知道这个 df 不支持时间查找，但我应该能够弄清楚我是否可以解决丢失的数据。
注意:我的数据集有数千行，我想根据唯一的公司 ID 求解值。
此示例的最终结果应显示每个月的值 100，但是，该值和缺失的月份会因公司而异。
@jl31，如果你想使用这些数据，请使用:

data = {"Company ID": ["111", "222", "333", "444", "555", "111","666", "222", "444", "333", "555", "666"],
        "Company Name": ["abc", "def", "ghi", "jkl", "mno", "aaa","pqr", "def", "jkl", "ghi", "mno", "pqr"],
        "Month": pd.date_range(start="2020-01-01",end="2020-12-01",freq='MS'),
        "Value": [100, 100, 100, 100, 0, 0, 0, 100, 0, 0, 0, 100],
       }

这将设置为:

   Company ID Company Name      Month  Value
0         111          abc 2020-01-01    100
5         111          aaa 2020-06-01      0
1         222          def 2020-02-01    100
7         222          def 2020-08-01    100
2         333          ghi 2020-03-01    100
9         333          ghi 2020-10-01      0
3         444          jkl 2020-04-01    100
8         444          jkl 2020-09-01      0
4         555          mno 2020-05-01      0
10        555          mno 2020-11-01      0
6         666          pqr 2020-07-01      0
11        666          pqr 2020-12-01    100

Answer 1

我尝试了各种方法来解决这个问题，但我能找到的唯一解决方案是遍历行。我会尝试找到更好的解决方案。
现在，这是我们如何获得结果。

第 1 步:遍历数据帧中的每一行

第 2 步:对于每一行，按公司 ID 匹配，然后检查 Month 是否为
范围内。检查范围:检查过去 6 个月和接下来的 6 个月
从当前月份开始的月份 (row.Month)。这些范围中的任何一个都将满足
6 个月标准

第 3 步:从 Value 中找出最小值和最大值柱子。这会
结果为(0 和一个正值，最大值为 100)或(0 和 0)。如果
Min = 0 & Max != 0，然后该值下降到 0 并返回到一个
正值，因此我们可以将 newVal 设置为 100。

代码如下:

import pandas as pd

data = {"Company ID": ["111", "222", "333", "444", "555", "111","666", "222", "444", "333", "555", "666"],
        "Company Name": ["abc", "def", "ghi", "jkl", "mno", "aaa","pqr", "def", "jkl", "ghi", "mno", "pqr"],
        "Month": pd.date_range(start="2020-01-01",end="2020-12-01",freq='MS'),
        "Value": [100, 100, 100, 100, 0, 0, 0, 100, 0, 0, 0, 100],
       }

df = pd.DataFrame(data)

df.sort_values(by=['Company ID'], inplace=True)
print (df)

for idx, row in df.iterrows():

    df.loc[idx,'maxVal'] = (df[(row['Company ID']==df['Company ID']) & (df['Month'] <= row.Month + pd.tseries.offsets.MonthBegin(6)) & (df['Month'] >= row.Month - pd.tseries.offsets.MonthBegin(6))]['Value'].max())
    df.loc[idx,'minVal'] = (df[(row['Company ID']==df['Company ID']) & (df['Month'] <= row.Month + pd.tseries.offsets.MonthBegin(6)) & (df['Month'] >= row.Month - pd.tseries.offsets.MonthBegin(6))]['Value'].min())
    
    if df.loc[idx,'minVal'] == 0 and df.loc[idx,'maxVal'] != 0:
        df.loc[idx,'newVal'] = 100
    else:
        df.loc[idx,'newVal'] = 0

df.sort_values(by=['Company ID'], inplace=True)
print (df)

输出将是:
输入数据帧:

   Company ID Company Name      Month  Value
0         111          abc 2020-01-01    100
5         111          aaa 2020-06-01      0   #should change to 100; range within 6 months
1         222          def 2020-02-01    100
7         222          def 2020-08-01    100
2         333          ghi 2020-03-01    100
9         333          ghi 2020-10-01      0   #should NOT change to 100, range outside 6 months
3         444          jkl 2020-04-01    100
8         444          jkl 2020-09-01      0   #should change to 100, range within 6 months
4         555          mno 2020-05-01      0
10        555          mno 2020-11-01      0
6         666          pqr 2020-07-01      0   #should change to 100, range within 6 months
11        666          pqr 2020-12-01    100

更新的数据帧:

   Company ID Company Name      Month  Value  maxVal  minVal  newVal
0         111          abc 2020-01-01    100   100.0     0.0   100.0
5         111          aaa 2020-06-01      0   100.0     0.0   100.0
1         222          def 2020-02-01    100   100.0   100.0     0.0
7         222          def 2020-08-01    100   100.0   100.0     0.0
2         333          ghi 2020-03-01    100   100.0   100.0     0.0
9         333          ghi 2020-10-01      0     0.0     0.0     0.0
3         444          jkl 2020-04-01    100   100.0     0.0   100.0
8         444          jkl 2020-09-01      0   100.0     0.0   100.0
4         555          mno 2020-05-01      0     0.0     0.0     0.0
10        555          mno 2020-11-01      0     0.0     0.0     0.0
6         666          pqr 2020-07-01      0   100.0     0.0   100.0
11        666          pqr 2020-12-01    100   100.0     0.0   100.0

删除 minVal 和 maxVal 列后，您将拥有:

   Company ID Company Name      Month  Value  newVal
0         111          abc 2020-01-01    100   100.0
5         111          aaa 2020-06-01      0   100.0  #Updated as expected
1         222          def 2020-02-01    100     0.0
7         222          def 2020-08-01    100     0.0
2         333          ghi 2020-03-01    100     0.0
9         333          ghi 2020-10-01      0     0.0  #Did NOT Update as expected
3         444          jkl 2020-04-01    100   100.0
8         444          jkl 2020-09-01      0   100.0  #Updated as expected
4         555          mno 2020-05-01      0     0.0
10        555          mno 2020-11-01      0     0.0
6         666          pqr 2020-07-01      0   100.0  #Updated as expected
11        666          pqr 2020-12-01    100   100.0