计算等于增值税的每行项目税率的程序

Question

我有以下建议：

     quantity#1    unit price#1    line amount#1    line amount#2    line amount#3    line amount#4    VAT
--  ------------  --------------  ---------------  ---------------  ---------------  ---------------  -----
 0           nan           nan                  5              nan              nan              nan   1.05
 2             1          1150.5               10               20               30              nan   6.6
 6             1          2458               2458              nan              nan              nan   0
13             1          1689                 10               20               30              nan   5.4
17             1           260                260               30              100               75  73.05

从这个DF中，我想从每个特定行的税率中提取税率。应动态计算税率行项目，以便每行金额乘以某个税率，合计增值税金额。税率可以是0.09、0.21和0.00，并且应该等于增值税（BTW）子集。我尝试了以下方法：

x = [0.09, 0.21, 0.00]

for i,row in df.iterrows():
    if row['Document Type'] == 0:
        df['line amount'[i]] * x == df['BTW']

然而，这是行不通的

期望输出：

      quantity#1    unit price#1    line amount#1    line amount#2    line amount#3    line amount#4    VAT
--  ------------  --------------  ---------------  ---------------  ---------------  ---------------  -----
 0           nan           nan                  5              nan              nan              nan   1.05
 2             1          1150.5               10               20               30              nan   6.6
 6             1          2458               2458              nan              nan              nan   0
13             1          1689                 10               20               30              nan   5.4
17             1           260                260               30              100               75  73.05

#new columns
      taxrate#1    taxrate#2        taxrate#3        taxrate #4    
--  ------------  --------------  ---------------  --------------- 
 0           0.21          nan               nan              nan            
 2           0.21          0.09              0.09             nan            
 6           0.00          nan               nan              nan            
13           0.09          0.09              0.09             nan            
17           0.21          0.09              0.09             0.09          

请帮忙

Answer 1

每行有3种不同的税率和4行金额，因此可以是3**4=81组合之一。我们可以计算每个组合的总增值税，然后从数据框中找到与增值税匹配的组合：

from itertools import product

# get all possible tax rate combinations
x = [0.09, 0.21, 0.00]
combinations = np.array(list(product(*[x]*4)))

# get amount columns
amounts = df.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(df['VAT'].round(1), axis=0).idxmax(axis=1)
taxrates = np.where(amounts.notna(), combinations[ix], np.nan)

# add taxrate columns to the original dataframe
taxrate_cols = amounts.columns.str.replace('line amount', 'taxrate')
df[taxrate_cols] = taxrates

df

输出：

    quantity#1  unit price#1  line amount#1  line amount#2  line amount#3  \
0          NaN           NaN              5            NaN            NaN   
2          1.0        1150.5             10           20.0           30.0   
6          1.0        2458.0           2458            NaN            NaN   
13         1.0        1689.0             10           20.0           30.0   
17         1.0         260.0            260           30.0          100.0   

    line amount#4    VAT  taxrate#1  taxrate#2  taxrate#3  taxrate#4  
0             NaN   1.05       0.21        NaN        NaN        NaN  
2             NaN   6.60       0.21       0.09       0.09        NaN  
6             NaN   0.00       0.00        NaN        NaN        NaN  
13            NaN   5.40       0.09       0.09       0.09        NaN  
17           75.0  73.05       0.21       0.09       0.09       0.09