python - pandas 动态获取连续周订单数量为空

Question

我有一个如下所示的数据框。我想找出上周(202008)以来按国家和产品划分的连续周下降情况。

import pandas as pd

raw_data = {'Country': ['UK','UK','UK','UK','UK','UK','UK','UK','UK','UK','UK','UK','US','US','UK','UK'],
            'Product':['A','A','A','A','A','A','A','A','B','B','B','B','C','C','D','D'],
            'Week': [202001,202002,202003,202004,202005,202006,202007,202008,202001,202006,202007,202008,202006,202008,202007,202008],
    'Orders': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]}

df = pd.DataFrame(raw_data, columns = ['Country','Product','Week','Orders'])

df2 = df.pivot_table(index=['Country','Product'], columns='Week',values='Orders', aggfunc='size').reset_index()

我想要下面的输出，它从上周开始检查，并且连续几周的值为零。

Answer 1

这是一种方法，按分组值循环并按周罢工分割:

df2 = df.groupby(['Country','Product']).size().reset_index()
contiguousWeeks = []

for index, row in df2.iterrows():
    current = df[(df.Country == row.Country) & (df.Product == row.Product)].Week.values
    splits = np.split(current, np.where(np.diff(current) != 1)[0]+1)[-1]
    contiguousWeeks.append(len(splits))

df2['CountStrike'] = contiguousWeeks

结果:

  Country Product  CountStrike
0      UK       A            8
1      UK       B            3
2      UK       D            2
3      US       C            1