How to delete records which have values in specific columns?-CodePudding

I have merged 2 dataframes, evt_df and eff_df. Evt_df contains columns like evt_id, evt_name, evt_email, eff_df contains columns like eff_id, eff_name, eff_email. I want to delete records which have values in columns which start with 'eff'.

I got:

EVT_ID  EVT_STATUS  EVT_VALIDFROM   EFF_ID  EFF_STATUS  EFF_VALIDFROM
ORLE-20210205   VAL 19.02.2021 12:48    ORLE-20210205   VAL 28.03.2021 12:54
ORLE-20210205   VAL 19.02.2021 12:48    ORLE-20210205   VAL 28.03.2021 12:54
ORLE-20210305   VAL 17.03.2021 15:12    ORLE-20210305   VAL 30.03.2021 09:48
ORLE-20210305   VAL 17.03.2021 15:12            
ORLE-20200304   VAL 05.03.2020 18:36            
ORLE-20200304   VAL 05.03.2020 18:36            
ORLE-20190930   VAL 01.10.2019 12:04            
ORLE-20200304   VAL 05.03.2020 18:36

I want:

EVT_ID  EVT_STATUS  EVT_VALIDFROM   EFF_ID  EFF_STATUS  EFF_VALIDFROM
ORLE-20210305   VAL 17.03.2021 15:12            
ORLE-20200304   VAL 05.03.2020 18:36            
ORLE-20200304   VAL 05.03.2020 18:36            
ORLE-20190930   VAL 01.10.2019 12:04            
ORLE-20200304   VAL 05.03.2020 18:36

I tried:

df = pd.read_excel('C:/Users/PI33588/Desktop/eff_month_all.xlsx')
print(len(df.index))
df = df[(df.filter(regex='EFF', axis=1)=='').any(1)]
print(len(df.index))

Got; 8 0

CodePudding user response：

Run this code: data.loc[:,data.columns.str.startswith('eff')] = None

Here's my example:

CodePudding user response：

filter columns strating iwth eff and rows that have empty spaces

df[(df.filter(regex='^eff', axis=1)=='').any(1)]



evt_id evt_name evt_email eff_id eff_name eff_email
     x      xxx       xxx                          
     x      xxx       xxx                          
     x      xxx       xxx