4 ms·

Python + SQL seems like a good match for many analysis problems, but Excel + SQL is not bad either. I like the ability to combine complex SQL views or SQL funct

by mongol 7y ago

Python + SQL seems like a good match for many analysis problems, but Excel + SQL is not bad either. I like the ability to combine complex SQL views or SQL functions with Excel pivot functionality, querying the database directly from Excel

bshipp 7y ago
Even after learning python, pandas, and SQL I still find myself firing up Excel first when exploring a new problem. The interface is incredibly simple for pasting, manipulating, and visualizing data. The warts really only start appearing when you try to build business applications on top of it and shortcomings with data integrity (amongst other issues) begin creeping in. But as a sketchpad for handling a problem? Excel is pretty hard to beat.
- avmich 7y ago
  > But as a sketchpad for handling a problem? Excel is pretty hard to beat. I'd recommend trying J. Download here - https://code.jsoftware.com/wiki/System/Installation#Installation https://code.jsoftware.com/wiki/System/Installation#Installa... . There is, of course, the famous barrier to entry, but the result, unsurprisingly, is also respectable.
  - bshipp 7y ago
    Thanks for the recommendation! I've honestly never heard of J and I'll take a look at it.
    - avmich 7y ago
      To understand what's going on I'd recommend "J for C programmers" (see links on Jsoftware site) and also the mailing list - it could be very helpful.
- samvher 7y ago
  I also avoid pandas for simple stuff. But rather than using Excel I use dplyr and other R tidyverse packages. I think pandas is a little bit more powerful but I find R much easier to use (easier than Excel when you get used to it).
danzig13 7y ago
PowerQuery/Get&Transform makes it go an even longer way.
- mongol 7y ago
  Yes that is next on my todo list to learn!
  - danzig13 7y ago
    I have eventually started learning Python/Pandas but PowerQuery plus having good SQL knowledge delayed it quite a bit.
iagovar 7y ago
Do you use powerquery? for that?
- mongol 7y ago
  Yes but just in a very basic way that I basically googled. I focus the analysis logic to the SQL layer (views, functions) and the interactive analysis using pivot functionality. I don't know powerquery well yet.
merlincorey 7y ago
Maybe I've not fully understood pivot tables (quite possible), but this example[0] seems to support my understanding... aren't pivot tables basically just aggregate functions such as `sum` and `avg` applied to a window of the table data (pivoted by rows -> columns)? Some of the executives I work with like to relate all their work in Excel and they just love pivot tables. I showed one of them the output of a table of data on web page backed by a database and they asked if I could export the data to Excel to make a pivot table that would then be displayed on the web page. Of course, I implemented their pivot table as queries against the database and created a new view to display it. I've never understood why I would need Excel to make Pivot tables when I already have SQL. [0] https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tables-in-sql-server-a-simple-sample/ https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tabl...
- bshipp 7y ago
  You're absolutely right. As someone coming into Python and SQL from a purely Excel background, the first realization I had was that I had been, in essence, programming Excel. Not in a rational VBA kind of way, but in how I was linking all of my cells (and sheets and books) and then using pivot tables, etc., to extend Excel beyond a single column/row perspective. Microsoft PowerPivot (or whatever it used to be called) was my gateway drug into the world of SQL. I can generate similar utilities to Excel/PivotTables using Plotly Dash or Pandas or even DataTables, but even after a few years of learning and practising it would take me less than 1/10th the time to generate an equivalent excel pivot and chart and have it displayed in front of a group. I'm not saying it's better, just that those execs are so comfortable with that visual-feedback excel approach that it'll be an uphill battle to convert them to a programmatic one. Pivot tables provide very rapid means of filtering, modifying output, and aggregating information than is otherwise possible inside Excel. To those coming from a two dimensional excel spreadsheet world, discovering pivot tables is like viewing the world in 3D. I don't see why you would go to Excel pivot tables from SQL; you already have a more powerful tool at your disposal, if you're comfortable with it. Going from Excel to SQL? That hurdle is a bit higher.
  - iagovar 7y ago

by mongol 7y ago

bshipp 7y ago

Even after learning python, pandas, and SQL I still find myself firing up Excel first when exploring a new problem. The interface is incredibly simple for pasting, manipulating, and visualizing data. The warts really only start appearing when you try to build business applications on top of it and shortcomings with data integrity (amongst other issues) begin creeping in. But as a sketchpad for handling a problem? Excel is pretty hard to beat.

avmich 7y ago
> But as a sketchpad for handling a problem? Excel is pretty hard to beat. I'd recommend trying J. Download here - https://code.jsoftware.com/wiki/System/Installation#Installation https://code.jsoftware.com/wiki/System/Installation#Installa... . There is, of course, the famous barrier to entry, but the result, unsurprisingly, is also respectable.
- bshipp 7y ago
  Thanks for the recommendation! I've honestly never heard of J and I'll take a look at it.
  - avmich 7y ago
    To understand what's going on I'd recommend "J for C programmers" (see links on Jsoftware site) and also the mailing list - it could be very helpful.
samvher 7y ago
I also avoid pandas for simple stuff. But rather than using Excel I use dplyr and other R tidyverse packages. I think pandas is a little bit more powerful but I find R much easier to use (easier than Excel when you get used to it).

danzig13 7y ago

PowerQuery/Get&Transform makes it go an even longer way.

mongol 7y ago
Yes that is next on my todo list to learn!
- danzig13 7y ago
  I have eventually started learning Python/Pandas but PowerQuery plus having good SQL knowledge delayed it quite a bit.

iagovar 7y ago

Do you use powerquery? for that?

mongol 7y ago
Yes but just in a very basic way that I basically googled. I focus the analysis logic to the SQL layer (views, functions) and the interactive analysis using pivot functionality. I don't know powerquery well yet.

merlincorey 7y ago

Maybe I've not fully understood pivot tables (quite possible), but this example[0] seems to support my understanding... aren't pivot tables basically just aggregate functions such as `sum` and `avg` applied to a window of the table data (pivoted by rows -> columns)? Some of the executives I work with like to relate all their work in Excel and they just love pivot tables. I showed one of them the output of a table of data on web page backed by a database and they asked if I could export the data to Excel to make a pivot table that would then be displayed on the web page. Of course, I implemented their pivot table as queries against the database and created a new view to display it. I've never understood why I would need Excel to make Pivot tables when I already have SQL. [0] https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tables-in-sql-server-a-simple-sample/ https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tabl...

bshipp 7y ago
You're absolutely right. As someone coming into Python and SQL from a purely Excel background, the first realization I had was that I had been, in essence, programming Excel. Not in a rational VBA kind of way, but in how I was linking all of my cells (and sheets and books) and then using pivot tables, etc., to extend Excel beyond a single column/row perspective. Microsoft PowerPivot (or whatever it used to be called) was my gateway drug into the world of SQL. I can generate similar utilities to Excel/PivotTables using Plotly Dash or Pandas or even DataTables, but even after a few years of learning and practising it would take me less than 1/10th the time to generate an equivalent excel pivot and chart and have it displayed in front of a group. I'm not saying it's better, just that those execs are so comfortable with that visual-feedback excel approach that it'll be an uphill battle to convert them to a programmatic one. Pivot tables provide very rapid means of filtering, modifying output, and aggregating information than is otherwise possible inside Excel. To those coming from a two dimensional excel spreadsheet world, discovering pivot tables is like viewing the world in 3D. I don't see why you would go to Excel pivot tables from SQL; you already have a more powerful tool at your disposal, if you're comfortable with it. Going from Excel to SQL? That hurdle is a bit higher.
- iagovar 7y ago