CODEWITHSUNDEEP
pythonexcel
i1100362
i1100362

Reputation: 259

Creating and saving data new sheet in excel using python

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)

Upvotes: 0

Views: 2993

Answers (1)

liam
liam

Reputation: 2014

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

Upvotes: 1

Related Questions