Is there a simple way to conduct the this transformation on a multi-indexed dataframe?

user_19308682 0
Viewed 15

Please see the attached image showing a DataFrame (left_table in the picture, wrote this as a code in the following). I want to transform it to the right_table in a simple way (using pivot, melt, transposing etc).

 arrays = [
    ["T1", "T1", "T2", "T2"],
    ["C2", "C3", "C2", "C3"],
            ]

tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=["C1", "second"])
index

Left_table = pd.DataFrame(np.random.randn(2, 4), index=["N1", "N2"], columns=index)

Left_table

enter image description here

multi-index reshape transform pivot
1 Answers

Try:

Left_table = (
    Left_table.stack(0)
    .reset_index()
    .rename(columns={"level_0": "C1", "C1": "T"})
)


Left_table.columns.name = None
Left_table = Left_table.sort_values(by=["T", "C1"])

print(Left_table.to_markdown(index=False))

Prints:

C1 T C2 C3
N1 T1 1.10134 0.52524
N2 T1 1.45332 0.226281
N1 T2 0.816961 -0.677362
N2 T2 1.00841 0.0634249
user_100359850
Related