如何快速基于（部件ID、年份、数量）二维数据生成带总计的数据透视表？

我用pypyodbc从SQL表中读取了一组数据（SQL服务器调用的配置和细节此处省略），最终执行sqldata=cur.fetchall()得到结果如下：

sqldata=[
('part00', '2023', 15), ('part01', '2023',  4), ('part02', '2023',  1), ('part00', '2022', 72), 
('part03', '2022', 48), ('part04', '2022', 22), ('part05', '2022', 16), ('part06', '2022', 16), 
('part07', '2022', 15), ('part08', '2022', 12), ('part09', '2022',  6), ('part10', '2022',  4), 
('part01', '2022',  4), ('part11', '2022',  3), ('part12', '2022',  3), ('part13', '2022',  2), 
('part14', '2022',  1), ('part15', '2022',  1), ('part02', '2022',  1), ('part16', '2022',  1), 
('part04', '2021', 35), ('part00', '2021', 20), ('part08', '2021', 18), ('part05', '2021',  3), 
('part07', '2021',  3), ('part09', '2021',  1), ('part16', '2021',  1), ('part03', '2021',  1), 
('part04', '2020', 43), ('part08', '2020', 15), ('part10', '2020',  9), ('part00', '2020',  9), 
('part07', '2020',  6), ('part05', '2020',  4), ('part17', '2020',  4), ('part03', '2020',  4), 
('part18', '2020',  3), ('part19', '2020',  2), ('part16', '2020',  2), ('part12', '2020',  2), 
('part20', '2020',  1), ('part09', '2020',  1), ('part21', '2020',  1), ('part00', '2019', 37), 
('part04', '2019', 18), ('part05', '2019',  8), ('part11', '2019',  5), ('part10', '2019',  4), 
('part22', '2019',  4), ('part16', '2019',  4), ('part23', '2019',  3), ('part03', '2019',  3), 
('part24', '2019',  3), ('part25', '2019',  2), ('part09', '2019',  2), ('part21', '2019',  2), 
('part20', '2019',  1), ('part13', '2019',  1), ('part14', '2019',  1), ('part19', '2019',  1), 
('part12', '2019',  1), ('part01', '2019',  1), ('part26', '2019',  1), ('part15', '2019',  1), 
('part04', '2018', 47), ('part00', '2018', 27), ('part13', '2018', 10), ('part12', '2018',  6), 
('part07', '2018',  4), ('part21', '2018',  3), ('part09', '2018',  2), ('part05', '2018',  2), 
('part15', '2018',  1), ('part04', '2017', 37), ('part00', '2017', 17), ('part12', '2017', 15), 
('part13', '2017',  7), ('part19', '2017',  6), ('part25', '2017',  2), ('part07', '2017',  2), 
('part03', '2017',  2), ('part27', '2017',  2), ('part21', '2017',  1), ('part09', '2017',  1), 
('part28', '2017',  1), ('part04', '2016', 49), ('part00', '2016', 29), ('part12', '2016', 12), 
('part02', '2016',  5), ('part15', '2016',  2), ('part24', '2016',  2), ('part08', '2016',  2), 
('part29', '2016',  1), ('part01', '2016',  1), ('part21', '2016',  1), ('part13', '2016',  1), 
('part28', '2016',  1), ('part25', '2016',  1), ('part04', '2015', 30), ('part00', '2015', 15), 
('part15', '2015',  8), ('part28', '2015',  5), ('part09', '2015',  4), ('part12', '2015',  2), 
('part02', '2015',  2), ('part01', '2015',  1), ('part13', '2015',  1), ('part29', '2015',  1), 
('part00', '2014',  6), ('part12', '2014',  2), ('part28', '2014',  1)
]

我现在的做法是先循环整理数据顺序，存入numpy数组，再遍历生成如下表格：

2023  2022  2021  2020  2019  2018  2017  2016  2015
Part00:      -    72    20     9    37    27    17    29    15  . 226
Part01:     15     4     -     -     1     -     -     1     1  . 22
Part02:      4     1     -     -     -     -     -     5     2  . 12
Part03:      1     -     1     4     3     -     2     -     -  . 11
Part04:      -    48    35    43    18    47    37    49    30  . 307
Part05:      -    22     3     4     8     2     -     -     -  . 39
Part06:      -    16     -     -     -     -     -     -     -  . 16
Part07:      -    16     3     6     -     4     2     -     -  . 31
Part08:      -    15    18    15     -     -     -     2     -  . 50
Part09:      -    12     1     1     2     2     1     -     4  . 23
Part10:      -     6     -     9     4     -     -     -     -  . 19
Part11:      -     4     -     -     5     -     -     -     -  . 9
...
Total: 908

肯定有更简洁的实现方法，我看pandas官方索引文档但不知道怎么入手。

用pandas可以几行代码搞定，步骤如下：

1. 导入pandas并转换数据为DataFrame
   先把元组列表转换成DataFrame，指定列名：

   import pandas as pd
   
   df = pd.DataFrame(sqldata, columns=['Part', 'Year', 'Value'])
   

2. 生成透视表
   用pivot_table把数据整理成以Part为行、Year为列的结构，空值先填充为0（后续替换成'-'）：

   pivot_df = df.pivot_table(index='Part', columns='Year', values='Value', fill_value=0)
   

3. 调整年份顺序
   按照目标表格的年份顺序（从新到旧，只保留2015-2023）：

   target_years = ['2023', '2022', '2021', '2020', '2019', '2018', '2017', '2016', '2015']
   pivot_df = pivot_df[target_years]
   

4. 添加每行小计
   计算每行的总和，作为新列：

   pivot_df['. Total'] = pivot_df.sum(axis=1)
   

5. 添加总计行
   计算每列的总和，追加到DataFrame末尾：

   total_row = pivot_df.sum(axis=0).rename('Total:')
   pivot_df = pd.concat([pivot_df, total_row.to_frame().T])
   

6. 替换空值（0）为'-'
   把非总计行的0值替换成'-'，总计行数值保留：

   pivot_df.loc[pivot_df.index != 'Total:', target_years] = pivot_df.loc[pivot_df.index != 'Total:', target_years].replace(0, '-')
   

7. 格式化输出
   用to_string()输出成目标格式，调整对齐方式：

   print(pivot_df.to_string(index=True, col_space=6, float_format='%d'))
   

运行以上代码后，就能得到和目标一致的表格，比numpy循环更高效，代码也更简洁。

内容的提问来源于stack exchange，提问作者Hannu