在 groupby 之后将组与一个数据帧合并

我试图回答这个问题 https://stackoverflow.com/questions/47117982/insert-missing-category-for-each-group-in-pandas-dataframe通过组级合并。下面是同一问题的稍作修改的版本，但我需要组级合并的输出。

以下是输入数据帧：

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

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']
categories =  pd.DataFrame(['a', 'b', 'c', 'd'], columns=['cat'])
print(categories)

    cat
0   a
1   b
2   c
3   d

这是预期的输出：

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

问题：

我可以通过for循环实现我想要的。有没有熊猫的方法可以做到这一点？

（我需要在之间执行外连接categories以及每组的groupby结果df.groupby('group'))

grouped = df.groupby('group')

merged_list = []
for g in grouped:
    merged = pd.merge(categories, g[1], how = 'outer', on='cat')
    merged_list.append(merged)

out = pd.concat(merged_list)

I think groupby + merge这只是过于复杂的方法。

所以使用速度更快reindex http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html by MultiIndex:

mux = pd.MultiIndex.from_product([df['group'].unique(), categories], names=('group','cat'))
df = df.set_index(['group','cat']).reindex(mux).swaplevel(0,1).reset_index()
#add missing values to group column
df['group'] = df['group'].mask(df['value'].isnull())
print (df)
  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    NaN    NaN     NaN
4   a    2.0    3.0     6.0
5   b    NaN    NaN     NaN
6   c    2.0    4.0     8.0
7   d    NaN    NaN     NaN

可能的解决方案：

df = df.groupby('group', group_keys=False)
       .apply(lambda x: pd.merge(categories, x, how = 'outer', on='cat'))
  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    NaN    NaN     NaN
0   a    2.0    3.0     6.0
1   b    NaN    NaN     NaN
2   c    2.0    4.0     8.0
3   d    NaN    NaN     NaN

Timings:

np.random.seed(123)
N = 1000000
L = list('abcd') #235,94.1,156ms

df = pd.DataFrame({'cat': np.random.choice(L, N, p=(0.002,0.002,0.005, 0.991)),
                   'group':np.random.randint(10000,size=N),
                   'value':np.random.randint(1000,size=N),
                   'value2':np.random.randint(5000,size=N)})
df = df.sort_values(['group','cat']).drop_duplicates(['group','cat']).reset_index(drop=True)
print (df.head(10))

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

def jez1(df):
    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()
    df['group'] = df['group'].mask(df['value'].isnull())
    return df

def jez2(df):
    grouped = df.groupby('group')
    categories =  pd.DataFrame(['a', 'b', 'c', 'd'], columns=['cat'])
    return grouped.apply(lambda x: pd.merge(categories, x, how = 'outer', on='cat'))



def coldspeed(df):
    grouped = df.groupby('group')
    categories =  pd.DataFrame(['a', 'b', 'c', 'd'], columns=['cat'])
    return pd.concat([g[1].merge(categories, how='outer', on='cat') for g in grouped])

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)

In [471]: %timeit jez1(df)
100 loops, best of 3: 12 ms per loop

In [472]: %timeit jez2(df)
1 loop, best of 3: 14.5 s per loop

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

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