CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
babis95
babis95

Reputation: 622

How to save DataFrame to Sheet2 in xlsx file

I created a simple DataFrame using Pandas, and I need to add/replace it to Sheet2 in my Xlsx File. Sheets in Xlsx files are: Sheet1, Sheet2 I have two problems:

First being that even tho I specify the sheetName, it all it does is removes all sheets that were already in the database, and creates only one with the name specified with the data.

Second is I can't remove the column index from the dataframe. I tried adding 'index_col=None' but all it does is causes errors.

Code So Far:

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import numpy as np

Number1 = '123456'
Number2 = '987654'
Number3 = '888888'
Comp = 'HelloAll'
excel_file = 'C:/Users/username/Desktop/testFile.xlsx'

data = {'Number1':  [Number1],
        'Number2':  [Number2],
        'Number3':  [Number3],
        'Comp':  [Comp]
        }

df = pd.DataFrame (data, columns = ['Number1','Number2', 'Number3', 'Comp'])
pd.set_option('display.max_columns', None)
print (df)
with ExcelWriter(excel_file) as writer:
    df.to_excel(writer, sheet_name='Sheet2')

Expected Result:

Number1 Number2 Number3 Comp
123456  987654  888888  HelloAll

Upvotes: 3

Views: 1300

Answers (2)

geekzeus
geekzeus

Reputation: 895

#pip install openpyxl

writer = pd.ExcelWriter('output.xlsx')  
df.to_excel(writer,"Sheetnamethatyouwant")  
writer.save()

EDIT

#For adding new data

from openpyxl import load_workbook

path = r"path of xlsx that you want to add"
book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

df.to_excel(writer, "sheetnamryouwant")
writer.save()

Upvotes: 0

Dimitris Thomas
Dimitris Thomas

Reputation: 1393

1) In order to preserve the sheets of your file the first thing you need to do is import all of them and then re-write them. One way that works good for me is using xlsxwriter:

import pandas as pd

# Read the 1st sheet and import it as first dataframe
#df1 = pd.read_excel('testFile.xlsx', sheet_name='Sheet1')
df1 = pd.DataFrame({'A': [1,2,3], 'B': [4,5,6]}) # for this example i created a simple df

# Read the 2nd sheet and import it as second dataframe
#df2 = pd.read_excel('testFile.xlsx', sheet_name='Sheet2')

# Do some data manipulation
Number1 = '123456'
Number2 = '987654'
Number3 = '888888'
Comp = 'HelloAll'

data = {'Number1': [Number1],
        'Number2': [Number2],
        'Number3': [Number3],
        'Comp': [Comp]}

df2 = pd.DataFrame (data, columns = ['Number1','Number2', 'Number3', 'Comp'])

# Kickstart the xlsxwriter
writer = pd.ExcelWriter('testFile.xlsx', engine='xlsxwriter')
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)

# Finally write the file
writer.save()

2) About your second problem just use the argument index=False when you write the file.

Upvotes: 2

Related Questions