在多索引数据帧中添加缺少的索引

Question

嗨，我有多索引熊猫数据帧。抱歉的图片，但我发现它更容易解释，而不是简单的代码

由于数据不一致，我的一些行丢失了Parent_category。在示例数据中，父类别为空

为了得到您在图片上看到的数据帧，我将数据按Child_category分组

如何为具有相同Child_category的行填充缺少的父类别字段

索引结构：

MultiIndex(levels=[['Apps', 'Bars', 'Bath', 'Beer', 'Books', 'Breakfast', 'Cellar', 'Charity', 'Cleaning', 'Clothing', 'Co-working', 'Coffee', 'Dining', 'Drugs', 'Education', 'Electronics', 'Entertainment', 'Groceries', 'Hair Cut', 'Hotel', 'Icecream', 'Lunch', 'Maintenance', 'Massage', 'Museums', 'Music', 'Parking', 'Petroleum', 'Rent', 'Repair', 'Resident', 'Snacks', 'Souvenir', 'Souvenirs', 'Spa & yoga', 'Taxi', 'Tea', 'Transport', 'Traveling', 'Visa', 'Yoga', 'Канцелярия'], ['', 'Car', 'Drinks', 'Eatings', 'Home', 'Spa & yoga', 'Transport', 'Traveling', 'Utilities', 'iTunes']],
           codes=[[0, 1, 1, 2, 3, 3, 4, 5, 5, 6, 6, 7, 8, 9, 10, 11, 11, 12, 12, 13, 14, 15, 16, 17, 18, 19, 20, 20, 21, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 36, 37, 37, 38, 39, 40, 41], [9, 0, 2, 4, 0, 2, 0, 0, 3, 0, 8, 0, 1, 0, 0, 0, 2, 0, 3, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 3, 4, 5, 7, 9, 1, 1, 1, 1, 4, 0, 7, 0, 0, 0, 0, 2, 0, 6, 0, 0, 5, 0]],
           names=['Child_category', 'Parent_category'],
           sortorder=0)

重新索引后，我得到以下数据帧。我猜用O（n^2）可以在循环中填充数据，但是要寻找优雅的解决方案

Answer 1

我相信你需要：

mux = pd.MultiIndex(levels=[['Apps', 'Bars', 'Bath', 'Beer', 'Books', 'Breakfast', 'Cellar', 'Charity', 'Cleaning', 'Clothing', 'Co-working', 'Coffee', 'Dining', 'Drugs', 'Education', 'Electronics', 'Entertainment', 'Groceries', 'Hair Cut', 'Hotel', 'Icecream', 'Lunch', 'Maintenance', 'Massage', 'Museums', 'Music', 'Parking', 'Petroleum', 'Rent', 'Repair', 'Resident', 'Snacks', 'Souvenir', 'Souvenirs', 'Spa & yoga', 'Taxi', 'Tea', 'Transport', 'Traveling', 'Visa', 'Yoga', 'Канцелярия'], ['', 'Car', 'Drinks', 'Eatings', 'Home', 'Spa & yoga', 'Transport', 'Traveling', 'Utilities', 'iTunes']],
           codes=[[0, 1, 1, 2, 3, 3, 4, 5, 5, 6, 6, 7, 8, 9, 10, 11, 11, 12, 12, 13, 14, 15, 16, 17, 18, 19, 20, 20, 21, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 36, 37, 37, 38, 39, 40, 41], [9, 0, 2, 4, 0, 2, 0, 0, 3, 0, 8, 0, 1, 0, 0, 0, 2, 0, 3, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 3, 4, 5, 7, 9, 1, 1, 1, 1, 4, 0, 7, 0, 0, 0, 0, 2, 0, 6, 0, 0, 5, 0]],
           names=['Child_category', 'Parent_category'],
           sortorder=0)
df = pd.DataFrame({'a': range(52)}, index=mux)

对于每个Child_category级别，获取第一个非空空间值：

print (df.rename({'':np.nan}, level=1)
        .reset_index()
        .groupby('Child_category')
        .first()
        .set_index('Parent_category', append=True)
        .head(20))

或将每个组的值Parent_category替换为空空格Child_category：

print (df.rename({'':np.nan}, level=1)
        .reset_index()
        .groupby('Child_category')
        .apply(lambda x: x.ffill().bfill())
        .set_index(['Child_category', 'Parent_category'])
        .head(20))