主页 / 问题 / 1140053
Accepted
Pavel
Asked:2020-06-12 17:58:19 +0000 UTC2020-06-12 17:58:19 +0000 UTC

如何计算每个元素与某个数的比例？

  • 772

卡在一个难题上，如何漂亮地计算每个元素与某个数字的比例。

有一个数据框：

                               2017     %_2017      2018       %_2018   2019       %_2019
LONG-TERM ASSETS               779,179      0.00    1,004,790   28.95   1,163,862   15.83
CURRENT ASSETS                 23,540,367   0.00    39,743,268  68.83   47,939,841  20.62
CAPITAL AND RESERVES           1,917,017    0.00    2,650,657   38.27   3,520,344   32.81
Borrowed funds (long-term)       401,343    0.00    828,342     106.39  2,115,672   155.41
Borrowed funds (short-term)    1,828,969    0.00    2,241,601   22.56   962,358     -57.07
Total LIABILITIES              24,320,474   0.00    40,747,718  67.54   49,600,194  21.73
Revenue                        83,126,293   0.00    125,638,096 51.14   154,129,923 22.68
Operating profit (loss)         2,044,700   0.00    2,398,963   17.33   4,630,956   93.04
Interest expense                   72,849   0.00    60,733      -16.63    128,529   111.63
Pre-tax profit (loss)            2,120,085  0.00    2,912,776   37.39   4,992,523   71.40
Net profit(loss)                 1,710,746  0.00    2,454,176   43.46   4,267,316   73.88

我想以百分比的形式向每年添加一列，以便将上面Total LIABILITIES的所有行计算为 k 行的一部分Total LIABILITIES（例如：CURRENT ASSETS / Total LIABILITIES等）。

下面的所有内容都Revenue被视为字符串 k Revenue(example:)的一部分Net profit(loss) / Revenue。

我试图分成 2 个 DataFrames（上面Total LIABILITIES的所有内容和下面的所有内容Revenue）并使用执行计算apply(lambda x: x/x.iloc[-1]，然后将两个 DataFrames 粘合到一个 using 中concat，但是在计算时，它给出了一个错误。

因此，您需要获取以下 DataFrame：

                                   2017     %_sh_2017 %_2017   2018     %_sh_2018  %_2018          
LONG-TERM ASSETS               779,179        3.20    0.00    1,004,790     2.46    28.95       
CURRENT ASSETS                 23,540,367     96.79   0.00   39,743,268     97.53   68.83   
CAPITAL AND RESERVES           1,917,017      7.88    0.00    2,650,657      6.50   38.27   
Borrowed funds (long-term)       401,343      ...     0.00      828,342      ...   106.39   
Borrowed funds (short-term)    1,828,969      ...     0.00    2,241,601      ...    22.56   
Total LIABILITIES              24,320,474     100     0.00   40,747,718      100    67.54   
Revenue                        83,126,293     100     0.00  125,638,096      100    51.14   
Operating profit (loss)         2,044,700     2.45    0.00    2,398,963      1.90   17.33   
Interest expense                   72,849      ...    0.00       60,733       ...  -16.63     
Pre-tax profit (loss)            2,120,085     2.55   0.00    2,912,776       2.31  37.39   
Net profit(loss)                 1,710,746     ...    0.00    2,454,176        ...  43.46
python

1 个回答

  1. Best Answer

    像这样的东西：

    idx1 = "Total LIABILITIES"
idx2 = "Revenue"

d1 = df.iloc[:(df.index == idx1).argmax()]
d2 = df.iloc[(df.index == idx2).argmax() + 1:]

cols = df.columns[df.columns.str.contains(r"^\d+")]

r1 = d1[cols] / df.loc[idx1, cols] * 100
r2 = d2[cols] / df.loc[idx2, cols] * 100

res = (pd
       .concat(
        [r1.append(pd.Series([100] * len(cols), index=cols, name=idx1)),
         r2.append(pd.Series([100] * len(cols), index=cols, name=idx2))])
       .add_prefix("%_sh_"))
res = pd.concat([df, res], axis=1)
new_order = pd.Series(res.columns.str.replace(r"\D", ""), index=res.columns).sort_values().index
res = res[new_order]

    结果：

    In [111]: res
Out[111]:
                                 2017  %_2017   %_sh_2017       2018  %_2018   %_sh_2018       2019  %_2019  \
LONG-TERM ASSETS               779179     0.0    3.203799    1004790   28.95    2.465880    1163862   15.83
CURRENT ASSETS               23540367     0.0   96.792386   39743268   68.83   97.534954   47939841   20.62
CAPITAL AND RESERVES          1917017     0.0    7.882318    2650657   38.27    6.505044    3520344   32.81
Borrowed funds (long-term)     401343     0.0    1.650227     828342  106.39    2.032855    2115672  155.41
Borrowed funds (short-term)   1828969     0.0    7.520285    2241601   22.56    5.501169     962358  -57.07
Total LIABILITIES            24320474     0.0  100.000000   40747718   67.54  100.000000   49600194   21.73
Revenue                      83126293     0.0  100.000000  125638096   51.14  100.000000  154129923   22.68
Operating profit (loss)       2044700     0.0    2.459751    2398963   17.33    1.909423    4630956   93.04
Interest expense                72849     0.0    0.087637      60733  -16.63    0.048340     128529  111.63
Pre-tax profit (loss)         2120085     0.0    2.550439    2912776   37.39    2.318386    4992523   71.40
Net profit(loss)              1710746     0.0    2.058008    2454176   43.46    1.953369    4267316   73.88

                              %_sh_2019
LONG-TERM ASSETS               2.346487
CURRENT ASSETS                96.652527
CAPITAL AND RESERVES           7.097440
Borrowed funds (long-term)     4.265451
Borrowed funds (short-term)    1.940230
Total LIABILITIES            100.000000
Revenue                      100.000000
Operating profit (loss)        3.004579
Interest expense               0.083390
Pre-tax profit (loss)          3.239165
Net profit(loss)               2.768649

    PS如果你想了解这个解决方案是如何工作的，那么我建议你分别执行每一行并查看中间结果。此外，由点分隔的长命令可以逐渐执行 - 首先我们执行命令的第一部分（直到第一个点），下一步我们添加下一个命令，等等。

    • 3

相关问题