使用excel生成excel文件

Question

比如说我的数据结构：

data = {
        "ADVERTISING_AND_MARKETING":[
            "CREATIVE_AGENCY",
            "FULL_SERVICE_AGENCY",
            "BUYING_AGENCY",
 
        ],
        "AGRICULTURE":[
            "FARMING_AND_RANCHING",
            "FISHING_AND_HUNTING_AND_FORESTRY_AND_LOGGING"
        ],
        "AUTOMOTIVE":[
            "T1_MOTORCYCLE",
            "RECREATIONAL",
            "T1_AUTOMOTIVE_MANUFACTURER",
            "T2_DEALER_ASSOCIATIONS",
        ]
}

如何生成excel，如：

vetical                           subvertical        
 
ADVERTISING_AND_MARKETING         CREATIVE_AGENCY
                                  FULL_SERVICE_AGENCY
                                  BUYING_AGENCY
                                  

                                  
AGRICULTURE                       FARMING_AND_RANCHING
                                  FISHING_AND_HUNTING_AND_FORESTRY_AND_LOGGING
                                  
AUTOMOTIVE                        T1_MOTORCYCLE
                                  RECREATIONAL
                                  T1_AUTOMOTIVE_MANUFACTURER 
                                  T2_DEALER_ASSOCIATIONS

对不起，我真的不擅长pandas，希望你能帮我

谢谢

Answer 1

当您执行to_csv操作时，原始数据将作为输出发送到文件，因此为了删除这些重复值，您可以执行类似的操作。 有点老套，但很管用

df = pd.json_normalize(data = {
        "ADVERTISING_AND_MARKETING":[
            "CREATIVE_AGENCY",
            "FULL_SERVICE_AGENCY",
            "BUYING_AGENCY",
 
        ],
        "AGRICULTURE":[
            "FARMING_AND_RANCHING",
            "FISHING_AND_HUNTING_AND_FORESTRY_AND_LOGGING"
        ],
        "AUTOMOTIVE":[
            "T1_MOTORCYCLE",
            "RECREATIONAL",
            "T1_AUTOMOTIVE_MANUFACTURER",
            "T2_DEALER_ASSOCIATIONS",
        ]
})

df = df.T.explode(0).reset_index()   # Transpose and explode the list to multiple rows
df.loc[df['index'].duplicated(), 'index'] = '' # Find all duplicates in the column and replace them with empty strings
df.columns = ['verticals', 'subverticals']  # Assign/Rename the columns names
df.to_csv('output.csv', index=False)

Answer 2

你想要这个吗

df  = pd.json_normalize(data).transpose().explode(0).reset_index().rename(columns={'index': 'vertical', 0 :'subvertical'})
df.to_excel('out.xlsx', index=False)

输出-

                    vertical                                   subvertical
0  ADVERTISING_AND_MARKETING                               CREATIVE_AGENCY
1  ADVERTISING_AND_MARKETING                           FULL_SERVICE_AGENCY
2  ADVERTISING_AND_MARKETING                                 BUYING_AGENCY
3                AGRICULTURE                          FARMING_AND_RANCHING
4                AGRICULTURE  FISHING_AND_HUNTING_AND_FORESTRY_AND_LOGGING
5                 AUTOMOTIVE                                 T1_MOTORCYCLE
6                 AUTOMOTIVE                                  RECREATIONAL
7                 AUTOMOTIVE                    T1_AUTOMOTIVE_MANUFACTURER
8                 AUTOMOTIVE                        T2_DEALER_ASSOCIATIONS