{"id":311,"date":"2022-01-04T00:00:00","date_gmt":"2022-01-04T00:00:00","guid":{"rendered":"https:\/\/tac.debuzzify.com\/?p=311"},"modified":"2023-06-27T06:29:55","modified_gmt":"2023-06-27T06:29:55","slug":"pandas-sql-query-on-dataframe","status":"publish","type":"post","link":"https:\/\/www.the-analytics.club\/pandas-sql-query-on-dataframe\/","title":{"rendered":"How to Run SQL Queries on Pandas Data Frames?"},"content":{"rendered":"\n<p>Pandas may be the de-facto standard library for data analysis in Python. Yet, most data scientists are more comfortable with SQL than Pandas data operations.<\/p>\n\n\n\n<p>At times it may feel like things are easy to do in SQL. Wouldn&#8217;t it be great if we had a way to get the best of both worlds?<\/p>\n\n\n\n<p>It does.<\/p>\n\n\n\n<p>A wonderful library in the Python ecosystem makes it possible to do this.<\/p>\n\n\n\n<p><strong><em>Related: <a href=\"\/summarize-datasets-in-pandas\">A Better Way to Summarize Pandas Dataframes.<\/a><\/em><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Write your first SQL query on a Pandas dataframe with Pandasql<\/h2>\n\n\n\n<p>Pandasql is an open-source package that lets you run SQL code on pandas data frames. You can check out its repository on <a href=\"https:\/\/github.com\/yhat\/pandasql\/\" target=\"_blank\" rel=\"noopener\">GitHub<\/a>.<\/p>\n\n\n\n<p>You can <a href=\"https:\/\/pypi.org\/project\/pandasql\/\" target=\"_blank\" rel=\"noopener\">install Pandasql with PyPI<\/a> as follows.<\/p>\n\n\n\n<p><script><br \/>\n            \/* This is ugly, but to ensure we only create this function once, and only call<br \/>\n            each JS library once, we need to check here in case there are multiple Wagtail<br \/>\n            blocks on this page. This will ensure we only load the minimum payload. *\/<br \/>\n            if(typeof loadPrismLanguage != 'function') {<br \/>\n                window.loadPrismLanguage = function(language) {<br \/>\n                    var libraries = [<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs\",<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/prism.min.js\"<br \/>\n                        },<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs-\" + language,<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/components\/prism-\" + language + \".min.js\"<br \/>\n                        },<br \/>\n        {<br \/>\n            \"id\": \"code-block-line-numbers\",<br \/>\n            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/plugins\/line-numbers\/prism-line-numbers.min.js\"<br \/>\n        }<\/p>\n<p>                    ];<\/p>\n<p>                    for(const library of libraries) {<br \/>\n                        if(document.getElementById(library[\"id\"]) == null) {<br \/>\n                            var s = document.createElement(\"script\");<br \/>\n                            s.id = library[\"id\"];<br \/>\n                            s.type = \"text\/javascript\";<br \/>\n                            s.src = library[\"url\"];<br \/>\n                            s.async = false;<br \/>\n                            document.body.appendChild(s);<br \/>\n                        }<br \/>\n                    }<br \/>\n                };<br \/>\n            }<\/p>\n<p>            loadPrismLanguage('bash');<\/p>\n<p>            language_class_name = 'language-bash';<br \/>\n            <\/script><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"pip install pandasql\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #88C0D0\">pip<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">install<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">pandasql<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Bash<\/span><\/div>\n\n\n\n<p><script><br \/>\n                var block_num = (typeof block_num === 'undefined') ? 0 : block_num;<br \/>\n                block_num++;<br \/>\n                document.getElementById('target-element-current').className = language_class_name;<br \/>\n                document.getElementById('target-element-current').id = 'target-element-' + block_num;<br \/>\n            <\/script><\/p>\n\n\n\n<p>Once you&#8217;ve installed it, you can use its self-function to query any data frame in the memory.<\/p>\n\n\n\n<p><script><br \/>\n            \/* This is ugly, but to ensure we only create this function once, and only call<br \/>\n            each JS library once, we need to check here in case there are multiple Wagtail<br \/>\n            blocks on this page. This will ensure we only load the minimum payload. *\/<br \/>\n            if(typeof loadPrismLanguage != 'function') {<br \/>\n                window.loadPrismLanguage = function(language) {<br \/>\n                    var libraries = [<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs\",<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/prism.min.js\"<br \/>\n                        },<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs-\" + language,<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/components\/prism-\" + language + \".min.js\"<br \/>\n                        },<br \/>\n        {<br \/>\n            \"id\": \"code-block-line-numbers\",<br \/>\n            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/plugins\/line-numbers\/prism-line-numbers.min.js\"<br \/>\n        }<\/p>\n<p>                    ];<\/p>\n<p>                    for(const library of libraries) {<br \/>\n                        if(document.getElementById(library[\"id\"]) == null) {<br \/>\n                            var s = document.createElement(\"script\");<br \/>\n                            s.id = library[\"id\"];<br \/>\n                            s.type = \"text\/javascript\";<br \/>\n                            s.src = library[\"url\"];<br \/>\n                            s.async = false;<br \/>\n                            document.body.appendChild(s);<br \/>\n                        }<br \/>\n                    }<br \/>\n                };<br \/>\n            }<\/p>\n<p>            loadPrismLanguage('python');<\/p>\n<p>            language_class_name = 'language-python';<br \/>\n            <\/script><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"from pandasql import sqldf\nfrom sklearn import datasets\n\ndata = datasets.load_wine(as_frame=True)['data']\n\nsqldf(&quot;SELECT * FROM data LIMIT 10&quot;, globals())\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pandasql <\/span><span style=\"color: #81A1C1\">import<\/span><span style=\"color: #D8DEE9FF\"> sqldf<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> sklearn <\/span><span style=\"color: #81A1C1\">import<\/span><span style=\"color: #D8DEE9FF\"> datasets<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">data <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> datasets<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">load_wine<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9\">as_frame<\/span><span style=\"color: #81A1C1\">=True<\/span><span style=\"color: #ECEFF4\">)[<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">data<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">]<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">sqldf<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">SELECT * FROM data LIMIT 10<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">globals<\/span><span style=\"color: #ECEFF4\">())<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p><script><br \/>\n                var block_num = (typeof block_num === 'undefined') ? 0 : block_num;<br \/>\n                block_num++;<br \/>\n                document.getElementById('target-element-current').className = language_class_name;<br \/>\n                document.getElementById('target-element-current').id = 'target-element-' + block_num;<br \/>\n            <\/script><\/p>\n\n\n\n<p>We&#8217;ve imported the wine recognition dataset available in the scikit learn toy dataset repository in the above example. We then queried the first ten records from the dataset.<\/p>\n\n\n\n<p>The first argument to the <code>sqldf<\/code> function is straightforward to understand. It&#8217;s a SQL query. The second argument, <code>global()<\/code> tells the function to use the global context to find the dataset. The other possible value for this argument is <code>local()<\/code> which tells the interpreter to look only within the context of a function block.<\/p>\n\n\n\n<p>If we&#8217;re to do the same using only Pandas operations, it will look as follows.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"df[:10]\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D8DEE9FF\">df<\/span><span style=\"color: #ECEFF4\">[:<\/span><span style=\"color: #B48EAD\">10<\/span><span style=\"color: #ECEFF4\">]<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p>Both operations results in the following dataset.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"657\" height=\"196\" src=\"https:\/\/www.the-analytics.club\/wp-content\/uploads\/2023\/06\/image-60.png\" alt=\"Pandas dataframe queried with SQL\" class=\"wp-image-817\" title=\"\" srcset=\"https:\/\/www.the-analytics.club\/wp-content\/uploads\/2023\/06\/image-60.png 657w, https:\/\/www.the-analytics.club\/wp-content\/uploads\/2023\/06\/image-60-300x89.png 300w\" sizes=\"(max-width: 657px) 100vw, 657px\" \/><\/figure><\/div>\n\n\n<p>At first, it may seem counterintuitive to add complexity to the trivial task of querying the first ten records. But, as our query gets bigger, it becomes easier to do it using a SQL query than the Pandas way.<\/p>\n\n\n\n<p><strong><em>PRO TIP: Query pandas using SQL without having to mention the global environment.<\/em><\/strong><\/p>\n\n\n\n<p>We can use the <code>functool.partial<\/code> utility to create a partial function that doesn&#8217;t require you to mention the environment every time. Here&#8217;s how to do it.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"from functools import partial\n\ngpandsql = partial(sqldf, env=globals())\n\ngpandsql(&quot;SELECT * FROM data LIMIT 10&quot;)\n\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> functools <\/span><span style=\"color: #81A1C1\">import<\/span><span style=\"color: #D8DEE9FF\"> partial<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">gpandsql <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">partial<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9FF\">sqldf<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">env<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #88C0D0\">globals<\/span><span style=\"color: #ECEFF4\">())<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">gpandsql<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">SELECT * FROM data LIMIT 10<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #ECEFF4\">)<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p><strong>Related: <a href=\"\/pandas-replace-column-values\"><em>Pandas Replace: The Faster and Better Approach to Change Values of a Column.<\/em><\/a><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What types of SQL queries you can perform on a Pandas dataframe?<\/h2>\n\n\n\n<p><code>Pandasql<\/code> uses Sqlite as its temporary backend. You can perform all kinds of SQL operations Sqlite supports. This includes GROUPBY, WHERE, and different kinds of JOINS.<\/p>\n\n\n\n<p>Here&#8217;s an example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"from functools import partial\nfrom pandasql import sqldf\nfrom sklearn import datasets\n\ndata = datasets.load_wine(as_frame=True)['data']\ntarget = datasets.load_wine(as_frame=True)['target']\n\n\ngpandsql = partial(sqldf, env=globals())\n\ngpandsql(&quot;&quot;&quot;\n    SELECT * \n    FROM data d \n    LEFT JOIN target t \n    WHERE \n        d.ash = 1.36 \n    LIMIT 5&quot;&quot;&quot;\n).describe()\n\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> functools <\/span><span style=\"color: #81A1C1\">import<\/span><span style=\"color: #D8DEE9FF\"> partial<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pandasql <\/span><span style=\"color: #81A1C1\">import<\/span><span style=\"color: #D8DEE9FF\"> sqldf<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> sklearn <\/span><span style=\"color: #81A1C1\">import<\/span><span style=\"color: #D8DEE9FF\"> datasets<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">data <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> datasets<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">load_wine<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9\">as_frame<\/span><span style=\"color: #81A1C1\">=True<\/span><span style=\"color: #ECEFF4\">)[<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">data<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">]<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">target <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> datasets<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">load_wine<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9\">as_frame<\/span><span style=\"color: #81A1C1\">=True<\/span><span style=\"color: #ECEFF4\">)[<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">target<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">]<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">gpandsql <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">partial<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9FF\">sqldf<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">env<\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #88C0D0\">globals<\/span><span style=\"color: #ECEFF4\">())<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">gpandsql<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #ECEFF4\">&quot;&quot;&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">    SELECT * <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">    FROM data d <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">    LEFT JOIN target t <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">    WHERE <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        d.ash = 1.36 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">    LIMIT 5<\/span><span style=\"color: #ECEFF4\">&quot;&quot;&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">).<\/span><span style=\"color: #88C0D0\">describe<\/span><span style=\"color: #ECEFF4\">()<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p>You can even perform more advanced operations such as <a href=\"https:\/\/www.sqlite.org\/windowfunctions.html\" target=\"_blank\" rel=\"noopener\">window functions<\/a>.<\/p>\n\n\n\n<p>However, SQLite is not a database designed for large-scale applications. Also, we should note that <a href=\"https:\/\/www.sqlite.org\/omitted.html\" target=\"_blank\" rel=\"noopener\">SQLite has some limitations<\/a> we need to be aware of.<\/p>\n\n\n\n<p>Also, note that we can only query dataframes using Pandasql. We can not create, update, or delete records using this library.<\/p>\n\n\n\n<p>Related: <a href=\"\/pipe-operations-in-python\"><strong><em>Use Pipe Operations in Python for More Readable and Faster Coding<\/em><\/strong><\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Final thoughts<\/h2>\n\n\n\n<p><a href=\"https:\/\/www.the-analytics.club\/welcome-to-the-age-of-citizen-data-scientists\/\" title=\"Welcome to the Age of Citizen Data Scientists\">Most data scientists<\/a> work well in SQL databases. For some, it isn&#8217;t easy to use Pandas operations.<\/p>\n\n\n\n<p>For them, Pandasql is a fantastic tool to enjoy the best of both worlds.<\/p>\n\n\n\n<p>I&#8217;ve also noticed that SQL queries are excellent for readability. They follow the natural language flow, making it easy for even novice programmers to grasp.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>Thanks for the read, friend. It seems you and I have lots of common interests. Say Hi to me on <a href=\"https:\/\/www.linkedin.com\/in\/thuwarakesh\/\" target=\"_blank\" rel=\"nofollow noopener\">LinkedIn<\/a>, <a href=\"https:\/\/twitter.com\/Thuwarakesh\" target=\"_blank\" rel=\"nofollow noopener\">Twitter<\/a>, and <a href=\"https:\/\/thuwarakesh.medium.com\/subscribe\" target=\"_blank\" rel=\"nofollow noopener\">Medium<\/a>. I\u2019ll break the ice for you.<\/p>\n\n\n\n<p>Not a Medium member yet? Please use this link to <a href=\"https:\/\/thuwarakesh.medium.com\/membership\" target=\"_blank\" rel=\"nofollow noopener\">become a member<\/a> because I earn a commission for referring at no extra cost for you.<\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Most data scientists are comfortable with SQL than pandas data operations. But you can query pandas data frames as you do with SQL databases.<\/p>\n","protected":false},"author":2,"featured_media":175,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"categories":[4,3],"tags":[],"taxonomy_info":{"category":[{"value":4,"label":"Data Science"},{"value":3,"label":"Python"}]},"featured_image_src_large":["https:\/\/www.the-analytics.club\/wp-content\/uploads\/2023\/06\/pandas-sql-1024x683.jpg",1024,683,true],"author_info":{"display_name":"Thuwarakesh","author_link":"https:\/\/www.the-analytics.club\/author\/thuwarakesh\/"},"comment_info":0,"category_info":[{"term_id":4,"name":"Data Science","slug":"data-science","term_group":0,"term_taxonomy_id":4,"taxonomy":"category","description":"","parent":0,"count":22,"filter":"raw","cat_ID":4,"category_count":22,"category_description":"","cat_name":"Data Science","category_nicename":"data-science","category_parent":0},{"term_id":3,"name":"Python","slug":"python","term_group":0,"term_taxonomy_id":3,"taxonomy":"category","description":"","parent":5,"count":52,"filter":"raw","cat_ID":3,"category_count":52,"category_description":"","cat_name":"Python","category_nicename":"python","category_parent":5}],"tag_info":false,"_links":{"self":[{"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/posts\/311"}],"collection":[{"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/comments?post=311"}],"version-history":[{"count":4,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/posts\/311\/revisions"}],"predecessor-version":[{"id":1295,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/posts\/311\/revisions\/1295"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/media\/175"}],"wp:attachment":[{"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/media?parent=311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/categories?post=311"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/tags?post=311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}