Untitled

This is a quick script on how to save Python pivot_table in an excel file.

Credit to  pbpython

In [1]:
import sqlalchemy
import pyodbc
from pandas import DataFrame
import pandas as pd

 

In [2]:
df = DataFrame


## connect to SqlServer Database and get information. 
try:

    import urllib
    params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};SERVER=.\MSSQLSERVER_ENT;DATABASE=Antrak;Trusted_Connection=yes;")

    engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect=%s"' % params)
    resoverall = engine.execute("SELECT * FROM FlightBookingsMain")


    df = df(resoverall.fetchall())
    df.columns = resoverall.keys()

    
except (RuntimeError, TypeError, NameError):
    print('Error in Conneccting')
    print(RuntimeError, TypeError, NameError)
finally:
    print("connected")

 

connected
In [8]:
df.head()

 

Out[8]:
First Name Last Name Sex Phone Fax Email Address Booking Date Departure Date Arrival Date Address1 Number of Passengers Total Cost Currency Numeric Code Company website Flight ID Number Flight Booking ID Insurance AFP Flight Name Age
0 Ellis Harris m 077 5665 3370 077 5665 3373 Ellis.E.Harris@mailinator.com 2012-12-18 2011-07-16 2000-08-23 63 Hull Road 2 983 710 http://qvc.com 4111113236285560 1 16 62 ZAR 62
1 Aaron Field m 079 6068 9016 079 6068 9015 Aaron.A.Field@mailinator.com 2014-10-28 2005-02-26 2000-07-30 42 Russell Rd 9 2452 710 http://msnbc.com 4111110402728960 2 83 38 ZAR 44
2 Archie Kaur m 070 3056 9804 070 3056 9809 Archie.M.Kaur@spambob.com 2003-11-17 2000-12-05 2007-03-07 10 Southend Avenue 8 1710 710 http://ap.org 4111110879255550 3 54 58 ZAR 60
3 Bradley Middleton m 070 0765 7814 070 0765 7816 Bradley.A.Middleton@dodgeit.com 2013-07-18 2011-02-26 2015-10-25 32 Pier Road 6 518 710 http://tripadvisor.com 4111111855573320 4 72 43 ZAR 66
4 Evan Reeves m 070 8266 4451 070 8266 4458 Evan.A.Reeves@dodgeit.com 2006-07-20 2006-06-20 2001-02-25 75 Moulton Road 7 1943 710 http://aliexpress.com 4111113022178850 5 68 52 ZAR 38

5 rows × 25 columns

In [9]:
table = pd.pivot_table(df, index=['Flight Name','First Name','Email Address'], values=['AFP', 'Age','Total Cost'], aggfunc = np.sum)
table

 

Out[9]:
AFP Age Total Cost
Flight Name First Name Email Address
AED Aaron Aaron.C.Savage@pookmail.com 281 136 7263
Adam Adam.H.Morley@trashymail.com 269 162 11194
Alfie Alfie.A.Noble@trashymail.com 267 230 9196
Amy Amy.J.Cox@spambob.com 256 221 4570
Archie Archie.H.Young@dodgeit.com 174 190 6641
Brandon Brandon.I.Gardiner@trashymail.com 170 156 10360
Charles Charles.K.Swift@spambob.com 288 166 4426
Christopher Christopher.R.Wall@dodgeit.com 185 149 4168
Connor Connor.F.Noble@spambob.com 196 111 9669
David David.M.Hutchinson@trashymail.com 294 136 7330
Edward Edward.S.Glover@mailinator.com 346 163 6607
Ellis Ellis.E.Skinner@pookmail.com 262 139 10432
Eve Eve.W.Jordan@trashymail.com 173 201 5309
Finley Finley.G.Winter@trashymail.com 308 174 10035
Georgia Georgia.B.Morgan@spambob.com 181 225 7915
Harley Harley.J.Naylor@spambob.com 199 166 8650
Harvey Harvey.L.Cooke@trashymail.com 173 187 12930
Holly Holly.L.Jenkins@spambob.com 209 153 8276
Jack Jack.G.Cartwright@dodgeit.com 216 164 10413
James James.E.Hutchinson@pookmail.com 201 168 9079
Jasmine Jasmine.A.Jarvis@trashymail.com 246 161 7528
Jodie Jodie.D.Ward@mailinator.com 108 208 7936
Katherine Katherine.A.Wallis@dodgeit.com 229 178 8430
Lara Lara.C.Field@spambob.com 246 172 4483
Leon Leon.A.Howarth@trashymail.com 270 176 8699
Lewis Lewis.O.Humphreys@spambob.com 283 212 8512
Logan Logan.I.Potts@trashymail.com 248 149 7535
Luca Luca.V.Mistry@spambob.com 198 161 7450
Maisie Maisie.E.Gould@mailinator.com 107 173 9824
Megan Megan.L.Wall@spambob.com 179 168 10045
ZWD Eve Eve.J.Richardson@mailinator.com 293 175 6929
Francesca Francesca.J.Harvey@mailinator.com 282 147 9915
Georgia Georgia.L.Thorpe@trashymail.com 262 196 7209
Harley Harley.J.Summers@trashymail.com 262 203 5583
Harvey Harvey.K.Hewitt@dodgeit.com 220 138 11835
Holly Holly.F.Saunders@spambob.com 230 209 9093
Jack Jack.H.Hardy@trashymail.com 204 198 8439
James James.N.Thomas@spambob.com 253 186 11881
Jasmine Jasmine.J.Perkins@spambob.com 258 163 12918
Joe Joe.E.Dale@pookmail.com 293 133 8577
Katie Katie.A.Fisher@pookmail.com 190 234 6150
Lara Lara.J.Rogers@mailinator.com 240 203 9293
Leon Leon.M.Pickering@dodgeit.com 272 189 6741
Lewis Lewis.R.Owen@trashymail.com 301 234 7407
Lola Lola.S.Browne@pookmail.com 214 215 7019
Lucas Lucas.L.Akhtar@pookmail.com 244 233 9245
Maisie Maisie.J.Nicholls@mailinator.com 244 236 9458
Melissa Melissa.A.Cartwright@trashymail.com 277 220 6727
Mollie Mollie.L.Cross@spambob.com 121 182 6956
Naomi Naomi.R.Peacock@dodgeit.com 136 162 8390
Noah Noah.Z.Begum@trashymail.com 333 193 10936
Olivia Olivia.M.Patel@mailinator.com 289 150 4494
Patrick Patrick.A.Stone@trashymail.com 211 162 8540
Riley Riley.M.Kerr@mailinator.com 120 207 8806
Samuel Samuel.R.Iqbal@pookmail.com 213 146 7014
Sebastian Sebastian.M.Anderson@dodgeit.com 178 209 6409
Sophia Sophia.N.Norman@dodgeit.com 222 138 7418
Summer Summer.N.Fry@spambob.com 282 91 7844
Tia Tia.L.Kirk@dodgeit.com 124 202 9477
Zachary Zachary.T.Hobbs@dodgeit.com 256 152 6878

10000 rows × 3 columns

In [10]:
table.xs('ZWD', level=0)

 

Out[10]:
AFP Age Total Cost
First Name Email Address
Adam Adam.S.Wallace@pookmail.com 221 163 9092
Alfie Alfie.R.Tomlinson@mailinator.com 288 169 5507
Alice Alice.H.Palmer@pookmail.com 111 197 9807
Amy Amy.T.Jordan@dodgeit.com 181 173 10045
Ava Ava.N.Gallagher@mailinator.com 262 182 9251
Brandon Brandon.M.Lynch@pookmail.com 153 164 6210
Charlie Charlie.E.Bradshaw@mailinator.com 260 149 6768
Connor Connor.K.Smith@dodgeit.com 282 155 9413
David David.C.Savage@spambob.com 226 188 4439
Edward Edward.L.Little@dodgeit.com 348 187 4139
Ella Ella.L.Baker@spambob.com 236 201 9232
Eloise Eloise.K.Cooper@trashymail.com 232 163 7877
Eve Eve.J.Richardson@mailinator.com 293 175 6929
Francesca Francesca.J.Harvey@mailinator.com 282 147 9915
Georgia Georgia.L.Thorpe@trashymail.com 262 196 7209
Harley Harley.J.Summers@trashymail.com 262 203 5583
Harvey Harvey.K.Hewitt@dodgeit.com 220 138 11835
Holly Holly.F.Saunders@spambob.com 230 209 9093
Jack Jack.H.Hardy@trashymail.com 204 198 8439
James James.N.Thomas@spambob.com 253 186 11881
Jasmine Jasmine.J.Perkins@spambob.com 258 163 12918
Joe Joe.E.Dale@pookmail.com 293 133 8577
Katie Katie.A.Fisher@pookmail.com 190 234 6150
Lara Lara.J.Rogers@mailinator.com 240 203 9293
Leon Leon.M.Pickering@dodgeit.com 272 189 6741
Lewis Lewis.R.Owen@trashymail.com 301 234 7407
Lola Lola.S.Browne@pookmail.com 214 215 7019
Lucas Lucas.L.Akhtar@pookmail.com 244 233 9245
Maisie Maisie.J.Nicholls@mailinator.com 244 236 9458
Melissa Melissa.A.Cartwright@trashymail.com 277 220 6727
Mollie Mollie.L.Cross@spambob.com 121 182 6956
Naomi Naomi.R.Peacock@dodgeit.com 136 162 8390
Noah Noah.Z.Begum@trashymail.com 333 193 10936
Olivia Olivia.M.Patel@mailinator.com 289 150 4494
Patrick Patrick.A.Stone@trashymail.com 211 162 8540
Riley Riley.M.Kerr@mailinator.com 120 207 8806
Samuel Samuel.R.Iqbal@pookmail.com 213 146 7014
Sebastian Sebastian.M.Anderson@dodgeit.com 178 209 6409
Sophia Sophia.N.Norman@dodgeit.com 222 138 7418
Summer Summer.N.Fry@spambob.com 282 91 7844
Tia Tia.L.Kirk@dodgeit.com 124 202 9477
Zachary Zachary.T.Hobbs@dodgeit.com 256 152 6878
In [ ]:
#lets create excel sheet for each flight name

 

In [12]:
writer = pd.ExcelWriter('flightGrouping.xlsx')

for flight in table.index.get_level_values(0).unique():
    temp_df = table.xs(flight, level=0)
    temp_df.to_excel(writer,manager)

writer.save()

 

In [ ]:

 

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *