python数据写入到excel不同sheet,使用pandas python从sheet1的数据中将sheet2添加到现有的excelfile中...

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 library pandas uses for xlsx, 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()