Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting query results into DataFrame in python

I am trying to perform manipulation on the result from a query using psycog2. Thus I have to covert result into pandas DataFrame. But when i use the following code and print, only the columns name are printed not the rows. I used 'pd.DataFrame.from_records' too but that did not work.

import psycopg2
import pandas as pd
import numpy as np
conn_string = "Connect_Info"
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
cursor.execute(query)
rows=pd.DataFrame(cursor.fetchall(),columns=['page_num','Frequency'])

for row in rows:
   print row

conn.commit();
conn.close();

The result of cursor.fetchall() -

(1L, 90990L)
(3L, 6532L)
(2L, 5614L)
(4L, 4016L)
(5L, 2098L)
(6L, 1651L)
(7L, 1158L)
(8L, 854L)
(9L, 658L)
(10L, 494L)
(11L, 345L)
(12L, 301L)
(13L, 221L)
(15L, 152L)
(14L, 138L)
(16L, 113L)
(17L, 93L)
(18L, 73L)
(20L, 62L)
(19L, 55L)
(22L, 44L)
(21L, 35L)
(23L, 29L)
(25L, 24L)
(27L, 19L)
(26L, 18L)
like image 374
maggs Avatar asked Dec 06 '22 21:12

maggs


2 Answers

Maybe not directly an answer on your question, but you should use read_sql_query for this instead doing the fetchall and wrap in DataFrame yourself. This would look like:

conn = psycopg2.connect(...)
rows = pd.read_sql_query(query, conn)

instead of all your code above.

And for your actual question, see http://pandas.pydata.org/pandas-docs/stable/basics.html#iteration for an explanation and the different options.
The basics is that iterating over a dataframe, iterates over the column names. To iterate over the rows you can use other functions like .iterrows() and .itertuples(). But remember, in most cases iterating manually over the rows is not needed.

like image 169
joris Avatar answered Dec 20 '22 20:12

joris


That is exactly what should happen when you iterate over a dataframe, you see the column names. If you want to see the df just print the df. To see the rows:

for ind, row in df.iterrows(): 
    print(row.values)

Or .values:

for row in df.values:
   print(row)
like image 31
Padraic Cunningham Avatar answered Dec 20 '22 21:12

Padraic Cunningham