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

Question

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.

KalEl · Accepted Answer · 2022-07-14 00:14:30Z

3

This should work -

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

answered Jul 14, 2022 at 0:14

KalEl

9,18613 gold badges51 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barry the Platipus · Accepted Answer · 2022-07-14 00:10:14Z

1

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

answered Jul 14, 2022 at 0:10

Barry the Platipus

10.5k2 gold badges9 silver badges33 bronze badges

Comments

SunStorm44 · Accepted Answer · 2022-07-14 00:15:56Z

1

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'

answered Jul 14, 2022 at 0:15

SunStorm44

818 bronze badges

Comments

Alexandre Guedes · Accepted Answer · 2022-07-14 01:39:39Z

1

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))

edited Jul 14, 2022 at 1:39

answered Jul 14, 2022 at 0:28

Alexandre Guedes

313 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related