Notebook 2 — Pandas Basics for CA Professionals

Goal: Work with real Excel-style data in Python using Pandas.

You will learn:

  1. What Pandas is and how it relates to Excel
  2. Series and DataFrame — the two main objects
  3. Loading a CSV file and looking at it
  4. Selecting columns and filtering rows
  5. Adding new calculated columns (like a new Excel column)
  6. Sorting data
  7. GroupBy — like a Pivot Table
  8. Saving your results back to Excel/CSV
  9. A short mini-project at the end

Throughout the notebook we will use Nepali invoice, sales and payroll data placed in the data/ folder beside this notebook.

1. What is Pandas?

Pandas is a Python library that gives you a DataFrame — basically an Excel sheet inside Python.

Excel Pandas
Workbook (your script)
Worksheet DataFrame
Column Series
Cell one value
Pivot Table groupby()
AutoFilter boolean mask 
import pandas as pd
import numpy as np
print("Pandas version:", pd.__version__)

Pandas version: 3.0.3

2. A Series — one column of data

A Series is a single column with a label for each row (the index).

monthly = pd.Series(
    data=[1250000, 1380000, 1120000, 1450000, 1620000],
    index=["Shrawan", "Bhadra", "Ashwin", "Kartik", "Mangsir"],
    name="Revenue_NPR",
)
print(monthly)
print()
print("Total :", monthly.sum())
print("Mean  :", monthly.mean())

Shrawan    1250000
Bhadra     1380000
Ashwin     1120000
Kartik     1450000
Mangsir    1620000
Name: Revenue_NPR, dtype: int64

Total : 6820000
Mean  : 1364000.0

Practice 1

Create a Series called expenses for these 5 months: - Shrawan: 920000, Bhadra: 980000, Ashwin: 870000, Kartik: 1050000, Mangsir: 1180000

Then print the total and the highest expense.

# Your turn:
Show solution 
expenses = pd.Series(
    [920000, 980000, 870000, 1050000, 1180000],
    index=["Shrawan","Bhadra","Ashwin","Kartik","Mangsir"],
    name="Expense_NPR",
)
print(expenses)
print("Total :", expenses.sum())
print("Max   :", expenses.max())
Shrawan 920000 Bhadra 980000 Ashwin 870000 Kartik 1050000 Mangsir 1180000 Name: Expense_NPR, dtype: int64 Total : 5000000 Max : 1180000

3. A DataFrame — the full table

A DataFrame is several Series joined together. Each column has a name; each row has an index.

df = pd.DataFrame({
    "Month":   ["Shrawan", "Bhadra", "Ashwin", "Kartik", "Mangsir"],
    "Revenue": [1250000, 1380000, 1120000, 1450000, 1620000],
    "Expense": [ 920000,  980000,  870000, 1050000, 1180000],
})
df
Month Revenue Expense
0 Shrawan 1250000 920000
1 Bhadra 1380000 980000
2 Ashwin 1120000 870000
3 Kartik 1450000 1050000
4 Mangsir 1620000 1180000

Practice 2

Create a small DataFrame with 4 employees of your choice. Columns: Name, Department, Salary. Print the DataFrame.

# Your turn:
Show solution 
staff = pd.DataFrame({
    "Name":       ["Ramesh", "Sita", "Hari", "Anita"],
    "Department": ["Audit", "Tax", "Audit", "Advisory"],
    "Salary":     [55000, 72000, 48000, 95000],
})
print(staff)
Name Department Salary 0 Ramesh Audit 55000 1 Sita Tax 72000 2 Hari Audit 48000 3 Anita Advisory 95000

4. Loading data from a CSV file

In real life you don't type the data — you read it from a file. We have a folder called data/ with several Nepali financial datasets already prepared.

invoices = pd.read_csv("data/invoices.csv")
invoices.head()        # show first 5 rows
Invoice_No Date Customer City Product Quantity Unit_Price Amount_NPR VAT_NPR Total_NPR Payment_Status
0 INV-20810001 2024-07-16 Janakpur Wholesale Birgunj Food 39 1219.0 47541.0 6180.33 53721.33 Paid
1 INV-20810002 2024-07-18 Kathmandu Distributors Kathmandu Food 27 1418.0 38286.0 4977.18 43263.18 Overdue
2 INV-20810003 2024-07-20 Biratnagar Enterprise Butwal Stationery 10 1309.0 13090.0 1701.70 14791.70 Paid
3 INV-20810004 2024-07-22 Pokhara Mart Birgunj Food 26 5501.0 143026.0 18593.38 161619.38 Paid
4 INV-20810005 2024-07-24 Janakpur Wholesale Bhaktapur Food 19 1669.0 31711.0 4122.43 35833.43 Paid

Practice 3

Load the file data/monthly_sales.csv into a DataFrame called sales and show the first 5 rows.

# Your turn:
Show solution 
sales = pd.read_csv("data/monthly_sales.csv")
sales.head()
Month Revenue_NPR Expense_NPR Units_Sold
0 Shrawan 1250000 920000 420
1 Bhadra 1380000 980000 465
2 Ashwin 1120000 870000 380
3 Kartik 1450000 1050000 498
4 Mangsir 1620000 1180000 540

5. Quick inspection — get to know your data

Code What it does
df.head(n) first n rows
df.tail(n) last n rows
df.shape (rows, columns)
df.columns column names
df.dtypes data type of each column
df.info() full overview
df.describe() quick statistics for numeric columns 
print("Shape   :", invoices.shape)
print("Columns :", list(invoices.columns))
print()
invoices.info()

Shape   : (200, 11)
Columns : ['Invoice_No', 'Date', 'Customer', 'City', 'Product', 'Quantity', 'Unit_Price', 'Amount_NPR', 'VAT_NPR', 'Total_NPR', 'Payment_Status']

<class 'pandas.DataFrame'>
RangeIndex: 200 entries, 0 to 199
Data columns (total 11 columns):
 #   Column          Non-Null Count  Dtype  
---  ------          --------------  -----  
 0   Invoice_No      200 non-null    str    
 1   Date            200 non-null    str    
 2   Customer        200 non-null    str    
 3   City            200 non-null    str    
 4   Product         200 non-null    str    
 5   Quantity        200 non-null    int64  
 6   Unit_Price      200 non-null    float64
 7   Amount_NPR      200 non-null    float64
 8   VAT_NPR         200 non-null    float64
 9   Total_NPR       200 non-null    float64
 10  Payment_Status  200 non-null    str    
dtypes: float64(4), int64(1), str(6)
memory usage: 17.3 KB

invoices.describe()    # statistics for all numeric columns
Quantity Unit_Price Amount_NPR VAT_NPR Total_NPR
count 200.000000 200.000000 200.000000 200.000000 200.000000
mean 24.355000 4012.410000 93674.160000 12177.640800 105851.800800
std 14.109371 2314.909302 81983.202634 10657.816342 92641.018976
min 1.000000 318.000000 891.000000 115.830000 1006.830000
25% 12.750000 1863.250000 33018.000000 4292.340000 37310.340000
50% 25.000000 3785.500000 67947.500000 8833.175000 76780.675000
75% 36.000000 5984.500000 135046.750000 17556.077500 152602.827500
max 49.000000 8416.000000 387590.000000 50386.700000 437976.700000

Practice 4

Load data/loans.csv into a DataFrame called loans. Then:

  1. Print its shape.
  2. Show its column names.
  3. Show describe() for the numeric columns.
# Your turn:
Show solution 
loans = pd.read_csv("data/loans.csv")
print("Shape   :", loans.shape)
print("Columns :", list(loans.columns))
loans.describe()
Shape : (300, 10) Columns : ['Loan_ID', 'Customer', 'Loan_Amount', 'Tenure_Months', 'Interest_Rate', 'Annual_Income', 'Credit_Score', 'Has_Collateral', 'Sector', 'Defaulted']
Loan_Amount Tenure_Months Interest_Rate Annual_Income Credit_Score Defaulted
count 3.000000e+02 300.000000 300.000000 3.000000e+02 300.000000 300.000000
mean 9.315000e+05 49.720000 11.916633 6.734000e+05 581.816667 0.413333
std 1.111396e+06 37.531547 2.339863 6.099118e+05 158.994109 0.493254
min 1.000000e+05 12.000000 8.040000 1.800000e+05 300.000000 0.000000
25% 2.500000e+05 24.000000 9.882500 3.600000e+05 443.500000 0.000000
50% 5.000000e+05 36.000000 11.830000 3.600000e+05 602.500000 0.000000
75% 1.000000e+06 60.000000 13.947500 6.000000e+05 718.500000 1.000000
max 5.000000e+06 120.000000 15.920000 2.400000e+06 849.000000 1.000000

6. Selecting columns

You can pick a single column (gives a Series), or several columns (gives a DataFrame).

# Single column → Series
invoices["Amount_NPR"].head()

0     47541.0
1     38286.0
2     13090.0
3    143026.0
4     31711.0
Name: Amount_NPR, dtype: float64

# Several columns → DataFrame
invoices[["Customer", "City", "Total_NPR"]].head()
Customer City Total_NPR
0 Janakpur Wholesale Birgunj 53721.33
1 Kathmandu Distributors Kathmandu 43263.18
2 Biratnagar Enterprise Butwal 14791.70
3 Pokhara Mart Birgunj 161619.38
4 Janakpur Wholesale Bhaktapur 35833.43

Practice 5

From invoices, show only the columns Invoice_No, Product, Quantity, Amount_NPR — first 10 rows.

# Your turn:
Show solution 
invoices[["Invoice_No","Product","Quantity","Amount_NPR"]].head(10)
Invoice_No Product Quantity Amount_NPR
0 INV-20810001 Food 39 47541.0
1 INV-20810002 Food 27 38286.0
2 INV-20810003 Stationery 10 13090.0
3 INV-20810004 Food 26 143026.0
4 INV-20810005 Food 19 31711.0
5 INV-20810006 Cosmetics 39 118404.0
6 INV-20810007 Food 3 22914.0
7 INV-20810008 Textile 45 184860.0
8 INV-20810009 Hardware 13 74412.0
9 INV-20810010 Electronics 28 44492.0

7. Filtering rows — the AutoFilter of Pandas

Just like np.array[condition], but on a whole table.

# Invoices with amount over NPR 1 lakh
big = invoices[invoices["Amount_NPR"] > 100000]
print("Found", len(big), "high-value invoices")
big.head()

Found 75 high-value invoices
Invoice_No Date Customer City Product Quantity Unit_Price Amount_NPR VAT_NPR Total_NPR Payment_Status
3 INV-20810004 2024-07-22 Pokhara Mart Birgunj Food 26 5501.0 143026.0 18593.38 161619.38 Paid
5 INV-20810006 2024-07-26 Birgunj Cargo Pokhara Cosmetics 39 3036.0 118404.0 15392.52 133796.52 Pending
7 INV-20810008 2024-07-30 Janakpur Wholesale Bhaktapur Textile 45 4108.0 184860.0 24031.80 208891.80 Overdue
13 INV-20810014 2024-08-11 Annapurna Stores Pokhara Stationery 41 2476.0 101516.0 13197.08 114713.08 Paid
19 INV-20810020 2024-08-23 Everest Suppliers Nepalgunj Cosmetics 49 3192.0 156408.0 20333.04 176741.04 Overdue 
# Combine conditions with & (and) and | (or) — wrap each in ()
overdue_big = invoices[(invoices["Payment_Status"] == "Overdue") &
                       (invoices["Amount_NPR"] > 50000)]
overdue_big.head()
Invoice_No Date Customer City Product Quantity Unit_Price Amount_NPR VAT_NPR Total_NPR Payment_Status
7 INV-20810008 2024-07-30 Janakpur Wholesale Bhaktapur Textile 45 4108.0 184860.0 24031.80 208891.80 Overdue
8 INV-20810009 2024-08-01 Biratnagar Enterprise Hetauda Hardware 13 5724.0 74412.0 9673.56 84085.56 Overdue
18 INV-20810019 2024-08-21 Lumbini Imports Lalitpur Stationery 28 1873.0 52444.0 6817.72 59261.72 Overdue
19 INV-20810020 2024-08-23 Everest Suppliers Nepalgunj Cosmetics 49 3192.0 156408.0 20333.04 176741.04 Overdue
26 INV-20810027 2024-09-06 Birgunj Cargo Bhaktapur Electronics 32 7444.0 238208.0 30967.04 269175.04 Overdue

Practice 6

From invoices:

  1. Find all invoices from the city "Kathmandu".
  2. Find all "Pending" invoices above NPR 30,000.
  3. Count how many invoices have Quantity > 30.
# Your turn:
Show solution 
ktm = invoices[invoices["City"] == "Kathmandu"]
pending = invoices[(invoices["Payment_Status"] == "Pending") &
                   (invoices["Amount_NPR"] > 30000)]
print("KTM invoices    :", len(ktm))
print("Pending > 30K   :", len(pending))
print("Qty > 30 count  :", (invoices["Quantity"] > 30).sum())
KTM invoices : 26 Pending > 30K : 31 Qty > 30 count : 73

8. Sorting

sort_values(by="ColumnName") is your friend — just like Data → Sort in Excel.

# Top 5 highest invoices
top5 = invoices.sort_values("Total_NPR", ascending=False).head(5)
top5[["Invoice_No","Customer","Total_NPR","Payment_Status"]]
Invoice_No Customer Total_NPR Payment_Status
96 INV-20810097 Nepalgunj Goods 437976.70 Pending
132 INV-20810133 Annapurna Stores 406037.25 Overdue
27 INV-20810028 Annapurna Stores 398613.15 Paid
163 INV-20810164 Kathmandu Distributors 352471.86 Pending
154 INV-20810155 Nepalgunj Goods 346756.32 Pending

Practice 7

Sort invoices by Quantity (largest first) and show the top 10.

# Your turn:
Show solution 
invoices.sort_values("Quantity", ascending=False).head(10)
Invoice_No Date Customer City Product Quantity Unit_Price Amount_NPR VAT_NPR Total_NPR Payment_Status
136 INV-20810137 2025-04-14 Nepalgunj Goods Lalitpur Stationery 49 4947.0 242403.0 31512.39 273915.39 Paid
41 INV-20810042 2024-10-06 Everest Suppliers Bhaktapur Electronics 49 2258.0 110642.0 14383.46 125025.46 Paid
96 INV-20810097 2025-01-24 Nepalgunj Goods Biratnagar Hardware 49 7910.0 387590.0 50386.70 437976.70 Pending
19 INV-20810020 2024-08-23 Everest Suppliers Nepalgunj Cosmetics 49 3192.0 156408.0 20333.04 176741.04 Overdue
103 INV-20810104 2025-02-07 Janakpur Wholesale Biratnagar Food 49 3702.0 181398.0 23581.74 204979.74 Overdue
196 INV-20810197 2025-08-12 Pokhara Mart Lalitpur Hardware 48 1666.0 79968.0 10395.84 90363.84 Pending
76 INV-20810077 2024-12-15 Nepalgunj Goods Kathmandu Textile 48 1000.0 48000.0 6240.00 54240.00 Paid
154 INV-20810155 2025-05-20 Nepalgunj Goods Hetauda Food 48 6393.0 306864.0 39892.32 346756.32 Pending
111 INV-20810112 2025-02-23 Annapurna Stores Lalitpur Cosmetics 48 4168.0 200064.0 26008.32 226072.32 Paid
28 INV-20810029 2024-09-10 Janakpur Wholesale Hetauda Hardware 47 660.0 31020.0 4032.60 35052.60 Paid

9. Creating new columns

You can build new columns from existing ones — like adding a new column with an Excel formula.

sales = pd.read_csv("data/monthly_sales.csv")

# New calculated columns
sales["Profit"]      = sales["Revenue_NPR"] - sales["Expense_NPR"]
sales["Margin_pct"]  = (sales["Profit"] / sales["Revenue_NPR"] * 100).round(2)
sales["VAT_NPR"]     = sales["Revenue_NPR"] * 0.13

sales
Month Revenue_NPR Expense_NPR Units_Sold Profit Margin_pct VAT_NPR
0 Shrawan 1250000 920000 420 330000 26.40 162500.0
1 Bhadra 1380000 980000 465 400000 28.99 179400.0
2 Ashwin 1120000 870000 380 250000 22.32 145600.0
3 Kartik 1450000 1050000 498 400000 27.59 188500.0
4 Mangsir 1620000 1180000 540 440000 27.16 210600.0
5 Poush 1780000 1250000 605 530000 29.78 231400.0
6 Magh 1550000 1100000 515 450000 29.03 201500.0
7 Falgun 1420000 1020000 478 400000 28.17 184600.0
8 Chaitra 1680000 1190000 560 490000 29.17 218400.0
9 Baishakh 1910000 1320000 640 590000 30.89 248300.0
10 Jestha 2030000 1410000 690 620000 30.54 263900.0
11 Ashadh 2150000 1490000 720 660000 30.70 279500.0

Practice 8

Load data/payroll.csv into pay. Add a new column Annual_Net = Net_Salary × 12. Then sort by Annual_Net (descending) and show the top 5 employees.

# Your turn:
Show solution 
pay = pd.read_csv("data/payroll.csv")
pay["Annual_Net"] = pay["Net_Salary"] * 12
pay.sort_values("Annual_Net", ascending=False).head(5)
Emp_ID Name Department Designation Years_Exp Basic_Salary Allowance Gross EPF_10pct SSF_1pct Net_Salary Annual_Net
24 E1024 Bishnu Paudel Advisory Manager 22 250000 100000.0 350000.0 25000.0 3500.0 321500.0 3858000.0
7 E1007 Kabita Rai Advisory Senior 8 250000 100000.0 350000.0 25000.0 3500.0 321500.0 3858000.0
14 E1014 Manoj Rana Tax Senior 17 250000 100000.0 350000.0 25000.0 3500.0 321500.0 3858000.0
15 E1015 Kalpana Pokhrel Audit Senior 2 180000 72000.0 252000.0 18000.0 2520.0 231480.0 2777760.0
4 E1004 Bikash Gurung Tax Junior 4 180000 72000.0 252000.0 18000.0 2520.0 231480.0 2777760.0

10. GroupBy — the Pandas Pivot Table

groupby() is the most powerful feature you'll use. It splits data into groups, applies a function to each group, and combines the result. Exactly like a Pivot Table.

# Total invoice amount per city
invoices.groupby("City")["Total_NPR"].sum().sort_values(ascending=False)

City
Bhaktapur     2608959.82
Biratnagar    2432223.30
Kathmandu     2299763.57
Hetauda       2175177.68
Lalitpur      2024371.27
Butwal        1997602.70
Birgunj       1989168.38
Janakpur      1975963.20
Nepalgunj     1934153.20
Pokhara       1732977.04
Name: Total_NPR, dtype: float64

# Multiple metrics at once
invoices.groupby("Payment_Status").agg(
    Count        = ("Invoice_No", "count"),
    Total_Amount = ("Total_NPR", "sum"),
    Avg_Amount   = ("Total_NPR", "mean"),
).round(0)
Count Total_Amount Avg_Amount
Payment_Status
Overdue 28 3268298.0 116725.0
Paid 133 13633958.0 102511.0
Pending 39 4268104.0 109439.0

Practice 9

Using invoices:

  1. Find the total Amount_NPR per Product.
  2. Find the average Quantity per Customer.
  3. Count how many invoices each City has.
# Your turn:
Show solution 
print(invoices.groupby("Product")["Amount_NPR"].sum())
print()
print(invoices.groupby("Customer")["Quantity"].mean().round(1))
print()
print(invoices.groupby("City").size())
Product Cosmetics 2304572.0 Electronics 2802522.0 Food 4614269.0 Hardware 3289388.0 Stationery 2863125.0 Textile 2860956.0 Name: Amount_NPR, dtype: float64 Customer Annapurna Stores 24.0 Biratnagar Enterprise 20.9 Birgunj Cargo 26.6 Everest Suppliers 25.0 Himalayan Traders 22.0 Janakpur Wholesale 24.2 Kathmandu Distributors 27.8 Lumbini Imports 26.1 Nepalgunj Goods 27.0 Pokhara Mart 21.8 Name: Quantity, dtype: float64 City Bhaktapur 26 Biratnagar 15 Birgunj 21 Butwal 18 Hetauda 20 Janakpur 18 Kathmandu 26 Lalitpur 16 Nepalgunj 20 Pokhara 20 dtype: int64

11. Saving your work to a file

Once you have cleaned / processed data, you can save the result.

top5 = invoices.sort_values("Total_NPR", ascending=False).head(5)

top5.to_csv("top5_invoices.csv", index=False)   # save as CSV
# top5.to_excel("top5_invoices.xlsx", index=False)   # needs openpyxl

print("Saved top5_invoices.csv")

Saved top5_invoices.csv

12. Mini-Project — Sales Analysis

Using data/invoices.csv, answer the following:

  1. Which city has the highest total sales?
  2. Which product is sold the most (by quantity)?
  3. What % of invoices are still pending or overdue?
  4. List the top 3 customers by total revenue.
  5. Find the average invoice amount city-wise.
inv = pd.read_csv("data/invoices.csv")

# Your code here
Show solution 
# 1
print("Top city by sales:")
print(inv.groupby("City")["Total_NPR"].sum().sort_values(ascending=False).head(1))
print()

# 2
print("Top product by qty:")
print(inv.groupby("Product")["Quantity"].sum().sort_values(ascending=False).head(1))
print()

# 3
pending_or_over = inv[inv["Payment_Status"] != "Paid"]
print(f"Pending+Overdue: {len(pending_or_over)/len(inv)*100:.1f}%")
print()

# 4
print("Top 3 customers:")
print(inv.groupby("Customer")["Total_NPR"].sum().sort_values(ascending=False).head(3))
print()

# 5
print("Average invoice per city:")
print(inv.groupby("City")["Total_NPR"].mean().round(0).sort_values(ascending=False))
Top city by sales: City Bhaktapur 2608959.82 Name: Total_NPR, dtype: float64 Top product by qty: Product Food 1018 Name: Quantity, dtype: int64 Pending+Overdue: 33.5% Top 3 customers: Customer Nepalgunj Goods 2634935.13 Kathmandu Distributors 2464900.64 Janakpur Wholesale 2335671.58 Name: Total_NPR, dtype: float64 Average invoice per city: City Biratnagar 162148.0 Lalitpur 126523.0 Butwal 110978.0 Janakpur 109776.0 Hetauda 108759.0 Bhaktapur 100345.0 Nepalgunj 96708.0 Birgunj 94722.0 Kathmandu 88452.0 Pokhara 86649.0 Name: Total_NPR, dtype: float64

What's next?

Pandas is now your everyday tool for working with tabular data. In the next notebook (03_Matplotlib_Seaborn) we will turn this data into charts — line graphs, bar charts, heatmaps — so we can present it to clients and partners.