Pandas分组后如何按mes和clif_cod筛选行？

I wrote this code:

import pandas as pd 
import numpy as np 

df = pd.DataFrame({
    'clif_cod' : [1,2,3,3,4,4,4], 
    'peds_val_fat' : [10.2, 15.2, 30.9, 14.8, 10.99, 39.9, 54.9], 
    'mes' : [1,2,4,5,5,6,12], 
    'ano' : [2016, 2016, 2016, 2016, 2016, 2016, 2016]
}) 

vetor_valores = df.groupby(['mes','clif_cod']).sum()

After running it, I get this output:

ano  peds_val_fat
mes clif_cod                      
1   1          2016         10.20
2   2          2016         15.20
4   3          2016         30.90
5   3          2016         14.80
    4          2016         10.99
6   4          2016         39.90
12  4          2016         54.90

I want to filter rows based on mes and clif_cod, but when I run list(vetor_valores), I only see the ano and peds_val_fat columns. How can I work with mes and clif_cod for filtering?

Solution #

Ah, I see the issue here! When you group by ['mes','clif_cod'], those two columns become the MultiIndex of your resulting DataFrame (vetor_valores) instead of regular columns. That's why they don't show up when you run list(vetor_valores) – that function only lists the names of non-index columns.

You've got two simple ways to fix this, depending on what you need:

1. Turn Indexes Back into Regular Columns #

If you want mes and clif_cod to behave like normal columns (so you can filter with them just like ano or peds_val_fat), just add reset_index() to your groupby line:

vetor_valores = df.groupby(['mes','clif_cod']).sum().reset_index()

Now when you run list(vetor_valores), you'll see all four columns: ['mes', 'clif_cod', 'ano', 'peds_val_fat']. Filtering is straightforward from here:

# Example: Filter rows where mes is 5 and clif_cod is 3
filtered_data = vetor_valores[(vetor_valores['mes'] == 5) & (vetor_valores['clif_cod'] == 3)]

2. Filter Directly Using the MultiIndex #

If you'd rather keep the index structure (which can be more efficient for large datasets), you can filter using the index levels directly. Here are a few handy methods:

Use loc with index tuples

Since it's a MultiIndex, you can target specific combinations of mes and clif_cod with tuples:

# Get the row where mes=5 and clif_cod=4
specific_row = vetor_valores.loc[(5, 4)]

Use query with index references

You can access index levels in query by using index[0] for mes and index[1] for clif_cod:

# Filter all rows where mes is 5
filtered_mes5 = vetor_valores.query('index[0] == 5')

Use xs (Cross Section) for quick level filtering

If you want to filter on just one index level (like all rows for a specific mes), xs is perfect:

# Get all rows where clif_cod=4
filtered_clif4 = vetor_valores.xs(4, level='clif_cod')

Pick the approach that fits your workflow best – both will let you filter using mes and clif_cod exactly how you need to.

内容的提问来源于stack exchange，提问作者Esther