使用Pandas将2个命令列表与公共元素合并

Question

所以我有两个指令列表..

list_yearly = [
{'name':'john',
 'total_year': 107
},
{'name':'cathy',
 'total_year':124
},
]

list_monthly =  [
{'name':'john',
 'month':'Jan',
 'total_month': 34
},
{'name':'cathy',
 'month':'Jan',
 'total_month':78
},
{'name':'john',
 'month':'Feb',
 'total_month': 73
},
{'name':'cathy',
 'month':'Feb',
 'total_month':46
},
]

目标是获得如下所示的最终数据集：

{'name':'john',
 'total_year': 107,
 'trend':[{'month':'Jan', 'total_month': 34},{'month':'Feb', 'total_month': 73}]
 },

 {'name':'cathy',
  'total_year':124,
  'trend':[{'month':'Jan', 'total_month': 78},{'month':'Feb', 'total_month': 46}]
  },

由于我的数据集是针对某一年所有12个月的大量学生的，所以我使用Pandas进行数据采集。这就是我的工作方式：

首先，使用name键将这两个列表组合为一个数据帧。

In [5]: df = pd.DataFrame(list_yearly).merge(pd.DataFrame(list_monthly))

In [6]: df
Out[6]:
     name    total_year month  total_month
0   john         107     Jan           34
1   john         107     Feb           73
2  cathy         124     Jan           78
3  cathy         124     Feb           46

ln [7]: df['trend'] = df.apply(lambda x: [x[['month', 'total_month']].to_dict()], axis=1)

In [8]: df
Out[8]:
    name    total_year month  total_month  \
0   john         107   Jan           34
1   john         107   Feb           73
2  cathy         124   Jan           78
3  cathy         124   Feb           46

                                  trend
0  [{u'total_month': 34, u'month': u'Jan'}]
1  [{u'total_month': 73, u'month': u'Feb'}]
2  [{u'total_month': 78, u'month': u'Jan'}]
3  [{u'total_month': 46, u'month': u'Feb'}]

In [9]: df[['name', 'total_year', 'trend']].to_dict(orient='records')
Out[9]:
[{'name': 'john',
  'total_year': 107,
  'trend': [{'month': 'Jan', 'total_month': 34}]},
 {'name': 'john',
  'total_year': 107,
  'trend': [{'month': 'Feb', 'total_month': 73}]},
 {'name': 'cathy',
  'total_year': 124,
  'trend': [{'month': 'Jan', 'total_month': 78}]},
 {'name': 'cathy',
  'total_year': 124,
  'trend': [{'month': 'Feb', 'total_month': 46}]}]

很明显，最终的数据集并不完全是我想要的。我得到的不是两个月都分开的两个数据集，而是四个月分开的数据集。我该如何解决这个问题呢？我更愿意在熊猫自身中修复它，而不是使用这个最终的输出将它再次降低到所希望的状态

Answer 1

实际上，您应该使用groupby根据name和total_year分组，而不是apply（作为第二步），您可以在groupby中创建所需的列表。示例-

df = pd.DataFrame(list_yearly).merge(pd.DataFrame(list_monthly))

def func(group):
    result = []
    for idx, row in group.iterrows():
        result.append({'month':row['month'],'total_month':row['total_month']})
    return result

result = df.groupby(['name','total_year']).apply(func).reset_index()
result.columns = ['name','total_year','trend']
result_dict = result.to_dict(orient='records')

演示-

In [9]: df = pd.DataFrame(list_yearly).merge(pd.DataFrame(list_monthly))

In [10]: df
Out[10]:
    name  total_year month  total_month
0   john         107   Jan           34
1   john         107   Feb           73
2  cathy         124   Jan           78
3  cathy         124   Feb           46

In [13]: def func(group):
   ....:     result = []
   ....:     for idx, row in group.iterrows():
   ....:         result.append({'month':row['month'],'total_month':row['total_month']})
   ....:     return result
   ....:

In [14]:

In [14]: result = df.groupby(['name','total_year']).apply(func).reset_index()

In [15]: result
Out[15]:
    name  total_year                                                  0
0  cathy         124  [{'month': 'Jan', 'total_month': 78}, {'month'...
1   john         107  [{'month': 'Jan', 'total_month': 34}, {'month'...

In [19]: result.columns = ['name','total_year','trend']

In [20]: result
Out[20]:
    name  total_year                                              trend
0  cathy         124  [{'month': 'Jan', 'total_month': 78}, {'month'...
1   john         107  [{'month': 'Jan', 'total_month': 34}, {'month'...

In [21]: result.to_dict(orient='records')
Out[21]:
[{'name': 'cathy',
  'total_year': 124,
  'trend': [{'month': 'Jan', 'total_month': 78},
   {'month': 'Feb', 'total_month': 46}]},
 {'name': 'john',
  'total_year': 107,
  'trend': [{'month': 'Jan', 'total_month': 34},
   {'month': 'Feb', 'total_month': 73}]}]