基于insights列表生成指定结构Google Sheets文件的代码修正需求

问题背景 #

• insights是一个包含3个元素的列表：
  • 前两个元素为嵌套字典（例如follower_demographics包含city、gender、country、age四个子字典）
  • 第三个元素为普通字典
• 预期行为：列表中每个元素对应生成一个Google Sheets文件：
  • 嵌套字典元素：生成一个文件，文件内创建多个子工作表（每个子字典对应一个工作表）
  • 普通字典元素：生成一个文件，文件内创建一个工作表
• 当前问题：现有create_gsheet函数错误地将嵌套字典的每个子项生成为独立文件（比如为follower_demographics生成4个文件），不符合预期结构

数据结构 #

完整insights结构 #

[
    {
        'city': {
            'name': 'follower_demographics',
            'period': 'lifetime',
            'title': 'Follower demographics',
            'type': 'city',
            'description': 'The demographic characteristics of followers, including countries, cities and gender distribution.',
            'df': ''
        },
        'gender': {
            'name': 'follower_demographics',
            'period': 'lifetime',
            'title': 'Follower demographics',
            'type': 'gender',
            'description': 'The demographic characteristics of followers, including countries, cities and gender distribution.',
            'df': ''
        },
        'country': {
            'name': 'follower_demographics',
            'period': 'lifetime',
            'title': 'Follower demographics',
            'type': 'country',
            'description': 'The demographic characteristics of followers, including countries, cities and gender distribution.',
            'df': ''
        },
        'age': {
            'name': 'follower_demographics',
            'period': 'lifetime',
            'title': 'Follower demographics',
            'type': 'age',
            'description': 'The demographic characteristics of followers, including countries, cities and gender distribution.',
            'df': ''
        }
    },
    {
        'city': {
            'name': 'reached_audience_demographics',
            'period': 'lifetime',
            'title': 'Reached audience demographics',
            'type': 'city',
            'description': 'The demographic characteristics of the reached audience, including countries, cities and gender distribution.',
            'df': ''
        },
        'gender': {
            'name': 'reached_audience_demographics',
            'period': 'lifetime',
            'title': 'Reached audience demographics',
            'type': 'gender',
            'description': 'The demographic characteristics of the reached audience, including countries, cities and gender distribution.',
            'df': ''
        },
        'country': {
            'name': 'reached_audience_demographics',
            'period': 'lifetime',
            'title': 'Reached audience demographics',
            'type': 'country',
            'description': 'The demographic characteristics of the reached audience, including countries, cities and gender distribution.',
            'df': ''
        },
        'age': {
            'name': 'reached_audience_demographics',
            'period': 'lifetime',
            'title': 'Reached audience demographics',
            'type': 'age',
            'description': 'The demographic characteristics of the reached audience, including countries, cities and gender distribution.',
            'df': ''
        }
    },
    {
        'name': 'follower_count',
        'period': 'day',
        'title': 'Follower Count',
        'description': 'Total number of unique accounts following this profile',
        'df': ''
    }
]

简化结构 #

insights = [
    follower_demographics,  # 嵌套字典
    reached_demographics,   # 嵌套字典
    followers_count         # 普通字典
]

嵌套子字典结构 #

demographics = {
    'name': '',
    'period': '',
    'title': '',
    'type': '',
    'description': '',
    'df': ''
}

现有错误代码 #

def create_gsheet(insights, folder_id):
    try:
        # create a list to store the created files
        files = []

        # iterate over the items in the insights dictionary
        for idx, (key, value) in enumerate(insights.items()):
            # check if the value is a dictionary
            if isinstance(value, dict):
                # Create a new file with the name taken from the 'title' key
                file = gc.create(value['title'], folder=folder_id)

                print(f"Creating {value['title']} - {idx}/{len(insights)}")

                # add the file to the list
                files.append(file)

                
                # Create a new sheet within the file with the name taken from the 'name' key
                sheet = file.add_worksheet(value['type'] + '_' + value['name'])

                # Set the sheet data to the df provided in the dictionary
                sheet.set_dataframe(value['df'], (1,1), encoding='utf-8', fit=True)

                sheet.frozen_rows = 1


        # delete the default sheet1 from all the created files
        for file in files:
            file.del_worksheet(file.sheet1)

    except Exception as error:
        print(F'An error occurred: {error}')
        sheet = None

修正后的代码 #

def create_gsheet(insights, folder_id):
    try:
        files = []
        total_items = len(insights)
        
        for idx, item in enumerate(insights, 1):
            # 判断是否为嵌套字典（所有子项都是包含'title'的字典）
            is_nested = all(isinstance(sub_item, dict) and 'title' in sub_item for sub_item in item.values())
            
            if is_nested:
                # 从第一个子项获取主文件标题
                first_sub_item = next(iter(item.values()))
                file_title = first_sub_item['title']
                print(f"Creating file: {file_title} - {idx}/{total_items}")
                
                # 创建主文件
                file = gc.create(file_title, folder=folder_id)
                files.append(file)
                
                # 遍历嵌套的子字典，创建工作表
                for sub_key, sub_value in item.items():
                    sheet_name = f"{sub_value['type']}_{sub_value['name']}"
                    sheet = file.add_worksheet(sheet_name)
                    sheet.set_dataframe(sub_value['df'], (1,1), encoding='utf-8', fit=True)
                    sheet.frozen_rows = 1
                    
                # 删除默认Sheet1
                file.del_worksheet(file.sheet1)
            else:
                # 处理普通字典，创建单个文件和工作表
                file_title = item['title']
                print(f"Creating file: {file_title} - {idx}/{total_items}")
                
                file = gc.create(file_title, folder=folder_id)
                files.append(file)
                
                sheet_name = f"{item['type']}_{item['name']}" if 'type' in item else item['name']
                sheet = file.add_worksheet(sheet_name)
                sheet.set_dataframe(item['df'], (1,1), encoding='utf-8', fit=True)
                sheet.frozen_rows = 1
                
                # 删除默认Sheet1
                file.del_worksheet(file.sheet1)
                
        return files
                
    except Exception as error:
        print(f'An error occurred: {error}')
        return None

修正关键点 #

1. 遍历对象修正：原代码错误地遍历insights.items()，但insights是列表，改为遍历列表元素
2. 嵌套字典判断：新增逻辑判断当前元素是否为嵌套字典（所有子项都是带title的统计字典）
3. 单文件多工作表生成：对于嵌套字典，先创建一个主文件，再遍历所有子项生成对应的工作表
4. 普通字典处理：单独处理普通字典元素，生成单个文件和工作表
5. 统一删除默认Sheet：在每个文件创建完成后立即删除默认Sheet1，避免后续批量处理的潜在问题

内容的提问来源于stack exchange，提问作者schradernm