主页 / 问题 / 1135627
Accepted
Anofelesoff
Asked:2020-06-03 16:31:27 +0000 UTC2020-06-03 16:31:27 +0000 UTC

从两个 DataFrame 计算指标

  • 772

有必要按月计算每个用户在每种服务（消息和电话）上花费的金额以及每月花费的资金总额）有关资费限制的信息在 df1 中提供，有关信息df2中的用户自己。如果消息或流量超过资费，那么我们考虑用户为此付出了多少，如果他没有超过限制，那么我们认为成本为0。

df1 = [[50, 4000, 3, 5, tarif1], [1000, 2000, 2, 4, tarif2]]
columns = ['messages_included', 'minutes_included','rub_per_message', 'rub_per_minute', 'tariff_name']
df2 = [[1, tarif1, 5, 20, 100], [1, tarif1, 6, 150, 250], [1, tarif1, 7, 100, 200], [2, tarif1, 5, 45, 10], [2, tarif1, 6, 100, 0], [3, tarif2, 5, 12, 98], [3, tarif2, 6, 10, 6000], [3, tarif2, 7, 800, 2000], [4, tarif2, 5, 80, 10], [4, tarif2, 6, 1000, 500]   
columns = ['user_id', 'tariff', 'month', 'total_messages', 'calls_sum']

预期结果，列将添加到 df2，即每月呼叫和消息的成本，以及总成本超过资费的列，包括消息和呼叫的成本。

columns = ['user_id', 'tariff', 'month', 'total_messages', 'calls_sum', 'messages_revenue', 'calls_revenue', 'total_revenue']

决赛桌应如下所示https://imgur.com/a/yvL3mgA

在此处输入图像描述

python

2 个回答

  1. Best Answer

    试试这样：

    t = df2.reset_index().merge(df1.rename(columns={"tariff_name": "tariff"})).set_index("index")
t["messages_revenue"] = (t["total_messages"] - t["messages_included"]).clip(0) * t["rub_per_message"]
t["calls_revenue"] = (t["calls_sum"] - t["minutes_included"]).clip(0) * t["rub_per_minute"]
t["total_revenue"] = t["messages_revenue"] + t["calls_revenue"]
res = df2.join(t.filter(regex="revenue"))

    结果：

    In [69]: res
Out[69]:
   user_id  tariff  month  total_messages  calls_sum  messages_revenue  calls_revenue  total_revenue
0        1  tarif1      5              20        100                 0              0              0
1        1  tarif1      6             150        250               300              0            300
2        1  tarif1      7             100        200               150              0            150
3        2  tarif1      5              45         10                 0              0              0
4        2  tarif1      6             100          0               150              0            150
5        3  tarif2      5              12         98                 0              0              0
6        3  tarif2      6              10       6000                 0          16000          16000
7        3  tarif2      7             800       2000                 0              0              0
8        4  tarif2      5              80         10                 0              0              0
9        4  tarif2      6            1000        500                 0              0              0
    • 2

  2. 替代解决方案：

    formulas = """
messages_revenue = (total_messages - messages_included) * rub_per_message
calls_revenue = (calls_sum - minutes_included) * rub_per_minute"""

t = (df2
     .reset_index()
     .merge(df1.rename(columns={"tariff_name": "tariff"}))
     .set_index("index"))

res = (df2
       .join(t.eval(formulas)
              .filter(regex="\_revenue")
              .clip(0)
              .eval("total_revenue = messages_revenue + calls_revenue")))

    结果：

    In [81]: res
Out[81]:
   user_id  tariff  month  total_messages  calls_sum  messages_revenue  calls_revenue  total_revenue
0        1  tarif1      5              20        100                 0              0              0
1        1  tarif1      6             150        250               300              0            300
2        1  tarif1      7             100        200               150              0            150
3        2  tarif1      5              45         10                 0              0              0
4        2  tarif1      6             100          0               150              0            150
5        3  tarif2      5              12         98                 0              0              0
6        3  tarif2      6              10       6000                 0          16000          16000
7        3  tarif2      7             800       2000                 0              0              0
8        4  tarif2      5              80         10                 0              0              0
9        4  tarif2      6            1000        500                 0              0              0
    • 2

相关问题