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

使用：

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

#compare if non missing values from back
a = df2.notna().iloc[:, ::-1]
#running sum
b = a.cumsum(axis=1)
#counter only for consecutive values
df = b-b.mask(a).ffill(axis=1).fillna(0).astype(int)

#convert all another consecutive values to 0 and get max for count last consecutive vals
val = df.mask(df.eq(0).cumsum(axis=1).ne(0), 0).max(axis=1).astype(str) 
#if 1 value different text
df2['Text'] = np.where(val != '1', 
                       'Last ' + val + ' consecutive weeks is not null', 
                       'Last ' + val + ' week is not null')
#connvert MultiIndex to columns
df2 = df2.reset_index()
print (df2)
Week Country Product  202001  202002  202003  202004  202005  202006  202007  \
0         UK       A     1.0     1.0     1.0     1.0     1.0     1.0     1.0   
1         UK       B     1.0     NaN     NaN     NaN     NaN     1.0     1.0   
2         UK       D     NaN     NaN     NaN     NaN     NaN     NaN     1.0   
3         US       C     NaN     NaN     NaN     NaN     NaN     1.0     NaN   

Week  202008                                  Text  
0        1.0  Last 8 consecutive weeks is not null  
1        1.0  Last 3 consecutive weeks is not null  
2        1.0  Last 2 consecutive weeks is not null  
3        1.0               Last 1 week is not null