CODEWITHSUNDEEP
pandas
Osman Emre
Osman Emre

Reputation: 37

How to filter values with the spesific index in pivot table (MultiDimensional Data )

tempdata =pd.pivot_table( gas_diesel_df, values = 'Quantity' ,index = ['Year','Country or Area'],
                      columns = ['Commodity - Transaction'])
tempdata.iloc[0:10]

At here, I just want to filter quantity values with a specific Country. But I can't use loc method. What do you suggest to special filtering in any pivot table?

Upvotes: 1

Views: 131

Answers (1)

jezrael
jezrael

Reputation: 862511

You can use xs or loc - docs:

Sample

print gas_diesel_df
   Year Country or Area Commodity - Transaction  Quantity
0  2015             A11                      B1        50
1  2015             A11                      C2        60
2  2015             A11                      B1        45
3  2015             A11                      B2        70
4  2015             B11                      B1        40
5  2015             B11                      C2        45
6  2015             C11                      B1        60
7  2015             C11                      B1        65

tempdata =pd.pivot_table( gas_diesel_df, values = 'Quantity' ,index = ['Year','Country or Area'],
                      columns = ['Commodity - Transaction'])
print tempdata
Commodity - Transaction    B1  B2  C2
Year Country or Area                 
2015 A11                 47.5  70  60
     B11                 40.0 NaN  45
     C11                 62.5 NaN NaN

print tempdata.xs('A11', level=1)
Commodity - Transaction    B1  B2  C2
Year                                 
2015                     47.5  70  60
print tempdata.loc[(slice(None),'A11'),:]
Commodity - Transaction    B1  B2  C2
Year                                 
2015                     47.5  70  60

EDIT:

If you need select by multiple years, use loc see doc:

print gas_diesel_df
   Year Country or Area Commodity - Transaction  Quantity
0  2013             A11                      B1        50
1  2015             A11                      C2        60
2  2015             A11                      B1        45
3  2015             A11                      B2        70
4  2015             B11                      B1        40
5  2014             B11                      C2        45
6  2014             C11                      B1        60
7  2014             C11                      B1        65

tempdata =pd.pivot_table( gas_diesel_df, values = 'Quantity' ,index = ['Year','Country or Area'],
                      columns = ['Commodity - Transaction'])

print tempdata
Commodity - Transaction    B1  B2  C2
Year Country or Area                 
2013 A11                 50.0 NaN NaN
2014 B11                  NaN NaN  45
     C11                 62.5 NaN NaN
2015 A11                 45.0  70  60
     B11                 40.0 NaN NaN

print tempdata.loc[2013]
Commodity - Transaction  B1  B2  C2
Country or Area                    
A11                      50 NaN NaN

print tempdata.loc[2013:2014]
Commodity - Transaction    B1  B2  C2
Year Country or Area                 
2013 A11                 50.0 NaN NaN
2014 B11                  NaN NaN  45
     C11                 62.5 NaN NaN

Upvotes: 1

Related Questions