程序计算等于ExchVAT的每个行项目的税率

Question

假设您有以下df:

d = {'line amount#1': [5.95], 'line amount#2': [5.95], 'line amount#3': [15.75],'line amount#4': [15.75], 'line amount#5': [3.9]
,'line amount#6': [2.9], 'line amount#7': [np.nan], 'line amount#8': [np.nan], 'line amount#9': [np.nan],'line amount#10': [np.nan]
, 'BTW':[5.85],'ExclVAT':[44.35], 'Totaal': [50.2]}
dfcalc = pd.DataFrame(data=d)
dfcalc 

仅显示行数量#1和#10的表格：

+----+-----------------+------------------+-------+-----------+----------+
|    |   line amount#1 |   line amount#10 |   BTW |   ExclVAT |   Totaal |
|----+-----------------+------------------+-------+-----------+----------|
|  0 |            5.95 |              nan |  5.85 |     44.35 |     50.2 |
+----+-----------------+------------------+-------+-----------+----------+

从这个DF中，我想从每个特定的行税率中提取税率。税率行项目应动态计算，以便行金额除以某个税率后，行金额加在一起时，合计为ExcludeBTW金额。税率可以是0.09、0.21和0.00，并且应等于增值税(BTW(子集。我试过以下几种：

from itertools import product
# get all possible tax rate combinations
x = [1.09, 1.21, 0.00]
combinations = np.array(list(product(*[x]*10)))
# get amount columns
amounts = dfcalc.filter(like='line amount')
# calculate total VAT for each row for each tax rate combination
vats = amounts.fillna(0).dot(combinations.T).round(1)

# for each row find the combination that gives total VAT
# that is equal to the value in VAT column for that row
ix = vats.eq(dfcalc['ExclVAT'].round(1), axis=0).idxmax(axis=1)
taxrates = np.where(amounts.notna(), combinations[ix], np.nan)

@perl回答了这个问题的一个变体：程序计算每行项目的税率等于增值税然而，不会再进一步了。。

所需产出是行金额除以等于不包括增值税的税率：

+----+-----------------+------------------+-------+-----------+----------+
|    |   line amount#1 |   line amount#2  |   BTW |   ExclVAT |   Totaal |
|----+-----------------+------------------+-------+-----------+----------|
|  0 |            4.92 |              4.92|  5.85 |     44.35 |     50.2 |
+----+-----------------+------------------+-------+-----------+----------+

请帮忙！

Answer 1

四舍五入有点问题，税率的组合实际上并没有给我们准确的答案。

我更新了代码，找到了能给我们提供最准确数字的组合：

from itertools import product
# get all possible tax rate combinations
x = [0.00, 0.09, 0.21]
combinations = np.array(list(product(*[x]*10)))
# get amount columns
amounts = dfcalc.filter(like='line amount')
# calculate amounts excluding VAT for each row for each tax rate combination
exclvat = amounts.fillna(0).dot((1 + combinations.T)**-1)
# for each row find the combination that gives amounts excluding VAT
# that is equal to the value in ExclVAT column for that row
ix = np.abs(exclvat.sub(dfcalc['ExclVAT'].squeeze(), 0)).idxmin(1)
taxrates = np.where(amounts.notna(), combinations[ix], np.nan)
# subtract tax from line amounts
dfcalc[amounts.columns] /= (1 + taxrates)
dfcalc['line amount sum'] = dfcalc.filter(like='line amount').sum(1)
dfcalc.T

输出：

0
line amount#1     4.917355
line amount#2     4.917355
line amount#3    14.449541
line amount#4    14.449541
line amount#5     3.223140
line amount#6     2.396694
line amount#7          NaN
line amount#8          NaN
line amount#9          NaN
line amount#10         NaN
BTW               5.850000
ExclVAT          44.350000
Totaal           50.200000
line amount sum  44.353628