Master Pandas From Scratch

Learn Pandas fundamentals, Series, DataFrames, data selection, filtering, cleaning, sorting, grouping, merging, reshaping, string operations, time series, data analysis, performance optimization, visualization, and practical projects through structured lessons.

COURSE SYLLABUS
1. Pandas Introduction and Environment Setup
Introduction to Pandas Installation and Environment Setup
2. Pandas Core Data Structures
Data Structures Overview Pandas Series DataFrames Indexes, Rows and Columns Shape, Size and Dimensions
3. Pandas Data Input and Output
Data Input and Output Overview Reading CSV, Excel and JSON Files Writing and Exporting Files
4. Initial Data Inspection
Data Inspection Overview Head and Tail Data Info and Types Descriptive Statistics
5. Selection and Indexing
Selection and Indexing Overview Column Selection loc and iloc Index Management
6. Filtering and Conditional Selection
Filtering Overview Boolean Filtering Multiple Conditions isin, between and str.contains
7. Basic Data Cleaning
Data Cleaning Overview Missing Values Duplicate Data Rename and Replace Data Type Conversion
8. Vectorized Operations and Sorting
Operations and Sorting Overview Column Operations Sorting Values and Indexes Sum, Mean, Max and Min
9. GroupBy and Aggregation
GroupBy Overview Grouping Data Aggregation Functions Group Filtering Group Transform
10. Merging, Joining and Concatenating
Merge, Join and Concat Overview Concatenation Merging DataFrames Joining by Index
11. Reshaping and Pivoting
Reshaping Overview Pivot Tables Melting Data Stack and Unstack Cross Tabulation
12. String and Categorical Data
String and Categorical Overview String Operations Categorical Data One-Hot Encoding
13. Time Series Analysis
Time Series Overview DateTime Operations Time-Based Indexes Resampling Data Shift and Rolling Windows
14. Custom Functions and Mapping
Apply and Mapping Overview Apply and Lambda Functions Map Operations Element-Wise Operations
15. Performance and Large Datasets
Performance Overview Memory Optimization Processing Files in Chunks PyArrow, Dask and Polars
16. Pandas Data Visualization
Visualization Overview Basic Plotting Histograms, Lines, Scatter and Box Plots Matplotlib and Seaborn

Column Operations in Pandas

Learn how to select, calculate, transform, update, and combine DataFrame columns using Pandas. Create derived columns, apply business rules, perform vectorized calculations, and validate your results.

1. What Are Column Operations?

A DataFrame contains rows and columns. Column operations allow you to work with an entire column or combine several columns without writing a separate Python loop for each row.

For example, Samadhan is analyzing student results. He has columns for student names, assignment marks, exam marks, and attendance. He wants to calculate total marks, average marks, eligibility, and final results.

Common Column Operations

  • Select one or more columns.
  • Create new columns from existing values.
  • Perform arithmetic calculations.
  • Apply conditions to update column values.
  • Transform text and numeric data.
  • Combine columns and remove unnecessary columns.
Original DataFrame Student Assignment Pradnya 18 Samiksha 16 Operations Addition Multiplication Conditions Result Student Total Pradnya 88 Samiksha 81 Transform existing columns into useful information Use vectorized expressions for clear and efficient calculations.

Column operations transform existing values into new analytical columns.

2. Create a Sample DataFrame

We will use a student dataset to demonstrate column selection, arithmetic, conditional updates, and transformations.

import pandas as pd

df = pd.DataFrame({
    "Student": [
        "Samadhan",
        "Pradnya",
        "Samiksha",
        "Aarav",
        "Neha"
    ],
    "Assignment": [18, 20, 16, 14, 19],
    "Exam": [72, 85, 65, 58, 78],
    "Attendance": [90, 96, 82, 68, 88]
})

print(df)

Expected Output

    Student  Assignment  Exam  Attendance
0  Samadhan          18    72          90
1   Pradnya          20    85          96
2  Samiksha          16    65          82
3     Aarav          14    58          68
4      Neha          19    78          88

The assignment is scored out of 20, the exam is scored out of 100, and attendance is represented as a percentage.

3. Select Individual Columns

Use square brackets with a column name to select a single column. The result is a Pandas Series.

assignment_marks = df["Assignment"]

print(assignment_marks)

Expected Output

0    18
1    20
2    16
3    14
4    19
Name: Assignment, dtype: int64

A Series contains one column of values and its associated index. It is useful for calculations, filtering, and summary statistics.

Select Multiple Columns

Pass a list of column names inside the outer square brackets to return a DataFrame.

selected = df[["Student", "Exam"]]

print(selected)

Expected Output

    Student  Exam
0  Samadhan    72
1   Pradnya    85
2  Samiksha    65
3     Aarav    58
4      Neha    78
Remember: df["Exam"] returns a Series, while df[["Exam"]] returns a DataFrame containing one column.

4. Add Two Columns Together

Arithmetic between two numeric Series is performed element by element. This allows you to calculate a total for each student.

Calculate Total Marks

df["Total"] = df["Assignment"] + df["Exam"]

print(df[["Student", "Assignment", "Exam", "Total"]])

Expected Output

    Student  Assignment  Exam  Total
0  Samadhan          18    72     90
1   Pradnya          20    85    105
2  Samiksha          16    65     81
3     Aarav          14    58     72
4      Neha          19    78     97

The new Total column contains the sum of the assignment and exam scores for each row.

Check the scoring system: The example adds the raw assignment and exam marks. If the assignment contributes only a weighted percentage of the final score, calculate the weighted total instead.

5. Calculate an Average Column

You can calculate an average using arithmetic or the mean(axis=1) method. The axis=1 argument calculates across columns for each row.

df["Average"] = df[["Assignment", "Exam"]].mean(axis=1)

print(df[["Student", "Assignment", "Exam", "Average"]])

Expected Output

    Student  Assignment  Exam  Average
0  Samadhan          18    72     45.0
1   Pradnya          20    85     52.5
2  Samiksha          16    65     40.5
3     Aarav          14    58     36.0
4      Neha          19    78     48.5

This average treats the assignment and exam columns equally, even though their maximum scores are different. For a meaningful final grade, normalize or weight the scores according to the assessment rules.

Calculate a Weighted Score

Suppose the assignment contributes 20% and the exam contributes 80% of the final score. First convert the assignment mark to a percentage, then apply the weights.

df["AssignmentPercent"] = (
    df["Assignment"] / 20 * 100
)

df["FinalScore"] = (
    df["AssignmentPercent"] * 0.20
    + df["Exam"] * 0.80
)

print(df[["Student", "FinalScore"]])

Expected Output

    Student  FinalScore
0  Samadhan        73.2
1   Pradnya        88.0
2  Samiksha        68.2
3     Aarav        59.2
4      Neha        80.8

6. Subtract, Multiply, and Divide Columns

You can use arithmetic operators to calculate differences, products, ratios, and percentages.

Calculate the Difference Between Exam and Assignment Marks

df["ScoreDifference"] = (
    df["Exam"] - df["Assignment"]
)

print(df[["Student", "ScoreDifference"]])

Expected Output

    Student  ScoreDifference
0  Samadhan               54
1   Pradnya               65
2  Samiksha               49
3     Aarav               44
4      Neha               59

Calculate a Ratio

df["ExamToAssignmentRatio"] = (
    df["Exam"] / df["Assignment"]
)

print(df[["Student", "ExamToAssignmentRatio"]])

The ratio compares exam marks to assignment marks. Ratios can be useful for analysis, but interpret them carefully because a small denominator can produce a large result.

Calculate Attendance as a Decimal

df["AttendanceDecimal"] = df["Attendance"] / 100

print(df[["Student", "AttendanceDecimal"]])

Expected Output

    Student  AttendanceDecimal
0  Samadhan                0.90
1   Pradnya                0.96
2  Samiksha                0.82
3     Aarav                0.68
4      Neha                0.88

7. Apply Conditions to Create a Result Column

Boolean conditions can classify rows based on one or more column values. This is useful for pass/fail status, eligibility, risk classification, and other business rules.

Pass or Fail Based on Exam Marks

df["Result"] = "Fail"

df.loc[df["Exam"] >= 40, "Result"] = "Pass"

print(df[["Student", "Exam", "Result"]])

Expected Output

    Student  Exam Result
0  Samadhan    72   Pass
1   Pradnya    85   Pass
2  Samiksha    65   Pass
3     Aarav    58   Pass
4      Neha    78   Pass

All students pass because each exam score is at least 40. The example uses a threshold of 40 marks out of 100.

Classify Attendance Eligibility

df["AttendanceStatus"] = "Eligible"

df.loc[
    df["Attendance"] < 75,
    "AttendanceStatus"
] = "Not Eligible"

print(df[["Student", "Attendance", "AttendanceStatus"]])

Expected Output

    Student  Attendance AttendanceStatus
0  Samadhan          90         Eligible
1   Pradnya          96         Eligible
2  Samiksha          82         Eligible
3     Aarav          68     Not Eligible
4      Neha          88         Eligible
Practical tip: Use loc when you need to update only rows that satisfy a condition. This makes the selection rule clear and explicit.

8. Use np.where() for Conditional Columns

The np.where() function creates an array of values based on a condition. It is convenient when there are two possible outcomes.

import numpy as np

df["AttendanceStatus"] = np.where(
    df["Attendance"] >= 75,
    "Eligible",
    "Not Eligible"
)

print(df[["Student", "Attendance", "AttendanceStatus"]])

This expression produces the same attendance classification as the previous example. Use np.where() for simple true/false choices; for more complicated classification rules, consider np.select().

9. Apply Multiple Conditions

Sometimes a result depends on more than one column. For example, a student may need both a sufficient exam score and attendance to be eligible for a certificate.

Certificate Eligibility

df["Certificate"] = np.where(
    (df["Exam"] >= 60) & (df["Attendance"] >= 75),
    "Eligible",
    "Not Eligible"
)

print(df[["Student", "Exam", "Attendance", "Certificate"]])

Expected Output

    Student  Exam  Attendance   Certificate
0  Samadhan    72          90       Eligible
1   Pradnya    85          96       Eligible
2  Samiksha    65          82       Eligible
3     Aarav    58          68   Not Eligible
4      Neha    78          88       Eligible

Both conditions must be true because the expression uses &, the element-wise AND operator for Boolean Series.

Important: Use parentheses around each comparison when combining Pandas Boolean conditions. Use & for AND and | for OR, rather than Python's scalar and and or.

10. Transform a Column with String Methods

Pandas provides the str accessor for vectorized string operations. These methods can standardize text across an entire column.

Example: Normalize Student Names

names = pd.Series([
    " samadhan ",
    "PRADNYA",
    "Samiksha ",
    " aarav"
])

clean_names = names.str.strip().str.title()

print(clean_names)

Expected Output

0    Samadhan
1     Pradnya
2    Samiksha
3       Aarav
dtype: object

str.strip() removes leading and trailing whitespace. str.title() converts text to title case. This is useful for presentation, but title case may not be appropriate for every name or language.

Create a Name Length Column

df["NameLength"] = df["Student"].str.len()

print(df[["Student", "NameLength"]])

Expected Output

    Student  NameLength
0  Samadhan           8
1   Pradnya           7
2  Samiksha           8
3     Aarav           5
4      Neha            4

11. Round Numeric Columns

Calculated values sometimes contain many decimal places. The round() method can round numeric values to a chosen number of decimal places.

df["FinalScore"] = (
    df["Assignment"] / 20 * 100 * 0.20
    + df["Exam"] * 0.80
)

df["FinalScoreRounded"] = df["FinalScore"].round(2)

print(df[["Student", "FinalScoreRounded"]])

Expected Output

    Student  FinalScoreRounded
0  Samadhan               73.2
1   Pradnya               88.0
2  Samiksha               68.2
3     Aarav               59.2
4      Neha               80.8

Rounding is useful for reports and presentation. Keep the required precision in mind when doing financial calculations or applying grading rules.

12. Update an Existing Column

You can replace an existing column with transformed values by assigning the result back to the same column name.

Increase All Exam Marks by Five Points

df_updated = df.copy()

df_updated["Exam"] = df_updated["Exam"] + 5

print(df_updated[["Student", "Exam"]])

Expected Output

    Student  Exam
0  Samadhan    77
1   Pradnya    90
2  Samiksha    70
3     Aarav    63
4      Neha    83

The example updates a copy of the DataFrame, preserving the original exam marks in df. In a real grading system, ensure that the adjustment is authorized and that the resulting scores stay within the valid range.

13. Combine Columns into a New Text Column

You can combine text columns using string operations. For example, a dataset may store first and last names separately.

people = pd.DataFrame({
    "FirstName": ["Samadhan", "Pradnya", "Samiksha"],
    "LastName": ["Patil", "Sharma", "Deshmukh"]
})

people["FullName"] = (
    people["FirstName"] + " " + people["LastName"]
)

print(people)

Expected Output

  FirstName LastName          FullName
0  Samadhan    Patil     Samadhan Patil
1   Pradnya   Sharma     Pradnya Sharma
2  Samiksha Deshmukh Samiksha Deshmukh

The new column joins the first and last names with a space. If either source column contains missing values, decide how those missing values should be handled before concatenation.

14. Remove Unnecessary Columns

During analysis, you may create temporary columns that are no longer needed. The drop() method can remove columns from a DataFrame.

df_clean = df.drop(
    columns=["ScoreDifference", "ExamToAssignmentRatio"],
    errors="ignore"
)

print(df_clean.columns.tolist())

The listed columns are removed if they exist. The errors="ignore" option prevents an error when a specified column is missing. Without it, Pandas raises an error for unknown labels.

Best practice: Use drop(columns=[...]) to make it explicit that you are removing columns rather than rows.

15. Understand Vectorized Operations

Vectorized operations apply a calculation to an entire Series or array. They are generally clearer and often more efficient than iterating over individual rows in Python.

Vectorized Calculation

df["Total"] = df["Assignment"] + df["Exam"]

Equivalent Row-by-Row Approach

totals = []

for _, row in df.iterrows():
    totals.append(
        row["Assignment"] + row["Exam"]
    )

df["TotalLoop"] = totals

Both approaches calculate the same totals for this dataset, but the vectorized expression is shorter and is the preferred starting point for ordinary column arithmetic.

Vectorized Operation Entire column One concise expression Row-by-Row Loop Read a row Repeat the calculation Prefer vectorized operations for straightforward column calculations.

Vectorized expressions make common column transformations concise and readable.

16. Complete Practical Example

Samiksha wants to calculate final student scores, determine certificate eligibility, and create a report ordered by final score.

import pandas as pd
import numpy as np

students = pd.DataFrame({
    "Student": [
        "Samadhan",
        "Pradnya",
        "Samiksha",
        "Aarav",
        "Neha"
    ],
    "Assignment": [18, 20, 16, 14, 19],
    "Exam": [72, 85, 65, 58, 78],
    "Attendance": [90, 96, 82, 68, 88]
})

# Convert assignment marks to a percentage.
students["AssignmentPercent"] = (
    students["Assignment"] / 20 * 100
)

# Calculate the weighted final score.
students["FinalScore"] = (
    students["AssignmentPercent"] * 0.20
    + students["Exam"] * 0.80
)

# Determine certificate eligibility.
students["Certificate"] = np.where(
    (students["Exam"] >= 60)
    & (students["Attendance"] >= 75),
    "Eligible",
    "Not Eligible"
)

# Round scores for reporting.
students["FinalScore"] = (
    students["FinalScore"].round(2)
)

# Sort the report from highest to lowest score.
report = students.sort_values(
    by="FinalScore",
    ascending=False
).reset_index(drop=True)

print(report[[
    "Student",
    "FinalScore",
    "Attendance",
    "Certificate"
]])

Expected Output

    Student  FinalScore  Attendance   Certificate
0   Pradnya        88.0          96       Eligible
1      Neha        80.8          88       Eligible
2  Samadhan        73.2          90       Eligible
3  Samiksha        68.2          82       Eligible
4     Aarav        59.2          68   Not Eligible

This workflow combines arithmetic calculations, conditional classification, rounding, and sorting. The final DataFrame is ready to use as a student performance report.

17. Common Mistakes to Avoid

  • Confusing a Series with a DataFrame: Single-bracket and double-bracket selection return different objects.
  • Using columns with incompatible types: Convert text to numeric values before arithmetic.
  • Forgetting parentheses in Boolean conditions: Parenthesize each comparison when using & or |.
  • Applying the wrong scoring formula: Confirm the scale and weight of each component.
  • Ignoring division by zero: Validate denominators before calculating ratios.
  • Overwriting source data unintentionally: Use df.copy() when preserving the original DataFrame matters.
  • Using loops for simple column arithmetic: Prefer vectorized operations when possible.
  • Removing columns too early: Confirm that no later calculation depends on them.

18. Quick Revision

Expression Purpose
df["Exam"] Select one column as a Series.
df[["Student", "Exam"]] Select multiple columns as a DataFrame.
df["Total"] = df["A"] + df["B"] Create a calculated column.
df[["A", "B"]].mean(axis=1) Calculate a row-wise average across selected columns.
df.loc[condition, "Column"] = value Update selected rows based on a condition.
np.where(condition, a, b) Select one of two values for each element.
df["Name"].str.strip() Remove leading and trailing whitespace.
df["Score"].round(2) Round numeric values to two decimal places.
df.drop(columns=["A"]) Remove a column.
df.copy() Create a separate DataFrame copy for transformations.

19. Practice Questions and Exercises

Concept Questions

  1. What is the difference between selecting one column and selecting a list of columns?
  2. How can you create a new column from two existing numeric columns?
  3. What does axis=1 mean when calculating a row-wise average?
  4. How do you update values for rows that satisfy a condition?
  5. What is the purpose of np.where()?
  6. Why should each comparison be enclosed in parentheses when combining Boolean conditions?
  7. Why are vectorized operations generally preferred for straightforward column calculations?

Hands-On Exercises

  1. Create a DataFrame containing employee names, base salaries, and bonuses.
  2. Calculate total compensation for each employee.
  3. Calculate a bonus percentage relative to base salary.
  4. Classify employees into salary categories using conditions.
  5. Combine first-name and last-name columns into a full-name column.
  6. Normalize text values using string methods.
  7. Round calculated numeric values to two decimal places.
  8. Remove temporary columns from the final report.
  9. Sort employees by total compensation in descending order.
  10. Validate the final output by checking column names, data types, and sample records.
Learning goal: You should now be able to select columns, calculate new values, apply conditional rules, transform text, and produce a clean analytical DataFrame.

Summary

Column operations are central to Pandas data transformation. Use square brackets to select columns, arithmetic expressions to calculate values, and loc or np.where() to apply conditions. String methods help standardize text, while round() and drop() support presentation and cleanup. Prefer vectorized expressions for ordinary column calculations, preserve original data when needed, and validate formulas before using results in reports.