CODEWITHSUNDEEP
pythonpandasdataframe
Joe
Joe

Reputation: 12417

Compare 2 consecutive cells in a dataframe

I have a dataframe (more than 150 rows and 16 columns) with multiindex like this:

              a001          a002          a003        a004         a005  
Year Week                                                                    
2017  1          0            1            1            3            0   
      2          1            2            2            4            0   
      3          2            0            3            5            0   
      4          0            0            4            0            0   
      5          0            1            5            0            0   
      6          0            2            6            1            0   
      7          0            0            7            2            0   
      8          1            0            0            3            0   
      9          2            0            0            0            0   
     10          3            2            0            0            0

What I would like is to have only the last numbers per column before the 0 in a following row:

              a001          a002          a003        a004         a005  
Year Week                                                                    
2017  1          0            0            0            0            0   
      2          0            0            0            0            0   
      3          0            2            0            0            0   
      4          2            0            0            5            0   
      5          0            0            0            0            0   
      6          0            0            0            0            0   
      7          0            2            0            0            0   
      8          0            0            7            0            0   
      9          0            0            0            3            0   
     10          0            0            0            0            0

I started to try with mask, but then I got stucked

for i in column:
    mask = (df[i] < df[i].shift())
    print mask

Can anyone help in this direction or with any other solution? Thanks in advance

Upvotes: 1

Views: 61

Answers (1)

jezrael
jezrael

Reputation: 862691

I think need compare 2 consecutive 0, replace another values to 0 by where, shift, convert NaNs to 0 by fillna and last to integer:

mask = (df != 0) & (df.shift(-1) == 0) & (df.shift(-2) == 0)
df1 = df.where(mask).shift().fillna(0).astype(int)

print (df1)
           a001  a002  a003  a004  a005
Year Week                              
2017 1        0     0     0     0     0
     2        0     0     0     0     0
     3        0     2     0     0     0
     4        2     0     0     5     0
     5        0     0     0     0     0
     6        0     0     0     0     0
     7        0     2     0     0     0
     8        0     0     7     0     0
     9        0     0     0     3     0
     10       0     0     0     0     0

EDIT:

Thanks @Joe for simplify code:

df1 = df.where((df != 0) & (df.shift(-1) == 0)).shift().fillna(0).astype(int)

Upvotes: 1

Related Questions