Pandas long to wide (unmelt or similar?)

Question

I have a melted, long-format dataframe like this:

name = ["A", "A", "B", "B"]
varA = [1, 2, 1, 2]
varB = [200, 250, 200, 250]
val = [4, 8, 1, 0]

df = pd.DataFrame(
    data=zip(name, varA, varB, val), columns=["name", "varA", "varB", "val"]
)

How can I reshape it like this?

There is a similar question here, so I tried the following, which threw the error ValueError: Index contains duplicate entries, cannot reshape

df2 = (df.set_index(['varA','varB'])
        .stack()
        .unstack(0)
        .reset_index()
        .rename_axis(None, axis=1))

I'm sure this is easy for someone familiar with all the Pandas functions/methods, but there are a ton of functions for the casual user to keep track of!

Answer 1

1. Make a helper column that assigns which rows are associated with name-1/val-1, and name-2/val-2
2. Using this new column we can now pivot the data without encountering that "duplicate entries" error

Clean the output:

3. sort the columns to match your output
4. The pivot will return a dataframe with a columnar multiindex. We'll need to flatten this by combining levels

pandas > 1.0.5

id_vars = ["varA", "varB"]

# Create an id column that tracks the id_vars
# Pivot using the id column
# Sort the columns to match OP expected output
pivoted_df = (
    df.assign(
        id=df.groupby(id_vars).cumcount().add(1).astype(str)
    )
    .pivot(index=id_vars, columns="id", values=["name", "val"])
    .sort_index(level=1, axis=1)
)

# flatten the column multiindex, insert the row index as values
flattened_columns = pivoted_df.columns.map("-".join)
pivoted_df = (
    pivoted_df.set_axis(flattened_columns, axis=1)
    .reset_index()
)

print(pivoted_df)
   varA  varB name-1 val-1 name-2 val-2
0     1   200      A     4      B     1
1     2   250      A     8      B     0

---

pandas <= 1.0.5

• pivot in these versions did not support using lists as arguments. A workaround is to instead use the pivot_table with a nonaggregating aggfunc prevent accidental aggregation.

id_vars = ["varA", "varB"]

# Create an id column that tracks the id_vars
# Pivot using the id column
# Sort the columns to match OP expected output
pivoted_df = (
    df.assign(
        id=df.groupby(id_vars).cumcount().add(1).astype(str)
    )
    .pivot_table(index=id_vars, columns="id", values=["name", "val"], aggfunc=lambda x: x)
    .sort_index(level=1, axis=1)
)

# flatten the column multiindex, insert the row index as values
flattened_columns = pivoted_df.columns.map("-".join)
pivoted_df = (
    pivoted_df.set_axis(flattened_columns, axis=1)
    .reset_index()
)