pandasql

pandasql allows you to query pandas DataFrames using SQL syntax. It works similarly to sqldf in R. pandasql seeks to provide a more familiar way of manipulating and cleaning data for people new to Python or pandas.

Installation

$ pip install -U pandasql

Basics

The main function used in pandasql is sqldf. sqldf accepts 2 parametrs - a sql query string - an set of session/environment variables (locals() or globals())

Specifying locals() or globals() can get tedious. You can defined a short helper function to fix this.

from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())

Querying

pandasql uses SQLite syntax. Any pandas dataframes will be automatically detected by pandasql. You can query them as you would any regular SQL table.

$ python
>>> from pandasql import sqldf, load_meat, load_births
>>> pysqldf = lambda q: sqldf(q, globals())
>>> meat = load_meat()
>>> births = load_births()
>>> print pysqldf("SELECT * FROM meat LIMIT 10;").head()
                  date  beef  veal  pork  lamb_and_mutton broilers other_chicken turkey
0  1944-01-01 00:00:00   751    85  1280               89     None          None   None
1  1944-02-01 00:00:00   713    77  1169               72     None          None   None
2  1944-03-01 00:00:00   741    90  1128               75     None          None   None
3  1944-04-01 00:00:00   650    89   978               66     None          None   None
4  1944-05-01 00:00:00   681   106  1029               78     None          None   None

joins and aggregations are also supported

>>> q = """SELECT
        m.date, m.beef, b.births
     FROM
        meats m
     INNER JOIN
        births b
           ON m.date = b.date;"""
>>> joined = pyqldf(q)
>>> print joined.head()
                    date    beef  births
403  2012-07-01 00:00:00  2200.8  368450
404  2012-08-01 00:00:00  2367.5  359554
405  2012-09-01 00:00:00  2016.0  361922
406  2012-10-01 00:00:00  2343.7  347625
407  2012-11-01 00:00:00  2206.6  320195

>>> q = "select
           strftime('%Y', date) as year
           , SUM(beef) as beef_total
           FROM
              meat
           GROUP BY
              year;"
>>> print pysqldf(q).head()
   year  beef_total
0  1944        8801
1  1945        9936
2  1946        9010
3  1947       10096
4  1948        8766

More information and code samples available in the examples folder or on our blog.

Release files for pandasql 0.7.3

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pandasql 0.7.3
File	Size	Uploaded
pandasql-0.7.3.tar.gz	26.7 kB	Apr 20, 2016	Details

Built distribution (wheel)

Table of built distributions (wheels) for pandasql 0.7.3
File	Interpreter	ABI	Platform	Reset
pandasql-0.7.3-py2.7.egg 36.3 kB Apr 20, 2016	Legacy Egg format	-	-	Details

Total release size: 63.0 kB

Release files / pandasql-0.7.3.tar.gz

Download URL	pandasql-0.7.3.tar.gz
Size	26.7 kB
Tags	Source
SHA-256 checksum How to use checksums	`1eb248869086435a7d85281ebd9fe525d69d9d954a0dceb854f71a8d0fd8de69`
BLAKE2b-256 checksum How to use checksums	`6bc4ee4096ffa2eeeca0c749b26f0371bd26aa5c8b611c43de99a4f86d3de0a7`
Upload date	Apr 20, 2016
Uploaded using Trusted Publishing? What is trusted publishing?	No

Release files / pandasql-0.7.3-py2.7.egg

Download URL	pandasql-0.7.3-py2.7.egg
Size	36.3 kB
Tags	Egg
SHA-256 checksum How to use checksums	`75f08c5cdfd19f61ceed8c38a6ac138c353776ad3be8e015edcee977c2299aad`
BLAKE2b-256 checksum How to use checksums	`c0101b2b422d6b3fc34a6b06bfcc41b954f7a71005d1318ed59e123d5ae70d5a`
Upload date	Apr 20, 2016
Uploaded using Trusted Publishing? What is trusted publishing?	No