Python Pandas - II — Study Notes

1. Introduction

In Python Pandas - I we learnt how to create Series and DataFrames in memory. In real life, however, data rarely arrives inside a program; it usually exists in files such as CSV, Excel, or databases. Python Pandas - II deals with the practical side of data handling: reading data from external sources, cleaning it, and applying operations such as sorting, aggregation, and grouping to extract meaningful information. This is the chapter that turns pandas from a toy library into a real data-analysis tool.

The chapter begins with the famous read_csv() function, which loads tabular data from a text file into a DataFrame, and its counterpart to_csv() used to save results back to disk. We then study missing-data handling, because real datasets almost always contain blank or null entries. Finally, we explore powerful operations such as groupby() for split-apply-combine analysis, sort_values() for ordering, agg() for custom aggregation, and concatenation for combining multiple DataFrames.

Every concept here is examinable both as code-writing and as output-prediction. The ability to trace what a pandas program does line by line is the single most important skill tested in the practical examination. This chapter therefore contains many small programs with their expected outputs, and you are advised to run each one on your own system to build confidence.

2. Importing and Exporting Data

2.1 Reading a CSV File

A CSV (Comma Separated Values) file stores tabular data as plain text where each line is a record and values are separated by commas. The function used to read such a file is pd.read_csv():

import pandas as pd
df = pd.read_csv("students.csv")
print(df.head())

The first row of the CSV file is treated as the column header by default. If the file has no header, the argument header=None is passed and columns are auto-named with integers:

df = pd.read_csv("data.txt", header=None)

Other commonly used arguments of read_csv() are sep, which specifies the delimiter (useful for tab-separated files where sep="\t"), and index_col, which tells pandas which column to use as the row index:

df = pd.read_csv("students.csv", index_col=0)

If a column contains blank values, pandas automatically fills them with NaN (Not a Number), which can be detected later using the missing-data methods.

2.2 Writing a DataFrame to a CSV File

The to_csv() method saves a DataFrame back to disk. The argument index controls whether the row labels are also written:

df.to_csv("output.csv")
df.to_csv("output.csv", index=False)

By default the index is written, which often results in an unwanted extra column when the file is read again. Practise using index=False for clean output.

2.3 Reading Data from Other Sources

Data can also come from Excel files, JSON files, or an HTML table:

df_excel = pd.read_excel("marks.xlsx", sheet_name="Sheet1")
df_json = pd.read_json("data.json")
df_html = pd.read_html("page.html")

read_excel() requires the openpyxl library to be installed. The corresponding export functions are to_excel(), to_json(), and to_html().

3. Handling Missing Data

Real-world data is almost never complete. Missing values are represented by NaN in pandas. Several methods are provided to deal with them.

3.1 Detecting Missing Values

import pandas as pd
import numpy as np
df = pd.DataFrame({"A": [1, 2, np.nan], "B": [4, np.nan, 6]})
print(df.isnull())
print(df.notnull())

• df.isnull() returns a DataFrame of the same shape with True wherever a value is missing.
• df.notnull() is the exact opposite, returning True wherever a value is present.

3.2 Dropping Missing Values

dropna() removes all rows that contain any missing value:

import pandas as pd
import numpy as np
df = pd.DataFrame({"A": [1, 2, np.nan], "B": [4, np.nan, 6]})
print(df.dropna())

Only the rows that have no missing value survive. The axis parameter can be used to drop columns instead: df.dropna(axis=1).

3.3 Filling Missing Values

fillna() replaces missing values with a given constant or with computed values:

print(df.fillna(0))
print(df.fillna(df.mean()))

The first statement replaces every NaN with 0. The second replaces each missing value with the mean of its column, a technique known as mean imputation.

4. Sorting Data

DataFrame rows can be sorted either by values or by index.

4.1 Sorting by Values

import pandas as pd
df = pd.DataFrame({"Name": ["Amit", "Ravi", "Simran"],
                   "Marks": [78, 92, 85]})
print(df.sort_values("Marks"))
print(df.sort_values("Marks", ascending=False))

By default sort_values("Marks") sorts in ascending order. Passing ascending=False sorts in descending order. For sorting by two columns, a list is passed: df.sort_values(["Class", "Marks"], ascending=[True, False]).

4.2 Sorting by Index

sort_index() arranges the rows in the order of their index labels:

df = pd.DataFrame({"Marks": [78, 92]}, index=[201, 102])
print(df.sort_index())

5. The groupby() Function

groupby() implements the split-apply-combine pattern: the DataFrame is split into groups based on the values of one or more columns, a function is applied to each group, and the results are combined into a single output.

import pandas as pd
df = pd.DataFrame({
    "City": ["Delhi", "Mumbai", "Delhi", "Mumbai"],
    "Sales": [100, 150, 120, 170]
})
print(df.groupby("City").sum())

Output:

        Sales
City
Delhi     220
Mumbai    320

Here rows are grouped by the City column and the sum of Sales is computed for each city. Common aggregations used with groupby() are sum(), mean(), count(), max(), min(), and median(). Multiple columns can be passed to group by several keys: df.groupby(["Class", "Section"]).mean().

5.1 Using agg() with groupby

The agg() method allows more than one function to be applied at once:

print(df.groupby("City").agg({"Sales": ["sum", "mean"]}))

This produces a DataFrame with both the total and the average sales for each city.

6. Merging and Concatenating DataFrames

6.1 Concatenating DataFrames

pd.concat() stacks DataFrames either vertically (default, axis=0) or horizontally (axis=1):

import pandas as pd
df1 = pd.DataFrame({"A": [1, 2]})
df2 = pd.DataFrame({"A": [3, 4]})
print(pd.concat([df1, df2]))
print(pd.concat([df1, df2], ignore_index=True))

The output of the first concat has indices 0, 1, 0, 1. Using ignore_index=True renumbers the rows to 0, 1, 2, 3.

6.2 Merging DataFrames

pd.merge() combines two DataFrames on a common column, similar to a SQL join:

import pandas as pd
s1 = pd.DataFrame({"Roll": [1, 2, 3], "Name": ["A", "B", "C"]})
s2 = pd.DataFrame({"Roll": [2, 3, 4], "Marks": [80, 90, 70]})
print(pd.merge(s1, s2, on="Roll"))

The result contains only those roll numbers present in both DataFrames, because the default join is an inner join. The how parameter can change this to "left", "right", or "outer".

7. Statistical Operations

A DataFrame has several built-in statistical methods:

import pandas as pd
df = pd.DataFrame({"Marks": [78, 92, 85, 68]})
print(df.sum())
print(df.mean())
print(df.max())
print(df.min())
print(df.median())
print(df.std())
print(df.describe())

• df.sum() and df.mean() compute the sum and arithmetic mean of each column.
• df.max() and df.min() return the largest and smallest values.
• df.median() gives the middle value after sorting.
• df.std() gives the standard deviation, a measure of spread.
• df.describe() prints a comprehensive summary of all numerical columns at once.

Quick Revision Tables

Table 1: Missing Data Handling Methods

Table 2: Import and Export Functions

Mind Map

Important Diagrams (SVG)

Diagram 1: Split-Apply-Combine Pattern of groupby()

Diagram 2: Data Import-Process-Export Pipeline

Common Mistakes

1. Forgetting that read_csv() treats the first row as the header, so files without a header produce a DataFrame with the first record missing.
2. Using to_csv() without index=False and later wondering why an extra unnamed column appears when reading the file back.
3. Confusing isnull() with dropna(); the first detects missing values while the second removes them.
4. Passing the wrong axis to dropna(); the default axis=0 drops rows, while axis=1 drops columns.
5. Applying fillna(0) blindly without considering that replacing missing values with zero changes the mean and other statistics.
6. Using groupby() without any aggregation function, which returns a DataFrameGroupBy object instead of results.
7. Expecting pd.concat([df1, df2]) to renumber the index automatically; ignore_index=True is needed for that.
8. Assuming pd.merge() performs an outer join by default; the default is an inner join.
9. Writing a CSV import filename that does not exist in the current working directory, causing a FileNotFoundError.

Exam Tips

1. Practise writing a complete program that reads a CSV, cleans it with dropna or fillna, and prints the result; this is a frequent 4-mark practical question.
2. Memorise the exact spelling of read_csv, to_csv, fillna, dropna, isnull, notnull, and groupby, since one-letter errors cause failures.
3. Remember that groupby("col").sum() returns a DataFrame indexed by the grouped column.
4. Know that pd.merge(df1, df2, on="key") behaves like an SQL inner join by default and how "how" changes it.
5. Be ready to predict output for sort operations, especially the effect of ascending=False.
6. Learn that df.describe() summarises only numerical columns by default.
7. In practical exams, always print the result with print(df.head()) so that the evaluator can see the output.

Conclusion

Python Pandas - II moves from creating DataFrames to working with real data. The read and write functions connect pandas to external files, missing-data methods such as isnull(), notnull(), dropna(), and fillna() handle the imperfect nature of real datasets, and sort_values() brings order to results. The groupby() function implements the powerful split-apply-combine paradigm that powers most aggregation work, while agg() allows multiple statistics to be computed in one call. Concatenation and merging combine data from multiple sources in the same way SQL joins do. Together with the statistical methods and describe(), these tools form the complete toolkit needed for the data-handling and data-visualisation chapters that follow in this course.