Program calculating tax rate per line item equaling into VAT

user_14736736 0
Viewed 164

I have the following 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

From this DF, I want to extract the tax rate from each specific line rates. The tax rate line items should be calculated dynamically so that each line amount times a certain tax rate totals the VAT amount. The tax rates can be 0.09, 0.21 and 0.00 and should be equal to the VAT (BTW) subset. I've tried the following:

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']

however this does not work..

Desired output:

      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

Please help!

pandas dataframe python
1 Answers

You have 3 different tax rates and 4 line amounts per row, so it can be one of 3**4 = 81 combinations. We can calculate total VAT for each combination, and then find the combination that matches the VAT from the dataframe:

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

Output:

    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
user_67927430
Related