0

I have a dataset in excel with this table and I want to use pandas.

 Date      Hour       SO2     Flag
22/05/2023  14:00     0.75      V
22/05/2023  15:00     0.79      M
22/05/2023  16:00     0.75      V
22/05/2023  17:00     0.79      V        
22/05/2023  18:00     0.65      Z

I want to replace the values of a cell in "SO2" with NaN if the letter of the right cell in the column "Flag" is M or Z.

I want the table looks like:

 Date      Hour       SO2     Flag
22/05/2023  14:00     0.75      V
22/05/2023  15:00     NaN       M
22/05/2023  16:00     0.75      V
22/05/2023  17:00     0.79      V        
22/05/2023  18:00     NaN      Z

And then export to an xlxs.

I could try with replace but I don't know how to modify xls.replace to add an if:

import numpy as np

import pandas as pd


xls = pd.read_excel('/content/data.xls', sheet_name='Horario')

data2 = xls.replace('*', '')

data2.to_excel('/content/fil2.xlsx',index=False)
docwho
  • 1
  • 2

2 Answers2

0

You can use a mask here:

import numpy as np

import pandas as pd


xls = pd.read_excel('/content/data.xls', sheet_name='Horario')

replace_mask = xls['Flag'].isin(['M','Z'])
xls.loc[replace_mask,'SO2'] = np.nan

data2.to_excel('/content/fil2.xlsx',index=False)
Learning is a mess
  • 7,479
  • 7
  • 35
  • 71
0

The loc accessor is used to locate the rows where the corresponding value in the Flag column is either M or Z, and the values in the SO2 column of those rows are replaced with NaN using the float('nan') syntax.

# Import file as you did

xls.loc[xls['Flag'].isin(['M', 'Z']), 'SO2'] = float('nan')

# Export file as you did
Aleff
  • 61
  • 8