英文:
total by rows of pivot table
问题
以下是您要翻译的内容：
"我有以下数据框（CSV）
item_group item_code total_qty total_amount cost_center
0 Drink IC06-1P 1 3.902 Cafe II
1 Drink IC09-1 1 2.927 Cafe II
2 BreakFast FS04-2 1 6.463 Cafe II
3 Drink IC08-1 1 2.927 Cafe II
4 Drink DT05-1 1 2.561 Cafe II
.. ... ... ... ... ...
79 Standard Food FS01-2 12 83.412 Cafe II
80 Standard Food FS01-1 13 101.465 Cafe II
81 Drink IC05-1 14 54.628 Cafe I
82 Standard Food FS01-2 35 243.285 Cafe I
83 Standard Food FS01-1 44 343.420 Cafe I
我正在尝试进行数据透视
# 导入pandas
import pandas as pd
# 读取CSV文件
df = pd.read_csv("data.csv")
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df1 = df.pivot_table(index=['item_group','item_code'],
columns=['cost_center'],
values=['total_qty','total_amount'],fill_value=0,
aggfunc='sum').swaplevel(axis=1).sort_index(level=0, axis=1)
out = df1.join(pd.concat({'total': df1.groupby(axis=1, level=1).sum()}, axis=1))
print(out)
结果
cost_center Cafe I - 103cafe ... total
total_amount total_qty ... total_amount total_qty
item_group item_code ...
Add Food AD001 1.952 4 ... 1.952 4.0
AD002 0.976 2 ... 0.976 2.0
AD003 1.952 4 ... 1.952 4.0
AD004 5.000 5 ... 5.000 5.0
AD007 0.976 2 ... 0.976 2.0
... ... ... ... ... ...
Standard Food FS01-2 243.285 35 ... 326.697 47.0
FS01-2P 20.853 3 ... 27.804 4.0
FS01-6 6.951 1 ... 6.951 1.0
FS02-1 19.389 3 ... 25.852 4.0
FS02-2 6.463 1 ... 25.852 4.0
例如，我想计算group_total（每个item_group的总和）
cost_center Cafe I Cafe II \...
total_amount total_qty total_amount
item_group item_code
Add Food AD001 1.952 4 0.000
AD002 0.976 2 0.000
->group_total 2.928 6 0.000
BreakFast FN10-1 5.000 1 0.000
FN10-1P 5.000 1 0.000
->group_total 10.00 2 0.000"
英文:
I have the following data frame (csv)
item_group item_code total_qty total_amount cost_center
0 Drink IC06-1P 1 3.902 Cafe II
1 Drink IC09-1 1 2.927 Cafe II
2 BreakFast FS04-2 1 6.463 Cafe II
3 Drink IC08-1 1 2.927 Cafe II
4 Drink DT05-1 1 2.561 Cafe II
.. ... ... ... ... ...
79 Standard Food FS01-2 12 83.412 Cafe II
80 Standard Food FS01-1 13 101.465 Cafe II
81 Drink IC05-1 14 54.628 Cafe I
82 Standard Food FS01-2 35 243.285 Cafe I
83 Standard Food FS01-1 44 343.420 Cafe I
I am trying to do a pivot
# Import pandas
import pandas as pd
# Reading CSV file
df = pd.read_csv("data.csv")
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df1 = df.pivot_table(index=['item_group','item_code'],
columns=['cost_center'],
values=['total_qty','total_amount'],fill_value=0,
aggfunc='sum').swaplevel(axis=1).sort_index(level=0, axis=1)
out = df1.join(pd.concat({'total': df1.groupby(axis=1, level=1).sum()}, axis=1))
print(out)
Result
cost_center Cafe I - 103cafe ... total
total_amount total_qty ... total_amount total_qty
item_group item_code ...
Add Food AD001 1.952 4 ... 1.952 4.0
AD002 0.976 2 ... 0.976 2.0
AD003 1.952 4 ... 1.952 4.0
AD004 5.000 5 ... 5.000 5.0
AD007 0.976 2 ... 0.976 2.0
... ... ... ... ... ...
Standard Food FS01-2 243.285 35 ... 326.697 47.0
FS01-2P 20.853 3 ... 27.804 4.0
FS01-6 6.951 1 ... 6.951 1.0
FS02-1 19.389 3 ... 25.852 4.0
FS02-2 6.463 1 ... 25.852 4.0
Example, I want to calculate the group_total (total by each item_group)
cost_center Cafe I Cafe II \...
total_amount total_qty total_amount
item_group item_code
Add Food AD001 1.952 4 0.000
AD002 0.976 2 0.000
->group_total 2.928 6 0.000
BreakFast FN10-1 5.000 1 0.000
FN10-1P 5.000 1 0.000
->group_total 10.00 2 0.000
答案1
得分: 1
I can help you translate the provided code snippet into Chinese:
也许你可以从这个链接中获取灵感
https://www.statology.org/pandas-pivot-table-subtotals/
import pandas as pd
df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
'position': ['G', 'G', 'F', 'F', 'G', 'F', 'F', 'F'],
'all_star': ['Y', 'N', 'Y', 'Y', 'N', 'N', 'N', 'Y'],
'points': [4, 4, 6, 8, 9, 5, 5, 12]})
my_table = pd.pivot_table(df, values='points',
index=['team', 'all_star'],
columns='position',
aggfunc='sum')
pd.concat([
y.append(y.sum().rename((x, '总计')))
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/
import pandas as pd
df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
'position': ['G', 'G', 'F', 'F', 'G', 'F', 'F', 'F'],
'all_star': ['Y', 'N', 'Y', 'Y', 'N', 'N', 'N', 'Y'],
'points': [4, 4, 6, 8, 9, 5, 5, 12]})
my_table = pd.pivot_table(df, values='points',
index=['team', 'all_star'],
columns='position',
aggfunc='sum')
pd.concat([
y.append(y.sum().rename((x, 'Total')))
for x, y in my_table.groupby(level=0)])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论