pandas :遍历DataFrames列表，并将每个导出到excel表 [英] Pandas: Iterate through a list of DataFrames and export each to excel sheets

问题描述

尝试自学编码以自动化工作中的一些繁琐任务.对于无意的无知，我深表歉意.

Trying to teach myself coding to automate some tedious tasks at work. I apologize for any unintentional ignorance.

我已经在熊猫(python 3.x)中创建了数据框.我想将每个数据框打印到不同的Excel工作表中.这是我拥有2个数据帧的功能，它可以完美工作，但我想对其进行缩放以遍历数据帧列表，以便使其更加动态.

I have created Data Frames in pandas (python 3.x). I want to print each data frame to a different excel sheet. Here is what I have for 2 Data Frames, it works perfect but I want to scale it to loop through a list of data frames so that I can make it a bit more dynamic.

writer = pandas.ExcelWriter("MyData.xlsx", engine='xlsxwriter')
Data.to_excel(writer, sheet_name="Data")
ByBrand.to_excel(writer, sheet_name="ByBrand")
writer.save()

这很容易，但是当需要创建50多个图纸时，它将变得很乏味.

Easy enough, but when there are 50+ sheets that need to be created it will get tedious.

这是我尝试过的方法，它不起作用:

Here is what I tried, it did not work:

writer = pandas.ExcelWriter("MyData.xlsx", engine='xlsxwriter')
List = [Data , ByBrand]
for i in List:
        i.to_excel(writer, sheet_name= i)
writer.save()

我认为问题在于sheet_name字段必须为字符串，因为按原样它会产生错误.但是，如果我将sheet_name ="i"放进去，它只会使用来自Data的数据创建一个名为"i"的工作表，而不会迭代到ByBrand.此外，如果未将工作表命名为相应的数据框，则excel文件将是一场噩梦，因此，对于编号表等事情，请不要提出任何建议.

I think the issue is that the sheet_name field must be a string because as-is it creates an error. But if I put sheet_name= "i", it only creates one sheet called "i" with the data from Data, but doesn't iterate to ByBrand. Also, the excel file would be a nightmare if the sheets weren't named to their corresponding data frame, so please no suggestions for things like numbered sheets.

非常感谢您，这个网站对于我的编码之旅非常宝贵.

Thank you so much in advance, this website has been invaluable for my journey into coding.

-斯蒂芬

推荐答案

从字符串'Data'到值Data相比，要容易得多.您可以使用locals()['Data']访问与字符串名称为'Data'的变量关联的值:

It is easier to go from the string 'Data' to the value Data than the other way around. You can use locals()['Data'] to access the value associated to the variable whose string name is 'Data':

import pandas as pd

writer = pd.ExcelWriter("MyData.xlsx", engine='xlsxwriter')
seq = ['Data', 'ByBrand']
for name in seq:
    df = locals()[name]
    df.to_excel(writer, sheet_name=name)
writer.save()

locals() 返回包含当前内容的只读字典范围的局部变量. globals() 返回包含当前范围的全局变量的字典. (因此，如果Data和ByBrand是在全局名称空间而不是本地名称空间中定义的，请使用globals()而不是locals().)

locals() returns a read-only dictionary containing the current scope's local variables. globals() returns a dictionary containing the current scope's global variables. (Thus, if Data and ByBrand are defined in the global namespace rather than the local namespace, use globals() instead of locals().)

另一个选择是在字典中收集DataFrame.而不是为每个DataFrame创建变量，而是做一个字典，然后将键作为工作表名称，将值作为DataFrames:

Another option is to collect the DataFrames in a dict. Instead of creating a variable for each DataFrame, make one dict, and let the keys be the sheet names and the values be DataFrames:

import pandas as pd

dfs = dict()
dfs['Data'] = ...
dfs['ByBrand'] = ...

writer = pd.ExcelWriter("MyData.xlsx", engine='xlsxwriter')
for name, df in dfs.items():
    df.to_excel(writer, sheet_name=name)
writer.save()

我认为这是可取的，因为它不需要像locals()或globals()这样的自省工具.第二种方法只是使用dict的用法:将键映射到值.

I think this is preferable since it does not require introspection tools like locals() or globals(). This second approach just uses a dict the way dicts are intended to be used: mapping keys to values.

这篇关于 pandas :遍历DataFrames列表，并将每个导出到excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！