使用pandas python从sheet1的数据中将sheet2添加到现有的excelfile中 [英] Adding sheet2 to existing excelfile from data of sheet1 with pandas python
问题描述
当我执行代码时,它仍然不会创建excel文件中的一个新工作表,只是用新的名称&所需的数据。
我已经创建了两个功能,第一个函数创建具有所需数据的Excel文件函数2获取列值&创建具有该列值的新工作表
这是功能2
def excelUpdate():
pre>
xls_file = pd.ExcelFile('Abc.xlsx')
df = xls_file.parse(0)
data = []
我在df.index中:
x = df ['Category'] [i]
print(df ['Category'] [i])
data.append(x)
table1 = pd.DataFrame(data)
table1.to_excel(writer,sheet_name ='Categories')
writer.save()
此外,我想获得表2中特定类别的计数。
请帮助
样本数据
我已经突出显示了我在第2页和第我想要表2中的每个类别的计数类别名称
索引| AppVersion |作者| **类别** |说明|评分|文本
0 | 1.15 | Miuwu | **慢** |值得| 1 |我在家时工作得很好,一周之后,3000英里远没有!
1 | 1.15 | abc | **问题** |自重启1 |没有这样的选择。
2 | 1.15 | Rax | **设计** |自重启1 |没有这样的选择。
3 | 1.15 | golo7 | **问题** |自重启1 |没有这样的选择。
4 | 1.15 | Marcog | **问题** |自重启1 |没有这样的选择。
解决方案您可以使用
openpyxl
,库熊猫
用于xlsx
,以实现这一点:从openpyxl导入pd
导入load_workbook
book = load_workbook('Abc.xlsx')
writer = pd.ExcelWriter('Abc.xlsx',engine ='openpyxl')
writer.book = book
writer.sheets = dict((ws.title,ws)for ws in book .worksheet)
然后,用你的
table1
准备:df ['Category']。value_counts()。to_frame()。to_excel(writer,sheet_name ='Categories' )
writer.save()
I am fetching data from web into an excel sheet using pandas & able to save it to sheet 1, now i want to fetch a column data into sheet 2 of same excel.
When I am executing the code it still doesn't create a new sheet in the excelfile, just overwrites the existing sheet with new name & desired data.
I have created two functions , first function create the excel file with desired data & function 2 to fetch the column values & create new sheet with that column values
This is Function 2
def excelUpdate(): xls_file = pd.ExcelFile('Abc.xlsx') df = xls_file.parse(0) data=[] for i in df.index: x=df['Category'][i] print(df['Category'][i]) data.append(x) table1 = pd.DataFrame(data) table1.to_excel(writer, sheet_name='Categories') writer.save()
Also I want to get the count of a particular category in sheet 2. Please help
Sample data
I have highlighted the data which I want in sheet 2 & I want the count of each Category in sheet 2 with category name
Index | AppVersion | Author | **Category** | Description | Rating | Text 0 | 1.15 | Miuwu | **Slow** | Worthless | 1 | Worked fine while I was home, a week later and 3000 miles away nothing!! 1 | 1.15 | abc | **Problem** | Self-reboot | 1 | No such option. 2 | 1.15 | Rax | **Design** | Self-reboot | 1 | No such option. 3 | 1.15 | golo7 | **Problem** | Self-reboot | 1 | No such option. 4 | 1.15 | Marcog | **Problem** | Self-reboot | 1 | No such option.
解决方案You can use
openpyxl
, the librarypandas
uses forxlsx
, to achieve this:import pandas as pd from openpyxl import load_workbook book = load_workbook('Abc.xlsx') writer = pd.ExcelWriter('Abc.xlsx', engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
Then, with your
table1
ready:df['Category'].value_counts().to_frame().to_excel(writer, sheet_name='Categories') writer.save()
这篇关于使用pandas python从sheet1的数据中将sheet2添加到现有的excelfile中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!