CODEWITHSUNDEEP
pythonpandasdataframepivot-table
Christian
Christian

Reputation: 1571

Pandas pivot_table: "merge" column values

Assume I have the following table:

from datetime import datetime
import pandas as pd

d = [[datetime(year=2021, month=1, day=1, minute=5), "A", "new", 3],
     [datetime(year=2021, month=1, day=1, minute=5), "B", "new", 6],
     [datetime(year=2021, month=1, day=1, minute=5), "C", "new", 7],
     [datetime(year=2021, month=1, day=1, minute=15), "A", "old", 6],
     [datetime(year=2021, month=1, day=1, minute=15), "B", "old", 2],
     [datetime(year=2021, month=1, day=1, minute=15), "C", "old", 14],
    ]

df = pd.DataFrame(data=d, columns=["Time", "Article", "Status", "Qty"])

I want to restructure this data such, that I have one line per "Time"-value and then for each article the columns "Qty" and "Status".

This I can nearly achieve using the pivot_table as follows:

pd.pivot_table(data=df, index=["Time"], columns=["Article"], values=["Status", "Qty"], aggfunc="last")

Which produces the following output for me:

Qty Status
Article A B C A B C
Time
2021-01-01 00:05:00 3 6 7 new new new
2021-01-01 00:15:00 6 2 14 old old old

However, I would like this to be grouped by the Article, rather than the value columns. So like it would be produced by the following code:

arrays = [
    ["A", "A", "B", "B", "C", "C", "qux", "qux"],
    ["Qty", "Status", "Qty", "Status", "Qty", "Status"],
]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=["Article", "Value"])
data_pivot=[
    [3, "new", 6, "new", 6, "new"],
    [6, "old", 2, "old", 14, "old"]
]
pd.DataFrame(data=data_pivot, columns=index, index=[datetime(year=2021, month=1, day=1, minute=5), datetime(year=2021, month=1, day=1, minute=15)])
Article A B C
Value Qty Status Qty Status Qty Status
2021-01-01 00:05:00 3 new 6 new 6 new
2021-01-01 00:15:00 6 old 2 old 14 old

Simply switching the values- and columns-keyword in the pivot_table-call somehow didn't give me the expected output either.

Unfortunately, I'm having trouble naming this issue so it's a bit hard for me to look for existing solutions (hence, the maybe strange title of this question), so I'm sorry if this was already asked many times.

Upvotes: 2

Views: 786

Answers (1)

jezrael
jezrael

Reputation: 863166

Use DataFrame.swaplevel with DataFrame.sort_index:

df = df.swaplevel(1,0,axis=1).sort_index(axis=1)

Upvotes: 3

Related Questions