Unit 9: Handling data with pandas

I. Foundations of Labelled Data Handling

pandas is an open-source Python library for organizing, cleaning, transforming, and analysing structured data. Created by Wes McKinney (development began in 2008), it builds mainly on NumPy and provides labelled data structures that resemble spreadsheets and database tables.

A. Introduction to pandas

pandas makes data analysis efficient by combining labelled rows and columns with concise, vectorized operations.

• Core structures: pandas provides two principal objects:
  • Series: a one-dimensional labelled collection.
  • DataFrame: a two-dimensional table composed of labelled rows and columns.
• Labels and axes: Row labels form the index; DataFrame column labels form the columns axis. Labels may be integers, strings, dates, or other hashable values.
• Data types: A column normally has one data type, such as int64, float64, bool, string, or datetime64[ns]; inspect types with .dtypes.
• Missing values: Depending on the column type, absent data may be represented by NaN, NaT, or pd.NA. Methods such as .isna(), .dropna(), and .fillna() handle them.
• Vectorization: Expressions operate on an entire Series or DataFrame without an explicit Python loop; for example, prices * 1.10 increases every non-missing price by 10%.
• Index alignment: Operations match values by labels rather than merely by position, reducing errors when datasets have differently ordered rows.
• Import convention: pandas is conventionally imported using the alias pd.

import pandas as pd

• Inspection tools: Common first checks include .head(), .tail(), .shape, .columns, .dtypes, and .info().
  • df.shape returns (r, c), where r is the number of rows and c is the number of columns.
  • df.head(3) displays the first three rows without altering the data.

II. One-Dimensional Labelled Data

A Series represents a single labelled sequence and is suitable for one variable, one table column, or a mapping from labels to values.

A. Series

A Series combines a one-dimensional array of values with an index that identifies each value.

• Construction: pd.Series(data, index=labels, name=label) creates a Series; if index is omitted, pandas supplies 0, 1, 2, ....
• Dictionary input: Dictionary keys become index labels and dictionary values become Series values.

marks = pd.Series(
    {"Asha": 82, "Bilal": 76, "Chen": 91},
    name="Marks"
)

• Label access: marks.loc["Asha"] returns 82; .loc uses index labels.
• Position access: marks.iloc[0] returns the first value, also 82; .iloc uses zero-based integer positions.
• Slicing: marks.iloc[0:2] selects positions 0 and 1, while a label slice such as marks.loc["Asha":"Chen"] includes both endpoints when those labels occur in order.
• Properties: marks.index reports labels, marks.dtype reports the value type, and marks.size reports the number of elements.
• Vectorized calculation: marks + 5 adds five to every mark and returns a new Series; the original remains unchanged unless reassigned.
• Boolean filtering: marks[marks >= 80] returns only Asha and Chen because their values satisfy the condition.
• Descriptive methods: .sum(), .mean(), .min(), .max(), .count(), and .value_counts() summarize values.
• Alignment behavior: Adding two Series pairs equal index labels. A label found in only one operand generally produces a missing result at that label.
• Limitation: A Series is one-dimensional; multiple related variables such as name, age, and score are more naturally stored in a DataFrame.

III. Two-Dimensional Labelled Tables

A DataFrame models rectangular data with a shared row index and named columns, although different columns may contain different data types.

A. DataFrame

A DataFrame organizes observations as rows and variables as columns, making it the central pandas structure for tabular datasets.

• Construction from a dictionary: Each dictionary key becomes a column, and equal-length lists provide its values.

students = pd.DataFrame({
    "Name": ["Asha", "Bilal", "Chen"],
    "Score": [82, 76, 91],
    "Passed": [True, True, True]
})

• Structure: students.shape is (3, 3): three rows and three columns. Its default index is 0, 1, 2.
• Column selection:
  • students["Score"] returns a Series.
  • students[["Name", "Score"]] returns a DataFrame because the selector is a list.
• Row selection: students.loc[1] selects the row labelled 1, whereas students.iloc[1] selects the second row by position.
• Combined selection: students.loc[students["Score"] >= 80, ["Name", "Score"]] selects qualifying rows and two named columns.
• Column creation: students["Grade"] = ["B", "C", "A"] adds a fourth column.
• Changing values: students.loc[1, "Score"] = 79 updates one cell identified by row label and column label.
• Removing data: students.drop(columns=["Passed"]) returns a table without Passed; assign the result or use inplace=True to retain the change.
• Index management: students.set_index("Name") uses names as row labels, while .reset_index() restores a regular column and a default integer index.
• Copies and views: Explicit .copy() is useful before modifying a filtered table, avoiding ambiguous chained assignments such as df[mask]["Score"] = 0.

IV. Ordering Rows and Labels

Sorting rearranges records for comparison, presentation, ranking, and later processing without changing the underlying values.

A. Sorting

pandas sorts either by stored values or by axis labels, and the distinction determines which method is appropriate.

1. Sorting by values:

   • Single key: df.sort_values("Score") orders rows from the smallest score to the largest.
   • Descending order: ascending=False reverses that order.
   • Multiple keys: df.sort_values(["Class", "Score"], ascending=[True, False]) sorts classes alphabetically and scores within each class from highest to lowest.
   • Missing values: na_position="first" or "last" controls where missing entries appear; the default is "last".

2. Sorting by labels:

   • Row index: df.sort_index() orders row labels.
   • Column labels: df.sort_index(axis=1) orders columns; axis=1 denotes the column axis.
   • Direction: ascending=False applies descending label order.

ranked = students.sort_values(
    by="Score",
    ascending=False,
    ignore_index=True
)

• Worked result: With scores 82, 76, 91, ranked orders Chen, Asha, and Bilal; ignore_index=True replaces the old row labels with 0, 1, 2.
• Non-destructive default: Sorting normally returns a new object. Use assignment, as above, to preserve the sorted result.
• Selection versus sorting: .nlargest(2, "Score") directly obtains the two highest-scoring rows and may be clearer than sorting the entire table and taking .head(2).

V. Persistent Tabular Data

Comma-separated values files store plain-text rows, typically with one record per line and fields separated by commas.

A. Working with CSV files

pandas converts CSV text into DataFrames and writes DataFrames back to CSV through configurable input and output methods.

• Reading: pd.read_csv("students.csv") treats the first row as column headings by default.
• Path handling: The argument may be a relative path such as "data/students.csv" or an absolute filesystem path.
• Useful parameters:
  • usecols=["Name", "Score"] loads selected columns.
  • dtype={"Score": "Int64"} requests a nullable integer type.
  • na_values=["NA", "-"] treats specified tokens as missing.
  • parse_dates=["ExamDate"] converts a date column where possible.
  • index_col="StudentID" uses an existing column as the index.
  • nrows=100 reads only the first 100 data rows.
• Alternative separators: sep=";" reads semicolon-delimited data even though the method remains read_csv.
• Large files: chunksize=10_000 returns an iterator of DataFrames, each containing at most 10,000 rows, reducing peak memory use.
• Writing: .to_csv() serializes a DataFrame.

students.to_csv(
    "students_clean.csv",
    index=False,
    encoding="utf-8"
)

• Index control: index=False prevents pandas from writing the DataFrame index as an extra field, which is usually appropriate for a default numerical index.
• Validation after loading: .head(), .shape, .dtypes, .isna().sum(), and .duplicated().sum() expose malformed types, missing values, and repeated records.
• Format limitation: CSV does not preserve pandas data types, formulas, formatting, or multiple tables; types must be inferred or specified again when the file is read.

VI. Transforming and Analysing Tables

DataFrame operations convert raw observations into selected, cleaned, summarized, or combined information while retaining row-and-column organization.

A. Operations using DataFrame

Operations using DataFrame include selection, arithmetic, missing-data treatment, aggregation, grouping, and combining related tables.

• Filtering: Conditions produce Boolean masks; df[df["Score"] >= 80] keeps rows whose score is at least 80.
• Derived columns: df["Percent"] = df["Marks"] / df["Maximum"] * 100 applies elementwise arithmetic, where Marks is the achieved value and Maximum is the possible value.
• String operations: df["Name"].str.strip().str.title() removes surrounding spaces and applies title case to non-missing strings.
• Missing-data operations:
  • df.dropna(subset=["Score"]) removes rows lacking a score.
  • df["Score"].fillna(df["Score"].median()) replaces missing scores with the column median.
• Aggregation: df["Score"].agg(["count", "mean", "max"]) produces several summaries in one operation; count excludes missing values.
• Grouping: df.groupby("Class")["Score"].mean() splits rows by class, calculates each group’s mean, and combines the results into a Series.
• Multiple group summaries: .agg(Mean="mean", Highest="max", Students="count") assigns meaningful names to calculated columns.
• Combining tables: pd.merge(left, right, on="StudentID", how="inner") joins rows sharing StudentID.
  • inner keeps matching keys only.
  • left keeps every key from the left table and inserts missing values where no right-side match exists.
• Concatenation: pd.concat([term1, term2], ignore_index=True) stacks tables vertically when their columns represent the same variables.
• Duplicate handling: df.drop_duplicates(subset=["StudentID"]) retains the first row for each repeated identifier unless keep is changed.
• Function application: Built-in vectorized methods are generally preferred, but df["Score"].map(lambda x: x + 2) can apply a custom scalar transformation.
• Performance principle: Column expressions, grouping, and vectorized string or numeric methods are normally faster and clearer than manually iterating with for loops.