Getting values in pivot table-CodePudding

This is the pivot table and I hope to get value in red and green rectangle

import pandas as pd
import numpy as np

# Create sample dataframes

df_user = pd.DataFrame( {'user': {0: 'a1', 1: 'a1', 2: 'a1', 3: 'a2', 4: 'a2', 5: 'a2', 6: 'a3', 7: 'a3', 8: 'a6', 9: 'a7', 10: 'a8'}, 'query': {0: 'orange', 1: 'strawberry', 2: 'pear', 3: 'orange', 4: 'strawberry', 5: 'lemon', 6: 'orange', 7: 'banana', 8: 'meat', 9: 'beer', 10: 'juice'}} )
df_user['count']=1
df_pivot=pd.pivot_table(df_user,index=['query'],columns=['user'],values=['count'],aggfunc=np.sum).fillna(0)

#getting value in red rectangle, incorrect
print(df_pivot.loc['banana':'beer','a1':'a2'])
#getting value in green rectangle, error
print(df_pivot.loc[:,'a8'])

What's the right way to get them?

CodePudding user response：

Use:

>> df_pivot.loc[["banana","beer"], ('count',['a1','a2'])]

        count
user    a1  a2
query       
banana  0.0 0.0
beer    0.0 0.0

and

>> df_pivot.loc[:, ('count',['a8'])]

           count
user        a8
query   
banana      0.0
beer        0.0
juice       1.0
lemon       0.0
meat        0.0
orange      0.0
pear        0.0
strawberry  0.0

CodePudding user response：

Try to first drop the first column level, like so:

df_pivot = df_pivot.droplevel(0,1)