Chapter 2 — Data Handling Using Pandas Ii

Overview: Merging, joining and concatenation are the primary ways to combine tabular data in pandas. They let you combine DataFrames by matching rows/columns (merge/join) or by stacking them vertically or horizontally (concatenate).

Merge (database-style joins): pd.merge() aligns rows using one or more keys (columns). It supports relational join types: inner, left, right, outer. Use on (same-named key in both), or left_on/right_on for different key names. You can also merge on index by setting left_index=True or right_index=True.

pd.merge(left, right, how="inner", on=None,
         left_on=None, right_on=None,
         left_index=False, right_index=False,
         suffixes=("_x","_y"), indicator=False, validate=None)

Join semantics:

• inner: rows with keys present in both tables
• left: all rows from left table; matching rows from right
• right: all rows from right table; matching from left
• outer: union of keys; missing values filled with NaN

DataFrame.join: convenience method that joins on index by default. Equivalent to merge with index alignment. Useful for adding columns from another DataFrame where the index is the key.

df.join(other_df, how="left", on=None, lsuffix="", rsuffix="")

Concatenation: pd.concat() stacks DataFrames along an axis. axis=0 (default) appends rows; axis=1 stacks columns. Useful for combining datasets with the same columns (vertical) or same index (horizontal).

pd.concat([df1, df2, df3], axis=0, join="outer", ignore_index=False, keys=None)

Key concat options:

• axis=0: stack rows (one below another)
• axis=1: join columns side-by-side
• ignore_index=True: reset the resulting index to sequential integers
• keys: create a hierarchical index to indicate source frames
• join='outer'|'inner': how to align columns when axis=0

Differences in short: merge/join are relational (match on keys); concat stacks whole DataFrames. Use merge/join when you want to combine related rows by key. Use concat when you want to append or combine datasets with similar structure.

Practical tips:

• Use indicator=True in pd.merge to see source of each row (left_only/right_only/both).
• Use validate to assert relationship (e.g., "one_to_one", "one_to_many").
• Resolve overlapping column names with suffixes.
• For large datasets, ensure index/key columns are properly typed and consider sorting if you rely on ordered joins.

Example code snippet (student tables):

# merge two tables on student_id
merged = pd.merge(students_info, marks, how="left", on="student_id")

# concatenate monthly sales
all_sales = pd.concat([jan, feb, mar], axis=0, ignore_index=True)

# join by index
df_with_extra = df1.join(df2, how="outer")

What it is: Reshaping and pivoting are operations in Pandas used to change the layout of a DataFrame so it is easier to analyse or visualise. The two main forms are wide (many columns representing categories) and long (one column for a variable type and one for its value). Reshaping converts between these forms; pivoting rearranges rows and columns to summarise data.

Why it matters: Many datasets come in a wide format (e.g., monthly columns) but analysis and plotting tools often expect long format. Pivoting lets you create summary tables (for example, total sales per product per month) that are easy to read and plot.

Key operations:

• melt — converts wide -> long. You specify identifier columns (id_vars) that remain, and measured columns (value_vars) that are unpivoted into a single value column.
• pivot — reshapes long -> wide when each index/column pair has a single value. Use when data is uniquely identified by index & column.
• pivot_table — like pivot but supports aggregation when multiple values map to the same index/column pair (provides aggfunc).
• stack / unstack — move a level of column index to the row index or vice versa; useful with MultiIndex.
• transpose (T) — flip rows and columns.

Typical workflow:

1. Inspect whether data is wide or long.
2. Decide the shape needed for analysis or plotting (e.g., grouping, time series, comparison across categories).
3. Use melt to make the data long or use pivot / pivot_table to create summary wide tables.

Important notes: pivot requires unique index/column/value triplets; pivot_table accepts duplicates and applies an aggregation function (sum, mean, count, etc.). stack/unstack preserve MultiIndex structure and are powerful for hierarchical data.

Small code examples (conceptual):

# melt (wide -> long)
df_long = df.melt(id_vars=['ID','Date'], value_vars=['Jan','Feb','Mar'], var_name='Month', value_name='Sales')

# pivot (long -> wide) — only if each (Date,Product) has one Amount
df_wide = df_long.pivot(index='Date', columns='Product', values='Amount')

# pivot_table with aggregation
pt = df.pivot_table(index='Region', columns='Product', values='Sales', aggfunc='sum', fill_value=0, margins=True)

# stack / unstack
s = df_wide.stack()   # columns -> rows
u = s.unstack(level=0)  # rows -> columns

What is Grouping and Aggregation?

Grouping and aggregation is a way to summarize data by categories. In pandas the common pattern is called split-apply-combine:

• Split: split the DataFrame into groups based on values in one or more columns (using groupby).
• Apply: apply aggregation functions (like sum, mean, count) or custom functions to each group.
• Combine: combine the results into a new summarized DataFrame or Series.

Basic usage: df.groupby('col') creates a GroupBy object. Common aggregations are .sum(), .mean(), .count(), .min(), .max(), .std(), and .agg() for multiple aggregations.

Grouping by multiple columns: df.groupby(['col1','col2']) groups on the combination of values from both columns.

'agg' and named aggregation: use agg to compute several statistics at once. Example of named aggregation:

df.groupby('City').agg(Total_Sales=('Sales','sum'), Avg_Price=('Price','mean'))

transform vs apply vs agg vs filter:

• agg (or aggregate) returns aggregated summary for each group (reduced size).
• transform returns a Series or DataFrame with the same index as the input, useful to add group-level statistics as columns (e.g., normalize by group mean).
• apply can run a custom function on each group and return complex results (slower for simple aggregations).
• filter keeps or removes entire groups based on a group-level condition (e.g., groups with count >= 10).

Resetting index and as_index: by default group keys become index. Use reset_index() or groupby(..., as_index=False) to keep group keys as columns.

Pivot tables: for two-dimensional summaries (rows × columns), use pd.pivot_table(df, index='A', columns='B', values='V', aggfunc='sum'), which is essentially grouping with an extra layout step.

Best practices and tips:

• Prefer vectorized aggregations (.agg) over .apply for performance.
• Use categorical dtypes for columns with few unique values to speed up grouping.
• Use named aggregation for clear column names in results.

Short example (conceptual):

# Sales DataFrame columns: ['City','Product','Sales','Units']
sales_by_city = df.groupby('City').agg({'Sales':'sum','Units':'sum'})
avg_price_by_product = df.groupby('Product')['Sales'].sum() / df.groupby('Product')['Units'].sum()

What are missing values?
Missing values (NaN, None) occur when data is not recorded or lost. In pandas they appear as NaN (float) or None (object).

Detecting missing values
Use:

df.isnull()         # boolean mask
df.isnull().sum()   # count missing per column
df.info()           # shows non-null counts per column

How to handle missing values — main strategies

• Drop rows/columns when missingness is small or whole column is useless:
  df.dropna(axis=0, how='any') # drop rows with any missing
df.dropna(axis=1, thresh=100) # keep columns with >=100 non-null
• Fill (impute) with constants or summary stats:
  df['Age'].fillna(0), or use mean/median/mode:
  df['Age'].fillna(df['Age'].mean()) Use median for skewed numeric distributions and mode for categorical values.
• Forward / backward fill (time series): propagate last valid value:
  df.fillna(method='ffill', limit=1) or method='bfill'. Useful for sensor/time data.
• Interpolation: estimate intermediate values (linear, time, polynomial):
  df['value'].interpolate(method='linear') or method='time' when index is datetime.
• Group-wise imputation: fill using group statistics (e.g., group mean):
  df['Salary'] = df.groupby('Dept')['Salary'].transform(lambda x: x.fillna(x.mean()))
• Indicator / flag for missingness: keep a boolean column to mark imputed rows:
  df['Age_missing'] = df['Age'].isnull()
• Model-based imputation: advanced methods (KNN, MICE, regression) available in sklearn (SimpleImputer, IterativeImputer) when simple methods bias results.

Best practices / cautions

• Decide per-variable: imputing a target variable incorrectly can introduce bias or leakage.
• Use median for skewed numeric data, mean for symmetric data, mode for categorical.
• For time series prefer forward/backward fill or interpolation.
• Record how many values were imputed and test models with/without imputation to check effects.

Quick workflow example (pandas)

# 1. detect
missing_counts = df.isnull().sum()
# 2. examine rows with missing in a column
df[df['Age'].isnull()]
# 3. impute numeric by median
df['Age'] = df['Age'].fillna(df['Age'].median())
# 4. impute categorical by mode
df['City'] = df['City'].fillna(df['City'].mode()[0])

Handling missing data carefully improves data quality and leads to more reliable analysis and models.

What is Hierarchical Indexing (MultiIndex)?

Hierarchical indexing (MultiIndex) is a feature of pandas that allows you to have multiple levels of index labels on rows and/or columns. Instead of a single index, a MultiIndex stores tuples of keys and lets you represent higher-dimensional data in a 2D DataFrame in a clear, structured way.

Why use MultiIndex?

• Represent grouped or multi-dimensional data compactly (for example region → product → month).
• Enable flexible selection, aggregation and reshaping along different levels.
• Work easily with pivoted tables, time series separated by category, or multi-key groupings.

How to create a MultiIndex

• From columns using df.set_index(['A', 'B']).
• From tuples using pd.MultiIndex.from_tuples([(a1,b1),(a2,b2)]).
• From product of levels with pd.MultiIndex.from_product([levels1, levels2]).

Common operations

• Selection by level: df.xs(key, level='level_name') or df.loc[pd.IndexSlice[level1_value, level2_slice], :].
• Swap or reorder levels: df.swaplevel(i, j) and df.reorder_levels([...]).
• Reshape: df.stack() (columns -> rows) and df.unstack(level) (rows -> columns).
• Aggregation by level: df.groupby(level='level_name').sum().
• Sorting: df.sort_index(level=[...]) to order by one or more index levels.

Selection examples (conceptual)

• Select all rows for product P in any region: df.xs('P', level='product').
• Select specific region and product: df.loc[('North','P'), :].

Best practices

• Name your index levels for clearer code: df.index.names = ['region','product'].
• Use sort_index on levels before slicing to ensure predictable results.
• Prefer xs and IndexSlice for readable multi-level selection.

Short example (HTML code block)

  # Suppose df has columns ['region','product','month','sales']
  df = df.set_index(['region','product','month'])
  # Total sales for each region and product
  totals = df.groupby(level=['region','product']).sum()
  # Sales for region 'East' and product 'Pen'
  east_pen = df.xs(('East','Pen'))

What it means
Applying functions to data in Pandas means transforming Series or DataFrame values by calling Python functions (built-in, NumPy or user-defined) either element-wise, row/column-wise, or group-wise to clean, compute, aggregate or reshape data.

Main methods

• Series.map – element-wise mapping for Series (can take a function or dict). Useful for replacing values or mapping categories.
• Series.apply – apply function element-wise to a Series (returns Series).
• DataFrame.apply – apply function along an axis (rows or columns). Use axis=0 for columns, axis=1 for rows.
• DataFrame.applymap – element-wise function for every cell in a DataFrame.
• Series.replace – map or replace specified values directly (fast for fixed replacements).
• groupby().apply / .agg / .transform – run functions on groups: .agg for reductions, .transform when you want aligned result with original index, .apply for custom group operations.
• Vectorized NumPy functions (e.g., np.log, np.where) – generally fastest; prefer over Python-level loops or apply when possible.

Important parameters/notes

• axis selects direction for DataFrame.apply (axis=1 for row-wise).
• raw in apply can pass ndarray (faster) instead of Series objects.
• Return types vary: apply may return Series, DataFrame, or scalar depending on function output. Use .agg to get predictable aggregated outputs.
• Avoid apply when a vectorized Pandas/NumPy alternative exists for speed.
• Handle missing values explicitly inside functions (use fillna or check for pd.isna).

Typical uses
data cleaning (string normalization, parsing), feature engineering (new columns from rows), converting units, categorical mapping, computing derived metrics (BMI, discounts), and group-level summaries (average by department).

Overview
Text data (strings) appears in many real-life tables: names, emails, product reviews, tweets, URLs. Pandas provides a vectorized string accessor (.str) and many tools to clean, transform and analyze text efficiently without Python-level loops.

Typical workflow

• Load data (CSV/Excel) and identify text columns.
• Cleaning / normalization: lowercase, trim whitespace, remove punctuation, normalize encodings.
• Parsing / extraction: split fields, extract patterns (e.g., domains, codes) using regex.
• Tokenization and simple NLP: split into words, remove stopwords, count word frequencies or create dummy variables.
• Feature creation: string length, presence/absence flags, n-grams, or categorical dummies.
• Analysis & visualization: frequency counts, top terms, trends over time.

Key pandas operations & examples

• Access string methods: df['text'].str.method(). These are vectorized and fast.
• Lowercase / uppercase / title: df['txt'].str.lower(), .str.upper(), .str.title()
• Strip whitespace: .str.strip()
• Replace / remove patterns: .str.replace(r"[^\w\s]", '', regex=True) to remove punctuation
• Test / filter: .str.contains('error', case=False, na=False), .str.startswith('http')
• Split into lists or columns: .str.split(',', expand=False) or .str.split(',', expand=True)
• Extract with regex groups: .str.extract(r'@(.+)$') to get email domain
• Find all matches: .str.findall(r'\w+')
• Length: .str.len() gives number of characters per string
• Concatenate strings: df['first'].str.cat(df['last'], sep=' ')
• Convert to categories: df['col'] = df['col'].astype('category') for memory & grouping
• Create dummies from space-separated tags: df['tags'].str.get_dummies(sep=' ')

Handling missing values
String ops may produce NaN. Use df['text'].fillna('') or pass na=False to boolean .str tests to avoid errors.

Regex in pandas
Regex powers extraction and replacement. Common methods: .str.contains(), .str.match(), .str.extract(), .str.replace(), .str.findall(). Example to extract phone code: df['phone'].str.extract(r'\((\d{3})\)').

Counting and frequencies
Use df['col'].value_counts() for categorical text. For word counts: split into words, explode and then value_counts:

words = df['review'].str.lower().str.replace(r"[^\w\s]", '', regex=True).str.split()
all_words = words.explode()
freq = all_words.value_counts()

Applying custom functions
For complex operations use .apply() or vectorized functions. Try to keep heavy ops outside Python loops. Example: sentiment flag

pos_words = {'good','great','excellent'}

def sentiment(s):
    words = set(s.split())
    return 'positive' if words & pos_words else 'neutral'

df['sentiment'] = df['review'].fillna('').str.lower().apply(sentiment)

Notes on performance
Prefer built-in vectorized .str methods and pandas functions (get_dummies, explode) over Python for-loops. For large-scale text mining (TF-IDF, embeddings) use scikit-learn or NLP libraries, then combine results in the DataFrame.

Example end-to-end snippet (clean + top words)

# clean, tokenize and get top 10 words
clean = df['review'].fillna('').str.lower().str.replace(r"[^\w\s]", '', regex=True)
words = clean.str.split().explode()
top10 = words.value_counts().head(10)

Curriculum links: string methods, regex basics, splitting and joining, value_counts, get_dummies, apply, handling NaNs.

What is a Time Series? A time series is a sequence of observations indexed by time (e.g., daily stock prices, hourly temperature, monthly sales). In Pandas, time series data is handled efficiently using datetime-like dtypes, DatetimeIndex, PeriodIndex and timedeltas.

Key components in Pandas

• Datetime conversion: use pd.to_datetime to convert strings to datetime64[ns].
• Indexing: set a DatetimeIndex with df.set_index('date_col'), enabling time-based selection and resampling.
• Resampling and frequency: resample('M'), resample('D'), asfreq – to change observation frequency and aggregate (mean, sum, etc.).
• Rolling and expanding windows: rolling(window).mean(), expanding().sum() for moving statistics.
• Shifting and lead/lag: shift(1), pct_change() for temporal shifts and percent changes.
• Datetime accessors: df['date'].dt.year, .month, .dayofweek, .hour, .day_name() to extract components.
• Timedelta arithmetic: perform date difference operations and use pd.to_timedelta for durations.
• Periods and time spans: Period and PeriodIndex for fixed spans (e.g., '2019Q1').
• Time zones: tz_localize and tz_convert for timezone-aware datetimes.

Common workflows

• Convert and set index: pd.to_datetime -> set_index -> sort_index.
• Resample to desired frequency and aggregate: df.resample('M').sum() or .mean().
• Smooth or denoise: df['value'].rolling(7).mean() or use exponential smoothing.
• Feature extraction: create columns like month, weekday, hour for modelling or grouping.
• Handle missing timestamps: reindex with a complete date_range and interpolate/fill missing values.

Why these matter: Time-aware indexing and resampling simplify aggregation over calendars, enable trend and seasonality analysis, and prepare data for forecasting models.

Overview: Advanced Input/Output (I/O) in pandas covers efficient reading and writing of many file formats (CSV, Excel, JSON, SQL, Parquet, Feather, HDF5, compressed files, web/URL sources) and techniques to handle large datasets, control parsing, optimize memory, and reliably exchange data with databases and external systems.

Common formats & trade-offs:

• CSV/TSV: universal, text-based, large files but slower and larger on disk.
• Excel (.xlsx): convenient for spreadsheets but slower, not ideal for very large data.
• JSON: flexible for hierarchical data; may be slower and larger than binary formats.
• Parquet/Feather: columnar binary formats — fast I/O, smaller size, ideal for analytics and large data.
• HDF5: efficient for very large arrays/frames with random access, but more complex metadata.
• SQL databases: persistent storage with query capability; use read_sql/to_sql and SQLAlchemy.

Key advanced reading/writing concepts:

• Parsing control: use usecols, dtype, parse_dates, converters, header, names, sep, quoting to precisely parse input and avoid costly post-processing.
• Memory optimization: set dtype (e.g., category for repeated strings), specify integers with nullable dtypes if needed, read only required columns (usecols), and disable low_memory when types are known.
• Chunked processing: read_csv(..., chunksize=...) or use an iterator to process a file in manageable parts and avoid loading entire file into memory.
• Compressed files & URLs: pandas can read compressed files (gzip, bz2, zip, xz) directly via compression parameter and can read from HTTP/HTTPS/ftp URLs.
• Binary formats: prefer parquet/feather when speed and disk size matter; they preserve dtypes and are columnar for analytic workloads.
• Database I/O: use read_sql_query/read_sql_table and df.to_sql with SQLAlchemy; use chunksize for bulk writes and control if_exists behaviour.
• Error handling: control bad lines with on_bad_lines, specify na_values to map missing value tokens, and use dtype converters for robust parsing.

Typical advanced patterns (code examples):

# 1) Read CSV in chunks, process and append results
for chunk in pd.read_csv('big.csv', chunksize=100000, usecols=['A','B','date'], parse_dates=['date'], dtype={'A': 'int32'}):
    chunk['B'] = chunk['B'].str.lower()
    # aggregate or write processed chunk to disk / database

# 2) Optimize dtypes after a quick read
df = pd.read_csv('data.csv', nrows=1000)
# infer and then set smaller dtypes
df['category_col'] = df['category_col'].astype('category')
# then read full file with known dtypes
full = pd.read_csv('data.csv', dtype={'category_col': 'category', 'id': 'int32'})

# 3) Write parquet with compression
df.to_parquet('out.parquet', compression='snappy', index=False)

# 4) Read from SQL with query
import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///mydb.sqlite')
df = pd.read_sql_query('SELECT * FROM sales WHERE date >= ?', engine, params=['2024-01-01'])

# 5) Write to SQL in chunks
df.to_sql('sales', engine, if_exists='append', index=False, chunksize=5000)

Best practices:

• Prefer binary columnar formats (Parquet/Feather) for repeated analytics work.
• Always specify dtype or use a small sample to infer dtypes and then enforce them when reading full data.
• Use chunksize/iterator for files that don’t fit into memory; process and persist intermediate results.
• Use compression for storage but test read/write times: compressed text can be slower to read but saves storage and I/O bandwidth.
• When writing to databases, use bulk insert (chunksize) and appropriate dtype mapping to reduce overhead.

Notes on reliability and portability:

• Be explicit about encoding (e.g., encoding='utf-8') when reading/writing text files.
• When exchanging data with other systems, include schema documentation (column names, dtypes, date formats) to avoid ambiguities.
• Use consistent NA tokens with na_values and keep reproducible reading code (avoid automatic type conversion surprises).

Descriptive statistics are numerical summaries that describe the main features of a dataset: measures of central tendency (where the data cluster), measures of dispersion (how spread out the data are), and measures of shape (symmetry and peakedness). In data analysis they give a quick overview before modelling or further processing.

In the context of Pandas (Class 12 Informatics Practices, Data Handling using Pandas II), descriptive statistics and summary operations are used to inspect DataFrame contents and produce concise summaries. Pandas provides built-in methods such as df.describe(), df.mean(), df.median(), df.mode(), df.std(), df.var(), df.count(), df.min(), df.max(), df.quantile(), df.value_counts() and df.corr() to compute these metrics quickly.

Typical steps when summarizing a dataset with Pandas:

• Use df.info() and df.head() to check types and sample rows.
• Use df.describe() to get count, mean, std, min, quartiles and max for numeric columns. For all columns use df.describe(include='all').
• Check missing values with df.isnull().sum() and unique counts with df.nunique().
• Compute specific statistics as needed: df['col'].median(), df['col'].mode(), df['col'].quantile(0.75), etc.
• Use df.corr() to see linear relationships between numeric variables.

Descriptive statistics help answer questions such as: what is the typical value, how much do values vary, are there outliers, and are two variables related? These answers guide data cleaning, visualization, and modelling choices.

What it is
Indexing and selection in pandas are the techniques used to access, slice and extract subsets of data from Series and DataFrame objects. Proper indexing lets you retrieve rows, columns, single values, ranges and hierarchical (MultiIndex) parts efficiently.

Fundamental concepts

• Index: labels for rows (and columns). Can be numbers, strings, datetimes, or a MultiIndex (hierarchical labels).
• Label-based vs position-based: .loc uses labels; .iloc uses integer positions. Regular bracket access df['col'] selects column by label.
• Scalar access: .at (label) and .iat (position) for fast single-value access.
• Boolean indexing: Use boolean masks (conditions) to select rows that satisfy criteria.
• Chained assignment warning: Avoid constructions like df[df['x']>0]['y'] = 0. Use df.loc[mask, 'y'] = 0 to safely set values.

Common selection patterns

• Select a column: df['col'] or df.col (note: df.col only when name is a valid attribute).
• Select multiple columns: df[['col1', 'col2']].
• Select rows by label: df.loc['row_label'] or range df.loc['a':'d'] (inclusive).
• Select rows by position: df.iloc[0:5] (end exclusive).
• Select rows & columns: df.loc[row_label, col_label] or df.iloc[row_pos, col_pos].
• Boolean mask: mask = df['marks'] > 75; df[mask].
• Query string: df.query('marks > 75 and class == "12A"') (readable for multiple conditions).

Index operations

• df.set_index('col') — make a column the index.
• df.reset_index() — move index back to a column.
• df.reindex(new_index, fill_value=...) — conform to a new index, optionally filling missing entries.
• MultiIndex selection: use tuples, slices or .xs to extract cross-sections.

Performance tips

• Use .loc/.iloc and .at/.iat for faster and clearer access.
• Avoid expensive Python loops; use vectorized boolean masks and built-in methods like df.query, df.isin, df.between.

Example workflow summary
Typical sequence: set an index (e.g., date), slice a range with .loc, apply a boolean mask to filter, and modify safe via .loc. Use reset_index to return to default integer index when needed.