python - 动态地对 pandas 数据帧进行连接

Question

我想过滤一列，然后动态连接生成的数据帧。我天真的做法是；给定一个数据帧，编写一个函数，根据列中的值进行过滤以变小然后连接。但我不知道如何动态加入。有更好的方法吗？

data = {'name': ['Jason', 'Molly', 'Jason', 'Jason', 'Molly'],
        'year': [2012, 2012, 2013, 2014, 2014],
        'sale': [41, 24, 31, 32, 31]}

df = pd.DataFrame(data)
print df

def joinDF(df):
    unique_yr = df.year.unique().tolist()  
    i = 1
    for yr in unique_yr:
        df1 = df.loc[df['year'] == yr]
        if len(df.index) != 0:
           #make columns unique then join on name
           df1[['year'+ str(i),'sale'+ str(i), 'name']] = df1[['year','sale','name']]
           i+=1
           print df1

joinDF(df)

   sale name  year
0   41  Jason  2012
1   24  Molly  2012
2   31  Jason  2013
3   32  Jason  2014
4   31  Molly  2014

   sale1 name   year1
0   41   Jason  2012
1   24   Molly  2012

   sale2 name   year2
2   31   Jason  2013

   sale3 name   year3
3   32   Jason  2014
4   31   Molly  2014

进行连接，生成的输出数据帧应如下所示:

    sale1 name1  year1  sale2   year2  sale3  year3
0   41   Jason  2012   31     2013   32    2014
1   24   Molly  2012   NA     NA     31    2014

Answer 1

您可以使用factorize与 pivot_table , df 按列 year 排序:

df['groups'] = (pd.factorize(df.year)[0] + 1).astype(str)

df1 = (df.pivot_table(index='name', columns='groups', values=['sale', 'year']))
df1.columns = [''.join(col) for col in df1.columns]
print (df1)
       sale1  sale2  sale3   year1   year2   year3
name                                              
Jason   41.0   31.0   32.0  2012.0  2013.0  2014.0
Molly   24.0    NaN   31.0  2012.0     NaN  2014.0

但是pivot_table使用aggfunc，如果重复则默认为aggfunc=np.mean。更好的示例解释是 here并在 docs .