写入Excel时如何在groupby之后在数据框中删除空值 [英] How to drop null values in dataframe after groupby while writing to excel

问题描述

我有以下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

我想用dfe的一列COLS和分组pivot，并用excel编写，以使每个ID数据都在一张纸上. 我试过的:-

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

我遇到的问题是在groupby的许多列为0之后，我想删除在groupby之后且写入excel之前为空的列.我无法在groupby之前删除空列，因为整个列将不为空

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

推荐答案

您可以通过 查看全文