7

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  .........................
TylerH
  • 20,799
  • 66
  • 75
  • 101
Jake Wagner
  • 786
  • 2
  • 12
  • 29

1 Answers1

6

Pivot would work here

df1 = df.pivot(index='Week_Ending', columns='TrafficTypes_Name', values='Traffic')


TrafficTypes_Name   Builder CommunityWebsite    Drive-by    OtherWebsite    Realtor ResidentReferral    SocialMedia VisitorCenterWalk-ins
Week_Ending                             
01/01/2017          3       1                   10          1               1       1                   1            18
Vaishali
  • 37,545
  • 5
  • 58
  • 86