透视表按行总计

huangapple
go评论93阅读模式
英文:

total by rows of pivot table

问题

以下是您要翻译的内容：

"我有以下数据框（CSV）

  1. item_group item_code  total_qty  total_amount        cost_center
  2. 0           Drink   IC06-1P          1         3.902  Cafe II
  3. 1           Drink    IC09-1          1         2.927  Cafe II 
  4. 2       BreakFast    FS04-2          1         6.463  Cafe II
  5. 3           Drink    IC08-1          1         2.927  Cafe II
  6. 4           Drink    DT05-1          1         2.561  Cafe II
  7. ..            ...       ...        ...           ...                ...
  8. 79  Standard Food    FS01-2         12        83.412  Cafe II
  9. 80  Standard Food    FS01-1         13       101.465  Cafe II
  10. 81          Drink    IC05-1         14        54.628   Cafe I
  11. 82  Standard Food    FS01-2         35       243.285   Cafe I
  12. 83  Standard Food    FS01-1         44       343.420   Cafe I

我正在尝试进行数据透视

  1. # 导入pandas
  2. import pandas as pd
  4. # 读取CSV文件
  6. df = pd.read_csv("data.csv")
  7. pd.set_option('display.max_rows', None)
  8. pd.set_option('display.max_columns', None)
  10. df1 = df.pivot_table(index=['item_group','item_code'],
  11.                     columns=['cost_center'],
  12.                     values=['total_qty','total_amount'],fill_value=0,
  13.                     aggfunc='sum').swaplevel(axis=1).sort_index(level=0, axis=1)
  14.     
  15. out = df1.join(pd.concat({'total': df1.groupby(axis=1, level=1).sum()}, axis=1))
  16. print(out)

结果

  1. cost_center             Cafe I - 103cafe            ...        total          
  2.                             total_amount total_qty  ... total_amount total_qty
  3. item_group    item_code                             ...                       
  4. Add Food      AD001                1.952         4  ...        1.952       4.0
  5.               AD002                0.976         2  ...        0.976       2.0
  6.               AD003                1.952         4  ...        1.952       4.0
  7.               AD004                5.000         5  ...        5.000       5.0
  8.               AD007                0.976         2  ...        0.976       2.0
  9. ...                                  ...       ...  ...          ...       ...
  10. Standard Food FS01-2             243.285        35  ...      326.697      47.0
  11.               FS01-2P             20.853         3  ...       27.804       4.0
  12.               FS01-6               6.951         1  ...        6.951       1.0
  13.               FS02-1              19.389         3  ...       25.852       4.0
  14.               FS02-2               6.463         1  ...       25.852       4.0

例如，我想计算group_total（每个item_group的总和）

  1. cost_center                       Cafe I                   Cafe II \...
  2.                             total_amount total_qty      total_amount   
  3. item_group    item_code                                                  
  4. Add Food      AD001                1.952         4             0.000   
  5.               AD002                0.976         2             0.000   
  6. ->group_total                      2.928         6             0.000
  7. BreakFast     FN10-1               5.000         1             0.000   
  8.               FN10-1P              5.000         1             0.000   
  9. ->group_total                      10.00         2             0.000"
英文:

I have the following data frame (csv)

  1. item_group item_code  total_qty  total_amount        cost_center
  2. 0           Drink   IC06-1P          1         3.902  Cafe II
  3. 1           Drink    IC09-1          1         2.927  Cafe II 
  4. 2       BreakFast    FS04-2          1         6.463  Cafe II
  5. 3           Drink    IC08-1          1         2.927  Cafe II
  6. 4           Drink    DT05-1          1         2.561  Cafe II
  7. ..            ...       ...        ...           ...                ...
  8. 79  Standard Food    FS01-2         12        83.412  Cafe II
  9. 80  Standard Food    FS01-1         13       101.465  Cafe II
  10. 81          Drink    IC05-1         14        54.628   Cafe I
  11. 82  Standard Food    FS01-2         35       243.285   Cafe I
  12. 83  Standard Food    FS01-1         44       343.420   Cafe I

I am trying to do a pivot

  1. # Import pandas
  2. import pandas as pd
  4. # Reading CSV file
  6. df = pd.read_csv("data.csv")
  7. pd.set_option('display.max_rows', None)
  8. pd.set_option('display.max_columns', None)
  10. df1 = df.pivot_table(index=['item_group','item_code'],
  11.                     columns=['cost_center'],
  12.                     values=['total_qty','total_amount'],fill_value=0,
  13.                     aggfunc='sum').swaplevel(axis=1).sort_index(level=0, axis=1)
  14.     
  15. out = df1.join(pd.concat({'total': df1.groupby(axis=1, level=1).sum()}, axis=1))
  16. print(out)

Result

  1. cost_center             Cafe I - 103cafe            ...        total          
  2.                             total_amount total_qty  ... total_amount total_qty
  3. item_group    item_code                             ...                       
  4. Add Food      AD001                1.952         4  ...        1.952       4.0
  5.               AD002                0.976         2  ...        0.976       2.0
  6.               AD003                1.952         4  ...        1.952       4.0
  7.               AD004                5.000         5  ...        5.000       5.0
  8.               AD007                0.976         2  ...        0.976       2.0
  9. ...                                  ...       ...  ...          ...       ...
  10. Standard Food FS01-2             243.285        35  ...      326.697      47.0
  11.               FS01-2P             20.853         3  ...       27.804       4.0
  12.               FS01-6               6.951         1  ...        6.951       1.0
  13.               FS02-1              19.389         3  ...       25.852       4.0
  14.               FS02-2               6.463         1  ...       25.852       4.0

Example, I want to calculate the group_total (total by each item_group)

  1. cost_center                       Cafe I                   Cafe II \...
  2.                             total_amount total_qty      total_amount   
  3. item_group    item_code                                                
  4. Add Food      AD001                1.952         4             0.000   
  5.               AD002                0.976         2             0.000   
  6. ->group_total                      2.928         6             0.000
  7. BreakFast     FN10-1               5.000         1             0.000   
  8.               FN10-1P              5.000         1             0.000   
  9. ->group_total                      10.00         2             0.000

答案1

得分: 1

I can help you translate the provided code snippet into Chinese:

  1. 也许你可以从这个链接中获取灵感
  3. https://www.statology.org/pandas-pivot-table-subtotals/
  5. import pandas as pd
  7. df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
  8.                    'position': ['G', 'G', 'F', 'F', 'G', 'F', 'F', 'F'],
  9.                    'all_star': ['Y', 'N', 'Y', 'Y', 'N', 'N', 'N', 'Y'],
  10.                    'points': [4, 4, 6, 8, 9, 5, 5, 12]})
  12. my_table = pd.pivot_table(df, values='points',
  13.                               index=['team', 'all_star'],
  14.                               columns='position',
  15.                               aggfunc='sum')
  17. pd.concat([
  18.     y.append(y.sum().rename((x, '总计')))
  19.     for x, y in my_table.groupby(level=0)])

Please note that code translations can sometimes vary depending on the context and specific requirements, so make sure to review it for your needs.

英文:

Perhaps you could take inspiration from this link

https://www.statology.org/pandas-pivot-table-subtotals/

  1. import pandas as pd
  3. df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
  4.                    'position': ['G', 'G', 'F', 'F', 'G', 'F', 'F', 'F'],
  5.                    'all_star': ['Y', 'N', 'Y', 'Y', 'N', 'N', 'N', 'Y'],
  6.                    'points': [4, 4, 6, 8, 9, 5, 5, 12]})
  7.  
  8. my_table = pd.pivot_table(df, values='points',
  9.                               index=['team', 'all_star'],
  10.                               columns='position',
  11.                               aggfunc='sum')
  13. pd.concat([
  14.     y.append(y.sum().rename((x, 'Total')))
  15.     for x, y in my_table.groupby(level=0)])

透视表按行总计

huangapple
  • 本文由 发表于 2023年4月19日 19:27:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/76053948.html
  • dataframe
  • pandas
  • pivot-table
  • python-3.x
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定