多索引和多列分组

我有一个具有3级索引和2级列的数据框。

                                       Group
                           Label       A        B       C        D
number      start          end              
1           2020-01-01  2020-12-31  -43.0    0      105.0   -37.0
            2020-12-15  2020-12-15  NaN     NaN      NaN    195.0
2           2019-01-01  2019-12-31  -35.0   80.0    -14.0   NaN
            2019-12-17  2019-12-17  NaN     NaN      NaN    141.0
            2020-01-01  2020-12-31  -15.0   45.0    -7.0    NaN
3           2020-12-17  2020-12-17  NaN     NaN      NaN    326.0
            2022-01-01  2022-12-31  NaN     50.0     NaN    NaN
            2023-12-31  2023-12-31  -25.0   NaN      NaN    NaN
            2023-01-01  2023-12-31  NaN    50.0      NaN    NaN            
            2020-12-15  2020-12-15  NaN     NaN      NaN    61.0
.............

我想按数字分组并开始(仅年份),对每个Label的值求和:

                                      Group
                           Label       A        B       C        D
number      start          end              
1           2020        2020        -43.0    0      105.0   232.0
2           2019        2019        -35.0   80.0    -14.0   141
            2020        2020        -15.0   45.0    -7.0    NaN
3           2020        2020        NaN     NaN      NaN    387.0
            2022        2022        NaN     50.0     NaN    NaN
            2023        2023        -25.0   50.0     NaN    NaN    
.............

请注意,也有更高级别的列(称为“组”,为了简化起见,我不包括在内)和其他子列(标签:A,B,C, D,对每个更高级别的列重复)。 我怎样才能做到这一点? 预先谢谢你

nokiafddfqq 回答:多索引和多列分组

您可以按名称引用MultiIndex级别,并使用DatetimeIndex.year来获取所关注级别的年份。对于所有丢失的分组单元,min_count=1给出NaN而不是0

df.groupby(['number',df.index.get_level_values('start').year,df.index.get_level_values('end').year]).sum(min_count=1)

                      A     B      C      D
number start end                           
1      2020  2020 -43.0   0.0  105.0  158.0
2      2019  2019 -35.0  80.0  -14.0  141.0
       2020  2020 -15.0  45.0   -7.0    NaN
3      2020  2020   NaN   NaN    NaN  387.0
       2022  2022   NaN  50.0    NaN    NaN
       2023  2023 -25.0  50.0    NaN    NaN
本文链接:https://www.f2er.com/3150384.html

大家都在问