如何将基于列值的文件与条件bas合并

Question

我必须将文件1与文件2合并，条件是

if GMV <500 per unit(GMV/Qty) then tax1% else Tax2%.

有人能帮我吗

File1               file2
ID Qty GMV          ID  Tax1%  Tax2%
A1 1   100          A1  5%      12%
A2 2   4000         A2  5%      12%
A3 5   3000         A3  12%     18%
A4 7   1200
A5 2   200

Answer 1

我认为你需要：

import numpy as np 
import pandas as pd 

df1 = pd.DataFrame({'id':['A1','A2','A3','A4','A5'], 'GMV':[100,4000,3000,1200,200]})
df2 = pd.DataFrame({'id':['A1','A2','A3'], 'tax1':['5%','5%','12%'], 'tax2':['12%','12%','18%']})

# Merge both dataframes on 'id'
df_new = df1.merge(df2, how='left', on='id')

# create new column using np.where 
df_new['tax'] = np.where(df_new['GMV']<500, df_new['tax1'], df_new['tax2'])
df_new.drop(['tax1','tax2'],1, inplace=True)
print(df_new)

输出

  GMV  id  tax                                                                                                                      
0   100  A1   5%                                                                                                                      
1  4000  A2  12%                                                                                                                      
2  3000  A3  18%                                                                                                                      
3  1200  A4  NaN                                                                                                                      
4   200  A5  NaN