python - Pandas Dataframe 具有各种列标准的求和函数

Question

此函数根据 5 个不同的条件(开始日期、结束日期、资金、帐户和分析)对数据框中的行进行求和:

df = pd.DataFrame(
    [
    ['02-09-2019',20190902,  20.00,  'F1','B1','I2'],
    ['23-09-2019',20190923,  237.36, 'F1','B1','I1'],
    ['15-11-2019',20191115,  200.00, 'F1','B1','I1'],
    ['16-11-2019',20191116,  2045.00, 'F1','B1','I2'],
    ['05-05-2020',20200505,  205.00, 'F2','B2','I1'],
    ],
    columns= ['Datestr','Datenum','Cost','Fund','Account','Analysis'])


def per_sum(startdate, enddate, fund, account, analysis):
    return df[(df.Datenum > startdate) &
              (df.Datenum < enddate) &
              (df.Fund == fund) &
              (df.Account == account) &
              (df.Analysis == analysis)
              ].Cost.sum()


per_sum(20190000,20200000,'F1','B1','I1')

如果未给出资金、帐户或分析数据，我如何调整此函数以继续计算总成本。

例如:如果我想找到总分析“I2”(在所有基金和账户中)。

这种事情不起作用:

per_sum(20190000,20200000,'','','I2')

谢谢

Answer 1

想法由 | 链接对于 bitwise OR通过空格进行比较的新条件:

def per_sum(startdate, enddate, fund, account, analysis):
    return df[(df.Datenum > startdate) &
              (df.Datenum < enddate) &
              ((df.Fund == fund) | (fund == '')) &
              ((df.Account == account) | (account == '')) &
              ((df.Analysis == analysis) | (analysis == ''))
              ].Cost.sum()

print(per_sum(20190000,20200000,'','',''))
2502.36

print(per_sum(20190000,20200000,'','','I2'))
2065.0

编辑:

如果还想使用日期时间进行过滤，一种可能的解决方案是添加 if-else更改开始和结束日期时间的语句:

def per_sum(startdate, enddate, fund, account, analysis):
    startdate = -np.inf if startdate == '' else startdate
    enddate = np.inf if enddate == '' else enddate
    return df[(df.Datenum > startdate) &
              (df.Datenum < enddate) &
              ((df.Fund == fund) | (fund == '')) &
              ((df.Account == account) | (account == '')) &
              ((df.Analysis == analysis) | (analysis == ''))
              ].Cost.sum()

print(per_sum('','','','',''))
2707.36