Pandas: Loading Data & EDA

1. Introduction to Pandas

1.1 🐼 What is Pandas?

Pandas is a Python library used for: 👉 Loading data 👉 Analyzing data 👉 Transforming data 👉 Cleaning data 👉 Saving processed data 📌 Imagine trying to filter, sort, and summarize a spreadsheet using only Python lists. Pandas gives us spreadsheet-like power directly inside Python. Pandas is very useful in Data Science because most real-world data comes in table-like format. Examples: - CSV files - Excel files - SQL tables - JSON files - Python lists or dictionaries

1.2 Installing Pandas

Pandas is an external library, so we need to install it first. Installation Code: pip install pandas Then we import it in Python: import pandas as pd 📌 pd is the common short name used for Pandas.

1.3 What is a DataFrame?

A DataFrame is the main data structure in Pandas. It is a 2D labelled data structure, similar to a table.

id	name	salary
1	Alice	70000
2	Bob	60000

1.3.1 Note

A DataFrame has: - Rows - Columns - Index - Values 📌 A DataFrame is like an Excel sheet or SQL table inside Python.

What is a DataFrame? — Understanding DataFrame Structure

1.4 Why Use Pandas?

With Pandas, we can perform operations like: ✅ Selecting columns ✅ Filtering rows ✅ Sorting data ✅ Grouping data ✅ Joining tables ✅ Handling missing values ✅ Reading and writing files 📌 Pandas helps us work with tabular data easily.

2. Loading CSV with Pandas

2.1 📄 Loading a CSV File

A CSV file can be loaded into a DataFrame using pd.read_csv().

2.2 Example: Load CSV

import pandas as pd# Load CSV file into DataFrame file_path = r"folder_1/folder_2/file.csv" df = pd.read_csv(file_path)# Display first few rows print(df.head())# Display last 2 rows print(df.tail(2))# Display random 5 rows print(df.sample(5))# Display column names print(df.columns) 📌 pd.read_csv() reads a CSV file and converts it into a DataFrame.

3. Common Arguments of pd.read_csv()

3.1 Why Use Arguments?

pd.read_csv() accepts different arguments to control how the file is loaded.

3.2 Common Arguments

Argument	Description	Example
`sep`	Delimiter used to separate values. Default is comma	`pd.read_csv("file.csv", sep="\t")`
`nrows`	Number of rows to read	`pd.read_csv("file.csv", nrows=100)`
`skiprows`	Number of rows to skip from the top	`pd.read_csv("file.csv", skiprows=5)`
`header`	Row number to use as column names	`pd.read_csv("file.csv", header=0)`
`names`	Custom column names	`pd.read_csv("file.csv", names=["col1", "col2"])`
`usecols`	Select only specific columns	`pd.read_csv("file.csv", usecols=["col1", "col2"])`
`encoding`	Encoding of the file	`pd.read_csv("file.csv", encoding="utf-8")`

3.3 Example: Read Only Selected Columns

import pandas as pddf = pd.read_csv("file.csv", usecols=["name", "salary"])print(df.head()) 📌 Simple idea: Arguments help us load only the data we need.

4. Loading Excel with Pandas

4.1 📘 Loading Excel Files

Excel files can be loaded into a DataFrame using pd.read_excel(). To read .xlsx files, we usually need the openpyxl library. Install it using: pip install openpyxl

4.2 Example: Load Excel File

import pandas as pd# Load first sheet df = pd.read_excel(r"folder_1/folder_2/file.xlsx")print(df.head())

4.3 Load Specific Sheet

df = pd.read_excel(r"folder_1/folder_2/file.xlsx", sheet_name="s_1")print(df.head())

5. 🗄️ Loading Data from SQL with Pandas

5.1 Reading SQL Data

Pandas can read data from SQL databases using pd.read_sql_query(). To connect to databases, we usually use SQLAlchemy. Install it using: pip install sqlalchemy For PostgreSQL, we may also need: pip install psycopg2-binary

5.2 Connection String Format

General format: dialect+driver://username:password@host:port/databaseExample for PostgreSQL: postgresql+psycopg2://username:password@host:port/database

5.3 Example: Read SQL Table

import pandas as pd from sqlalchemy import create_engineconnection_string = "postgresql+psycopg2://username:password@localhost:5432/database_name"engine = create_engine(connection_string)# Engine manages the connection between Pandas and the database df = pd.read_sql_query("SELECT * FROM table_name", engine)print(df.head())engine.dispose() 📌 Pandas can read SQL query results directly into a DataFrame.

6. 🔌 Common Connection Strings

Database	Connection String Format
SQLite	`sqlite:///path_to_db.db`
PostgreSQL	`postgresql+psycopg2://username:password@host:port/database`
MySQL	`mysql+pymysql://username:password@host:port/database`
SQL Server	`mssql+pyodbc://username:password@host:port/database?driver=ODBC+Driver+17+for+SQL+Server`
Oracle	`oracle+cx_oracle://username:password@host:port/database`

Pandas Data Ingestion from csv, excel and sql

7. 🧱 Creating DataFrame from Python Data

7.1 🧱 Creating DataFrames Manually

A DataFrame can also be created from Python data structures like: ✅ List ✅ Dictionary ✅ List of dictionaries

7.2 From List

import pandas as pddata = [[1, "Alice"],[2, "Bob"],[3, "Charlie"] ]df = pd.DataFrame(data, columns=["ID", "Name"])print(df)

7.3 From Dictionary

import pandas as pddata = {"ID": [1, 2, 3], "Name": ["Alice", "Bob", "Charlie"]}df = pd.DataFrame(data)print(df)

7.4 From List of Dictionaries

import pandas as pddata = [{"ID": 1, "Name": "Alice"},{"ID": 2, "Name": "Bob"} ]df = pd.DataFrame(data)print(df)

8. 🔍 Exploratory Data Analysis with Pandas

8.1 What is EDA?

EDA means Exploratory Data Analysis. It is the process of understanding the main characteristics of data. During EDA, we usually check: ✅ Number of rows and columns ✅ Column names ✅ Data types ✅ Missing values ✅ Duplicate values ✅ Summary statistics ✅ Unique value counts ✅ Correlations

8.2 Common Pandas Functions for EDA

Function	Description	Example
`.shape`	Returns number of rows and columns	`df.shape`
`.head()`	Returns first n rows	`df.head(5)`
`.tail()`	Returns last n rows	`df.tail(2)`
`.sample()`	Returns random n rows	`df.sample(4)`
`.columns`	Returns column labels	`df.columns`
`.dtypes`	Returns data types of columns	`df.dtypes`
`.info()`	Shows summary including data types and non-null counts	`df.info()`
`.describe()`	Generates summary statistics	`df.describe()`
`.value_counts()`	Counts unique values in a column	`df["column"].value_counts()`
`.isnull()`	Detects missing values	`df.isnull().sum()`
`.duplicated()`	Detects duplicate rows	`df.duplicated().sum()`
`.corr()`	Computes correlation of numeric columns	`df.corr(numeric_only=True)`

8.3 Example: Basic EDA

import pandas as pddf = pd.read_csv("file.csv")print(df.shape) print(df.head()) df.info() print(df.describe()) print(df.isnull().sum()) print(df.duplicated().sum()) 📌 EDA helps us understand data before cleaning or modeling.

9. 📊 Data Profiling Report using ydata-profiling

9.1 What is ydata-profiling?

ydata-profiling is an external library used to generate a detailed data profiling report. It creates an interactive HTML report with details about: 👉 Data quality 👉 Missing values 👉 Distributions 👉 Correlations 👉 Possible data issues Install it using: pip install ydata-profiling

9.2 Example: Generate Profiling Report

import pandas as pd from ydata_profiling import ProfileReportdf = pd.read_csv(r"path_to_your_data.csv")profile = ProfileReport(df, title="Data Profiling Report")profile.to_file("report.html") 📌 Note: ydata-profiling can sometimes have compatibility issues with newer Python versions.

10. Pandas Series

10.1 📌 What is a Series?

A Series is a one-dimensional labelled array in Pandas. A single column from a DataFrame is usually a Series. Example:age_series = df["age"] 📌 DataFrame is like a full table. Series is like one column.

10.2 Series Example

import pandas as pddf = pd.read_csv(r"base_ball_data_path.csv")age_series = df["age"]filtered_age = age_series[age_series < 22]print(filtered_age) print(filtered_age.mean())

10.3 Element-wise Operations

Pandas Series supports element-wise operations, similar to NumPy. bmi_series = df["weight"] / (df["height"] ** 2)print(bmi_series + 1) 📌 Operations on a Series are applied value by value.

11. 💾 Writing DataFrame to File or Database

11.1 💾 Why Save Data?

After filtering, cleaning, or transforming data, we often need to save the result. A DataFrame can be saved to: ✅ CSV ✅ Excel ✅ SQL database

11.2 Save DataFrame to CSV

df.to_csv("filtered_data.csv", index=False) This saves the DataFrame as a CSV file without writing the index column.

11.3 Save DataFrame as Pipe-Separated File

df.to_csv("filtered_pipe.psv", sep="|", index=False)

11.4 Save DataFrame to Excel

df.to_excel("data.xlsx", index=False)

11.5 Save DataFrame to SQL

df.to_sql("table_name", engine, if_exists="replace", index=False)

Common values for if_exists

Value	Meaning
fail	Fails if table already exists
replace	Drops old table and creates new one
append	Adds data to existing table

Saving Pandas DataFrame to CSV, Excel and SQL

11.6 Note

📌 Simple idea: Use to_csv(), to_excel(), or to_sql() depending on where you want to save the DataFrame.