Select the first subgroup in Pandas multi-index group

user_3463297 0
Viewed 674

I have a pandas dataframe with 2 level of indexes. For each level 1 Index, I want to select 1st Level 2 Index records.

df = pd.DataFrame({'Person': [1, 1, 1, 2, 2, 2, 3, 3, 3], 
    'Year': ['2020','2020', '2019','2019','2019','2018', '2019','2018','2017'],'class':list('AISAAIASS'),
                  'val': randint(0, 10, 9)})
df

Person  Year class val
0   1   2020    A   8
1   1   2020    I   7
2   1   2019    S   6
3   2   2019    A   8
4   2   2019    A   1
5   2   2018    I   2
6   3   2019    A   0
7   3   2018    S   6
8   3   2017    S   8

I want 2020(Year) records for Person 1 (2 in no), 2019 records (2 in no.) for Person 2 and 2019 record ( 1 record) for Person 3.

I looked into lot of codes, still unable to get the answer. Is there a simple way of doing it?

pandas-groupby pandas python-3.x python
1 Answers

Use Index.get_level_values with Index.duplicated for first MultiIndex values and then filter by Index.isin:

np.random.seed(2020)
df = pd.DataFrame({'Person': [1, 1, 1, 2, 2, 2, 3, 3, 3], 
                  'Year': ['2020','2020', '2019','2019','2019','2018', '2019','2018','2017'],
                  'class':list('AISAAIASS'),
                  'val': np.random.randint(0, 10, 9)}).set_index(['Person','Year'])

idx = df.index[~df.index.get_level_values(0).duplicated()]
df1 = df[df.index.isin(idx)]

Or get first index values by GroupBy.head by first level:

df1 = df[df.index.isin(df.groupby(['Person']).head(1).index)]

print (df1)
            class  val
Person Year           
1      2020     A    0
       2020     I    8
2      2019     A    6
       2019     A    3
3      2019     A    7
user_29010020
Related