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

Question

我有以下 df:

     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