Python 基于行条件计算(pandas)

发布于03月29日

我遇到了以下的问题:我有一个Pandas 相框，看起来像这样.

id_tranc	sum	bid
1	4000	2.3%
1	20000	3.5%
2	100000	if >=100 000 - 1.6%, if < 100 000 - 100$
3	30000	if >=100 000 - 1.6%, if < 100 000 - 100$
1	60000	500$

加入时间:清华2007年01月25日下午3:33

dataframe = pd.DataFrame({
    'id_tranc': [1, 1, 2, 3, 1],
    'sum': [4000, 20000, 100000, 30000, 60000],
    'bid': ['2.3%', '3.5%', 'if >=100 000 - 1.6%, if < 100 000 - 100$', 
            'if >=100 000 - 1.6%, if < 100 000 - 100$', '500$']})

计算'佣金'所需，取决于'sum'和'bid'列.最后一个框架应该是这样的:

id_tranc	sum	bid	comission
1	4000	2.3%	92
1	20000	3.5%	700
2	100000	if >=100 000 - 1.6%, if < 100 000 - 100$	1600
3	30000	if >=100 000 - 1.6%, if < 100 000 - 100$	100
1	60000	500$	500

如果以df['commission'] = df['sum'] * df['bid']计算—仅为前2条记录获得结果.请告诉我如何正确地做这件事.

推荐答案

我会写一个基于正则表达式和operator的小型解析器:

from operator import ge, lt, gt, le
import re

def logic(value, bid):
    # define operators, add other ones if needed
    ops = {'>=': ge, '>': gt, '<': lt, '<=': le}
    # remove spaces, split conditions on comma
    conditions = bid.replace(' ', '').split(',')
    # then loop over them, the first match will be used
    for cond in conditions:
        # extract operator, threshold, commission, unit
        m = re.search('(?:if(\W+)(\d+)-)?(\d+\.?\d*)([%$])', cond)
        if not m:     # if no match, ignore
            continue
        op, thresh, com, unit = m.groups()
        # if no condition or condition is valid
        if (not op) or (op and ops[op](value, float(thresh))):
            if unit == '%':                     # handle % case
                return value * float(com)/100
            elif unit == '$':                   # handle fixed com
                return float(com)

df['comission'] = [logic(val, bid) for val, bid in zip(df['sum'], df['bid'])]

# or with apply, which is less efficient
# df['comission'] = df.apply(lambda row: logic(row['sum'], row['bid']), axis=1)

输出:

   id_tranc     sum                                       bid  comission
0         1    4000                                      2.3%       92.0
1         1   20000                                      3.5%      700.0
2         2  100000  if >=100 000 - 1.6%, if < 100 000 - 100$     1600.0
3         3   30000  if >=100 000 - 1.6%, if < 100 000 - 100$      100.0
4         1   60000                                      500$      500.0

Regex:

regex demo

(?:if(\W+)(\d+)-)?  # optionally match a condition (operator and threshold)
(\d+\.?\d*)         # match the value of the commission
([%$])              # match type of commission (% or $)

可复制输入:

df = pd.DataFrame({'id_tranc': [1, 1, 2, 3, 1], 'sum': [4000, 20000, 100000, 30000, 60000],
                   'bid': ['2.3%', '3.5%', 'if >=100 000 - 1.6%, if < 100 000 - 100$',
                           'if >=100 000 - 1.6%, if < 100 000 - 100$', '500$']})