我有以下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
请帮忙!
您有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