Skip to main content
 首页 » 编程设计

Python:如何将复杂的 SQL 聚合语句翻译成 pandas

2024年10月25日9三少

对于 pandas,创建等效于 SQL group by 语句的最佳方法是什么,使用:

  • 每个字段上的不同聚合函数(例如,我需要总和 field1,field2的平均值和field3的最大值)
  • 稍微多一点 复杂的计算,如 sum(field1)/sum(field2),例如对于加权 平均值

假设我有一个包含城市级别数据的表,我想按国家和地区汇总它。在 SQL 中我会写:

select Country, Region 
, count(*) as '# of cities' 
,sum(GDP) as GDP 
,avg(Population) as 'avg # inhabitants per city' 
,sum(male_population) / sum(Population) as '% of male population' 
from CityTable 
group by Country, Region 

我怎样才能在 pandas 中做同样的事情?谢谢!

请您参考如下方法:

>>> df 
   Country  Region  GDP  Population  male_population 
0      USA      TX   10         100               50 
1      USA      TX   11         120               60 
2      USA      KY   11         200              120 
3  Austria  Wienna    5          50               34 
>>> 
>>> df2 = df.groupby(['Country','Region']).agg({'GDP': [np.size, np.sum], 'Population': [np.average, np.sum], 'male_population': np.sum}) 
>>> df2 
                GDP     male_population Population      
               size sum             sum    average  sum 
Country Region                                          
Austria Wienna    1   5              34         50   50 
USA     KY        1  11             120        200  200 
        TX        2  21             110        110  220 
>>> 
>>> df2['% of male population'] = df2['male_population','sum'].divide(df2['Population','sum']) 
>>> df2 
                GDP     male_population Population      % of male population 
               size sum             sum    average  sum                      
Country Region                                                               
Austria Wienna    1   5              34         50   50                 0.68 
USA     KY        1  11             120        200  200                 0.60 
        TX        2  21             110        110  220                 0.50 
>>> 
>>> del df2['male_population', 'sum'] 
>>> del df2['Population', 'sum'] 
>>> df2.columns = ['# of cities', 'GDP', 'avg # inhabitants per city', '% of male population'] 

结果

>>> df2 
                # of cities  GDP  avg # inhabitants per city  % of male population 
Country Region                                                                     
Austria Wienna            1    5                          50                  0.68 
USA     KY                1   11                         200                  0.60 
        TX                2   21                         110                  0.50