title: How to transpose rows to columns in Pandas?
body: <p>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:</p>
  <p><a href="https://stackoverflow.com/questions/31658183/how-to-switch-columns-rows-in-a-pandas-dataframe">how to switch columns rows in a pandas dataframe</a></p>
  <p><a href="https://stackoverflow.com/questions/36259593/how-to-convert-rows-values-in-dataframe-to-columns-labels-in-python-after-groupb">How to convert rows values in dataframe to columns labels in Python after groupby?</a></p>
  <p><a href="https://stackoverflow.com/questions/41861846/convert-row-to-column-in-python-pandas">Convert column to row in Python Pandas</a></p>
  <p><a href="https://stackoverflow.com/questions/17298313/python-pandas-convert-rows-as-column-headers">Python Pandas: Convert Rows as Column headers</a></p>
  <p>None seem to work.</p>
  <pre><code>import psycopg2
  import pandas as pd
  import xlsxwriter
  
  try:
      conn = psycopg2.connect(private stuff cannot be shared)
  except:
      print (&quot;I am unable to connect to the database&quot;)
  
  cursor = conn.cursor()
  
  writer = pd.ExcelWriter(&quot;Z:/AWS/SQLQueries/Phoebe's Request.xlsx&quot;,engine = 'xlsxwriter')
  
  query20 = &quot;&quot;&quot;SELECT 2 AS rowtype
   , source AS &quot;TrafficTypes_Name&quot;
   , COUNT(source) AS &quot;Traffic&quot;
   , to_char(week,'MM/dd/yyyy') AS &quot;Week_Ending&quot;
  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 &quot;Week_Ending&quot;
   , rowtype&quot;&quot;&quot;
  
  cursor.execute(query20)
  
  result = cursor.fetchall()
  
  first = pd.DataFrame(result, columns = [&quot;rowtype&quot;,&quot;TrafficTypes_Name&quot;,&quot;Traffic&quot;,&quot;Week_Ending&quot;])
  
  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 (&quot;Query 20 Created&quot;)
  
  writer.save()
  </code></pre>
  <p>Excel sheet:</p>
  <pre><code>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
  </code></pre>
  <p>Requesting:</p>
  <pre><code>Week_Ending    Visitor Center Walk-ins       Resident Referral         Community Website            Realtor  ....................
  01/01/2017          18                        1                           1                             1  .........................
  </code></pre>