如何在 Pandas 中将行转置为列? [英] How to transpose rows to columns in Pandas?

问题描述

我正在将 SQL 查询的结果写入 Excel 工作表并尝试将行转换为列，但似乎无法让 Pandas 做出让步；Excel 似乎存在某种难题.我看过:

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:

如何在 Pandas 数据框中切换列行

如何在分组后将数据框中的行值转换为 Python 中的列标签?

在 Python Pandas 中将列转换为行

Python Pandas:将行转换为列标题

似乎都不起作用.

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 表格:

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

请求:

Week_Ending    Visitor Center Walk-ins       Resident Referral         Community Website            Realtor  ....................
01/01/2017          18                        1                           1                             1  .........................

推荐答案

Pivot 可以在这里工作

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

这篇关于如何在 Pandas 中将行转置为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！