CPDS in Python Lab - Pandas

Pandas is a Python package used for data manipulation and analysis.

It is especially useful when data are arranged in rows and columns with meaningful labels.

Pandas is commonly used for:

The standard import is:

import pandas as pd

The two most important Pandas data structures are:

  1. Series — one-dimensional labelled data
  2. DataFrame — two-dimensional tabular data

1. Importing Pandas

The standard convention is:

import pandas as pd

Here, pd is an alias for Pandas.

Question 1

Import Pandas using the alias pd.

import pandas as pd

2. What is a Series?

A Series is a one-dimensional labelled array.

Each value has an associated index.

For example,

[ [10,20,30,40] ]

stored as a Series automatically receives indices

[ 0,1,2,3. ]

A Series is created using:

pd.Series(...)

Question 2

Create a Series containing

[ 10,20,30,40. ]

import numpy as np
np.array([10,20,30,40])
array([10, 20, 30, 40])
pd.Series([10,20,30,40])
0
0 10
1 20
2 30
3 40

Pandas displays both:

  • the index on the left,
  • the value on the right.

3. Series Values and Index

Question 3

Print:

  1. the values,
  2. the index,
  3. the data type,
  4. the number of elements.
series = pd.Series([10,20,30,40])
series.values
array([10, 20, 30, 40])
series
0
0 10
1 20
2 30
3 40 

series.index
RangeIndex(start=0, stop=4, step=1)
series.dtype
dtype('int64')
series.size
4
series.shape
(4,)
s.values
s.index
s.dtype
s.size

4. Statistical Functions on a Series

A Series supports many useful functions such as:

mean()
sum()
max()
min()
std()
count()

Question 4

For the Series

[ [10,20,30,40], ]

find the mean, sum, maximum and minimum.

series = pd.Series([10,20,30,40])
series.sum()
np.int64(100)
series.mean()
np.float64(25.0)
series.max()
40
series.min()
10

5. Series with Custom Labels

Instead of automatic integer indices, we can assign meaningful labels.

Question 5

Create a Series representing battery levels:

Drone Battery
Drone A 92
Drone B 89
Drone C 85
Drone D 91

Use the drone names as indices.

series.index
RangeIndex(start=0, stop=4, step=1)
series
0
0 10
1 20
2 30
3 40 

series[0:4]
0
0 10
1 20
2 30
3 40 

sereis1 = pd.Series([92,89,85,91], index=["Drone A", "Drone B", "Drone C", "Drone D"])
sereis1
0
Drone A 92
Drone B 89
Drone C 85
Drone D 91 

sereis1["Drone A"]
np.int64(92)

Question 6

Print the battery level of "Drone C" using its label.

6. Vectorized Operations on a Series

Question 7

Suppose every measurement must be increased by 5. Add 5 to the complete Series without using a loop.

sereis = pd.Series([10,20,30,40])
sereis
0
0 10
1 20
2 30
3 40 

sereis+10
0
0 20
1 30
2 40
3 50 

sereis**2
0
0 100
1 400
2 900
3 1600

7. What is a DataFrame?

A DataFrame is a two-dimensional labelled data structure with rows and columns.

It is similar to a spreadsheet, an Excel table, or a database table.

Different columns may contain different data types.

A common way to create a DataFrame is from a dictionary.

Question 8

Create the following DataFrame:

Name Age Marks
A 20 75
B 21 85
C 19 90 
data = {
    'Name': ['A', 'B', 'C','D','A', 'B', 'C','D'],
    'Age': [20, 21, 19, 22, 20, 21, 19, 22],
    'Marks': [75, 85, 90, 40, 75, 85, 90, 40]
}
df = pd.DataFrame(data)

print(df)
  Name  Age  Marks
0    A   20     75
1    B   21     85
2    C   19     90
3    D   22     40
4    A   20     75
5    B   21     85
6    C   19     90
7    D   22     40
df['Age']
Age
0 20
1 21
2 19
3 22
4 20
5 21
6 19
7 22 

df[df['Age']>20]
Name Age Marks
1 B 21 85
3 D 22 40
5 B 21 85
7 D 22 40 
df.loc[(df['Age']>20) & (df['Marks']>80), "Name"]
Name
1 B
5 B 

df["Name"] == "B"
Name
0 False
1 True
2 False
3 False
4 False
5 True
6 False
7 False 

df.iloc[df["Name"] == "B"]
---------------------------------------------------------------------------
NotImplementedError                       Traceback (most recent call last)
/tmp/ipykernel_2859/4266178184.py in <cell line: 0>()
----> 1 df.iloc[df["Name"] == "B"]

/usr/local/lib/python3.13/dist-packages/pandas/core/indexing.py in __getitem__(self, key)
   1189             maybe_callable = com.apply_if_callable(key, self.obj)
   1190             maybe_callable = self._check_deprecated_callable_usage(key, maybe_callable)
-> 1191             return self._getitem_axis(maybe_callable, axis=axis)
   1192 
   1193     def _is_scalar_access(self, key: tuple):

/usr/local/lib/python3.13/dist-packages/pandas/core/indexing.py in _getitem_axis(self, key, axis)
   1736 
   1737         if com.is_bool_indexer(key):
-> 1738             self._validate_key(key, axis)
   1739             return self._getbool_axis(key, axis=axis)
   1740 

/usr/local/lib/python3.13/dist-packages/pandas/core/indexing.py in _validate_key(self, key, axis)
   1576             if hasattr(key, "index") and isinstance(key.index, Index):
   1577                 if key.index.inferred_type == "integer":
-> 1578                     raise NotImplementedError(
   1579                         "iLocation based boolean "
   1580                         "indexing on an integer type "

NotImplementedError: iLocation based boolean indexing on an integer type is not available
df.iloc[(df["Name"] == "B").tolist()]
df.loc[(df["Name"] == "B") | (df["Name"] == "C"), "Marks"]

8. DataFrame Properties

Useful DataFrame attributes include:

df.shape
df.columns
df.index
df.dtypes
df.size

Question 9

For the previous DataFrame, print:

  1. shape,
  2. column names,
  3. index,
  4. data types,
  5. total number of elements.
df.shape
df.columns.tolist()
df.index.tolist( )
df.size
df.dtypes

9. head() and tail()

Large datasets may contain thousands of rows.

Instead of printing everything, use:

df.head()
df.tail()

By default, these display the first or last five rows.

A number may also be supplied:

df.head(2)

Question 10

Display only the first two rows.

df
df.head()
df.head(2)
df.tail(2)
df.tail()

Assignment 52 - DataFrame Basics

Create a DataFrame containing:

Name Age Marks
A 20 75
B 21 85
C 19 90
  1. Create the DataFrame.
  2. Print the DataFrame.
  3. Print its shape.
  4. Print its column names.
  5. Display the first two rows.
dictionary = {
    "Name": ['A','B','C','D'],
    "Age": [20,21,19, '-'],
    "Marks": [75,85,90, '-']
}
df = pd.DataFrame(dictionary)
print(df)
df.dtypes

10. Selecting a Single Column

Question 11

Select the Marks column.

df["Marks"]
df["Marks"].dtype('int')
df[df["Marks"]>80]

A column is selected using its name:

df["column_name"]

11. Selecting Multiple Columns

Question 12

Select Name and Marks together.

df[["Name", "Marks"]]
df["Name"]

To select more than one column, use a list of column names:

df[["Column1", "Column2"]]

12. Selecting Rows Using iloc

Question 13

Select the first row.

df.iloc[0]
df["Name"]
df.iloc[(df["Name"]=="A").tolist()]
df
series[0]
df[0]
df.iloc[0]

iloc selects rows and columns using their numerical positions.

df.iloc[row_index]

Question 14

Select the first three rows.

df.iloc[0:3]

13. Selecting a Particular Value

We can use row and column labels to retrieve one value.

One convenient method is:

df.loc[row_label, "column_name"]

Question 15

Select the value in the second row and the Marks column.

df.loc[0,"Marks"]
df
df.loc[0,"Marks"]

Assignment 54 - Selecting Rows and Columns

  1. Select the Marks column.
  2. Select Name and Marks together.
  3. Select the first row using iloc.
  4. Select the first three rows.
  5. Select the value in row 2 and column Marks.
df = pd.DataFrame({
    "Name": ['A','B','C','D'],
    "Age": [20,21,19, '-'],
    "Marks": [75,85,90, '-']
})
df
df["Gender"] = np.array(["M", "F", "M", "F"])
df
df["Marks"]
df[["Name", "Marks"]]
df.iloc[0]
df.loc[0,"Marks"]
df.dtypes
df.drop(3)
df = df.drop(3)
df["Marks"].to_numpy(dtype = np.float32)
df["Marks"] = df["Marks"].to_numpy(dtype = np.float32)
df.dtypes

14. Adding a New Column

A new column can be created directly.

Suppose we want to record whether each student passed.

Question 16

Create a new column called Passed that contains True when Marks are at least 50.

df["Passed"] = (df["Marks"]>=80).to_numpy()
df
df["Passed"] = (df["Marks"]>=80).map({True: "Pass", False: "Fail"})
df

15. Deleting a Column

Question 17

Remove the Passed column.

df.drop(columns = "Passed")
df.drop("Passed", axis = 1)
df

A column may be removed using:

df.drop("column_name", axis=1)

16. Reading CSV Files

A CSV file stores tabular data in rows and columns separated by commas.

Question 18

Suppose a file named students.csv exists. Write the command required to read it into a DataFrame called df.

Pandas reads a CSV file using:

pd.read_csv("filename.csv")
excel = pd.read_csv("test.csv")
excel
type(excel)
df = pd.DataFrame( excel)
df
type(df)

17. Creating a Small CSV for Practice

We can create a small dataset and save it as a CSV file before reading it.

Question 19

Create a CSV file named students.csv and read it back using Pandas.

18. Information About a Dataset

Question 20

Display information about the current dataset.

df.info()

The function

df.info()

provides information such as number of rows, number of columns, column names, data types, missing values.

19. Statistical Summary

Question 21

Display summary statistics.

df.describe()

The function

df.describe()

provides descriptive statistics for numerical columns.

It includes count, mean, standard deviation, minimum, quartiles, maximum.

20. Filtering Data

A DataFrame can be filtered using conditions.

Question 22

Display students whose Marks are greater than 80.

df[df["Marks"]>80]

Question 23

Display students whose Age is greater than or equal to 20.

df[df["Age"]>=17]

Question 24

Display students whose Marks are between 70 and 90.

df[(df["Marks"]>70) & (df["Marks"]<90)]

Question 25

Display students satisfying both:

[ >20 ]

and

[ >80. ]

For multiple conditions, use & for AND.

df[(df["Age"]>17) & (df["Marks"]>80)]

Question 26

Display only Name and Marks for students scoring more than 80.

df
df["Marks"]>80
(df["Marks"]>80).to_numpy()
df.loc[(df["Marks"]>80).to_numpy(), "Name"]

21. Missing Data

Real datasets often contain missing observations.

Pandas represents many missing numerical values using NaN.

Question 27

Create a dataset containing some missing Age and Marks values.

np.nan
dataset = pd.DataFrame({
    "Name": ['A','B','C','D', 'E', 'F'],
    "Age": [20,21,19, np.nan, 21, 20],
    "Marks": [75,85,90, np.nan, np.nan, np.nan]
})
dataset
dataset.isnull()
dataset.isnull().sum()
dataset.fillna(0)
dataset
dataset["Marks"].fillna(0)
(dataset["Marks"].fillna(0)).to_numpy()
dataset["Marks"] = (dataset["Marks"].fillna(0)).to_numpy()
dataset
dataset.dropna()
df.isnull()
df.isnull().sum()
df.dropna()
df.fillna(...)

Question 28

Check where missing values occur.

Question 29

Count the missing values in each column.

Question 30

Remove all rows containing at least one missing value.

Question 31

Replace missing Age values with the mean Age.

(dataset.dropna()["Age"]).to_numpy().mean()
dataset["Age"].fillna((dataset.dropna()["Age"]).to_numpy().mean())
dataset
dataset["Age"] = (
    dataset["Age"].fillna((dataset.dropna()["Age"]).to_numpy().mean())
).to_numpy()
dataset

Question 32

Replace missing Marks values with 0.

22. Sorting Data

Question 33

Sort students according to Marks in ascending order.

dataset.sort_values("Age", ascending = False)
df.sort_values(by="column")

Descending order is obtained using:

ascending=False

Question 34

Sort Marks in descending order.

23. Basic Statistics on a DataFrame Column

Question 35

Find:

  1. mean Marks,
  2. minimum Marks,
  3. maximum Marks.
(dataset["Marks"]).to_numpy()
(dataset["Marks"]).to_numpy().mean()
dataset["Marks"].mean()

24. GroupBy

groupby() divides data into groups according to the values in a column. useful when we want statistics separately for different categories.

Question 36

Create a dataset containing student Department information.

dataset = pd.DataFrame({
    "Name": ['A','B','C','D', 'E', 'F'],
    "Age": [20,21,19, np.nan, 21, 20],
    "Marks": [75,85,90, np.nan, np.nan, np.nan],
    "Course": ["AM", "AM", "I", "I", "AM", "I"]
})
dataset
Name Age Marks Course
0 A 20.0 75.0 AM
1 B 21.0 85.0 AM
2 C 19.0 90.0 I
3 D NaN NaN I
4 E 21.0 NaN AM
5 F 20.0 NaN I 
dataset.groupby("Course")["Marks"].mean()
Marks
Course
AM 80.0
I 90.0

Question 37

Find the mean Marks for each Department.

Question 38

Find the maximum Marks for each Department.

Question 39

Count the number of students in each Department.

dataset.groupby("Course")["Name"].count()
Name
Course
AM 3
I 3

Question 40

Find the mean Age and mean Marks for each Department.

dataset.groupby("Course")[["Marks", "Age"]].mean()
Marks Age
Course
AM 80.0 20.666667
I 90.0 19.500000

25. Combining Data - merge()

merge() combines DataFrames using a common key.

Question 41

Create:

  • one DataFrame containing Student_ID and Name,
  • another containing Student_ID and Marks.

Then merge them using Student_ID.

df_1 = pd.DataFrame({
    "Student_ID": [1,2,3,4,5],
    "Name": ["A", "B", "C", "D", "E"],
})

df_2 = pd.DataFrame({
    "Student_ID": [1,2,3,4,5],
    "Marks": [100,80,40,70,80]
})

pd.merge(df_1,df_2)
Student_ID Name Marks
0 1 A 100
1 2 B 80
2 3 C 40
3 4 D 70
4 5 E 80

26. Combining Data with concat()

concat() joins DataFrames along an axis.

Question 42

Concatenate two DataFrames vertically.

df_1 = pd.DataFrame({
    "Name": ["A", "B", "C", "D", "E"],
    "Marks": [100,80,40,70,80]
})

df_2 = pd.DataFrame({
    "Name": ["F", "G", "H", "I", "J"],
    "Marks": [100,80,40,70,80],
    "Age": [20,21,19, np.nan, 21]
})

pd.concat([df_1, df_2], ignore_index=True)
Name Marks Age
0 A 100 NaN
1 B 80 NaN
2 C 40 NaN
3 D 70 NaN
4 E 80 NaN
5 F 100 20.0
6 G 80 21.0
7 H 40 19.0
8 I 70 NaN
9 J 80 21.0

For vertical concatenation:

pd.concat([df1, df2], ignore_index=True)
  • merge() combines tables using a common key, similar to joining database tables.
  • concat() stacks DataFrames vertically or horizontally.

27. Machine Learning

Machine learning datasets are often stored as DataFrames.

Suppose the columns are:

Age, Income, Experience, Purchased

The first three columns are input features.

The column Purchased is the target we want to predict.

The usual notation is:

\[ X = \text{input features}, \qquad y = \text{target}. \]

Question 44

Create a small example dataset.

dataset = pd.DataFrame({
    "Age":[19,20,21,22],
    "Income":[150,200,300,150],
    "Experience":[1,2,3,4],
    "Purchased":["Yes", "No", "Yes", "No"]
})

Question 45

Check the dataset for missing values.

dataset.isnull().sum()
0
Age 0
Income 0
Experience 0
Purchased 0

Question 46

Separate the input features from the target.

The input features are:

Age, Income, Experience

and the target is:

Purchased
dataset
Age Income Experience Purchased
0 19 150 1 Yes
1 20 200 2 No
2 21 300 3 Yes
3 22 150 4 No 
input_features = dataset[["Age","Income","Experience"]]
target = dataset[["Purchased"]]
input_features, target
(   Age  Income  Experience
 0   19     150           1
 1   20     200           2
 2   21     300           3
 3   22     150           4,
   Purchased
 0       Yes
 1        No
 2       Yes
 3        No)

28. Converting Pandas Data to NumPy

Question 47

Convert X and y into NumPy arrays.

X = input_features.to_numpy()
y = target.to_numpy()
X, y
(array([[ 19, 150,   1],
        [ 20, 200,   2],
        [ 21, 300,   3],
        [ 22, 150,   4]]),
 array([['Yes'],
        ['No'],
        ['Yes'],
        ['No']], dtype=object))

A DataFrame or Series can be converted to a NumPy array using:

.to_numpy()

Same way to list by

.to_list()

Question 48

Print the shapes of X and y.

For (n) observations and (p) features:

\[ X \in \mathbb{R}^{n\times p}, \qquad y \in \mathbb{R}^{n}. \]

X.shape
(4, 3)

Student Dataset Analysis

Consider the following data:

Name Department Age Marks
A Math 20 75
B Math 21 85
C Physics 19 90
D Physics 22 78
E CS 20 88
F CS 21 92

Question 49

Using Pandas:

  1. Create the DataFrame.
  2. Display its shape.
  3. Display only Name and Marks.
  4. Display students scoring more than 80.
  5. Find the mean Marks.
  6. Sort students by Marks in descending order.
  7. Find mean Marks department-wise.
  8. Convert Age and Marks into a NumPy array.
  9. Pass and create a csv file.
dataset = pd.DataFrame({
    "Name": ['A','B','C','D', 'E', 'F'],
    "Department": ['Math', 'Math', 'Physics', 'Physics', 'CS', 'CS'],
    "Age": [20,21,19,22,20,21],
    "Marks": [75,85,90,78,88,92]
})

dataset
Name Department Age Marks
0 A Math 20 75
1 B Math 21 85
2 C Physics 19 90
3 D Physics 22 78
4 E CS 20 88
5 F CS 21 92 
dataset.shape
(6, 4)
dataset[["Name", "Marks"]]
Name Marks
0 A 75
1 B 85
2 C 90
3 D 78
4 E 88
5 F 92 
dataset[dataset["Marks"]>=80]
Name Department Age Marks
1 B Math 21 85
2 C Physics 19 90
4 E CS 20 88
5 F CS 21 92 
dataset.loc[(dataset["Marks"]>=80),"Name"]
Name
1 B
2 C
4 E
5 F 

dataset["Marks"].mean()
np.float64(84.66666666666667)
dataset[["Marks","Age"]].mean()
0
Marks 84.666667
Age 20.500000 

dataset.sort_values("Marks", ascending=False)
Name Department Age Marks
5 F CS 21 92
2 C Physics 19 90
4 E CS 20 88
1 B Math 21 85
3 D Physics 22 78
0 A Math 20 75 
dataset.groupby("Department")["Marks"].mean()
Marks
Department
CS 90.0
Math 80.0
Physics 84.0 

dataset.to_csv("myfile.csv")

Import

import pandas as pd

Series

pd.Series(...)
s.values
s.index
s.mean()
s.max()
s.min()

DataFrame

pd.DataFrame(...)
df.shape
df.columns
df.dtypes

Display data

df.head()
df.tail()
df.info()
df.describe()

Select columns and rows

df["Marks"]
df[["Name", "Marks"]]
df.iloc[0]
df.loc[1, "Marks"]

Filtering

df[df["Marks"] > 80]

Missing values

df.isnull()
df.isnull().sum()
df.dropna()
df.fillna(...)

Sorting

df.sort_values(by="Marks")

Grouping

df.groupby("Department")["Marks"].mean()

Combining

pd.merge(...)
pd.concat(...)

CSV files

pd.read_csv(...)
df.to_csv(...)

Machine learning

X = df[["feature1", "feature2"]]
y = df["target"]

X = X.to_numpy()
y = y.to_numpy()