pandas sort values in pivot table

Question

I have a dataframe and I want to get all rows grouped in id where after row with country = russia and month = march is followed by a line with country != russia

input dataframe:

import pandas as pd
import numpy as np
data = {'fruit': ['pear','pear','pear','banana', 'banana', 'banana', 'apricot', 'pear','watermelon','pear','banana', 'banana', 'banana','banana', 'melon', 'cherry','banana', 'kiwi', 'kiwi', 'kiwi'],
'country': ['france','france', 'france', 'russia', 'russia', 'russia','russia', 'france','russia','usa', 'russia', 'ghana','ghana','ghana', 'ghana', 'albania','andorra', 'russia', 'russia', 'russia'],
'id': ['01','01','01','01','01','01','02','02','03','03','011', '011', '011','011', '6', '6','6', '5', '5', '5'],
'id1': ['01','01','01','01','01','01','02','02','03','03','011', '011', '011','011', '6', '6','6', '5', '5', '5'],
'month': ['january','september','january','january','september','january','january', 'september','march','march', 'november', 'march', 'january','january', 'march', 'january','july', 'march', 'march', 'april']        
}
df = pd.DataFrame(data, columns = ['fruit','country', 'id','id1', 'month'])

I made pivot table with pd.pivot_table(df, values='id', index=['fruit','country'], columns='id1', aggfunc='count') and i got many unuseless rows, where i have NaN or very small figures.

How to sort pivot table to get rows with figures no less than 3? Can anyone see the problem

I need to get this dataframe

data = {'fruit': ['banana', 'banana', 'kiwi','pear'],
'country': [ 'ghana','russia','russia','france'],
'01': [np.nan,3,np.nan,3],
'011': [3,1,np.nan,np.nan],
'5': [np.nan,np.nan,3,np.nan]
}
df = pd.DataFrame(data, columns = ['fruit','country', '01', '011','5'])

user_13014172 · Answer

If df2 is your pivot table, you can do this:

row_mask = np.any((df2 >= 3).values, axis=1)
col_mask = np.any((df2 >= 3).values, axis=0)
df2.loc[row_mask, col_mask]

            id1  01     011       5
fruit   country             
banana  ghana   NaN     3.0     NaN
        russia  3.0     1.0     NaN
kiwi    russia  NaN     NaN     3.0
pear    france  3.0     NaN     NaN

user_15997119 · Answer

Is this your desired outcome? It is "to get rows with figures no less than 3", but different from your outcome picture..

df = df.pivot_table(index=['fruit','country'], columns='id1', values='id', aggfunc='count')
df['total'] = df.sum(axis=1)
df.drop(df.loc[df['total']<3].index, inplace=True)
df.dropna(how='all', axis=1, inplace=True)

Output

         id1    01  011 02  5   total
fruit   country                 
banana  ghana   NaN 3.0 NaN NaN 3.0
banana  russia  3.0 1.0 NaN NaN 4.0
kiwi    russia  NaN NaN NaN 3.0 3.0
pear    france  3.0 NaN 1.0 NaN 4.0

pandas sort values in pivot table

2 Answers