Keep the original sheet existing while creating a new one in the same excel file-CodePudding

I am trying to copy 3 columns from "Old Sheet"(which already exists) to "New Sheet" (which hasn't been created yet). The excel filee is as follows.

However, after the copy was completed, "Old Sheet" was gone.

My code is as follows.

import pandas
from openpyxl import load_workbook

file_source =r'C:\Users\user\Desktop\Data.xlsx'

cols = ['iso2','cnum','country']

copy_columns=pd.read_excel(file_source, sheet_name="Old Sheet", usecols=cols).to_excel(file_source, sheet_name="New Sheet", index=False)

I want to keep both of "Old Sheet" and "New Sheet" existing. Please help me correcting the code.

CodePudding user response：

import pandas as pd

df = pd.DataFrame(zip(['a','b','c'],[1,2,3],[6,7,5]), columns=['name','val_1','val_2'])

df.to_excel('data.xlsx',sheet_name='old_sheet',index=False)

new_df = pd.read_excel('data.xlsx',sheet_name='old_sheet', usecols=['name','val_2'])
new_df['val_2']  = 10

with pd.ExcelWriter('data.xlsx', engine='openpyxl', mode='a') as writer:
    new_df.to_excel(writer, sheet_name='new_sheet',index=False)


old = pd.read_excel('data.xlsx',sheet_name='old_sheet')
new = pd.read_excel('data.xlsx',sheet_name='new_sheet')

print(old)
>>
  name  val_1  val_2
0    a      1      6
1    b      2      7
2    c      3      5


print(new)
>>
  name  val_2
0    a     16
1    b     17
2    c     15