1

I have some data stored in PostgreSQL database, which contains fields like cost, start date, end date, country, etc. Please take a look at the data here.

Now what I want to do is extract some of the important features/fields from this data and store them in a separate CSV file or pandas data frame so I can use the extracted data for analysis.

Is there any python script to do this task? Please let me know. Thanks.

Community
  • 1
  • 1
4M01
  • 265
  • 6
  • 13
  • 1
    you dont need python - just use `copy(select cost, start date, end date, country, etc from table) to 'path/to/file.csv' delimiter ','` – Vao Tsun Nov 21 '17 at 09:32

1 Answers1

1

Firstly you should import your postgresql table data into dataframe which can be done by ,

import psycopg2 as pg
import pandas.io.sql as psql

# get connected to the database
connection = pg.connect("dbname=mydatabase user=postgres")

dataframe = psql.frame_query("SELECT * FROM <tablename>", connection)

explained here https://gist.github.com/00krishna/9026574 . After that we can select specific columns in pandas dataframe . these can be done by ,

df1 = dataframe[['projectfinancialtype','regionname']] 
# here you can select n number of feature columns which is available in your dataframe i had only took 2 fields of your json

Now for putting these feature column into csv we can use code like these,

df1.to_csv("pathofoutput.csv", cols=['projectfinancialtype','regionname'])
#it will create csv with your feature columns

May these helps

Shubham Sharma
  • 2,763
  • 5
  • 31
  • 46