蟒蛇熊猫没有在Excel文件中创建多个选项卡？

Question

我有一个从第三方API中提取的python脚本。该脚本循环运行 3 个不同的城市，并为每个城市创建一个数据框。然后，我将数据框作为选项卡传输到Excel工作表中。下面是代码。

    sublocation_ids = [

                {

                  "id": 163,

                  "name": "Atlanta, GA"

                },

                {

                  "id": 140,

                  "name": "Austin, TX"

                },

                {

                  "id": 164,

                  "name": "Baltimore, MD"

                } 

             ]

filter_text = "(headline:coronavirus OR summary:coronavirus OR headline:covid-19 OR summary:covid-19) AND categories:{}"

writer = pd.ExcelWriter(excel_path)

    for sub in sublocation_ids:

        city_num_int = sub['id']

        city_num_str = str(city_num_int)

        city_name = sub['name']

        filter_text_new = filter_text.format(city_num_str)

        data = json.dumps({"filters": [filter_text_new], "sort_by":"created_at", "size":2})

        r = requests.post(url = api_endpoint, data = data).json()

        articles_list = r["articles"] 

        articles_list_normalized = json_normalize(articles_list)

        df = articles_list_normalized

        df['publication_timestamp'] = pd.to_datetime(df['publication_timestamp'])

        df['publication_timestamp'] = df['publication_timestamp'].apply(lambda x: x.now().strftime('%Y-%m-%d'))

        df.to_excel(writer, sheet_name = city_name)

        writer.save()

我面临的当前问题是，只有一个选项卡是在excel工作表中创建的，用于我从API中提取数据的第一个城市“亚特兰大，GA”。如何为目录中的每个城市创建选项卡，或者我的代码是否有任何问题？

Answer 1

请参阅文档中的以下部分：df.to_excel()如果要写入工作簿中的多个工作表，则必须指定一个 ExcelWriter 对象：df2 = df1.copy()with pd.ExcelWriter('output.xlsx') as writer:      df1.to_excel(writer, sheet_name='Sheet_name_1')    df2.to_excel(writer, sheet_name='Sheet_name_2') 因此，您可能需要退出循环。writer.save()

Answer 2

我不能代表你的代码，因为我不能运行它，“filter_text”似乎是你编写但不包括的函数。基本上你有两个错误之一，我可以看到，首先，不清楚您在哪里初始化对象。writer第二，你用每个循环覆盖工作表 - 将其移动到循环之外。pd.ExcelFile可以用作上下文管理器 - 因此您需要关闭/保存它。def close(self):&nbsp; &nbsp; """synonym for save, to make it more file-like"""&nbsp; &nbsp; return self.save()&nbsp;writer = pd.ExcelWriter('file.xlsx')for sub in sublocation_ids:&nbsp; &nbsp; city_num_int = sub['id']&nbsp; &nbsp; city_num_str = str(city_num_int)&nbsp; &nbsp; city_name = sub['name']&nbsp; &nbsp; filter_text_new = filter_text.format(city_num_str)&nbsp; &nbsp; data = json.dumps({"filters": [filter_text_new], "sort_by":"created_at", "size":2})&nbsp; &nbsp; r = requests.post(url = api_endpoint, data = data).json()&nbsp; &nbsp; articles_list = r["articles"]&nbsp;&nbsp; &nbsp; articles_list_normalized = json_normalize(articles_list)&nbsp; &nbsp; df = articles_list_normalized&nbsp; &nbsp; df['publication_timestamp'] = pd.to_datetime(df['publication_timestamp'])&nbsp; &nbsp; df['publication_timestamp'] = df['publication_timestamp'].apply(lambda x: x.now().strftime('%Y-%m-%d'))&nbsp; &nbsp; df.to_excel(writer, sheet_name = city_name)writer.save() # move this after you've finished writing to your writer object.表格作为字典如果您对类的内部感到好奇，请在对象上使用，以便您可以看到元数据。.__dict__.writer = pd.ExcelWriter('file.xlsx')df.to_excel(writer,sheet_name='Sheet1')df.to_excel(writer,sheet_name='Sheet2')print(writer.__dict__){'path': 'file.xlsx',&nbsp;'sheets': {'Sheet1': <xlsxwriter.worksheet.Worksheet at 0x11a05a79a88>,&nbsp; 'Sheet2': <xlsxwriter.worksheet.Worksheet at 0x11a065218c8>},&nbsp;'cur_sheet': None,&nbsp;'date_format': 'YYYY-MM-DD',&nbsp;'datetime_format': 'YYYY-MM-DD HH:MM:SS',&nbsp;'mode': 'w',&nbsp;'book': <xlsxwriter.workbook.Workbook at 0x11a064ff1c8>}