Python Pandas Iterate over columns and also update columns based on apply conditions

user_13475017 0
Viewed 90

I am trying to update dataframe columns based on consecutive columns values.
If columns say col1 and col2 has >0 and <0 values, then same columns should get updated as col2=col1 + col2 and col1=0 and also counter +1 (gives how many fixes has been done throughout the column).

Dataframe look like: 

id  col0    col1    col2    col3    col4    col5 col6   col7    col8    col9    col10
1   0   5   -5  5   -5  0 0 1   4   3   -3 
2   0   0   0   0   0   0 0 4   -4  0   0 
3   0   0   1   2   3   0 0 0   5   6   0

Required Dataframe after applying logic:

id  col0    col1    col2    col3    col4    col6    col7    col8    col9    col10   fix
1   0   0   0   0   0   0   0 1 4   0   0 0 3
2   0   0   0   0   0   0   0 0 0   0   0 0 1
3   0   0   1   2   3   0   0 0 5   6   0 9 0

I tried:

def fix_count(row):
    row['fix_cnt'] = 0

    for i in range(0, 10):
        if ((row['col' + str(i)] > 0) & 
            (row['col' + str(i + 1)] < 0)):

            row['col' + str(i + 1)] = row['col' + str(i)] + row['col' + str(i + 1)]
            row['col' + str(i)] = 0

            row['fix_cnt'] += 1

            return (row['col' + str(i)],
                    row['col' + str(i + 1)],
                    row['fix_cnt'])

df.apply(fix_count, axis=1)

It failed IndexError: index 11 is out of bounds for axis 0 with size 11.

I also looked into df.iteritems but I couldn't figure out the way.

DDL to generate DataFrame:

import pandas as pd

df = pd.DataFrame({'id': [1, 2, 3],
                   'col0': [0, 0, 0],
                   'col1': [5, 0, 0],
                   'col2': [-5, 0, 1],
                   'col3': [5, 0, 2],
                   'col4': [-5, 0, 3],
                   'col5' : [0, 0, 0],
                   'col6': [0, 0, 0],
                   'col7': [1, 4, 0],
                   'col8': [4, -4, 5],
                   'col9': [3, 0, 6],
                   'col10': [-3, 0, 0]})

Thanks!

pandas dataframe python
2 Answers

Here is an approach without loops:

c = df.gt(0) & df.shift(-1,axis=1).lt(0)

out = (df.mask(c.shift(axis=1).fillna(False),df+df.shift(axis=1))
      .mask(c,0).assign(Fix=c.sum(1)))

print(out)

   id  col0  col1  col2  col3  col4  col6  col7  col8  col9  col10  Fix
0   1     0     0     0     0     0     0     1     4     0      0    3
1   2     0     0     0     0     0     0     0     0     0      0    1
2   3     0     0     1     2     3     0     0     5     6      0    0

Details:

  • c checks if current column is > 0 and next column is <0.
  • Add current column to next column in the next column to where c is True.
  • Set the current column to 0 if c is True.
  • Get sum of c for changes done.
user_98406370

Your code logic is fine. Just a small correction while returning the row from your function works as expected:

def fix_count(row):
    row['fix_cnt'] = 0

    for i in range(0, 10):
        if ((row['col' + str(i)] > 0) & 
            (row['col' + str(i + 1)] < 0)):

            row['col' + str(i + 1)] = row['col' + str(i)] + row['col' + str(i + 1)]
            row['col' + str(i)] = 0

            row['fix_cnt'] += 1

    return (row)

df.apply(fix_count, axis=1)

Try this and let me know if this works!

user_126903440
Related