Adding sheet2 to existing excelfile from data of s

Question

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.

Answer 1

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()