Logo
Machine Learning (2026-2027) - Advanced Pandas and Reading Data from Many Sources

In the print dialog, choose "Save as PDF" as the destination.

Machine Learning, Week 1

Advanced Pandas and Reading Data from Many Sources

Load data from CSV, text, Excel, HTML and JSON into a DataFrame, then filter, group, clean, join and reshape it.

Objectives

  • Read CSV and text files with the right sep, header, names, skiprows, skipfooter and nrows
  • Read Excel sheets, HTML tables and JSON files into a DataFrame
  • Select rows with loc and a boolean mask, and summarise columns with describe
  • Use groupby and pivot_table to aggregate, and apply to create new columns
  • Find and fix missing values with isnull, fillna and dropna
  • Combine tables with concat, merge and join

Week 1 of the plan

Where This Sits in the Course

  • Two notebooks: Advanced Pandas Operations and How to read data from different text based (and non-text based) sources
  • Data: a superstore file of 9994 orders, and small house-price files in many formats
  • Project milestone this week: form a team of 3 with a project idea, or a team of 2 with a research paper

Plan for the Two Hours

PartWhat we doTime
1Reading data from many sources35 min
2Selecting, filtering and grouping25 min
3Missing values, joins and reshaping25 min
4Common mistakes and the project milestone5 min
5Practice with answers, then takeaways30 min

Part 1

Reading Data from Many Sources

One function per format, one DataFrame at the end

One Function per Source

Sourcepandas functionReturns
CSV, zipped CSVpd.read_csva DataFrame
Other delimited textpd.read_tablea DataFrame
Excel workbookpd.read_excela DataFrame, or a dict of them
Tables in a web pagepd.read_htmla list of DataFrames
JSON filepd.read_jsona DataFrame

Read the Files from the Repository

python
base = ("https://raw.githubusercontent.com/tirthajyoti/"
        "Machine-Learning-with-Python/master/Pandas%20and%20Numpy/"
        "Read_data_various_sources/")
df1 = pd.read_csv(base + "CSV_EX_1.csv")
df1.shape      # (5, 4)

A Clean CSV

CSV_EX_1.csv, five houses

text
Bedroom, Sq. foot, Locality, Price ($)
2, 1500, Good, 300000
3, 1300, Fair, 240000
3, 1900, Very good, 450000
3, 1850, Bad, 280000
2, 1640, Good, 310000
The raw file
python
df1 = pd.read_csv("CSV_EX_1.csv")
df1.shape      # (5, 4)
  • Line 1 becomes the column names
  • Each other line becomes one row
  • Numbers become int64, words stay text

Worked Example: A File with No Header

CSV_EX_2.csv holds the same five houses, without the header line

text
   2   1500        Good   300000
0  3   1300        Fair   240000
1  3   1900   Very good   450000
2  3   1850         Bad   280000
3  2   1640        Good   310000

The Fix: header=None and names

python
df2 = pd.read_csv("CSV_EX_2.csv",
    header=None,
    names=['Bedroom', 'Sq.ft',
           'Locality', 'Price($)'])
text
   Bedroom  Sq.ft    Locality  Price($)
0        2   1500        Good    300000
1        3   1300        Fair    240000
2        3   1900   Very good    450000
3        3   1850         Bad    280000
4        2   1640        Good    310000

Worked Example: Semicolons Instead of Commas

CSV_EX_3.csv: Bedroom; Sq. foot; Locality; Price ($)

  • df3.shape is (5, 1): no comma, so each line stays one text value
  • The fix is pd.read_csv("CSV_EX_3.csv", sep=';'), which gives (5, 4)

Worked Example: Extra Lines at the Top and Bottom

CSV_EX_skipfooter.csv

text
Filetype: CSV,,,
,Info about some houses,,
Bedroom, Sq. foot, Locality, Price ($)
2,1500, Good,300000
...
2,1640, Good,310000
, This is the end of file,,
  • (8, 4), columns Filetype: CSV, Unnamed: 1, Unnamed: 2, Unnamed: 3
  • The fix: skiprows=2, skipfooter=1, engine='python' gives the clean (5, 4)

Big Files: nrows and Reading in Chunks

python
rows_in_a_chunk = 10
num_chunks = 5
colnames = pd.read_csv("Boston_housing.csv", nrows=2).columns
list_of_dataframe = []
for i in range(0, num_chunks*rows_in_a_chunk, rows_in_a_chunk):
    df = pd.read_csv("Boston_housing.csv", header=0, skiprows=i,
                     nrows=rows_in_a_chunk, names=colnames)
    list_of_dataframe.append(df)
  • nrows=2 reads only the first 2 rows
  • Each pass skips i lines and reads the next 10
  • 5 chunks, each of shape (10, 14)
  • Stacked together they equal the first 50 rows of the file (506 in total)

Worked Example: Blank Lines

CSV_EX_blankline.csv has two empty lines between the houses

OptionShapeWhat you see
default (True)(5, 4)blank lines are skipped
skip_blank_lines=False(7, 4)two rows of NaN, and 2 becomes 2.0

Try It: The read_csv Explorer

Zipped CSV Files

  • pd.read_csv("CSV_EX_1.zip") sees the .zip extension and unzips on the fly
  • No extra argument is needed: compression defaults to 'infer'
  • The result equals the plain CSV_EX_1.csv read

Excel: One Sheet or Every Sheet

python
pd.read_excel("Housing_data.xlsx", sheet_name='Data_Tab_1')
SheetShape
Data_Tab_1(9, 14)
Data_Tab_2(4, 14)
Data_Tab_3(16, 14)

read_table Expects Tabs

python
pd.read_table("Table_tab_separated.txt")   # (5, 4)
pd.read_table("Table_EX_1.txt")             # (5, 1)
pd.read_table("Table_EX_1.txt", sep=',')    # (5, 4)
  • read_table is read_csv with a tab as the default sep
  • Table_tab_separated.txt uses tabs: a clean (5, 4)
  • Table_EX_1.txt uses commas: one column, (5, 1), until you pass sep=','

Tables from a Web Page

pd.read_html(url, header=0) returns a list, one DataFrame per HTML table

RankNOCGoldTotal
1United States46121
2Great Britain2767
3China2670
4Russia‡1956
5Germany1742

Reading JSON

text
[
  {"Bedroom": 2, "Sq. foot": 1500,
   "Locality": "Good", "Price ($)": 300000},
  ...
]
houses.json: the five houses as JSON
python
dj = pd.read_json('houses.json')
dj[dj['Locality'] == 'Good']   # rows 0 and 4
  • read_json turns a list of records into rows: the same (5, 4) table
  • The column names come with no stray spaces
  • Filtering works as on any DataFrame: rows 0 and 4 are Good

Stata and PDF Files

  • pd.read_stata("rscfp2016.dta") reads a Stata data file the same way
  • read_pdf from the separate tabula-py package extracts tables from PDF pages
  • It returns a list of DataFrames and runs Java behind the scenes
  • PDF headers are hard to read, so the notebook passes names through pandas_options

Part 2

Selecting, Filtering and Grouping

The Advanced Pandas Operations notebook, on the superstore orders

Open the Notebook in Colab

text
https://colab.research.google.com/github/
tirthajyoti/Machine-Learning-with-Python/blob/master/
Pandas%20and%20Numpy/Advanced%20Pandas%20Operations.ipynb
Join the three lines into one address, or use the Open in Colab link on the lesson page.
  • The notebook reads Sample - Superstore.xls from its own folder, which Colab does not have
  • The next slide reads the same file from the repository instead

Load the Superstore Orders

python
url = ("https://raw.githubusercontent.com/tirthajyoti/"
       "Machine-Learning-with-Python/master/"
       "Pandas%20and%20Numpy/Sample%20-%20Superstore.xls")
df = pd.read_excel(url)
df.shape                          # (9994, 21)
df.drop('Row ID', axis=1, inplace=True)
df.shape                          # (9994, 20)
  • One row per order line: dates, ship mode, customer, place, product, Sales, Quantity, Discount, Profit
  • The workbook has 4 sheets: Orders, Returns, People, Missing

Pick Rows and Columns with loc

loc takes a list of index labels and a list of column names

python
df.loc[[i for i in range(5, 10)],
       ['Customer ID', 'City', 'Postal Code', 'Sales']]
RowCustomer IDCitySales
5BH-11710Los Angeles48.860
6BH-11710Los Angeles7.280
7BH-11710Los Angeles907.152
8BH-11710Los Angeles18.504
9BH-11710Los Angeles114.900

Worked Example: describe on Records 100 to 199

python
df_subset = df.loc[[i for i in
    range(100, 200)], ['Sales', 'Profit']]
df_subset.describe()
StatisticSalesProfit
mean262.9570.348
std858.984170.745
min1.788-1359.992
50% (median)66.9609.654
max8159.952585.552

The Boxplot of the Same 100 Records

Boxplot of Sales and Profit for records 100 to 199, y axis cut at 0 and 500
  • df_subset.plot.box() draws one box per column
  • The box spans the middle half: Sales 21.3 to 177.1
  • plt.ylim(0, 500) zooms in: 12 sales and 1 profit above 500, and 20 negative profits, are off the chart
python
df_subset.plot.box()
plt.ylim(0, 500)
plt.grid(True)

Worked Example: A Boolean Mask

df_subset = df.loc[[i for i in range(10)], ['Ship Mode', 'State', 'Sales']]

text
        Ship Mode       State     Sales
0    Second Class    Kentucky  261.9600
1    Second Class    Kentucky  731.9400
2    Second Class  California   14.6200
3  Standard Class     Florida  957.5775
4  Standard Class     Florida   22.3680
5  Standard Class  California   48.8600
6  Standard Class  California    7.2800
7  Standard Class  California  907.1520
8  Standard Class  California   18.5040
9  Standard Class  California  114.9000

Combine Conditions with & and |

python
df_subset[(df_subset['State'] != 'California') & (df_subset['Sales'] > 100)]
  • Rows 0, 1, 3: rows 7 and 9 are in California
  • & means both, | means either, and each condition needs its own parentheses

groupby: Split, Apply, Combine

Flow diagram: Split then Apply then Combine.

Split

rows by State

Apply

mean of each group

Combine

one row per State

python
byState = df_subset.groupby('State')
byState.mean(numeric_only=True)

Worked Example: Mean Sales per State

mF = 957.5775 + 22.3682 = 489.97275
mK = 261.96 + 731.942 = 496.95

groupby in Current pandas

Notebook codeTodayWrite instead
byState.mean()TypeError: text columns cannot be averagedbyState.mean(numeric_only=True)
byState.sum()glues the Ship Mode text togetherbyState.sum(numeric_only=True)
byState['Sales'].mean()worksthe same, as a Series

Try It: DataFrame Operations, Step by Step

Part 3

Missing Values, Joins and Reshaping

The Missing sheet and small slices of the orders

Find the Missing Values

python
df_missing = pd.read_excel(url, sheet_name='Missing')
for c in df_missing.columns:
    miss = df_missing[c].isnull().sum()
    if miss > 0:
        print('{} has {} missing value(s)'.format(c, miss))
    else:
        print('{} has NO missing value!'.format(c))
text
Customer has 1 missing value(s)
Product has 2 missing value(s)
Sales has 1 missing value(s)
Quantity has 1 missing value(s)
Discount has NO missing value!
Profit has 1 missing value(s)
11 rows, 6 columns

Worked Example: Fill the Missing Sale

Sales rows 2 to 4 are 8.560, NaN, 22.720; the other 10 values add up to 3015.52

  • ffill() copies the value above: 8.56
  • bfill() copies the value below: 22.72
μ = 3015.5210 = 301.552

Worked Example: dropna

CallWhat survives
dropna(axis=0)?
dropna(axis=1)?
dropna(axis=1, thresh=10)?
  • axis=0: the 6 rows with no gap (1, 2, 4, 6, 9, 10)
  • axis=1: only Discount, the one full column
  • thresh=10: columns with at least 10 values, so only Product is dropped

Spotting Outliers with a Boxplot

Boxplot of Sales and Profit for 50 sampled orders, with two planted negative sales at -1000 and -500
  • 50 random orders, with two wrong sales planted: -1000 and -500
  • Sales can never be negative: the two points far below the box are the errors

Worked Example: concat

Three tables of 2 orders each, columns Customer ID, State, Sales, Profit, rows 0 to 5

  • axis=0 stacks rows: (6, 4)
  • axis=1 puts the tables side by side, matched on the index: (6, 12)
  • No row label is shared, so axis=1 leaves 48 of the 72 cells NaN

Worked Example: merge on a Key

df_1 and df_2: rows 0 to 3, keys CG-12520 twice, DV-13045, SO-20335

python
pd.merge(df_1, df_2, on='Customer ID', how='inner')
2 × 2 + 1 + 1 = 6

Inner, Left, Right, Outer

df_1 with df_3 (rows 2 to 5: DV-13045, SO-20335 twice, BH-11710)

howKeepsRows
innerkeys in both tables3
leftevery df_1 row5
rightevery df_3 row4
outerevery key, sorted6

pivot_table: Averages per Region

python
df.pivot_table(values=['Sales', 'Quantity', 'Profit'], index=['Region'], aggfunc='mean')
RegionProfitQuantitySales
Central17.093.78215.77
East32.143.73238.34
South28.863.83241.80
West33.853.83226.49

value_counts: The Quick Count

python
df['Ship Mode'].value_counts()
Ship ModeOrders
Standard Class5968
Second Class1945
First Class1538
Same Day543

sort_values: Order the Rows

python
df_subset.sort_values(by='Sales')
df_subset.sort_values(by=['State', 'Sales'])
  • By Sales: row 6 (7.28) comes first and row 3 (957.5775) last
  • Each row keeps its index label
  • By ['State', 'Sales']: State first, then Sales breaks ties
  • So the six California rows lead, from 7.28 to 907.152

Worked Example: apply a Function

categorize_sales: Low below 50, Medium below 200, High otherwise

python
def categorize_sales(price):
    if price < 50:
        return "Low"
    elif price < 200:
        return "Medium"
    else:
        return "High"

df['Sales'].apply(categorize_sales)
  • High, Low, Medium
  • On all 9994 orders: Low 4849, High 2579, Medium 2566

Part 4

Common Mistakes and the Project Milestone

Five minutes before the practice

Before you practise

Common Mistakes

  • A single column after reading: the sep does not match the file
  • Losing the first row: the file has no header, so use header=None
  • Reading a local file name in Colab: use the repository address
  • Forgetting the parentheses around each condition joined by & or |
  • Old notebook calls: fillna(method=), mean() on text columns, chained assignment
  • Merging on a key that repeats and not checking the row count

Your team project

Project Milestone: Your Team

  1. Form a team of 3 with a project idea, or a team of 2 with a research paper
  2. Write down the question your project answers, or the paper you will study
  3. Find the dataset and load it with the reader that fits its format
  4. Check shape, the column names and isnull().sum() before anything else

Part 5

Practice: Your Turn

About 30 minutes, answers follow each task

About 5 minutes

Practice 1: A Messy Export

text
Exported by the lab system
Section 3
ID;Name;Quiz;Lab
101;A1;7;9
102;B2;5;8
103;C3;9;10
104;D4;6;7
End of export
p1_scores.txt
python
pd.read_csv('p1_scores.txt', sep=';', skiprows=2,
            skipfooter=1, engine='python')

About 8 minutes

Practice 2: Mask, groupby, apply

text
  Region       Ship Mode  Sales
0   East     First Class  120.0
1   West  Standard Class   45.5
2   East  Standard Class  310.0
3  South    Second Class   80.0
4   West     First Class  220.0
5  South  Standard Class   15.0
6   East    Second Class   95.0
7   West  Standard Class   60.5
  1. Rows with Sales above 100 and Region not West
  2. Sum and mean of Sales per Region
  3. categorize_sales counts
  • Rows 0 and 2
  • Sums: East 525.0, South 95.0, West 326.0
  • Means: East 175.0, South 47.5, West 108.666667
  • Medium 4, Low 2, High 2

About 5 minutes

Practice 3: Missing Values

Qty = 4, NaN, 6, NaN, 10 and Price = 2.5, 3.0, NaN, 4.0, 5.5 and Item = pen, ink, None, pad, tape

  1. ffill() and bfill() of Qty
  2. Qty filled with its mean
  3. Which rows survive dropna(axis=0)? Which columns survive dropna(axis=1, thresh=4)?
  • ffill: 4, 4, 6, 6, 10; bfill: 4, 6, 6, 10, 10
  • Mean 6.666667, so Qty becomes 4, 6.666667, 6, 6.666667, 10
  • Rows 0 and 4; columns Price and Item (Qty has only 3 values)

About 5 minutes

Practice 4: Count the Merge Rows

left: Key = K1, K2, K2, K3 with A = 1, 2, 3, 4. right: Key = K2, K3, K3, K4 with B = 10, 20, 30, 40

howRowsWhy
inner4K2: 2 × 1, K3: 1 × 2
left5the 4 inner rows + K1 with NaN
right5the 4 inner rows + K4 with NaN
outer6the 4 inner rows + K1 + K4

About 7 minutes

Practice 5: In Colab, on the Superstore

  1. How many orders come from Texas?
  2. Total Sales and Profit per Category with groupby
  3. How many orders lost money (Profit below 0)?
  4. Total Profit per Region with pivot_table: which region is lowest?

Answers

Practice 5: Answers

QuestionAnswer
Texas orders985
Sales per CategoryFurniture 741999.80, Office Supplies 719047.03, Technology 836154.03
Profit per CategoryFurniture 18451.27, Office Supplies 122490.80, Technology 145454.95
Orders with a loss1871
Lowest Profit regionCentral, 39706.36

Key Takeaways

  1. One reader per format: read_csv, read_table, read_excel, read_html, read_json
  2. Check the shape and column names right after reading: they reveal a wrong sep or header
  3. Filter with a boolean mask, summarise with groupby and pivot_table
  4. Find gaps with isnull, then decide: fill or drop
  5. Join tables with concat, merge or join, and count the rows after

Open this lesson

Mahmoud AbasAdvanced Pandas and Reading Data from Many Sources

Machine Learning (2026-2027) - Advanced Pandas and Reading Data from Many Sources