Class 12 Data Handling using Pandas – II

Analysing data with pandas: descriptive statistics, sorting, groupby, missing values with count, isnull, dropna and fillna, and moving data between pandas and MySQL.

Key ideas

sum(), max(), min() and count() of whole numbers stay whole; mean() and median() always give a float (78.0).

median() is the middle sorted value (the mean of the middle two if the count is even); mode() the commonest; std() the spread.

sort_values(by='Marks') puts the rows lowest first (ascending=False: highest first); each row keeps its label.

df.groupby('City')['Marks'].sum() gives one value per group, with the group labels in sorted order.

A missing value (shown by pandas as Not a Number) makes its column a float column, so its sum prints like 225.0.

count(), sum() and mean() skip missing values; isnull().sum() counts them; dropna() drops each row with one; fillna(0) fills in 0.

pd.read_sql() reads an SQL query’s result into a DataFrame; df.to_sql() writes a DataFrame into a database table.

Remember

mean()

total ÷ number of values present: (74 + 25 + 21 + 60 + 45) ÷ 5 = 45.0

dropna().shape

(rows with no missing value, the same number of columns)

fillna(0).sum()

the same total as sum(), printed as a float

groupby

df.groupby('Store')['Price'].max(); adding ['North'] picks one group

Range of a column

df['Marks'].max() - df['Marks'].min()

describe()

count, mean, std, min, quartiles and max of each numeric column

Watch out

Writing a mean as a whole number: 210 ÷ 5 prints as 42.0.

Dividing by every row when some values are missing: mean() divides by the values present.

Expecting groupby to keep the order of the rows: the group labels come out sorted.

Thinking dropna() removes only the missing cell: it removes the whole row.

Worked example

DataFrame df:
index | Item | Store | Price | Stock
P1 | Eraser | South | — | 90
P2 | Diary | North | 74 | 50
P3 | Pen | East | 25 | 27
P4 | Notebook | East | 21 | 56
P5 | Folder | East | 60 | 72
P6 | Crayons | North | 45 | 75
(— marks a missing value; pandas prints it as Not a Number.)
Write the output of:
(a) print(df['Price'].count())
(b) print(df['Price'].sum())
(c) print(df.dropna().shape)

Working: (a) and (b) skip missing values, and the column is a float column; (c) drops the rows with a missing value

Answer: (a) 5; (b) 225.0; (c) (5, 4)