Links

Pivot rows to columns

Tags: #pandas #pivot #snippet #operations #utils #data
Author: Ismail Chihab​
Description: This notebook demonstrates how to use the Pandas library to transform data by pivoting rows into columns.

Input

Import library

import pandas as pd

Setup Data

data = {
"LABEL": [
"Sales",
"Sales",
"Sales",
"Gross Profit",
"Gross Profit",
"Gross Profit",
"EBIT",
"EBIT",
"EBIT",
],
"DATE": ["Jan", "Feb", "Mar", "Jan", "Feb", "Mar", "Jan", "Feb", "Mar"],
"VALUE": [0, 2, 3, 4, 5, 6, 7, 8, 9],
}
df = pd.DataFrame(data)
df

Setup Variables

# Name of the column you want to pivot
col_pivot = "DATE"
​
# Name of the column containing the values
col_value = "VALUE"
​
# List of columns not be pivot
cols_index = ["LABEL"]

Model:

def pivot_data(df_init, col_pivot, col_value, cols_index):
# Drop duplicates
df = df_init.copy()
df = df.drop_duplicates()
columns = df[col_pivot].unique().tolist()
​
# Pivot
df = pd.pivot(df, index=cols_index, values=col_value, columns=col_pivot)
for col in cols_index:
df.loc[:, col] = df.index.get_level_values(0)
df = df.reset_index(drop=True)
df = df.reindex(columns=cols_index + columns)
return df

Output

Display result

table = pivot_data(df, col_pivot, col_value, cols_index)
table