Data Handling Using Pandas - I

Written and reviewed by Rohit Singh, Computer Science & Commerce faculty at UniDrill (MCA, Osmania University · 6 years teaching). Based on NCERT Informatics Practices Class XII, Chapter 2.

A spreadsheet that lives inside Python

NumPy arrays are fast, but a NumPy array has no column names and holds one data type. A class result sheet has student names, subjects, marks and remarks, with text and numbers mixed and labels you want to use. Pandas gives you exactly that: labelled, mixed type, spreadsheet like data that you can load from a CSV file, filter, edit and save, all in a few lines. The name comes from "PANel DATa".

Pandas is built on NumPy, and it works hand in hand with Matplotlib for charts. You install it with pip install pandas (Python must already be installed) and import it with import pandas as pd. The alias pd is only a convention.

Pandas versus NumPy

Pandas has two main data structures in this book: the Series (one dimensional) and the DataFrame (two dimensional).

Series: a labelled column

A Series is a one dimensional sequence of values of any type, each with a label called its index. Think of a single spreadsheet column. If you give no index, the labels are the integers 0 to N minus 1.

If you pass an index along with an array, its length must equal the number of values. Four values with three labels raise a ValueError (length of values does not match the index).

The right hand column shows the values and the left hand column shows the index. Text values give dtype: object.

Reading a Series: position or label

There are two kinds of index. The positional index is the integer position starting at 0. The labelled index is whatever label you assigned. Both work for reading, and a list of them selects several elements.

This is the single most tested rule of the chapter: position slices exclude the end, label slices include it. A way to remember: positions are counted by the machine, which stops before the end; labels are names that you chose on purpose, so the named last one belongs to the answer.

Worked example: slicing and updating. A Series sa has values 10 to 15 and index a to f.

The wrong step is treating the label slice like a position slice and stopping at d.

Attributes and methods of a Series

Pause and predict: for s = pd.Series([12, np.nan, 10]), what are s.size and s.count()?

Check your answer: size is 3 because the missing value still occupies a slot. count() is 2 because it counts only real values. That gap between the two is the number of missing values.

Arithmetic on Series: matching by label

Operations between two Series pair elements by index label, not by position. This is index matching (alignment). Labels present in only one Series produce NaN (Not a Number), which marks a missing value, so the operation does not fail.

Worked example: addition and subtraction. Let seriesA = pd.Series([1,2,3,4,5], index=['a','b','c','d','e']) and seriesB = pd.Series([10,20,-10,-50,100], index=['z','y','a','c','e']).

Division shows why the fill value matters: seriesA.div(seriesB, fill_value=0) gives inf at labels b and d (2 divided by 0 and 4 divided by 0) and 0.0 at y and z (0 divided by 20 and by 10). The operators + - * / are equivalent to add sub mul div without a fill value. Note that fill_value is applied only where one side is missing, not where both are.

Beyond the textbook. For the exam: a Series can be read as cap[1] by position or cap['USA'] by label. In reality: newer versions of Pandas warn when an integer is used on a Series whose index is not made of integers, because it is ambiguous whether you mean the label or the position, and they are moving towards treating such integers as labels only. The clear forms are cap.iloc[1] for position and cap.loc['USA'] for label. Why it matters: when your index itself is made of integers, as in pd.Series([...], index=[3, 5, 1]), s[1] means the label 1, not the second element.

Series versus NumPy ndarray

DataFrame: a table with row and column labels

A DataFrame is a two dimensional labelled structure like a table in MySQL or a spreadsheet. It has a row index and a column index, and each column can hold a different data type. Each column of a DataFrame is a Series, and a DataFrame can be thought of as a dictionary of Series.

Worked example: dictionary of lists.

Passing columns=['State', 'VDF', 'GArea'] reorders the columns. For a list of dictionaries, [{'a': 10, 'b': 20}, {'a': 5, 'b': 10, 'c': 20}] gives 2 rows and 3 columns (a, b, c), with NaN in column c of the first row. The number of columns is the maximum number of keys in any dictionary.

Now take a dictionary of Series holding marks, with the subjects as the row index:

The students are the columns and the subjects are the rows, because the dictionary keys became the column labels.

Attributes of a DataFrame

For ResultDF the shape is (3, 5) and the size is 15. For the forest data in the next table, shape is (4, 3) and size is 12.

Rows and columns: add, change, delete, rename

Wrong lengths are errors: a new row with fewer values than columns raises ValueError (mismatched columns), and a new column with fewer values than rows raises ValueError (length does not match the index). Also, drop and rename return a new DataFrame, so you assign the result back (ResultDF = ResultDF.drop('Science', axis=0)), otherwise nothing seems to change. If several rows share the same label, drop removes all of them.

Worked example: update or add? ResultDF has rows Maths, Science and Hindi. What happens with ResultDF.loc['English'] = [85, 86, 83, 80, 90, 89] (after the Preeti column was added), and then the same line with different marks?

Accessing data with loc

DataFrame.loc[] selects by labels. A single row label returns that row as a Series. A single column label (after a colon) returns the column as a Series. A list of labels returns a DataFrame.

Boolean indexing works on the actual values. ResultDF.loc['Maths'] > 90 returns False for Arnab (90), True for Ramit (92), False for Samridhi (89), False for Riya (81) and True for Mallika (94). Notice the rule again: slicing in a DataFrame with loc includes the end label. If you ask for a label that does not exist, you get a KeyError.

Pause and predict: for the ResultDF above, what does ResultDF.loc['Maths':'Hindi', 'Riya'] return?

Check your answer: The Riya column for all three rows from Maths to Hindi (the end label is included), as a Series: 81, 71 and 67. Because the column is a single label, you get a Series and not a DataFrame.

Pause and predict: for pd.DataFrame([10, 20, 30, 40, 50]), what does .loc[2] return, the third row or the row labelled 2?

Check your answer: The row labelled 2, which happens to be the third row here: the value 30. In loc, an integer is always a label, never a position.

Joining DataFrames with append

The chapter uses append() to attach the rows of one DataFrame below another. Columns that are missing in the first one are added, and gaps become NaN.

With dFrame1 having columns C1, C2, C3 and dFrame2 having C2, C5, the result has four columns C1, C2, C3, C5 and all the rows of both, with NaN wherever a table had no value. Two parameters appear in questions:

Beyond the textbook. For the exam: DataFrame.append() merges two DataFrames. In reality: append() was removed in Pandas 2.0, and current code uses pd.concat([dFrame1, dFrame2]), with the same ignore_index option. The result is identical. Why it matters: if code copied from a book fails with "DataFrame has no attribute append", this is the reason.

Importing and exporting CSV files

A CSV (comma separated values) file is a plain text file where each line is a record and values are separated by commas. Spreadsheet programs open it directly.

Worked example: import then export. The file ResultData.csv has the header RollNo,Name,Eco,Maths and three lines of data.

The wrong step is forgetting index=False: the output then begins every line with the row label 0, 1, 2.

Beyond the textbook. For the exam: read_csv takes the file path, sep and header. In reality: when the first line is not a header, pass header=None and supply names, otherwise the first data line is eaten as column titles. Why it matters: a CSV with an unexpected header shows up as wrong column names and one fewer row than you expected.

Exam traps

60-second revision

Where this chapter connects

A Series is a labelled version of the array from Introduction to NumPy, and the idea of slicing, shape and element wise operations carries over directly. The next chapter, Data Handling using Pandas - II, adds descriptive statistics, sorting, grouping and more operations on DataFrames, and Plotting Data using Matplotlib turns these tables into charts. A DataFrame is also the Python version of the SQL tables you queried in Querying and SQL Functions.