如何在Pandas数据集上组合和进行分组计算？

Question

我正在写一篇经济学论文，需要一些帮助来组合和转换两个数据集。在

我有两个pandas数据帧，一个包含国家及其邻国（borderdf）的列表，例如

borderdf
country    neighbor
sweden     norway
sweden     denmark
denmark    germany
denmark    sweden

一个是每个国家和年份的数据（datadf），例如

^{pr2}$

我需要在datadf中为neighbormeangdp创建一个列，该列包含所有邻居的gdp平均值，如neighbordf所示。我希望我的结果是这样的：

datadf
country    year    gdp    neighborsmeangdp
sweden     2004    5454   5565
sweden     2005    5676   5775

我该怎么做？在

Answer 1

您可以使用pandasmerge函数直接合并这两者。 这里的诀窍是，您实际上想要将datadf中的国家列与您的borderdf中的邻居列合并。 然后使用groupby和mean得到平均邻近gdp。 最后，与数据合并，得到该国的国内生产总值。 例如：

import pandas as pd
from StringIO import StringIO

border_csv = '''
country, neighbor
sweden, norway
sweden, denmark
denmark, germany
denmark, sweden
'''

data_csv = '''
country, gdp, year
sweden, 5454, 2004
sweden, 5676, 2005
norway, 3433, 2004
norway, 3433, 2005
denmark, 2132, 2004
denmark, 2342, 2005
'''

borders = pd.read_csv(StringIO(border_csv), sep=',\s*', header=1)
data = pd.read_csv(StringIO(data_csv), sep=',\s*', header=1)

merged = pd.merge(borders,data,left_on='neighbor',right_on='country')
merged = merged.drop('country_y', axis=1)
merged.columns = ['country','neighbor','gdp','year']


grouped = merged.groupby(['country','year'])
neighbor_means = grouped.mean()
neighbor_means.columns = ['neighbor_gdp']
neighbor_means.reset_index(inplace=True)

results_df = pd.merge(neighbor_means,data, on=['country','year'])

Answer 2

我认为一个直接的方法是把GDP值放进borderDataFrame。那么{cd5>要做什么呢

In [178]:

borderdf[2004]=[datadf2.ix[(item, 2004)].values[0] for item in borderdf.neighbor]
borderdf[2005]=[datadf2.ix[(item, 2005)].values[0] for item in borderdf.neighbor]
gpdf=borderdf.groupby(by=['country']).sum()
df=pd.DataFrame(gpdf.unstack(), columns=['neighborsmeangdp'])
df=df.reset_index()
df=df.rename(columns = {'level_0':'year'})
print pd.ordered_merge(datadf, df)
   country   gdp  year  neighborsmeangdp
0  denmark  2132  2004              7586
1  germany  2132  2004               NaN
2   norway  3433  2004               NaN
3   sweden  5454  2004              5565
4  denmark  2342  2005              8018
5  germany  2342  2005               NaN
6   norway  3433  2005               NaN
7   sweden  5676  2005              5775

[8 rows x 4 columns]

当然，我得为德国编些数据

^{pr2}$

我敢肯定事实上她做得更好。在