Python for Data Analysis: Numpy, Pandas & Visualization

This section demonstrates building interactive charts using Plotly and Streamlit. The process involves: (1) importing plotly.graph_objects as go, (2) creating a go.Figure() and adding go.Candlestick() data with date, open, high, low, close values, (3) reading price data from a database using pandas.read_sql(), and (4) displaying the chart using st.plotly_chart(). The dashboard allows users to type in stock symbols and view interactive candlestick charts with pan and zoom functionality.

This video tutorial demonstrates how to build an interactive Python dashboard using Streamlit and Plotly libraries, covering essential concepts including data uploading, date filtering, multi-select sidebar filters, dynamic chart generation (bar charts, pie charts, time series analysis, scatter plots, and treemaps), and data downloading functionality. The instructor walks through creating a complete sales analytics dashboard from scratch using the Superstore dataset, showing how to implement responsive layouts, apply CSS styling, and create live-filtered visualizations that update automatically based on user selections.

Plotly Express provides a high-level API for creating interactive visualizations in Streamlit that closely resembles the syntax of pandas' plotting methods. It simplifies the creation of common chart types like line charts, bar charts, and scatter plots. The API automatically generates plotly figures with reasonable defaults, and additional customization options like titles, markers, and text annotations can be added through method arguments.

Plotly Express enables interactive data visualization in Streamlit applications. The workflow involves: (1) installing plotly library via pip, (2) reading CSV data using pandas pd.read_csv() into DataFrames, (3) aggregating data using groupby() for categorical analysis, (4) creating pie charts with px.pie() for categorical distributions, and (5) creating bar charts with px.bar() for comparing values across categories. Charts are displayed using st.plotly_chart() and offer interactive features including zoom, pan, auto-scale, and image download capabilities. This enables developers to transform raw data into interactive visualizations for data exploration and presentation.

This comprehensive section covers the Python dashboard ecosystem including Voila, Plotly Dash, and Streamlit. Voila converts Jupyter notebooks to standalone web apps using IPython widgets with minimal boilerplate, ideal for incremental enhancement of existing analyses. Plotly Dash uses Flask with explicit front-end/back-end separation, providing maximum customization through HTML-like layouts and declared input-output dependencies. Streamlit offers automatic reactivity and caching for rapid prototyping. The section demonstrates building interactive dashboards from static analysis, adding dropdowns for filtering, and implementing cascading dropdowns where major category selections dynamically update minor category options. Key challenges include converting matplotlib plots to Plotly for interactivity and managing widget callbacks. The progression shows how analysts can enhance their work incrementally, starting from simple filtering to sophisticated multi-widget applications that respond to user input in real-time.

To load results from an SQL query into a pandas DataFrame, use the pd.read_sql_query() function. Pass the SQL query string and the database connection object as arguments. This method executes the query on the database server and returns the results as a pandas DataFrame, which can then be manipulated using pandas' extensive data handling capabilities. This approach combines the power of SQL for querying with pandas for data transformation.

This video demonstrates how to connect Python and Pandas to SQL databases using MySQL as an example, covering three main approaches: (1) Using MySQL Connector to establish a direct connection, create a cursor, execute SQL queries, and retrieve results row-by-row; (2) Using SQLAlchemy's create_engine to create a connection string with username, password, host, and database name; (3) Using Pandas' read_sql method to directly load query results into DataFrames, and writing DataFrames back to SQL tables using to_sql with options like if_exists='replace' or if_exists='append' to handle existing tables.

This video demonstrates how to read SQL database tables into Pandas DataFrames using the read_sql function (which supports both table names and SQL statements with filtering) and write DataFrames back to SQL databases using the to_sql function, with important considerations including specifying index=False to avoid index column conflicts, using if_exists='append' to add data to existing tables (as 'replace' would overwrite the entire table), and the need to install SQLAlchemy for database connectivity.

Pandas can read data from SQL databases using pd.read_sql_query() or pd.read_sql(). For SQLite (file-based database), create a connection with sqlite3.connect(), execute a SQL query, and pass the result to read_sql_query(). For other databases (MySQL, PostgreSQL), install appropriate drivers and manage connections accordingly. The SQL query specifies which data to retrieve, and Pandas converts the result into a DataFrame. This enables reading relational database data into the same analysis framework.

Pandas provides built-in APIs for reading and writing data to SQL databases using sqlite3 for SQLite databases and SQLAlchemy with pymysql for MySQL databases. For SQLite, use sqlite3.connect() to establish a connection and pd.read_sql() to read data into a DataFrame, or df.to_sql() to write data back. For MySQL, create a SQLAlchemy engine using create_engine() with the database URL, then use engine.connect() to establish a connection, followed by read_sql() and to_sql() methods. The read_sql() method accepts SQL queries and connection objects, while to_sql() requires a table name and connection object. DataFrames can have their index set using the index_col parameter during reading or set_index() method after reading.

Beyond basic scaling, advanced preprocessing includes polynomial feature generation for capturing nonlinear relationships, and one-hot encoding for categorical variables. Quantile transformer provides robustness against outliers by using percentiles instead of means. These techniques dramatically affect model performance, as demonstrated by comparing transformed versus untransformed data in predictive pipelines. Understanding when and how to apply these transformations is essential for building effective machine learning systems.

Categorical variables require encoding since ML models expect numerical inputs. Ordinal encoding assigns numbers to categories but imposes arbitrary ordering that affects model behavior. One-hot encoding creates binary columns for each category, avoiding ordering assumptions but potentially creating wide feature spaces. Pandas' get_dummies() performs one-hot encoding, but requires careful handling of unseen categories in test data. Converting columns to pandas Categorical type before encoding ensures consistent feature alignment. ColumnTransformer applies different transformations to different feature subsets, essential for heterogeneous datasets. For high-cardinality variables, strategies include target encoding/leave-one-out encoding (replacing categories with target summaries), hashing encoding (fixed-size features with potential collisions), and limiting to top N categories. Feature engineering creates new features to improve model performance. Polynomial features create interactions by multiplying combinations of original features, allowing linear models to capture non-linear decision boundaries. Power transformations (Box-Cox and Yeo-Johnson) make skewed data more Gaussian-like, helping linear models and neural networks. Box-Cox applies X^λ/λ (with log for λ=0); Yeo-Johnson extends to negative values. Optimal λ is estimated from data to maximize Gaussian-like distribution.

Preprocessing involves: (1) Handling missing values using dropna() or manual removal of mostly-empty columns, or imputation techniques, (2) Encoding categorical variables using OneHotEncoding, OrdinalEncoding, or custom replacement functions with replace() or map(), (3) Removing outliers after initial model creation to avoid premature decisions.

Effective feature engineering involves encoding categorical variables while preserving meaningful relationships, detecting and treating outliers using IQR-based methods, and identifying multicollinearity through correlation analysis. Critical decisions include whether to apply preprocessing based on model requirements—statistical models need scaling and multicollinearity handling, while tree-based models tolerate raw data. Making separate data copies for different model types ensures appropriate preprocessing without unnecessary modifications.

Data preprocessing is essential before feeding data to machine learning algorithms. Data imputation handles missing or incorrect data by replacing it with most common values. Outlier handling uses box plots to identify unusual data points that can degrade model accuracy. One hot encoding converts categorical data (like team names) into numerical data using binary columns. Data grouping makes data more understandable by finding mean, median, or mode. Data scaling brings data to a workable range. A correlation matrix shows relationships between features using color intensity. Diagonal elements are always 1. Underfitting occurs when a model has insufficient data points to train properly, resulting in high bias and low variance. Overfitting occurs when a model learns too much from training data, resulting in high variance and low bias. Both result in poor model performance.

Distributed frameworks like Dask and Spark enable horizontal scaling while retaining pandas-like APIs. These systems scale by adding nodes, with the ability to scale down to single nodes during off-peak hours. Key benefits include identical APIs for single-node and distributed execution, lazy evaluation that compiles DAGs of operations, and efficient parallel execution. Spark integrates with Python through PySpark, while Dask implements much of the pandas/numpy API. These systems execute actions only when results are needed, optimizing resource utilization across clusters.

Dask is a Python library that enables parallel and distributed computing by extending familiar Python data science libraries (numpy, pandas, scikit-learn) to handle large-scale data across clusters, using a dynamic task scheduler that automatically manages data locality, resilience, and load balancing through aggressive measurement and dynamic scheduling heuristics.

Dask is a pure Python framework for parallel computing that provides familiar pandas-like APIs, enabling Python developers to build scalable data pipelines with cleaner code and better debugging compared to PySpark, which requires transcompiling Python code to Java bytecode and introduces complex error handling; however, Dask performs better than PySpark for single-source operations but lags behind in join-heavy workloads due to its task-based parallelization model.

This video explains how to select appropriate data processing frameworks based on data scale. Pandas handles 1-5 GB safely, up to 30 GB with chunk size, but lacks failover features. Dask extends pandas/numpy/scikit-learn for parallel processing, handling 200+ GB. Apache Spark/PySpark handles petabyte-scale data with distributed processing. Framework selection depends on memory constraints, data volume, and processing requirements.

Dask is a pure Python framework that scales to hundreds of machines, part of Anaconda with a DataFrame API similar to pandas. It handles data larger than RAM by splitting it transparently across the cluster. Each worker is a separate Python process, eliminating GIL limitations. Dask uses lazy evaluation where transformations execute only at the final step, with a task scheduler tracking dependencies and data movement. It can register as a Joblib backend, allowing scikit-learn to delegate parallelization without knowing work is distributed. Dask provides excellent visualization including a task stream showing real-time parallelization effectiveness, where white indicates idle cores (bottlenecks) and red indicates network data shuffling.