CODEWITHSUNDEEP
pandasdataframepivotexport-to-excel
Scope
Scope

Reputation: 789

How to drop null values in dataframe after groupby while writing to excel

I have the following dfe:-

  ID CATEG   LEVEL     COLS    VALUE COMMENTS
    1   A        2     Apple    428  comment1
    1   A        3     Apple    175  comment1
    1   C        1     Apple    226  comment1
    1   C        2     Apple    884  comment1
    1   C        3     Apple    289  comment1
    1   B        1     Apple    712  comment1
    1   B        2     Apple    849  comment1
    2   B        3     Apple    376  comment1
    2   C        None  Orange   591  comment1
    2   B        None  Orange   135  comment1
    2   D        None  Orange   423  comment1
    2   A        None  Orange   866  comment1
    2            None  Orange   496  comment2

I want to pivot by one column COLS of dfe , groupby ID and write in excel such that each ID data is on one sheet. What I tried :-

df=pd.pivot_table(dfe,index=['ID','CATEG','LEVEL'],columns=['COLS'],values=['VALUE'])
    

with pd.ExcelWriter('file.xlsx',options={'nan_inf_to_errors': True}) as writer :
        df.groupby('ID').apply(lambda x: x.to_excel(writer,sheet_name=str(x.name),na_rep=0,index=True))
writer.save()

The problem I'm facing doing so is after the groupby many columns are 0, I want to remove columns which are null after the groupby and before writing to excel. I cannot remove null column before groupby as the whole column won't be null then

Upvotes: 1

Views: 171

Answers (1)

jezrael
jezrael

Reputation: 863246

You can remove all columns with only missing values by DataFrame.dropna by how='all' and axis=1 parameters:

with pd.ExcelWriter('file.xlsx',options={'nan_inf_to_errors': True}) as writer :
        df.groupby('ID').apply(lambda x: x.dropna(how='all', axis=1).to_excel(writer,sheet_name=str(x.name),na_rep=0,index=True))
writer.save()

Upvotes: 1

Related Questions