I am writing the result of an SQL query into an Excel sheet and attempting to transpose rows into columns but cannot seem to get Pandas to budge; there seems to be an conundrum of some sort with Excel. I have looked at:
how to switch columns rows in a pandas dataframe
How to convert rows values in dataframe to columns labels in Python after groupby?
Convert column to row in Python Pandas
Python Pandas: Convert Rows as Column headers
None seem to work.
import psycopg2
import pandas as pd
import xlsxwriter
try:
conn = psycopg2.connect(private stuff cannot be shared)
except:
print ("I am unable to connect to the database")
cursor = conn.cursor()
writer = pd.ExcelWriter("Z:/AWS/SQLQueries/Phoebe's Request.xlsx",engine = 'xlsxwriter')
query20 = """SELECT 2 AS rowtype
, source AS "TrafficTypes_Name"
, COUNT(source) AS "Traffic"
, to_char(week,'MM/dd/yyyy') AS "Week_Ending"
FROM amazon.tracker
where project_id = 'PCR'
GROUP
BY source
, to_char(week,'MM/dd/yyyy')
UNION ALL
SELECT 1
, 'Visitor Center Walk-ins'
, COUNT(source)
, to_char(week,'MM/dd/yyyy') as week
FROM amazon.tracker
where project_id = 'PCR'
GROUP
BY to_char(week,'MM/dd/yyyy')
ORDER
BY "Week_Ending"
, rowtype"""
cursor.execute(query20)
result = cursor.fetchall()
first = pd.DataFrame(result, columns = ["rowtype","TrafficTypes_Name","Traffic","Week_Ending"])
first.drop(first.columns[0],axis=1, inplace = True)
first.pivot(index = 'Week_Ending', columns = 'TrafficTypes_Name' , values = 'Traffic' )
first.to_excel(writer, sheet_name = 'Visitor Traffic',index = False)
print ("Query 20 Created")
writer.save()
Excel sheet:
TrafficTypes_Name Traffic Week_Ending
Visitor Center Walk-ins 18 01/01/2017
Resident Referral 1 01/01/2017
Community Website 1 01/01/2017
Realtor 1 01/01/2017
Other Website 1 01/01/2017
Social Media 1 01/01/2017
Builder 3 01/01/2017
Drive-by 10 01/01/2017
Requesting:
Week_Ending Visitor Center Walk-ins Resident Referral Community Website Realtor ....................
01/01/2017 18 1 1 1 .........................
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…