How to properly write if-then lambda statement for pandas df?

user_14991027 0
Viewed 73

I have the following code:

data = [[11001218, 'Value', 93483.37, 'G', '', 93483.37, '', '56117J100', 'FRA', 'Equity'], 
        [11001218, 'Value', 3572.73, 'G', 3572.73, '', '56117J100', '', 'LUM', 'Equity'], 
        [11001218, 'Value', 89910.64, 'G', 89910.64, '', '56117J100', '', 'WAR', 'Equity'],
        [11005597, 'Value', 72640313.34,'L','',72640313.34, 'REVR21964', '','IN2',  'Repo']
       ]

df = pd.DataFrame(data, columns = ['ID', 'Type', 'Diff', 'Group', 'Amount','Amount2', 'Id2', 'Id3', 'Executor', 'Name'])

def logic_builder(row, row2, row3):
    if row['Name'] == 'Repo' and row['Group'] == 'L':
        return 'Fine resultant'
    elif (row['ID'] == row2['ID']) and (row['ID'] == row3['ID']) and (row['Group'] == row2['Group']) and (row['Group'] == row3['Group']) and (row['Executor'] != row2['Executor']) and (row['Executor'] != row3['Executor']):    
        return 'Difference in Executor'

df['Results'] = df.apply(lambda row: logic_builder(row, row2, row3), axis=1)

If you look at the first 3 rows, they are all technically the same. They contain the same ID, Type, Group, and Name. The only difference is the executor, hence I would like my if-then statement to return "Difference in Executor". I am having trouble figuring out how to right the if-then to look at all the rows with similar attributes for the fields I mentioned above.

Thank you.

pandas apply if-statement lambda python
2 Answers

You can pass a single row, then determine its index and look for the other rows with df.iloc[index].

Here an example

def logic_builder(row):
    global df #you need to access the df

    i = row.name #row index

    #get next rows
    try:
        row2 = df.iloc[i+1] 
        row3 = df.iloc[i+2]
    except IndexError:
        return
    
    if row['Name'] == 'Repo' and row['Group'] == 'L':
        return 'Fine resultant'
    elif (row['ID'] == row2['ID']) and (row['ID'] == row3['ID']) and (row['Group'] == row2['Group']) and (row['Group'] == row3['Group']) and (row['Executor'] != row2['Executor']) and (row['Executor'] != row3['Executor']):    
        return 'Difference in Executor'

df['Results'] = df.apply(logic_builder, axis=1)

Of course, since the result depend on the next two rows, you can't run it on the last 2 rows of the dataframe.

user_31900760

You can modify the function a bit to perform on a chunk/slice of a dataframe, based on group using groupby since you are performing the action per group. A modified version of the function you have written would look something like this:

def logic_builder(group):
    if group['Name'].eq('Repo').all() and group['Group'].eq('L').all():
        return 'Fine resultant'
    elif group['Group'].nunique()==1 and group['Executor'].nunique()>1:    
        return 'Difference in Executor'

row1, row2, row3,..,rown is not going to work actually, because there may be one or more rows per group, so better strategy is to perform if else using all, and nunique (which essentially gives number of unique values in the selected column) for the above logic that you have.

Then apply the function on groupby object:

df.groupby('ID').apply(logic_builder)
ID
11001218    Difference in Executor
11005597            Fine resultant
dtype: object

You can finally join above value to the actual dataframe if needed.

user_91363480
Related