How to group with multiple index in Python?-CodePudding

Problem

I want to pivot and group values. Despite looking through the guidance

What I want

# multi-level columns
items = pd.MultiIndex.from_tuples([('Job1', 'T'),('Job1', 'F'), ('Job1', 'X'),
                                 ('Job2', 'T'),('Job2', 'F'), ('Job2', 'X')])

# creating a DataFrame
dataFrame = pd.DataFrame([[2, 0, 0, 1, 1, 0], 
                          [0, 2, 0,0, 1, 1], 
                          [2, 0, 0,2, 0, 0], 
                          [0, 0, 2,0, 0, 2]],
                         index=['North', 'South', 'East', "West"],
                         columns=items)

# DataFrame
dataFrame

CodePudding user response：

One option is to pivot each Job column and concat the result

cols = ['Job1', 'Job2', 'Job3']
dfs = []

for col in cols:
    df = pd.crosstab(df1['Area'], df1[col])
    df.columns = pd.MultiIndex.from_product([[col], df.columns.tolist()])
    dfs.append(df)

out = pd.concat(dfs, axis=1)

print(out)

      Job1       Job2       Job3
         F  T  X    F  T  X    F  T  X
Area
East     0  0  2    0  0  2    0  1  1
North    0  2  0    1  1  0    0  1  1
South    2  0  0    1  0  1    1  0  1
West     0  2  0    0  2  0    1  1  0