DataFrame: N largest indexes values (from level=1) to n columns

Question

I am trying to convert such a df:

df = pd.DataFrame({'A': ['A1', 'A1', 'A1', 'A1', 'A1', 'A1', 'A2', 'A2', 'A2', 'A2', 'A2', 'A2', 'A2'],
      'B': ['B1', 'B1', 'B2', 'B2', 'B3', 'B3', 'B4', 'B5', 'B6', 'B7', 'B7', 'B8', 'B8']})

by taking n (here 2) largest indexes (by count of B) to:

My way of doing it:

df = df.groupby(['A', 'B'])['A'].count()
df = df.groupby(level=0).nlargest(2).reset_index(level=0, drop=True)

what gives me (which is close to what I need):

Now, the only methods I know to transform MultiIndex are:

df.reset_index(level=1)
df.unstack()

But they don't give me what I am looking for. Is there any dataframe method that will do it for me or I need to do it around with apply. One way of doing it would be to loop through every pair of: df.index.get_level_values(level=1) and putting it to new df of 2 columns. But this will break If one index.level=0, will have only one index.level=1

Also: I don't care for order of (nlargest) when the count is the same.

score 2 · Answer 1 · answered May 16 '18 at 10:54

While @jezrael answer is much faster and easier (I will use it), this is what I developed, when I was working on it:

df = pd.DataFrame({'A': ['A1', 'A1', 'A1', 'A1', 'A1', 'A1', 'A2', 'A2', 'A2', 'A2', 'A2', 'A2', 'A2'],
      'B': ['B1', 'B1', 'B2', 'B2', 'B3', 'B3', 'B4', 'B5', 'B6', 'B7', 'B7', 'B8', 'B8']})

df = df.groupby(['A', 'B'])['A'].count()
df = df.groupby(level=0).nlargest(2).reset_index(level=0, drop=True)
df = df.unstack()

df_new = pd.DataFrame(columns=['A', '1_Largest', '2_largest'])

for i, row in enumerate(['A1', 'A2']):
    df_new.loc[i, :] = row
    df_new.loc[i, '1_Largest'] = df.loc[row].sort_values(ascending=False).index[0]
    df_new.loc[i, '2_largest'] = df.loc[row].sort_values(ascending=False).index[1]

df_new.set_index('A')

jezrael · Accepted Answer · 2018-05-16T10:42:28.067

Use SeriesGroupBy.value_counts which by default sort with select top 2 index values by head and then DataFrame contructor:

a = df.groupby('A')['B'].apply(lambda x: x.value_counts().head(2).index.tolist())
print (a)
A
A1    [B1, B3]
A2    [B7, B8]
Name: B, dtype: object

If want use your code:

df = df.groupby(['A', 'B'])['A'].count()
df = df.groupby(level=0).nlargest(2).reset_index(level=0, drop=True)

df = df.rename('C').reset_index().groupby('A')['B'].apply(list)
print (df)
A
A1    [B1, B2]
A2    [B7, B8]
Name: B, dtype: object

df1 = (pd.DataFrame(a.values.tolist(), index=a.index)
         .rename(columns=lambda x: x+1)
         .add_suffix('_nlargest'))
print (df1)
   1_nlargest 2_nlargest
A                       
A1         B1         B3
A2         B7         B8

DataFrame: N largest indexes values (from level=1) to n columns

2 Answers2

Linked