Replace values within a column if a certain condition is met using Python

user_5942100 0
Viewed 88

I have a dataset where I would like to map values based on a specific condition and override the values that are in an existing column.

Data

ID  Date    Location    Used  Status
AA  Q121    NY          20    ok
AA  Q221    NY          50    ok
AA  Q321    NY          10    ok
BB  Q121    CA          1     ok
BB  Q221    CA          0     yes
BB  Q321    CA          500   yes
BB  Q421    CA          700   no
CC  Q121    AZ          50    no

Desired

ID  Date    Location    Used    Status
AA  Q121    NY                  closed
AA  Q221    NY                  closed
AA  Q321    NY                  closed
BB  Q121    CA          1       ok
BB  Q221    CA          0       yes
BB  Q321    CA          500     yes
BB  Q421    CA          700     no
CC  Q121    AZ          50      no

Doing

df['Used'] = np.where(((df.ID == 'AA') & (df.Date >= 'Q121')), '', df['Used'])
df['Status'] = np.where(((df.ID == 'AA') & (df.Date >= 'Q121')), 'closed', '')

The script above replaces 'ok' with 'closed' , however, it also deletes the remaining values in the column. Any suggestion is appreciated.

pandas numpy python
4 Answers

This should work -

df.loc[(df.ID == 'AA') & (df.Date >= 'Q121'), 'Used'] = ''
df.loc[(df.ID == 'AA') & (df.Date >= 'Q121'), 'Status'] = 'closed'
user_1964620

Please see the answer using np.where as stated in the question:

d = {
    'ID': ['AA', 'AA', 'AA', 'BB', 'BB'],
    'Date': ['Q121', 'Q221', 'Q321', 'Q121', 'Q221'],
    'Location': ['NY', 'NY', 'NY', 'CA', 'CA'],
    'Used': [20, 50, 10, 1, 0],
    'Status': ['ok', 'ok', 'ok', 'ok', 'yes']
}

df = pd.DataFrame(d)

idx = np.where((df.ID == 'AA') & (df.Date >= 'Q121'))[0].tolist()

df.loc[idx, 'Used'] = np.nan
df.loc[idx, 'Status'] = 'Closed'
user_195412230

You can use apply for this:

df['Used'],df['Status'] = zip(*df.apply(lambda x: ('', 'closed') if (x.ID == 'AA') and (x.Date >= 'Q121') else (x.Used, x.Status), axis = 1))
user_195456550 
df.loc[(df['ID'].eq('AA')) & (df['Date'] >= 'Q121'), 'Status'] = 'closed'
user_194751850
Related