使用同一列的值之和进行转换

Question

我有以下数据框：-

traffic_type    date        unique_visitors         region   total_views
desktop         01/04/2018  72                      aug      50
mobileweb       01/04/2018  1                       aug      60
total           01/04/2018  sum(mobileweb+desktop)  aug      100
desktop         01/04/2018  75848907.6              world    20
mobileweb       01/04/2018  105737747.4             world    30
total           01/04/2018  sum(mobileweb+desktop)  world    40

这可能是一个重复，所以任何类似问题的链接也会有帮助，我可以 在类似的行上构建脚本。 正如你所看到的，我需要在“唯一访客”栏中填写的数据是桌面和手机的总和 前提是他们在同一地区和同一日期。我需要的数据帧

traffic_type    date        unique_visitors region  total_views
desktop         01/04/2018  72              aug     50
mobileweb       01/04/2018  1               aug     60
total           01/04/2018  73              aug     100
desktop         01/04/2018  75848907.6      world   20
mobileweb       01/04/2018  105737747.4     world   30
total           01/04/2018  181,586,655     world   40

再次，我很抱歉，如果这是重复我正在寻找参考链接，如果不是确切的解决方案

Answer 1

您可以使用“逐行”和“检查和求和”，如下所示

import pandas as pd

df = pd.DataFrame([["desktop","01/04/2018",72,"aug",50],
                ["mobileweb","01/04/2018",1,"aug",60],
                ["total","01/04/2018","","aug",100],
                ["desktop","01/04/2018",75848907.6 ,"world",20],
                ["mobileweb","01/04/2018",105737747.4,"world",30],
                ["total","01/04/2018","","world",40]],
                columns=["traffic_type","date","unique_visitors","region","total_views"])

for index, row in df.iterrows():
    if row["unique_visitors"] == "":
        df.at[index,"unique_visitors"] = df.loc[(df['date'] == row["date"]) & (df["region"] == row["region"]) & (df["unique_visitors"] != ""), 'unique_visitors'].sum()

print(df)

输出

 traffic_type        date unique_visitors region  total_views
0      desktop  01/04/2018              72    aug           50
1    mobileweb  01/04/2018               1    aug           60
2        total  01/04/2018              73    aug          100
3      desktop  01/04/2018     7.58489e+07  world           20
4    mobileweb  01/04/2018     1.05738e+08  world           30
5        total  01/04/2018     1.81587e+08  world           40

对于最终答案，您应该一行一行地将这些行添加到原始数据集中

Answer 2

这应该能奏效。创建排除文本行的新df。然后使用pd.to_numeric和.groupby区域更改为数字格式，以获得.sum()。在此数据帧中创建一个新的“traffic type”列并设置为“total”，这样您就可以在多个列上pd.merge将求和值返回到数据帧中。然后，使用np.where逻辑更新值，并删除不需要的helper列以获得最终结果

import pandas as pd, numpy as np
df1 = df.copy().loc[df['unique_visitors'] != '']
df1['unique_visitors'] = pd.to_numeric(df1['unique_visitors'])
df1 = df1.groupby('region')['unique_visitors'].sum().reset_index()
df1['traffic_type'] = 'total'
df2=pd.merge(df, df1, how='left', on=['traffic_type', 'region'], suffixes=('', '_y'))
df2['unique_visitors'] = np.where((df2['traffic_type'] == 'total'),
                                 df2['unique_visitors_y'],
                                 df2['unique_visitors'])
df2 = df2.drop('unique_visitors_y', axis=1)
df2