pandas Python：将数据输出到 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/52823481/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python: Output Data to Excel Spreadsheet
提问by Anthony
I need to do what is probably very simple for an experienced coder. My Python program successfully does the following:
对于有经验的编码员来说，我需要做的可能非常简单。我的 Python 程序成功执行以下操作：
- Import an excel spreadsheet
- Iterate through the rows and count repeating occurrences of a "Model", whether the model (Location) is consumed or not, and who "consumed" the model (and how many they've consumed). Anything not "Consumed" will be considered "Available".
- Print the "Model", "Consumed", "Available", and "Requestor"(someone who's consumed the model).
- 导入 Excel 电子表格
- 遍历行并计算“模型”的重复出现次数，模型（位置）是否被消耗，以及谁“消耗”了模型（以及他们消耗了多少）。任何不是“已消费”的东西都将被视为“可用”。
- 打印“Model”、“Consumed”、“Available”和“Requestor”（使用过模型的人）。
Instead of just printing the values, I need them to be added to a sheet on a excel workbook with Model, Consumed, Avaible, and Requestors as the column headers.
我需要将它们添加到 Excel 工作簿上的工作表中，而不是仅打印值，并将模型、已使用、可用和请求者作为列标题。
Here's how I'm printing the values:
这是我打印值的方式：
if not REQUESTORLIST:
print(CURRENTMODEL, "Consumed:", CONSUMEDCOUNTER, "Available:", AVAILABLECOUNTER)
else:
print(CURRENTMODEL,"Consumed:",CONSUMEDCOUNTER, "Available:",AVAILABLECOUNTER,REQUESTORS)
Here are the data types:
以下是数据类型：
print(type(CURRENTMODEL))
print(type(CONSUMEDCOUNTER))
print(type(AVAILABLECOUNTER))
print(type(REQUESTORS))
Outputs:
输出：
<class 'str'>
<class 'int'>
<class 'int'>
<class 'collections.Counter'>
Finally, program outputs truncated:
最后，程序输出被截断：
Model WS-SFP Consumed: 1 Available: 2 Counter({'Requester Anthony House': 1})
I'm brand new to programming (this is the first program I've written) and am having trouble finding a way to get these values to write to an excel sheet with the four column headers I need. I've tried to convert them to strings and use .write but have been unsuccessful so far. Do you have any suggestions?
我是编程的新手（这是我编写的第一个程序）并且无法找到将这些值写入带有我需要的四个列标题的 Excel 工作表的方法。我试图将它们转换为字符串并使用 .write 但到目前为止没有成功。你有什么建议吗？
EDIT: Thanks for the quick responses. I'm thinking it may be helpful for me to just post my code. I'm open to feedback on how to optimize this thing as it's likely subpar. I've been experimenting with .write, which skips rows in the excel sheet output, doesn't fill column headers, etc. Probably not my best option.
编辑：感谢您的快速回复。我认为发布我的代码可能对我有帮助。我愿意接受关于如何优化这个东西的反馈，因为它可能低于标准。我一直在试验 .write，它跳过 Excel 工作表输出中的行，不填充列标题等。可能不是我最好的选择。
import os
import openpyxl
import matplotlib
import numpy
import pandas as pd
import xlrd
import xlwt
from xlwt import Workbook
import xlsxwriter
from collections import Counter
#file to pull data from
excel_file = 'Customer_Inventory_Test.xlsx'
models = pd.read_excel(excel_file)
#file to export results
workbook = xlsxwriter.Workbook('Inventory Report.xlsx')
worksheet = workbook.add_worksheet()
row = 0
col = 0
ROWCOUNT = models.shape[0]
while True:
CONSUMEDCOUNTER = 0
AVAILABLECOUNTER = 0
REQUESTORLIST = []
#break when no more rows
if row == ROWCOUNT:
break
MODEL = models.iloc[row, [0]]
#convert to string for comparison
MODEL = MODEL.to_string()
CURRENTMODEL = MODEL
LOCATION = models.iloc[row, [2]]
LOCATION = LOCATION.to_string()
while CURRENTMODEL == MODEL:
if "Consumed" in LOCATION:
CONSUMEDCOUNTER += 1
REQUESTOR = models.iloc[row, [17]]
# convert to string for comparison
REQUESTOR = REQUESTOR.to_string()
REQUESTORLIST.append(REQUESTOR)
else:
AVAILABLECOUNTER += 1
row += 1
if row == ROWCOUNT:
break
MODEL = models.iloc[row, [0]]
MODEL = MODEL.to_string()
LOCATION = models.iloc[row, [2]]
LOCATION = LOCATION.to_string()
REQUESTORS = Counter(REQUESTORLIST)
if not REQUESTORLIST:
worksheet.write(row, col, CURRENTMODEL)
worksheet.write(row, col + 1, CONSUMEDCOUNTER)
worksheet.write(row, col + 2, AVAILABLECOUNTER)
print(CURRENTMODEL[9:], "Consumed:", CONSUMEDCOUNTER, "Available:",
AVAILABLECOUNTER)
else:
worksheet.write(row, col, CURRENTMODEL)
worksheet.write(row, col + 1, CONSUMEDCOUNTER)
worksheet.write(row, col + 2, AVAILABLECOUNTER)
#worksheet.write(row, col + 3, REQUESTORS) <- Doesn't like
#requestors data structure
print(CURRENTMODEL[9:],"Consumed:",CONSUMEDCOUNTER,
"Available:",AVAILABLECOUNTER,REQUESTORS)
workbook.close()
回答by smj
Welcome to Stack Overflow. Check out this DataFrame method: .to_excel(). You need to call this method from your dataframe, providing a file path, which will be where the new Excel file will be located. An example: my_data_frame.to_excel('path/to/my/new_file.xlsx').
欢迎使用堆栈溢出。查看此 DataFrame 方法：.to_excel()。您需要从数据框中调用此方法，提供一个文件路径，该路径将是新 Excel 文件所在的位置。一个例子：my_data_frame.to_excel('path/to/my/new_file.xlsx')。
回答by andre.brito
You could provide more details of your code, so we can help you more easily. Anyway, there are some ways you can do what you need, but I recommend you to use the library pandas.
您可以提供代码的更多详细信息，以便我们更轻松地为您提供帮助。无论如何，有一些方法可以满足您的需求，但我建议您使用库 pandas。
Here are some links with examples.
这里有一些带有示例的链接。
https://www.datacamp.com/community/tutorials/pandas-tutorial-dataframe-pythonhttps://xlsxwriter.readthedocs.io/working_with_pandas.html
https://www.datacamp.com/community/tutorials/pandas-tutorial-dataframe-python https://xlsxwriter.readthedocs.io/working_with_pandas.html
PS: You can even perform your counts using the structures of the pandas.
PS：您甚至可以使用Pandas的结构进行计数。
回答by Anthony
I did more experimenting with converting the list back into a dataframe, and it looks like I have a working solution for now.
我做了更多的尝试将列表转换回数据框，看起来我现在有一个可行的解决方案。
Here's what I've got:
这是我所拥有的：
#NEWLIST is the list that changes with each loop iteration.
NEWLIST = [(CURRENTMODEL[9:], CONSUMEDCOUNTER, AVAILABLECOUNTER, REQUESTORS)]
CURRENTLIST.extend(NEWLIST)
df = pd.DataFrame(CURRENTLIST, columns=["Model","Consumed","Available","Requestor(s)"])
writer = pd.ExcelWriter('Inventory Summary Report.xlsx')
df.to_excel(writer,'Sheet1',freeze_panes=(1,1), index=False)
