12: Programming-Based Analytics Tools
- Page ID
- 48133
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\dsum}{\displaystyle\sum\limits} \)
\( \newcommand{\dint}{\displaystyle\int\limits} \)
\( \newcommand{\dlim}{\displaystyle\lim\limits} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\(\newcommand{\longvect}{\overrightarrow}\)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\ket}[1]{\left| #1 \right>}\)
\(\newcommand{\bra}[1]{\left< #1 \right|}\)
\(\newcommand{\braket}[2]{\left< #1 \vphantom{#2} \right| \left. #2 \vphantom{#1} \right>}\)
\(\newcommand{\braopket}[3]{\left< #1 \vphantom{#2}\vphantom{#3} \right| #2 \vphantom{#1}\vphantom{#3} \left| #3 \vphantom{#1}\vphantom{#2} \right>}\)
\(\newcommand{\qmvec}[1]{\mathbf{\vec{#1}}}\)
\(\newcommand{\op}[1]{\hat{\mathbf{#1}}}\)
\(\newcommand{\expect}[1]{\langle #1 \rangle}\)
\(\newcommand{\dfn}[1]{\emph{\textbf{#1}}}\)
Modern data analytics relies on a rich ecosystem of programming languages and tools. In this chapter, we focus on three pillars of analytics programming: Python, R, and SQL. Each brings specialized libraries, environments, and idioms for handling data. We explain key libraries in each language, show code examples, and discuss how these tools integrate into end-to-end workflows. Throughout, we emphasize reproducible development (e.g. using notebooks and markdown) and performance considerations.
Learning Objectives
-
Apply Python analytics libraries (NumPy, pandas, scikit-learn) for data manipulation, analysis, and modeling.
-
Use Jupyter Notebooks for interactive, reproducible analytics workflows.
-
Apply R and tidyverse packages (dplyr, ggplot2, tidyr) for data wrangling and visualization.
-
Write SQL queries for analytics: aggregation, joins, window functions, and CTEs.
-
Compare Python, R, and SQL for analytics tasks and select appropriate tools for given scenarios.
-
Design reproducible analytics workflows integrating multiple programming tools.
- 12.1: Python for Data Analytics
- Python’s analytics stack centers on NumPy (arrays and numerical computing), pandas (DataFrames for loading, cleaning, and transforming data), and scikit-learn (classification, regression, clustering, and preprocessing). Work is done in interactive notebooks (e.g., Jupyter/JupyterLab) that mix code, text, and output; data is loaded and manipulated with pandas (e.g., read_csv, groupby/agg) and modeled with scikit-learn’s shared API (fit/predict). Python is general-purpose, strong for scripting and
- 12.2: R for Statistical Analysis
- R’s analytics workflow centers on the tidyverse: dplyr for manipulation (filter, select, mutate, summarize, group_by), ggplot2 for layered visualization, and tidyr for reshaping (pivot_longer, pivot_wider). RStudio (Posit) is the main IDE; R Markdown combines narrative, code chunks, and output and compiles to HTML, PDF, Word, or slides for reproducible, document-driven analysis. Typical flows use dplyr pipes (%>%) for wrangling and ggplot2 for plots (e.g., revenue by product), and base/add-on mo
- 12.3: SQL for Data Manipulation
- SQL is declarative and used to query relational data; important skills include joins (INNER/LEFT, etc.), subqueries and CTEs (WITH), and window functions (RANK, LAG, SUM() OVER) for row-level and grouped calculations.
- 12.4: Integration between Tools and Workflows
- Python and R connect to databases (e.g., sqlite3, DBI, dbplyr, pandas.read_sql); reticulate runs Python from R; R Markdown and Jupyter support multiple languages and SQL (e.g., %%sql); CSV, Parquet, Feather, Arrow support cross-language data exchange.