pandas df中无循环的产品需求下降计算

Question

我在使用shift和diff时遇到了麻烦，我觉得它很简单？假设我的客户有不同的产品需求，并且他们以自上而下的优先级进行处理。我希望它不会循环，而是高效的...

df_situation = pd.DataFrame(
    {
        "cust": [1, 2, 3, 3,4],
        "prod": [1, 1, 1, 2,2],
        "available": [1000, np.nan, np.nan, 2000, np.nan],
        "needed": [200, 300, 1000, 1000,1000],
    }
)

​

​

我的目标是获得一些像这样的额外列，但它看起来像是差异计算和移位操作处于“鸡和蛋的问题情况”。

​

​

提前感谢您的任何提示

Answer 1

leftover_prod是可用的ffill -累积需求groupby cumsum

a = df_situation['available'].ffill()
df_situation['leftover_prod'] = (
        a - df_situation.groupby('prod')['demand'].cumsum()
)

0     800.0
1     500.0
2    -500.0
3    1000.0
4       0.0
Name: leftover_prod, dtype: float64

fulfilled_cust为demand (如果有足够的leftover_prod )或leftover_prod groupby shift + np.where

s = (df_situation.groupby('prod')['leftover_prod']
     .shift()
     .fillna(df_situation['available']))

df_situation['fulfilled_cust'] = np.where(
    s.ge(df_situation['demand']), df_situation['demand'], s
)

0     200.0
1     300.0
2     500.0
3    1000.0
4    1000.0
Name: fulfilled_cust, dtype: float64

missing_cust是demand - fulfilled_cust

df_situation['missing_cust'] = (
        df_situation['demand'] - df_situation['fulfilled_cust']
)

0      0.0
1      0.0
2    500.0
3      0.0
4      0.0
Name: missing_cust, dtype: float64

合在一起：

a = df_situation['available'].ffill()
df_situation['leftover_prod'] = (
        a - df_situation.groupby('prod')['demand'].cumsum()
)
s = (df_situation.groupby('prod')['leftover_prod']
     .shift()
     .fillna(df_situation['available']))

df_situation['fulfilled_cust'] = np.where(
    s.ge(df_situation['demand']), df_situation['demand'], s
)

df_situation['missing_cust'] = (
        df_situation['demand'] - df_situation['fulfilled_cust']
)

   cust  prod  available  demand  leftover_prod  fulfilled_cust  missing_cust
0     1     1     1000.0     200          800.0           200.0           0.0
1     2     1        NaN     300          500.0           300.0           0.0
2     3     1        NaN    1000         -500.0           500.0         500.0
3     3     2     2000.0    1000         1000.0          1000.0           0.0
4     4     2        NaN    1000            0.0          1000.0           0.0

使用的导入和DataFrame：

import numpy as np
import pandas as pd

df_situation = pd.DataFrame({
    "cust": [1, 2, 3, 3, 4],
    "prod": [1, 1, 1, 2, 2],
    "available": [1000, np.nan, np.nan, 2000, np.nan],
    "demand": [200, 300, 1000, 1000, 1000],
})

(将“需要”改为“需求”，如图所示。)