Subtract value from given criteria in Python-CodePudding

I have a dataset, df, where I would like to subtract a specific value from a certain column if both columns match a criteria. As well as creating a new column that would contain what is being subtracted.

Data

start   id  q1_avail    q1_used 
50      aa  10          40  
20      bb  5           15

Desired

start   id  q1_avail    q1_used    delta
50      aa  8           40         2
20      bb  4           15         1

Logic

we are looking for the 'q1_avail' column as well as the id 'aa'. we are then subtracting the value 2 from 10 under the q1_avail column the value 2 is what is being subtracted from 10 . 10-8 = 2

Doing

mask = df['q1_avail'] 

df.loc[mask, 'q1_avail] -= out['id'].map({'aa':2,
                                          'bb':1,})

Any suggestion is appreciated

CodePudding user response：

I would use normal .apply(function, axis=1) to run function on every row separatelly and this function could use if/else to check value in id and change q1_avail and create delta

Minimal working example

text = '''start   id  q1_avail    q1_used 
50      aa  10          40  
20      bb  5           15'''

import pandas as pd
import io

df = pd.read_csv(io.StringIO(text), sep="\s ")

def function(row):
    if row['id'] == 'aa':
        row['q1_avail'] -=2
        row['delta'] = 2
    if row['id'] == 'bb':
        row['q1_avail'] -=1
        row['delta'] = 1
    return row

df = df.apply(function, axis=1)

print(df)

Result:

   start  id  q1_avail  q1_used  delta
0     50  aa         8       40      2
1     20  bb         4       15      1

You may also create more complex function and even get parameters but then you need to use lambda

def function(row, maps):
    
    row['delta'] = maps[row['id']]
    row['q1_avail'] -= row['delta']

    return row

df = df.apply(lambda row:function(row, {'aa':2, 'bb':1}), axis=1)

EDIT:

With your last code it could be

df['delta'] = df['id'].map({'aa': 2, 'bb':1})

df['q1_avail'] = df['q1_avail'] - df['delta']
#df['q1_avail'] -= df['delta']