<story><title>Practical SQL for Data Analysis</title><url>https://hakibenita.com/sql-for-data-analysis</url></story><parent_chain><item><author>m16ghost</author><text>&amp;gt;Pandas is a very popular tool for data analysis. It comes built-in with many useful features, it&amp;#x27;s battle tested and widely accepted. However, pandas is not always the best tool for the job.&lt;p&gt;SQL is very useful, but there are some data manipulations which are much easier to perform in pandas&amp;#x2F;dplyr&amp;#x2F;data.table than in SQL. For example, the article discusses how to perform a pivot table, which takes data in a &amp;quot;long&amp;quot; format, and makes it &amp;quot;wider&amp;quot;.&lt;p&gt;In the article, the pandas version is:&lt;p&gt;&amp;gt;pd.pivot_table(df, values=&amp;#x27;name&amp;#x27;, index=&amp;#x27;role&amp;#x27;, columns=&amp;#x27;department&amp;#x27;, aggfunc=&amp;#x27;count&amp;#x27;)&lt;p&gt;Compared to the SQL version:&lt;p&gt;&amp;gt;SELECT
    role,
    SUM(CASE department WHEN &amp;#x27;R&amp;amp;D&amp;#x27; THEN 1 ELSE 0 END) as &amp;quot;R&amp;amp;D&amp;quot;,
    SUM(CASE department WHEN &amp;#x27;Sales&amp;#x27; THEN 1 ELSE 0 END) as &amp;quot;Sales&amp;quot;
FROM
    emp
GROUP BY
    role;&lt;p&gt;Not only does the SQL code require you to know up front how many distinct columns you are creating, it requires you to write a line out for each new column. This is okay in simple cases, but is untenable when you are pivoting on a column with hundreds or more distinct values, such as dates or zip codes.&lt;p&gt;There are some SQL dialects which provide pivot functions like in pandas, but they are not universal.&lt;p&gt;There are other examples in the article where the SQL code is much longer and less flexible, such as binning, where the bins are hardcoded into the query.</text></item></parent_chain><comment><author>kbelder</author><text>I&amp;#x27;ve been doing a lot of data analysis in Pandas recently.  I started off thinking that for efficiency&amp;#x27;s sake, I should do as much initial processing in the DB as possible, and use Pandas just for the higher level functions that were difficult to do in SQL.&lt;p&gt;But after some trial and error, I find it much faster to pull relatively large, unprocessed datasets and do everything in Pandas on the local client.  Faster both in total analysis time, and faster in DB cycles.&lt;p&gt;It seems like a couple of simple &amp;quot;select * from cars&amp;quot; and &amp;quot;select * from drivers where age &amp;lt; 30&amp;quot;, and doing all the joining, filtering, and summarizing on my machine, is often less burdensome on the db than doing it up-front in SQL.&lt;p&gt;Of course, this can change depending on the specific dataset, how big it is, how you&amp;#x27;re indexed, and all that jazz.  Just wanted to mention how my initial intuition was misguided.</text></comment>
<story><title>Practical SQL for Data Analysis</title><url>https://hakibenita.com/sql-for-data-analysis</url></story><parent_chain><item><author>m16ghost</author><text>&amp;gt;Pandas is a very popular tool for data analysis. It comes built-in with many useful features, it&amp;#x27;s battle tested and widely accepted. However, pandas is not always the best tool for the job.&lt;p&gt;SQL is very useful, but there are some data manipulations which are much easier to perform in pandas&amp;#x2F;dplyr&amp;#x2F;data.table than in SQL. For example, the article discusses how to perform a pivot table, which takes data in a &amp;quot;long&amp;quot; format, and makes it &amp;quot;wider&amp;quot;.&lt;p&gt;In the article, the pandas version is:&lt;p&gt;&amp;gt;pd.pivot_table(df, values=&amp;#x27;name&amp;#x27;, index=&amp;#x27;role&amp;#x27;, columns=&amp;#x27;department&amp;#x27;, aggfunc=&amp;#x27;count&amp;#x27;)&lt;p&gt;Compared to the SQL version:&lt;p&gt;&amp;gt;SELECT
    role,
    SUM(CASE department WHEN &amp;#x27;R&amp;amp;D&amp;#x27; THEN 1 ELSE 0 END) as &amp;quot;R&amp;amp;D&amp;quot;,
    SUM(CASE department WHEN &amp;#x27;Sales&amp;#x27; THEN 1 ELSE 0 END) as &amp;quot;Sales&amp;quot;
FROM
    emp
GROUP BY
    role;&lt;p&gt;Not only does the SQL code require you to know up front how many distinct columns you are creating, it requires you to write a line out for each new column. This is okay in simple cases, but is untenable when you are pivoting on a column with hundreds or more distinct values, such as dates or zip codes.&lt;p&gt;There are some SQL dialects which provide pivot functions like in pandas, but they are not universal.&lt;p&gt;There are other examples in the article where the SQL code is much longer and less flexible, such as binning, where the bins are hardcoded into the query.</text></item></parent_chain><comment><author>ziml77</author><text>I&amp;#x27;ve always been disappointed by the SQL pivot. It&amp;#x27;s hardly useful for me if I have to know up-front all of the columns it&amp;#x27;s going to pivot out into. The solution would be to use another SQL query to generate a dynamic SQL query, but at that point I would rather just use Pandas</text></comment>