Data Handling using Pandas - II

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 3.

Which student is improving? Five lines can tell you

Twelve rows of marks sit in front of you: four students, three unit tests, five subjects. Your eyes cannot say who improved, which test was hardest or who is the most consistent. Pandas can answer each of those in one line. This chapter is the toolkit for that: summarise the data, sort it, group it, reshape it, clean it, and move it in and out of MySQL.

Every example below uses one DataFrame called df, built from the unit test marks (maximum 25 in each subject).

The resulting DataFrame has row labels 0 to 11. The marks for each row, in the order Maths, Science, S.St, Hindi, Eng, are:

Descriptive statistics: one function per question

Descriptive statistics squeeze many numbers into a few that describe them. Each function below is called on a whole DataFrame (one answer per column) or on a single column.

Rows or columns? The axis rule. By default (axis=0) a statistic runs down the rows and gives one answer per column. With axis=1 it runs across the columns and gives one answer per row.

Worked example: median of Maths in UT1. Write the statements to find the median Maths mark in Unit Test 1.

The common wrong step is to take the 2nd or 3rd value of the unsorted list. Median always works on sorted data.

Worked example: totals for one student. Find Raman's total marks in each subject, and in each unit test.

The first sum() runs down the three rows and gives one total per subject. The second, with axis=1, runs across the five subjects and gives one total per unit test.

Pause and predict: df['Hindi'].mode() is run on the full data. Three rows show 24, two rows show 25, and every other mark appears once or twice. What does it return?

Check your answer: A Series with index 0 and the value 24. mode() always returns a Series, because when two values tie for most frequent, both are returned. Here 24 appears three times, which beats every other mark, so there is a single row.

Worked example: variance by hand. Mishti's Maths marks are 15, 18 and 17. Find the variance.

Variance is the average of the squared distances from the mean. Pandas divides by $n-1$, not $n$:

$$s^2 = \dfrac{\sum (x_i - \bar{x})^2}{n-1}$$

The common wrong step is dividing by 3, which gives 1.556 and matches none of the options. The same figure, 2.333333, appears for Mishti in the group results later in this chapter.

Beyond the textbook. For the exam: df.max(), df.sum() and df.min() work on the whole DataFrame, including the Name column, and df.mean() is used on numeric columns. In reality: recent versions of Pandas raise a TypeError if you call mean(), median() or std() on a DataFrame that still contains a text column, so you write df.mean(numeric_only=True) or select the numeric columns first. Using numeric_only=True is safe in every version.

describe() produces the whole summary at once. For three subject columns:

The 25%, 50% and 75% rows are the first, second and third quartiles, which are the same values quantile(.25), quantile(.5) (the median) and quantile(.75) return. You can ask for several at once with df.quantile([.25, .75]).

Aggregation: many values in, one value out

Aggregation turns an array of values into a single number. The aggregate functions are max, min, sum, count, std and var. The method aggregate() (short form agg()) lets you name them as strings and ask for several at once.

With axis=1, aggregate('sum', axis=1) adds Maths and Science for every row. The first three rows give 43, 41 and 33.

Sorting a DataFrame

sort_values() rearranges the rows by the values in one or more columns. The syntax is DataFrame.sort_values(by, axis=0, ascending=True). The row labels travel with their rows, so after sorting the index is no longer 0, 1, 2 in order.

Worked example: sort with a tie-break. For Unit Test 3, sort the students by Science (low to high), and break ties using Hindi.

Zuhaire and Mishti both have 18 in Science. Python moves on to the second column in the list, Hindi, where 23 is smaller than 25, so Zuhaire comes first. For a descending sort add ascending=False, for example dfUT3.sort_values(by=['Eng'], ascending=False).

Pause and predict: In the output above, what is the row label of the last student, and why is it not 3?

Check your answer: It is 8 (Ashravy). Sorting moves whole rows, so each row keeps the label it had in df. Use reset_index(drop=True) if you want fresh labels.

groupby: split, apply, combine

groupby() is the Pandas version of SQL's GROUP BY. It works in three steps.

Useful members of a groupby object are first() (first row of each group), size() (rows per group), groups (a dictionary of group name and its row labels) and get_group(name) (all the rows of one group). Group by more than one column with a list: df.groupby(['Name','UT']).

Worked example: average Maths in each unit test. Which test was the hardest for Maths?

UT3 has the lowest average, 16.25. You can check it by hand: the UT3 marks are 14, 22, 12 and 17, which add to 65, and $65/4 = 16.25$.

To get several statistics per group, pass a list. This is the statement for the mean, variance, standard deviation and median of Maths for each student:

Ashravy's variance (44.33) is by far the largest because the Maths marks swing between 24 and 12, while Mishti's and Zuhaire's are steady.

Beyond the textbook. For the exam: learn the three steps, split, apply, combine, and the members first, size, groups and get_group. In reality: df.groupby('Name')['Maths'].mean() does the same job as the SQL query SELECT AVG(Maths) FROM marks GROUP BY Name. The idea is identical, only the syntax differs, so what you learn in Querying and SQL Functions carries over. Note the spelling: in Python it is groupby, one lowercase word.

Altering the index

By default the row labels are 0, 1, 2… When you filter a DataFrame, the labels of the surviving rows stay as they were, so they are no longer continuous.

The labels are 0, 3, 6 and 9. These functions fix or change the index:

Reversing it with reset_index() moves Name back into a normal column and restores 0, 1, 2, 3.

Reshaping data: pivot and pivot_table

The shape of data is how it is laid out in rows and columns. A long list of (store, year, sales) rows is hard to compare. Reshaping turns it into a grid with one row per store and one column per year.

index names the column that becomes the row labels, columns the column whose values become the new headings, and values the column that fills the grid. Combinations that never occurred, such as S2 in 2016, appear as NaN. Questions are now one-liners: pivot1.loc['S1'].sum() gives 62000.0 for store S1 over all years, and pivot1.loc['S3'].max() gives 450000.0.

Worked example: why does pivot raise an error? In a stock table every pen colour appears twice:

pivot_table() solves this by aggregating the duplicates. Its syntax is pivot_table(data, values=None, index=None, columns=None, aggfunc='mean'), and the default aggregate is the mean.

The blue pen was priced at 50 and 20, so the table shows the mean, 35.0. You can choose another aggregate, such as aggfunc='sum', or several at once with a list like aggfunc=['sum','max','mean'], or a different function per column with a dictionary like aggfunc={'Price(Rs)': len, 'Units_in_stock': 'mean'}. The function len counts the rows behind each cell.

Pause and predict: You call stock.pivot_table(index='Item', columns='Color', values='Units_in_stock'). What value appears for Pen and Blue?

Check your answer: 34.5, the mean of 55 and 14, because the default aggregate is the mean. Pencil and Blue is NaN, because no such row exists.

Handling missing values

A missing value is shown as NaN. It appears when data was not collected or the attribute does not apply. Missing values distort results, and there are two main strategies: drop the rows that have them, or fill (estimate) them.

Suppose a fourth test happened and Raman missed Maths, Science and English, and no retest is possible. The DataFrame below holds Raman's four rows, and only the UT4 row has NaN.

isnull() returns a True/False DataFrame, any() tells whether a column has at least one True, and sum() counts the Trues, since True counts as 1. The second line of the code adds the column counts and prints the total number of missing cells, 3.

Worked example: one student, three missing-value policies. Each teacher converts Raman's marks to a percentage out of the total marks of the tests held. The subjects have maximum 25 marks per test.

Same student, three different answers. Dropping reduces the number of rows, so the denominator falls from 100 to 75. Filling keeps four rows.

Common wrong step. If you do nothing and compute r['Maths'].sum() * 100 / (25 * 4), Pandas quietly skips the NaN while adding, and gives 57.0. That looks like a normal answer but it is the same as treating the missing test as zero. Missing data never raises an error, it just changes the result.

Beyond the textbook. For the exam: write fillna(method='pad') and fillna(method='bfill'). In reality: newer Pandas versions deprecate the method= argument, and the same results come from ffill() (forward fill) and bfill(). Whichever you write, the idea is unchanged: any filled value is an estimate, so the results are close approximations and not the actual figures.

Import and export between Pandas and MySQL

Real data lives in files and databases, not in dictionaries you type. Pandas can read a MySQL table into a DataFrame (import) and write a DataFrame into a MySQL table (export). Two libraries make the connection possible.

create_engine() takes a connection string. Its parts are the driver, the user name, the password, the host, the port and the database name:

Importing. Three functions read a table into a DataFrame, each taking the connection (engine) as its second argument:

If the INVENTORY table holds two CNG cars, E001 and E002, both of Car3, the output is:

Exporting. DataFrame.to_sql(table, sql_conn, if_exists='fail', index=True) writes a DataFrame into a table. The argument if_exists decides what happens when the table already exists.

This creates the table showroom_info in the database. With index=True (the default) the DataFrame's row labels are also written as an extra column, so pass index=False when you only want the data.

Pause and predict: You run df.to_sql('showroom_info', engine) twice, with the same engine. What happens on the second run?

Check your answer: It raises a ValueError. The default is if_exists='fail', and the table was created by the first run. Pass 'append' to add rows or 'replace' to overwrite.

Exam traps

60-second revision

Where this chapter connects