9. 用apply计算每州的加权平均SAT分数

  1. #  读取college，'UGDS', 'SATMTMID', 'SATVRMID'三列如果有缺失值则删除行
  2.  In[74]: college = pd.read_csv('data/college.csv')
  3.          subset = ['UGDS', 'SATMTMID', 'SATVRMID']
  4.          college2 = college.dropna(subset=subset)
  5.          college.shape
  6. Out[74]: (7535, 27)
  8.  In[75]: college2.shape
  9. Out[75]: (1184, 27)
  1. #  自定义一个求SAT数学成绩的加权平均值的函数
  2.  In[76]: def weighted_math_average(df):
  3.              weighted_math = df['UGDS'] * df['SATMTMID']
  4.              return int(weighted_math.sum() / df['UGDS'].sum())
  5. #  按州分组，并调用apply方法，传入自定义函数
  6.  In[77]: college2.groupby('STABBR').apply(weighted_math_average).head()
  7. Out[77]: STABBR
  8.          AK    503
  9.          AL    536
  10.          AR    529
  11.          AZ    569
  12.          CA    564
  13.          dtype: int64
  1. #  效果同上
  2.  In[78]: college2.groupby('STABBR').agg(weighted_math_average).head()
  3. Out[78]:

9. 用apply计算每州的加权平均SAT分数 - 图1

  1. #  如果将列限制到SATMTMID，会报错。这是因为不能访问UGDS。
  2.  In[79]: college2.groupby('STABBR')['SATMTMID'].agg(weighted_math_average)
  3. ---------------------------------------------------------------------------
  4. TypeError                                 Traceback (most recent call last)
  5. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc (pandas/_libs/index.c:5126)()
  7. pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item (pandas/_libs/hashtable.c:14010)()
  9. TypeError: an integer is required
  11. During handling of the above exception, another exception occurred:
  13. KeyError                                  Traceback (most recent call last)
  14. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in agg_series(self, obj, func)
  15.   2177         try:
  16. -> 2178             return self._aggregate_series_fast(obj, func)
  17.   2179         except Exception:
  19. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in _aggregate_series_fast(self, obj, func)
  20.   2197                                     dummy)
  21. -> 2198         result, counts = grouper.get_result()
  22.   2199         return result, counts
  24. pandas/_libs/src/reduce.pyx in pandas._libs.lib.SeriesGrouper.get_result (pandas/_libs/lib.c:39105)()
  26. pandas/_libs/src/reduce.pyx in pandas._libs.lib.SeriesGrouper.get_result (pandas/_libs/lib.c:38888)()
  28. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in <lambda>(x)
  29.    841         func = self._is_builtin_func(func)
  30. --> 842         f = lambda x: func(x, *args, **kwargs)
  31.    843 
  33. <ipython-input-76-01eb90aa258d> in weighted_math_average(df)
  34.      1 def weighted_math_average(df):
  35. ----> 2     weighted_math = df['UGDS'] * df['SATMTMID']
  36.      3     return int(weighted_math.sum() / df['UGDS'].sum())
  38. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/series.py in __getitem__(self, key)
  39.    600         try:
  40. --> 601             result = self.index.get_value(self, key)
  41.    602 
  43. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/indexes/base.py in get_value(self, series, key)
  44.   2476             return self._engine.get_value(s, k,
  45. -> 2477                                           tz=getattr(series.dtype, 'tz', None))
  46.   2478         except KeyError as e1:
  48. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value (pandas/_libs/index.c:4404)()
  50. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value (pandas/_libs/index.c:4087)()
  52. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc (pandas/_libs/index.c:5210)()
  54. KeyError: 'UGDS'
  56. During handling of the above exception, another exception occurred:
  58. TypeError                                 Traceback (most recent call last)
  59. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc (pandas/_libs/index.c:5126)()
  61. pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item (pandas/_libs/hashtable.c:14010)()
  63. TypeError: an integer is required
  65. During handling of the above exception, another exception occurred:
  67. KeyError                                  Traceback (most recent call last)
  68. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in aggregate(self, func_or_funcs, *args, **kwargs)
  69.   2882             try:
  70. -> 2883                 return self._python_agg_general(func_or_funcs, *args, **kwargs)
  71.   2884             except Exception:
  73. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in _python_agg_general(self, func, *args, **kwargs)
  74.    847             try:
  75. --> 848                 result, counts = self.grouper.agg_series(obj, f)
  76.    849                 output[name] = self._try_cast(result, obj, numeric_only=True)
  78. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in agg_series(self, obj, func)
  79.   2179         except Exception:
  80. -> 2180             return self._aggregate_series_pure_python(obj, func)
  81.   2181 
  83. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in _aggregate_series_pure_python(self, obj, func)
  84.   2210         for label, group in splitter:
  85. -> 2211             res = func(group)
  86.   2212             if result is None:
  88. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in <lambda>(x)
  89.    841         func = self._is_builtin_func(func)
  90. --> 842         f = lambda x: func(x, *args, **kwargs)
  91.    843 
  93. <ipython-input-76-01eb90aa258d> in weighted_math_average(df)
  94.      1 def weighted_math_average(df):
  95. ----> 2     weighted_math = df['UGDS'] * df['SATMTMID']
  96.      3     return int(weighted_math.sum() / df['UGDS'].sum())
  98. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/series.py in __getitem__(self, key)
  99.    600         try:
  100. --> 601             result = self.index.get_value(self, key)
  101.    602 
  103. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/indexes/base.py in get_value(self, series, key)
  104.   2476             return self._engine.get_value(s, k,
  105. -> 2477                                           tz=getattr(series.dtype, 'tz', None))
  106.   2478         except KeyError as e1:
  108. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value (pandas/_libs/index.c:4404)()
  110. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value (pandas/_libs/index.c:4087)()
  112. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc (pandas/_libs/index.c:5210)()
  114. KeyError: 'UGDS'
  116. During handling of the above exception, another exception occurred:
  118. TypeError                                 Traceback (most recent call last)
  119. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc (pandas/_libs/index.c:5126)()
  121. pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item (pandas/_libs/hashtable.c:14010)()
  123. TypeError: an integer is required
  125. During handling of the above exception, another exception occurred:
  127. KeyError                                  Traceback (most recent call last)
  128. <ipython-input-79-1351e4f306c7> in <module>()
  129. ----> 1 college2.groupby('STABBR')['SATMTMID'].agg(weighted_math_average)
  131. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in aggregate(self, func_or_funcs, *args, **kwargs)
  132.   2883                 return self._python_agg_general(func_or_funcs, *args, **kwargs)
  133.   2884             except Exception:
  134. -> 2885                 result = self._aggregate_named(func_or_funcs, *args, **kwargs)
  135.   2886 
  136.   2887             index = Index(sorted(result), name=self.grouper.names[0])
  138. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/groupby.py in _aggregate_named(self, func, *args, **kwargs)
  139.   3013         for name, group in self:
  140.   3014             group.name = name
  141. -> 3015             output = func(group, *args, **kwargs)
  142.   3016             if isinstance(output, (Series, Index, np.ndarray)):
  143.   3017                 raise Exception('Must produce aggregated value')
  145. <ipython-input-76-01eb90aa258d> in weighted_math_average(df)
  146.      1 def weighted_math_average(df):
  147. ----> 2     weighted_math = df['UGDS'] * df['SATMTMID']
  148.      3     return int(weighted_math.sum() / df['UGDS'].sum())
  150. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/series.py in __getitem__(self, key)
  151.    599         key = com._apply_if_callable(key, self)
  152.    600         try:
  153. --> 601             result = self.index.get_value(self, key)
  154.    602 
  155.    603             if not is_scalar(result):
  157. /Users/Ted/anaconda/lib/python3.6/site-packages/pandas/core/indexes/base.py in get_value(self, series, key)
  158.   2475         try:
  159.   2476             return self._engine.get_value(s, k,
  160. -> 2477                                           tz=getattr(series.dtype, 'tz', None))
  161.   2478         except KeyError as e1:
  162.   2479             if len(self) > 0 and self.inferred_type in ['integer', 'boolean']:
  164. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value (pandas/_libs/index.c:4404)()
  166. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value (pandas/_libs/index.c:4087)()
  168. pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc (pandas/_libs/index.c:5210)()
  170. KeyError: 'UGDS'
  1. #  apply的一个不错的功能是通过返回Series，创建多个新的列
  2.  In[80]: from collections import OrderedDict
  3.          def weighted_average(df):
  4.              data = OrderedDict()
  5.              weight_m = df['UGDS'] * df['SATMTMID']
  6.              weight_v = df['UGDS'] * df['SATVRMID']
  8.              data['weighted_math_avg'] = weight_m.sum() / df['UGDS'].sum()
  9.              data['weighted_verbal_avg'] = weight_v.sum() / df['UGDS'].sum()
  10.              data['math_avg'] = df['SATMTMID'].mean()
  11.              data['verbal_avg'] = df['SATVRMID'].mean()
  12.              data['count'] = len(df)
  13.              return pd.Series(data, dtype='int')
  15.          college2.groupby('STABBR').apply(weighted_average).head(10)
  16. Out[80]:

9. 用apply计算每州的加权平均SAT分数 - 图2

  1. #  多创建两个新的列
  2.  In[81]: from collections import OrderedDict
  3.          def weighted_average(df):
  4.              data = OrderedDict()
  5.              weight_m = df['UGDS'] * df['SATMTMID']
  6.              weight_v = df['UGDS'] * df['SATVRMID']
  8.              wm_avg = weight_m.sum() / df['UGDS'].sum()
  9.              wv_avg = weight_v.sum() / df['UGDS'].sum()
  11.              data['weighted_math_avg'] = wm_avg
  12.              data['weighted_verbal_avg'] = wv_avg
  13.              data['math_avg'] = df['SATMTMID'].mean()
  14.              data['verbal_avg'] = df['SATVRMID'].mean()
  15.              data['count'] = len(df)
  16.              return pd.Series(data, dtype='int')
  18.          college2.groupby('STABBR').apply(weighted_average).head(10)
  19. Out[81]:

9. 用apply计算每州的加权平均SAT分数 - 图3

更多

  1. #  自定义一个返回DataFrame的函数，使用NumPy的函数average计算加权平均值，使用SciPy的gmean和hmean计算几何和调和平均值
  2.  In[82]: from scipy.stats import gmean, hmean
  3.          def calculate_means(df):
  4.              df_means = pd.DataFrame(index=['Arithmetic', 'Weighted', 'Geometric', 'Harmonic'])
  5.              cols = ['SATMTMID', 'SATVRMID']
  6.              for col in cols:
  7.                  arithmetic = df[col].mean()
  8.                  weighted = np.average(df[col], weights=df['UGDS'])
  9.                  geometric = gmean(df[col])
  10.                  harmonic = hmean(df[col])
  11.                  df_means[col] = [arithmetic, weighted, geometric, harmonic]
  13.              df_means['count'] = len(df)
  14.              return df_means.astype(int)
  16.          college2.groupby('STABBR').filter(lambda x: len(x) != 1).groupby('STABBR').apply(calculate_means).head(10)
  17. Out[82]:

9. 用apply计算每州的加权平均SAT分数 - 图4