在pandas datafram中为每个组插入缺少的类别

Question

我需要为每个组插入缺失的类别，下面是一个示例：

import pandas as pd
import numpy as np

df = pd.DataFrame({ "group":[1,1,1 ,2,2],
                   "cat": ['a', 'b', 'c', 'a', 'c'] ,
                   "value": range(5),
                   "value2": np.array(range(5))* 2})

df

# test dataframe

cat group   value value2
a   1         0   0
b   1         1    2
c   1         2    4
a   2         3    6
c   2         4    8

假设我有一些categories = ['a', 'b', 'c', 'd']。ifcat列 不包含列表中的类别，我要插入 一行，用于值为0的每个组。 如果是类别，如何在每个组中插入一行，以便获取每个组的所有类别

^{pr2}$

Answer 1

groupby这里不是必需的，只需要^{}通过MultiIndex：

categories = ['a', 'b', 'c', 'd']
mux = pd.MultiIndex.from_product([df['group'].unique(), categories], names=('group','cat'))
df = df.set_index(['group','cat']).reindex(mux, fill_value=0).swaplevel(0,1).reset_index()
print (df)
  cat  group  value  value2
0   a      1      0       0
1   b      1      1       2
2   c      1      2       4
3   d      1      0       0
4   a      2      3       6
5   b      2      0       0
6   c      2      4       8
7   d      2      0       0

有很多解决方案，所以我添加了时间安排：

^{pr2}$

---

def zero(df):
    from itertools import product
    dfo = pd.DataFrame(list(product(df['group'].unique(), categories)),
                            columns=['group', 'cat'])
    return dfo.merge(df, how='left').fillna(0)

def wen(df):
    return df.groupby('group',as_index=False).apply(lambda x : x.set_index('cat').reindex(categories)).fillna(0).reset_index().drop('level_0',1)

def bharath(df):
    mux = pd.MultiIndex.from_product([df['group'].unique(), categories], names=('group','cat'))
    return mux.to_frame().merge(df,on=['cat','group'],how='outer').fillna(0)

def akilat90(df):
    grouped = df.groupby('group')
    categories =  pd.DataFrame(['a', 'b', 'c', 'd'], columns=['cat'])
    merged_list = []
    for g in grouped:
        merged = pd.merge(categories, g[1], how = 'outer', on='cat')
        merged['group'].fillna(merged['group'].mode()[0],inplace=True) # replace the `group` column's `NA`s by mode
        merged.fillna(0, inplace=True)
        merged_list.append(merged)

    return pd.concat(merged_list)


print (jez(df))
print (coldspeed(df))
print (zero(df))
print (wen(df))
print (bharath(df))
print (akilat90(df))

---

In [262]: %timeit (jez(df))
100 loops, best of 3: 11.5 ms per loop

In [263]: %timeit (bharath(df))
100 loops, best of 3: 16 ms per loop

In [264]: %timeit (zero(df))
10 loops, best of 3: 28.3 ms per loop

In [265]: %timeit (wen(df))
1 loop, best of 3: 8.74 s per loop

In [266]: %timeit (coldspeed(df))
1 loop, best of 3: 8.2 s per loop

In [297]: %timeit (akilat90(df))
1 loop, best of 3: 23.6 s per loop

Answer 2

有点复杂，但您可以使用groupby+reindex：

categories = ['a', 'b', 'c', 'd']

def f(x):
    return x.reindex(categories, fill_value=0)\
                   .assign(group=x['group'][0].item())

df.set_index('cat').groupby('group', group_keys=False).apply(f).reset_index()


  cat  group  value  value2
0   a      1      0       0
1   b      1      1       2
2   c      1      2       4
3   d      1      0       0
4   a      2      3       6
5   b      2      0       0
6   c      2      4       8
7   d      2      0       0

Answer 3

这是单线解决方案。。。

df.groupby('group',as_index=False).apply(lambda x : x.set_index('cat').\
       reindex(categories)).fillna(0).reset_index().drop('level_0',1)
Out[601]: 
  cat  group  value  value2
0   a    1.0    0.0     0.0
1   b    1.0    1.0     2.0
2   c    1.0    2.0     4.0
3   d    0.0    0.0     0.0
4   a    2.0    3.0     6.0
5   b    0.0    0.0     0.0
6   c    2.0    4.0     8.0
7   d    0.0    0.0     0.0