2
$\begingroup$

Rather than learn a new package/language, I'd like to use my existing SQL skills to manipulate pandas dataframes in Python. Does anyone know of a way to do this, or perhaps a package that will allow me to do this?

$\endgroup$
5
$\begingroup$

I found a package called pandasql, which is based on sqldf for R. It seems quite a bit slower than doing the transformations with the pandas package, but it gets the job done. Just put the SQL query into a string like this:

query_string = """
    select * from df
"""

Then use the string in the pandasql.sqldf package, as follows:

new_dataframe = pandasql.sqldf(query_string, globals())

Choose globals() or locals(), depending on the scope you want for your variables.

As I mentioned, it seems a bit slow, but I couldn't find anything else. I may use this from time to time until I become better at Pandas.

Sean

| improve this answer | |
$\endgroup$
1
$\begingroup$

Based on my experience you can almost do everything that can be done using pandas in your sql. I've not seen recent versions of pandas but I remember that sql is even better because using pandas you are restricted to the size of memory. If memory fills out you may crash, something that does not happen using sql commands. You can save your pandas data frame in a csv file and manipulate that csv file using your sql. This link and also here may help you. Also for importing your csv file to your sql, you have not specified what sql you have but this link may help you. Other sqls also provide this behavior.

| improve this answer | |
$\endgroup$
  • $\begingroup$ Thanks for the answer. My data are already in a pandas dataframe for various reasons. I'd rather not export to CSV, run my SQL, and then import again... Thanks though! I'll check out your helpful links. $\endgroup$ – Sean McCarthy Jan 22 '18 at 1:10
  • 1
    $\begingroup$ @Sean_Calgary If you are using big data, I recommend you using your sql. I forget to say this in the answer :) $\endgroup$ – Media Jan 22 '18 at 1:12
1
$\begingroup$

There's actually a new package called dataframe_sql that does just what you're looking for. It's different from Pandasql in that it translates sql directly to pandas methods, which eliminates the slow down caused by that package. If you want information about installation or how it works you can check it out at https://github.com/zbrookle/dataframe_sql

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.