如何在熊猫的多列中获得对行求和的百分比？

Question

我正在尝试获取百分比表格数据，其中我尝试使用 pandas 的交叉表函数，但每列的行明智总和不正确（我用 Excel 总和检查了这一点）。基本上，在我的进出口贸易数据中，我试图获得每个国家的时期百分比。

表格数据：

这是关于公共要点的表格数据，我想按时期获得每个国家/地区的百分比。

为了获得按列计算的总和，我这样做了：

import pandas as pd

df=pd.read_csv('minimal_data.csv', encoding='utf-8')

df.loc[:,'Total'] = df.sum(axis=1)

但是这个总和和做excel sum的方式不一样。我不知道为什么。

然后我尝试以下获取百分比表格数据：

pd.crosstab(index=df.index, 

                     columns=df.columns, 

                     values=df.columns.value, 

                     aggfunc='sum', 

                     normalize='index').applymap('{:.2f}%'.format)

我期待表格数据的百分比，其中每个国家按时期的百分比。我不知道为什么，在我的尝试中，我没有得到正确的总和和预期百分比表。谁能指出我？有什么快速的解决方案可以完成这项工作吗？

我认为 usingcrosstab就在这里，但我没有通过保持相同的行和列名称约定得到正确的百分比表。任何想法使这项工作？

Answer 1

目前尚不清楚您所说的“总和”错误或与 Excel 不同是什么意思。如果你想要你计算的总数的百分比，你可以这样做（它会更容易，即，不需要设置索引，如果你已经阅读了日期作为索引的 csv）：df = df.set_index('quarter')df.div(df.Total, axis=0).applymap(lambda x: f'{x * 100:.2f}%')

Answer 2

要获得百分比，df.set_index('quarter').apply(lambda x: (x / x.sum())*100, axis=1)输出&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AUSTRAL&nbsp; &nbsp; &nbsp;CANADA&nbsp; &nbsp; &nbsp;N ZEAL&nbsp; &nbsp; &nbsp;MEXICO&nbsp; &nbsp;NICARAG&nbsp; &nbsp;URUGUAY&nbsp; &nbsp; C RICA&nbsp; &nbsp; BRAZIL&nbsp; &nbsp;HONDURA&nbsp; &nbsp;IRELANDquarter&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2014-01-01&nbsp; 25.440018&nbsp; 25.682501&nbsp; 26.799560&nbsp; 13.356812&nbsp; 4.645008&nbsp; 2.502126&nbsp; 1.185601&nbsp; 0.000000&nbsp; 0.388373&nbsp; 0.0000002014-04-01&nbsp; 34.489028&nbsp; 20.473965&nbsp; 27.223601&nbsp; 10.739338&nbsp; 3.545756&nbsp; 2.637722&nbsp; 0.645318&nbsp; 0.000000&nbsp; 0.245270&nbsp; 0.0000002014-07-01&nbsp; 41.388462&nbsp; 19.418827&nbsp; 17.413776&nbsp; 13.046643&nbsp; 4.365293&nbsp; 3.062794&nbsp; 1.000460&nbsp; 0.000000&nbsp; 0.303746&nbsp; 0.0000002014-10-01&nbsp; 45.921175&nbsp; 19.947340&nbsp; 12.453399&nbsp; 10.987784&nbsp; 6.659666&nbsp; 2.472346&nbsp; 1.220976&nbsp; 0.000000&nbsp; 0.337314&nbsp; 0.0000002015-01-01&nbsp; 34.779864&nbsp; 18.914200&nbsp; 23.802183&nbsp; 12.789158&nbsp; 4.607413&nbsp; 3.750432&nbsp; 1.113557&nbsp; 0.000000&nbsp; 0.242027&nbsp; 0.0011662015-04-01&nbsp; 40.115581&nbsp; 15.889617&nbsp; 24.620569&nbsp; 12.233570&nbsp; 2.614697&nbsp; 3.684628&nbsp; 0.669135&nbsp; 0.000000&nbsp; 0.140994&nbsp; 0.0312102015-07-01&nbsp; 44.545033&nbsp; 19.933480&nbsp; 16.419047&nbsp; 13.207045&nbsp; 1.903940&nbsp; 3.151725&nbsp; 0.706372&nbsp; 0.000000&nbsp; 0.000000&nbsp; 0.1333572015-10-01&nbsp; 36.019231&nbsp; 25.727244&nbsp; 12.442655&nbsp; 16.527229&nbsp; 4.201449&nbsp; 3.803939&nbsp; 0.998293&nbsp; 0.000000&nbsp; 0.000000&nbsp; 0.2799612016-01-01&nbsp; 29.991387&nbsp; 22.293687&nbsp; 24.963800&nbsp; 15.665886&nbsp; 3.364758&nbsp; 2.537703&nbsp; 0.964889&nbsp; 0.000000&nbsp; 0.000000&nbsp; 0.2178902016-04-01&nbsp; 28.368131&nbsp; 22.124064&nbsp; 26.707744&nbsp; 16.011170&nbsp; 2.974021&nbsp; 2.736466&nbsp; 0.902486&nbsp; 0.000000&nbsp; 0.008214&nbsp; 0.1677042016-07-01&nbsp; 25.368992&nbsp; 28.843584&nbsp; 17.562638&nbsp; 18.601159&nbsp; 4.361163&nbsp; 4.197427&nbsp; 0.900461&nbsp; 0.001082&nbsp; 0.000000&nbsp; 0.1634942016-10-01&nbsp; 19.623932&nbsp; 30.095599&nbsp; 11.720699&nbsp; 27.695783&nbsp; 5.386881&nbsp; 3.950341&nbsp; 1.098037&nbsp; 0.262948&nbsp; 0.000000&nbsp; 0.1657802017-01-01&nbsp; 20.799706&nbsp; 22.871970&nbsp; 23.475104&nbsp; 23.519770&nbsp; 4.726189&nbsp; 2.564349&nbsp; 1.105563&nbsp; 0.777981&nbsp; 0.000000&nbsp; 0.1593662017-04-01&nbsp; 20.961391&nbsp; 24.807151&nbsp; 22.372555&nbsp; 20.141108&nbsp; 4.201882&nbsp; 3.848614&nbsp; 0.717434&nbsp; 2.847786&nbsp; 0.000000&nbsp; 0.1020792017-07-01&nbsp; 26.326774&nbsp; 27.124571&nbsp; 16.796464&nbsp; 20.485338&nbsp; 4.180663&nbsp; 3.973982&nbsp; 0.748360&nbsp; 0.050250&nbsp; 0.122305&nbsp; 0.1912922017-10-01&nbsp; 26.996354&nbsp; 29.432880&nbsp; 11.569669&nbsp; 22.702213&nbsp; 5.579304&nbsp; 2.623607&nbsp; 0.794317&nbsp; 0.000000&nbsp; 0.156468&nbsp; 0.1451882018-01-01&nbsp; 20.148823&nbsp; 25.861165&nbsp; 24.566617&nbsp; 19.748647&nbsp; 5.864245&nbsp; 2.507594&nbsp; 0.946862&nbsp; 0.000000&nbsp; 0.218396&nbsp; 0.1376502018-04-01&nbsp; 22.281189&nbsp; 26.300865&nbsp; 24.879217&nbsp; 18.074004&nbsp; 4.368848&nbsp; 3.058836&nbsp; 0.757353&nbsp; 0.000000&nbsp; 0.196459&nbsp; 0.0832292018-07-01&nbsp; 24.996713&nbsp; 28.873588&nbsp; 16.749910&nbsp; 19.016680&nbsp; 5.816461&nbsp; 3.499820&nbsp; 0.757308&nbsp; 0.000000&nbsp; 0.140196&nbsp; 0.1493242018-10-01&nbsp; 25.305780&nbsp; 31.831372&nbsp; &nbsp;9.842619&nbsp; 22.351502&nbsp; 6.039240&nbsp; 3.353802&nbsp; 0.824540&nbsp; 0.000000&nbsp; 0.236478&nbsp; 0.214668在折线图中绘制>>> df.plot(kind='line')<matplotlib.axes._subplots.AxesSubplot object at 0x7f418a3710b8>>>> from matplotlib import pyplot as plt>>> plt.show()