英文:
python dataframe: create a column, with dynamic calculation/percentage based on day and month
问题
这是我的数据框。最后两列是我可以在查询中创建的计算。
第一列用于计算所有“Good Result”的百分比
df['FirstCol'] = np.where(df['Result'].isin(['Good']), df['Vol'] / df[df['Result'] == 'Good']['Vol'].sum(), 0)
第二列用于计算所有“Result”的百分比
df['SecondCol'] = df['Vol'] / df['Vol'].sum()
对于其他列，代码需要更加动态，这是我正在努力解决的问题。
第三列应该基于每个月份获得百分比。因此，行0-8的百分比应为100%，行9-17也是如此。
第四列应该基于每天和月份获得百分比。因此，行0-2的百分比应为100%，行3-5以此类推。我想要一个动态查询，因为我不想每个月都要更改代码。
期望的输出如下：
Day Month Result Vol FirstCol SecondCol ThirdCol1-9 MonthlyCol1-30 26 5 Good 123 1% 0% 1% 14%1 26 5 Bad 716 0% 2% 5% 82%2 26 5 Other 36 0% 0% 0% 4%3 26 6 Good 4721 26% 11% 31% 36%4 26 6 Bad 7148 0% 16% 48% 54%5 26 6 Other 1387 0% 3% 9% 10%6 27 5 Good 196 1% 0% 1% 22%7 27 5 Bad 627 0% 1% 4% 71%8 27 5 Other 60 0% 0% 0% 7%9 27 6 Good 6188 34% 14% 21% 44%10 27 6 Bad 6688 0% 15% 23% 48%11 27 6 Other 1068 0% 2% 4% 8%12 28 5 Good 339 2% 1% 1% 22%13 28 5 Bad 1114 0% 3% 4% 73%14 28 5 Other 72 0% 0% 0% 5%15 28 6 Good 6524 36% 15% 23% 49%16 28 6 Bad 6103 0% 14% 21% 45%17 28 6 Other 820 0% 2% 3% 6%
英文:
I am struggling with the following.
This is my data frame. The last two columns, are calculations which I am able to create in my query.
Day Month Result Vol FirstCol Second Col0 26 5 Good 123 1% 0%1 26 5 Bad 716 0% 2%2 26 5 Other 36 0% 0%3 26 6 Good 4721 26% 11%4 26 6 Bad 7148 0% 16%5 26 6 Other 1387 0% 3%6 27 5 Good 196 1% 0%7 27 5 Bad 627 0% 1%8 27 5 Other 60 0% 0%9 27 6 Good 6188 34% 14%10 27 6 Bad 6688 0% 15%11 27 6 Other 1068 0% 2%12 28 5 Good 339 2% 1%13 28 5 Bad 1114 0% 3%14 28 5 Other 72 0% 0%15 28 6 Good 6524 36% 15%16 28 6 Bad 6103 0% 14%17 28 6 Other 820 0% 2%
First column to calculate the percentage for all Good Result
df['FirstCol'] = np.where(df['Result'].isin(['Good']),df['Vol']/df[df['Result']=='Good']['Vol'].sum(),0)
Second column to calculate the percentage for all Result
df['SecondCol'] = df['Vol']/df['Vol'].sum()
For the other the code has to be more dynamic, which I am struggling with.
The Third Column should get the % based on each Month. So the percentage for row 0-8 should be 100% and the same for row 9-17.
The Forth Column should get the % based on each Day and Month. So the percentage for row 0-2 should be 100% and the same for row 3-5 and so on. I want to have a dynamic query. Because I don't want to change each month.
Desired OutputDay Month Result Vol FirstCol Second Col 0-17 Third Col 1-9 (Month) Forth Col 1-3 (Day)0 26 5 Good 123 1% 0% 1% 14%1 26 5 Bad 716 0% 2% 5% 82%2 26 5 Other 36 0% 0% 0% 4%3 26 6 Good 4721 26% 11% 31% 36%4 26 6 Bad 7148 0% 16% 48% 54%5 26 6 Other 1387 0% 3% 9% 10%6 27 5 Good 196 1% 0% 1% 22%7 27 5 Bad 627 0% 1% 4% 71%8 27 5 Other 60 0% 0% 0% 7%9 27 6 Good 6188 34% 14% 21% 44%10 27 6 Bad 6688 0% 15% 23% 48%11 27 6 Other 1068 0% 2% 4% 8%12 28 5 Good 339 2% 1% 1% 22%13 28 5 Bad 1114 0% 3% 4% 73%14 28 5 Other 72 0% 0% 0% 5%15 28 6 Good 6524 36% 15% 23% 49%16 28 6 Bad 6103 0% 14% 21% 45%17 28 6 Other 820 0% 2% 3% 6%
答案1
得分: 1
# 对数据框进行排序以获得更好的输出：df = df.sort_values(by=['月份', '日期'])df['第三列'] = df.groupby('月份')['容量'].transform(lambda x: (x / x.sum()) * 100)df['第四列'] = df.groupby(['日期', '月份'])['容量'].transform(lambda x: (x / x.sum()) * 100)print(df)
输出：
日期 月份 结果 容量 第一列 第二列 第三列 第四列0 26 5 好 123 1% 0% 3.746573 14.0571431 26 5 差 716 0% 2% 21.809321 81.8285712 26 5 其他 36 0% 0% 1.096558 4.1142866 27 5 好 196 1% 0% 5.970149 22.1970557 27 5 差 627 0% 1% 19.098386 71.0079288 27 5 其他 60 0% 0% 1.827597 6.79501712 28 5 好 339 2% 1% 10.325921 22.22950813 28 5 差 1114 0% 3% 33.932379 73.04918014 28 5 其他 72 0% 0% 2.193116 4.7213113 26 6 好 4721 26% 11% 11.614633 35.6140624 26 6 差 7148 0% 16% 17.585554 53.9227525 26 6 其他 1387 0% 3% 3.412306 10.4631869 27 6 好 6188 34% 14% 15.223756 44.37751010 27 6 差 6688 0% 15% 16.453859 47.96328211 27 6 其他 1068 0% 2% 2.627500 7.65920815 28 6 好 6524 36% 15% 16.050385 48.51639816 28 6 差 6103 0% 14% 15.014638 45.38558817 28 6 其他 820 0% 2% 2.017369 6.098014
英文:
If I understand you correctly, you want to groupby by multiple columns:
# sort the dataframe to have nicer output:df = df.sort_values(by=['Month', 'Day'])df['Third Col'] = df.groupby('Month')['Vol'].transform(lambda x: (x / x.sum()) *100)df['Fourth Col'] = df.groupby(['Day', 'Month'])['Vol'].transform(lambda x: (x / x.sum())*100)print(df)
Prints:
Day Month Result Vol FirstCol Second Col Third Col Fourth Col0 26 5 Good 123 1% 0% 3.746573 14.0571431 26 5 Bad 716 0% 2% 21.809321 81.8285712 26 5 Other 36 0% 0% 1.096558 4.1142866 27 5 Good 196 1% 0% 5.970149 22.1970557 27 5 Bad 627 0% 1% 19.098386 71.0079288 27 5 Other 60 0% 0% 1.827597 6.79501712 28 5 Good 339 2% 1% 10.325921 22.22950813 28 5 Bad 1114 0% 3% 33.932379 73.04918014 28 5 Other 72 0% 0% 2.193116 4.7213113 26 6 Good 4721 26% 11% 11.614633 35.6140624 26 6 Bad 7148 0% 16% 17.585554 53.9227525 26 6 Other 1387 0% 3% 3.412306 10.4631869 27 6 Good 6188 34% 14% 15.223756 44.37751010 27 6 Bad 6688 0% 15% 16.453859 47.96328211 27 6 Other 1068 0% 2% 2.627500 7.65920815 28 6 Good 6524 36% 15% 16.050385 48.51639816 28 6 Bad 6103 0% 14% 15.014638 45.38558817 28 6 Other 820 0% 2% 2.017369 6.098014
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论