Pivot table where index and column value contain only list value-CodePudding

Having a dataframe as below:

df1 = pd.DataFrame({'Product':['A','A','A','B','B','C','C','C','E','E','E'],
                    'things':['B','C','D','C','D','D','A','B','A','B','C'],'Marks2':[10,20,6,50, 88,23,140,9,60,65,70]})
df1

current_list=['E', 'A', 'B', 'C']

is it possible that i can create a pivot table with index and columns only contain the current_list values and values is marks2?

expected output:

CodePudding user response：

You can just pivot as usual and reindex:

(df1.pivot('Product','things', 'Marks2')
    .reindex(current_list, axis=0)
    .reindex(current_list, axis=1)
)

Output:

things    E      A     B     C
Product                       
E       NaN   60.0  65.0  70.0
A       NaN    NaN  10.0  20.0
B       NaN    NaN   NaN  50.0
C       NaN  140.0   9.0   NaN

It is a bit better to filter/query your data before pivot:

(df1.query('Product in @current_list and things in @current_list')
    .pivot('Product','things', 'Marks2')
    .reindex(current_list, axis=0)
    .reindex(current_list, axis=1)
)