Transform 1‑D Data to 2‑D Tables with Pandas Pivot_table and Unstack

Rescue pandas Plan (1) – Converting a One‑Dimensional Array to a Two‑Dimensional Array

Data requirement

Requirement breakdown

Data import

Requirement processing

Method 1: pivot_table

Method 2: unstack

Summary

Many users avoid pandas for data manipulation, preferring other libraries. This series aims to show pandas’ power and encourage its adoption.

Data Requirement

Transform the left‑hand data (shown in the red box) into the right‑hand format.

For small datasets, simple operations may suffice, but larger datasets benefit from pivot_table.

Requirement Breakdown

All values in tag_name become new column names, while period and simei should remain unique identifiers.

Data Import

First, import the data:

Requirement Processing

Method 1: pivot_table

Using pivot_table directly raises a non‑numeric data error because the default aggfunc is mean. Define a custom function to handle strings:

def eval_func(x):
    return x

df = pd.pivot_table(df, values='tag_value2', index=['period', 'simei'], columns=['tag_name'], aggfunc=eval_func).reset_index()
df.columns = df.columns.values

After resetting the index and renaming columns, the desired format is achieved.

Method 2: unstack

The same result can be achieved with unstack:

df = df.set_index(['period', 'simei', 'tag_name']).unstack('tag_name')
df.columns = df.columns.get_level_values(1).values
df.reset_index()

Set the target columns as index, then unstack tag_name to turn it into column headers. After flattening multi‑level columns and resetting the index, the final table matches the requirement.

Summary

For large‑scale data processing, pandas remains a powerful tool. This example showcases only a fraction of its capabilities; readers are encouraged to explore further and share alternative solutions.