Program Calculating Tax Rate Per Line Item Equaling Into Vat

I have the following df: quantity#1 unit price#1 line amount#1 line amount#2 line amount#3 line amount#4 VAT -- ------------ -------------- --------------

Solution 1:

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#1unitprice#1lineamount#1lineamount#2lineamount#3  \
0NaNNaN5NaNNaN21.01150.51020.030.061.02458.02458NaNNaN131.01689.01020.030.0171.0260.026030.0100.0lineamount#4VATtaxrate#1taxrate#2taxrate#3taxrate#40NaN1.050.21NaNNaNNaN2NaN6.600.210.090.09NaN6NaN0.000.00NaNNaNNaN13NaN5.400.090.090.09NaN1775.073.050.210.090.090.09