CODEWITHSUNDEEP
excelpython-3.xpandascsv
think-maths
think-maths

Reputation: 967

Creating csv file from pandas dataframe as per the sheet names in file from condition over a column

I have a excel file which has multiple sheets(6) in it. I am writing a python script to convert the each individual sheet into a csv file.

My input file looks like this and this is for example sheetname = class5

Name   ID  Result

Mark   11   Pass
Tom    22   Fail
Jane   33   Pass
Colin  44   Not Appeared

like this I have multiple sheets in the excel

I need to convert them in csv file having just 'Name' and sheetname for only 'pass' and 'fail' candidates and not for 'not appeared'candidates like this:

csv file to be created class5.csv which has content just:

Mark,class5
Tom,class5
Jane,class5

Note: No 'colin' as he did not appear

This one one sheet like this I have multiple sheets so I wrote the script which reads the excel and creates the Name,class csv file but I am not able to put filter from the column 'Result' for not displaying 'Not appeared' as now for this code I have all the Pass, fail and not appeared 

xls = pd.read_excel('path_of_file',sheet_name = None)
for sheet_name, df in xls.items():
    df['sheet'] = sheet_name
    df[['Name','sheet']].to_csv(f'{sheet_name}.csv', header=False)

Need to know how I can add filter to my Result column to remove just 'Not appeared' students name?

Upvotes: 1

Views: 836

Answers (1)

jezrael
jezrael

Reputation: 862511

You can filter by condition and also by column name Name with boolean indexing and DataFrame.loc:

xls = pd.read_excel('path_of_file',sheet_name = None)
for sheet_name, df in xls.items():
    df = df.loc[df["Result"] != 'Not Appeared', ['Name']].copy()
    df['sheet'] = sheet_name
    df.to_csv(f'{sheet_name}.csv', header=False)

Upvotes: 1

Related Questions