Transform 1D Arrays to 2D with pandas: pivot_table vs unstack

Data Requirement

Goal: transpose the data shown on the left into the format on the right.

For small datasets a simple approach works, but with larger data pivot_table can fail.

Requirement Breakdown

We need to use all values in tag_name as new column names, while period and simei serve as unique identifiers.

Data Import

First, import the data.

Processing Methods

Method 1: pivot_table

pivot_table raises an error because the data are not numeric; the default aggfunc is mean. Define a custom function that simply returns the input.

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 shape is obtained.

Method 2: unstack

Alternatively, 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 relevant columns as index, unstack tag_name to become column headers, flatten the multi‑level columns, and reset the index.

Summary

pandas remains a powerful tool for handling large datasets; this example demonstrates only a fraction of its capabilities. Thanks to the data provider, and readers are encouraged to share alternative solutions.