Pandas drop_level(), pivot(), pivot_table(), reorder_levels(), sort_values(), sort_index()

The Pandas DataFrame/Series has several methods to handle Missing Data. When applied to a DataFrame/Series, these methods evaluate and modify the missing elements.

Preparation

Before any data manipulation can occur, two (2) new libraries will require installation.

• The Pandas library enables access to/from a DataFrame.
• The NumPy library supports multi-dimensional arrays and matrices in addition to a collection of mathematical functions.

To install these libraries, navigate to an IDE terminal. At the command prompt ($), execute the code below. For the terminal used in this example, the command prompt is a dollar sign ($). Your terminal prompt may be different.

$ pip install pandas

Hit the <Enter> key on the keyboard to start the installation process.

$ pip install numpy

Hit the <Enter> key on the keyboard to start the installation process.

If the installations were successful, a message displays in the terminal indicating the same.

Feel free to view the PyCharm installation guide for the required libraries.

• How to Install Pandas on PyCharm
• How to Install NumPy on PyCharm

Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

import pandas as pd
import numpy as np 

DataFrame drop_level()

The drop_level() method removes the specified index or column from a DataFrame/Series. This method returns a DataFrame/Series with the said level/column removed.

The syntax for this method is as follows:

DataFrame.droplevel(level, axis=0)

For this example, we generate random stock prices and then drop (remove) level Stock-B from the DataFrame.

nums = np.random.uniform(low=0.5, high=13.3, size=(3,4))
df_stocks = pd.DataFrame(nums).set_index([0, 1]).rename_axis(['Stock-A', 'Stock-B'])
print(df_stocks)

result = df_stocks.droplevel('Stock-B')
print(result)

• Line [1] generates random numbers for three (3) lists within the specified range. Each list contains four (4) elements (size=3,4). The output saves to nums.
• Line [2] creates a DataFrame, sets the index, and renames the axis. This output saves to df_stocks.
• Line [3] outputs the DataFrame to the terminal.
• Line [4] drops (removes) Stock-B from the DataFrame and saves it to the result variable.
• Line [5] outputs the result to the terminal.

Output

df_stocks

result

DataFrame pivot()

The pivot() method reshapes a DataFrame/Series and produces/returns a pivot table based on column values.

The syntax for this method is as follows:

DataFrame.pivot(index=None, columns=None, values=None)

For this example, we generate 3-day sample stock prices for Rivers Clothing. The column headings display the following characters.

• A (for Opening Price)
• B (for Midday Price)
• C (for Opening Price)

cdate_idx = ['01/15/2022', '01/16/2022', '01/17/2022'] * 3
group_lst = list('AAABBBCCC')
vals_lst  = np.random.uniform(low=0.5, high=13.3, size=(9))

df = pd.DataFrame({'dates':  cdate_idx,
                                    'group':  group_lst,
                                   'value':  vals_lst})
print(df)

result = df.pivot(index='dates', columns='group', values='value')
print(result)

• Line [1] creates a list of dates and multiplies this by three (3). The output is three (3) entries for each date. This output saves to cdate_idx.
• Line [2] creates a list of headings for the columns (see above for definitions). Three (3) of each character are required (9 characters). This output saves to group_lst.
• Line [3] uses np.random.uniform to create a random list of nine (9) numbers between the set range. The output saves to vals_lst.
• Line [4] creates a DataFrame using all the variables created on lines [1-3]. The output saves to df.
• Line [5] outputs the DataFrame to the terminal.
• Line [6] creates a pivot from the DataFrame and groups the data by dates. The output saves to result.
• Line [7] outputs the result to the terminal.

Output

df

result

DataFrame pivot_table()

The pivot_table() method streamlines a DataFrame to contain only specific data (columns). For example, say we have a list of countries with associated details. We only want to display one or two columns. This method can accomplish this task.

The syntax for this method is as follows:

DataFrame.pivot_table(values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All', observed=False, sort=True)

For this example, a comma-delimited CSV file is read in. Then, a pivot table is created based on selected parameters.

Code – Example 1

df = pd.read_csv('countries.csv')
df = df.head(5)
print(df)

result = pd.pivot_table(df, values='Population', columns='Capital')
print(result)

• Line [1] reads in a CSV file and saves to a DataFrame (df).
• Line [2] saves the first five (5) rows of the CSV file to df (over-writing df).
• Line [3] outputs the DataFrame to the terminal.
• Line [4] creates a pivot table from the DataFrame based on the Population and Capital columns. The output saves to result.
• Line [5] outputs the result to the terminal.

Output

df

result

For this example, a comma-delimited CSV file is read in. A pivot table is created based on selected parameters. Notice the max function.

Code – Example 2

df = pd.read_csv('countries.csv')
df = df.head(5)

result = pd.pivot_table(df, values='Population', columns='Capital', aggfunc=[max])
print(result)

• Line [1] reads in a comma-separated CSV file and saves to a DataFrame (df).
• Line [2] saves the first five (5) rows of the CSV file to df (over-writing df).
• Line [3] creates a pivot table from the DataFrame based on the Population and Capital columns. The max population is a parameter of aggfunc. The output saves to result.
• Line [4] outputs the result to the terminal.

Output

result

DataFrame reorder_levels()

The reorder_levels() method re-arranges the index of a DataFrame/Series. This method can not contain any duplicate level(s) or drop level(s).

The syntax for this method is as follows:

DataFrame.reorder_levels(order, axis=0)

For this example, there are five (5) students. Each student has some associated data with it. Grades generate by using np.random.randint().

index = [(1001, 'Micah Smith', 14), (1001, 'Philip Jones', 15), 
         	(1002, 'Ben Grimes', 16), (1002, 'Alicia Heath', 17), (1002, 'Arch Nelson', 18)]
m_index = pd.MultiIndex.from_tuples(index)
grades_lst = np.random.randint(45,100,size=5)
df = pd.DataFrame({"Grades": grades_lst}, index=m_index)
print(df)

result = df.reorder_levels([1,2,0])
print(result)

• Line [1] creates a List of tuples. Each tuple contains three (3) values. The output saves to index.
• Line [2] creates a MultiIndex from the List of Tuples created on line [1] and saves to m_index.
• Line [3] generates five (5) random grades between the specified range and saves to grades_lst.
• Line [4] creates a DataFrame from the variables on lines [1-3] and saves to df.
• Line [5] outputs the DataFrame to the terminal.
• Line [6] re-orders the levels as specified. The output saves to result.
• Line [7] outputs the result to the terminal.

Output

df

result

DataFrame sort_values()

The sort_values() method sorts (re-arranges) the elements of a DataFrame.

The syntax for this method is as follows:

DataFrame.sort_values(by, axis=0, ascending=True, inplace=False, kind='quicksort', na_position='last', ignore_index=False, key=None)

For this example, a comma-delimited CSV file is read in. This DataFrame sorts on the Capital column in descending order.

df = pd.read_csv('countries.csv')
result = df.sort_values(by=['Capital'], ascending=False)
print(result)

• Line [1] reads in a comma-delimited CSV file and saves to df.
• Line [2] sorts the DataFrame on the Capital column in descending order. The output saves to result.
• Line [3] outputs the result to the terminal.

Output

DataFrame sort_index()

The sort_index() method sorts the DataFrame.

The syntax for this method is as follows:

DataFrame.sort_index(axis=0, level=None, ascending=True, inplace=False, kind='quicksort', na_position='last', sort_remaining=True, ignore_index=False, key=None)

For this example, a comma-delimited CSV file is read into a DataFrame. This DataFrame sorts on the index Country column.

df = pd.read_csv('countries.csv')
df = df.set_index('Country')
result = df.sort_index()
print(result)

• Line [1] reads in a comma-delimited CSV file and saves to df.
• Line [2] sets the index of the DataFrame to Country. The output saves to df (over-writing original df).
• Line [3] sorts the DataFrame (df) on the indexed column (Country) in ascending order (default). The output saves to result.
• Line [4] outputs the result to the terminal.

Output

Further Learning Resources

This is Part 13 of the DataFrame method series.

• Part 1 focuses on the DataFrame methods abs(), all(), any(), clip(), corr(), and corrwith().
• Part 2 focuses on the DataFrame methods count(), cov(), cummax(), cummin(), cumprod(), cumsum().
• Part 3 focuses on the DataFrame methods describe(), diff(), eval(), kurtosis().
• Part 4 focuses on the DataFrame methods mad(), min(), max(), mean(), median(), and mode().
• Part 5 focuses on the DataFrame methods pct_change(), quantile(), rank(), round(), prod(), and product().
• Part 6 focuses on the DataFrame methods add_prefix(), add_suffix(), and align().
• Part 7 focuses on the DataFrame methods at_time(), between_time(), drop(), drop_duplicates() and duplicated().
• Part 8 focuses on the DataFrame methods equals(), filter(), first(), last(), head(), and tail()
• Part 9 focuses on the DataFrame methods equals(), filter(), first(), last(), head(), and tail()
• Part 10 focuses on the DataFrame methods reset_index(), sample(), set_axis(), set_index(), take(), and truncate()
• Part 11 focuses on the DataFrame methods backfill(), bfill(), fillna(), dropna(), and interpolate()
• Part 12 focuses on the DataFrame methods isna(), isnull(), notna(), notnull(), pad() and replace()
• Part 13 focuses on the DataFrame methods drop_level(), pivot(), pivot_table(), reorder_levels(), sort_values() and sort_index()
• Part 14 focuses on the DataFrame methods nlargest(), nsmallest(), swap_level(), stack(), unstack() and swap_axes()
• Part 15 focuses on the DataFrame methods melt(), explode(), squeeze(), to_xarray(), t() and transpose()
• Part 16 focuses on the DataFrame methods append(), assign(), compare(), join(), merge() and update()
• Part 17 focuses on the DataFrame methods asfreq(), asof(), shift(), slice_shift(), tshift(), first_valid_index(), and last_valid_index()
• Part 18 focuses on the DataFrame methods resample(), to_period(), to_timestamp(), tz_localize(), and tz_convert()
• Part 19 focuses on the visualization aspect of DataFrames and Series via plotting, such as plot(), and plot.area().
• Part 20 focuses on continuing the visualization aspect of DataFrames and Series via plotting such as hexbin, hist, pie, and scatter plots.
• Part 21 focuses on the serialization and conversion methods from_dict(), to_dict(), from_records(), to_records(), to_json(), and to_pickles().
• Part 22 focuses on the serialization and conversion methods to_clipboard(), to_html(), to_sql(), to_csv(), and to_excel().
• Part 23 focuses on the serialization and conversion methods to_markdown(), to_stata(), to_hdf(), to_latex(), to_xml().
• Part 24 focuses on the serialization and conversion methods to_parquet(), to_feather(), to_string(), Styler.
• Part 25 focuses on the serialization and conversion methods to_bgq() and to_coo().

Also, have a look at the Pandas DataFrame methods cheat sheet!