Creating and saving data new sheet in excel using python

Question

I am absolute beginner, In the below code I have used pivot table , now I want to store the pivot table in different sheet(eg.:"Sheet 2" &"sheet 3"). how can I create a new sheet and save the data in it. is it possible to save the entire work in the exciting worksheet(here in my case :"Stratification_worksheet.xlxs") and can check via MS Excel??

#importing pandas
import pandas as pd
import numpy as np
#Assigning the worksheet to file
file="Stratification_worksheet.xlsx"
#Loading the spreadsheet 
data= pd.ExcelFile(file)
#sheetname
print(data.sheet_names)
#loading the sheetname to df1
df=data.parse("Auftrag")
print(df)
# creating tuples
L1=["PMC11","PMP11","PMP21","PMC21","PMP23"]
L2=["PTP33B","PTP31B","PTC31B"]
m1=df["ordercode"].str.startswith(tuple(L1))
m2=df["ordercode"].str.startswith(tuple(L2))
#creating a new column preessurerange and slicing the pressure range from order code
a=df["ordercode"].str.slice(10,12)
b=df["ordercode"].str.slice(11,13)
df["pressurerange"]= np.select([m1,m2],[a,b], default =np.nan)
print(df)
#creating a new column Pressureunit and slicing the pressure unit from ordercode
c=df["ordercode"].str.slice(12,13)
d=df["ordercode"].str.slice(14,15)
df["pressureunit"]= np.select([m1,m2],[c,d], default =np.nan)
print(df)  

#creating a tempcolumn to store pressurerange and pressure unit 
df["pressuresensor"]=df["pressurerange"] + df["pressureunit"]
print(df)

#pivottable 
print(df.pivot_table(values="total",columns="pressuresensor",aggfunc={"total":np.sum}))

print(df.pivot_table(values="total",columns="pressurerange",aggfunc={"total":np.sum}))

#check the columns
print(list(df))
print(df.dtypes)

To clarify, do you want to save a new sheet in an existing excel file? — liam
– liam, Commented Sep 3, 2018 at 12:13
https://xlsxwriter.readthedocs.io/working_with_pandas.html should help. — Haskar P
– Haskar P, Commented Sep 3, 2018 at 12:36

liam · Accepted Answer · 2018-09-03 12:53:56Z

1

If you ever want to write without losing the original data then use this

import pandas as pd
import numpy as np
from openpyxl import load_workbook

path = r"C:\Users\..."

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name = 'new')
df4.to_excel(writer, sheet_name = 'alsonew')
writer.save()
writer.close()

answered Sep 3, 2018 at 12:53

liam

2,0223 gold badges25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

i1100362 Over a year ago

thanks, It works. but whenever I run the code the new sheets are keep on gets created. can we stop that?

Collectives™ on Stack Overflow

Creating and saving data new sheet in excel using python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related