Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
673 views
in Technique[技术] by (71.8m points)

python - How to transpose rows to columns in Pandas?

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...