Skip to main content
 首页 » 编程设计

python之基于多索引列数据框中的一系列列进行切片

2024年12月31日7rubylouvre

我正在通过执行以下操作创建我的数据框:

months        = [ 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' ] 
monthyAmounts = [ "actual", "budgeted", "difference" ] 
 
income = [] 
names  = [] 
 
for x in range( incomeIndex + 1, expensesIndex ): 
    amounts = [ randint( -1000, 15000 ) for x in range( 0, len( months ) * len( monthyAmounts ) ) ] 
    income.append( amounts ) 
    names.append( f"name_{x}" ) 
 
index    = pd.Index( names, name = 'category' ) 
columns  = pd.MultiIndex.from_product( [ months, monthyAmounts ], names = [ 'month', 'type' ] ) 
incomeDF = pd.DataFrame( income, index = index, columns = columns ) 

数据框如下所示: (删除了 3 月 - 12 月)

          Jan                            Feb                        ...              
          actual   budgeted   difference actual budgeted difference 
name_13   14593     -260      10165      9767     629    10054 
name_14    6178     1398      13620      1821   10986     -663 
name_15    2432     3279       7545      8196    1052     7386 
name_16    9964    13098      10342      5564    4631     7422 

我想要的是为每一行切分 1 月至 5 月的差异列。我能做的是通过以下方式对所有月份的差异列进行切片:

incomeDifferenceDF = incomeDF.loc[ :, idx[ :, 'difference' ] ] 

这给了我一个看起来像这样的数据框: (删除了 3 月至 12 月)

         Jan        Feb          .... 
         difference difference                          
name_13       10165      10054   
name_14       13620       -663   
name_15        7545       7386   
name_16       10342       7422   

我试过的是:

incomeDifferenceDF = incomeDF.loc[ :, idx[ 'Jan' : 'May', 'difference' ] ] 

但这给了我错误:

UnsortedIndexError: 'MultiIndex slicing requires the index to be lexsorted: slicing on levels [0], lexsort depth 0' 

所以,这看起来很接近,但我不确定如何解决这个问题。

我也试过:

incomeDifferenceDF = incomeDF.loc[ :, idx[ ['Jan':'May'], 'difference' ] ] 

但这只会产生错误:

SyntaxError: invalid syntax 
( Points at ['Jan':'May'] ) 

执行此操作的最佳方法是什么?

请您参考如下方法:

如果需要通过MultiIndex进行选择,需要 bool 掩码:

index    = pd.Index( [1,2,3,4], name = 'category' ) 
budgetMonths = pd.date_range( "January, 2018", periods = 12, freq = 'BM' )  
months        = [ 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',  
                  'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' ] 
monthyAmounts = [ "actual", "budgeted", "difference" ] 
columns = pd.MultiIndex.from_product( [ months, monthyAmounts ], names = [ 'month', 'type' ]) 
incomeDF = pd.DataFrame( 10, index = index, columns = columns ) 
 
#trick for get values between  
idx = pd.Series(0,index=months).loc['Jan' : 'May'].index 
print (idx) 
Index(['Jan', 'Feb', 'Mar', 'Apr', 'May'], dtype='object') 
 
mask1 = incomeDF.columns.get_level_values(0).isin(idx) 
mask2 = incomeDF.columns.get_level_values(1) == 'difference' 
 
incomeDifferenceDF = incomeDF.loc[:, mask1 & mask2] 
print (incomeDifferenceDF) 
month           Jan        Feb        Mar        Apr        May 
type     difference difference difference difference difference 
category                                                        
1                10         10         10         10         10 
2                10         10         10         10         10 
3                10         10         10         10         10 
4                10         10         10         10         10