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

如果公司 ID 从正值变为 0，然后在约 6 个月内变回正值，有没有办法可以将 0 值更改为 100 ？我知道这个 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

标签： pythonpandas

Answer 1

我尝试了各种方法来解决这个问题，但我能找到的唯一解决方案是遍历行。我会尝试找到更好的解决方案。

现在，这是我们如何获得结果的方法。

• 第 1 步：遍历数据框中的每一行
• 第 2 步：对于每一行，按公司 ID 匹配，然后检查月份是否在范围内。检查范围：检查从当前月份开始的最近 6 个月和下 6 个月（row.Month）。这些范围中的任何一个都将满足 6 个月的标准
• Value第 3 步：从列中找到最小值和最大值。这将导致（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