Python Pandas-将groupby函数的结果返回给父表[英] Python Pandas - returning results of groupby function back to parent table

本文是小编为大家收集整理的关于Python Pandas-将groupby函数的结果返回给父表的处理方法,想解了Python Pandas-将groupby函数的结果返回给父表的问题怎么解决?Python Pandas-将groupby函数的结果返回给父表问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

[使用Python3]我正在使用pandas读取CSV文件,分组数据框,将函数应用于分组数据,然后将这些结果添加到原始数据框架中.

我的输入看起来像这样:

email                   cc  timebucket  total_value
john@john.com           us  1           110.50
example@example.com     uk  3           208.84
...                     ... ...         ...

基本上,我正在尝试按cc进行分组,并计算该组中total_value中每个值的百分位等级.其次,我想对这些结果应用流语句.我需要将这些结果添加回原始/父数据库.这样看起来像这样:

email                   cc  timebucket  total_value     percentrank rankbucket
john@john.com           us  1           110.50          48.59       mid50
example@example.com     uk  3           208.84          99.24       top25
...                     ... ...         ...             ...         ...

下面的代码给我一个AssertionError,我无法弄清楚原因.我是Python和Pandas的新手,所以可以解释一个.

代码:

import pandas as pd
import numpy as np
from scipy.stats import rankdata

def percentilerank(frame, groupkey='cc', rankkey='total_value'):
    from pandas.compat.scipy import percentileofscore

    # Technically the below percentileofscore function should do the trick but I cannot
    # get that to work, hence the alternative below. It would be great if the answer would
    # include both so that I can understand why one works and the other doesnt.
    # func = lambda x, score: percentileofscore(x[rankkey], score, kind='mean')

    func = lambda x: (rankdata(x.total_value)-1)/(len(x.total_value)-1)*100
    frame['percentrank'] = frame.groupby(groupkey).transform(func)


def calc_and_write(filename):
    """
    Function reads the file (must be tab-separated) and stores in a pandas DataFrame.
    Next, the percentile rank score based is calculated based on total_value and is done so within a country.
    Secondly, based on the percentile rank score (prs) a row is assigned to one of three buckets:
        rankbucket = 'top25' if prs > 75
        rankbucket = 'mid50' if 25 > prs < 75
        rankbucket = 'bottom25' if prs < 25
    """

    # Define headers for pandas to read in DataFrame, stored in a list
    headers = [
        'email',            # 0
        'cc',               # 1
        'last_trans_date',  # 3
        'timebucket',       # 4
        'total_value',      # 5
    ]

    # Reading csv file in chunks and creating an iterator (is supposed to be much faster than reading at once)
    tp = pd.read_csv(filename, delimiter='\t', names=headers, iterator=True, chunksize=50000)
    # Concatenating the chunks and sorting total DataFrame by booker_cc and total_nett_spend
    df = pd.concat(tp, ignore_index=True).sort(['cc', 'total_value'], ascending=False)

    percentilerank(df)

编辑: 根据要求,这是回溯日志:

Traceback (most recent call last):
  File "C:\Users\m\Documents\Python\filter_n_split_3.py", line 85, in <module>
    print(calc_and_write('tsv/test.tsv'))
  File "C:\Users\m\Documents\Python\filter_n_split_3.py", line 74, in calc_and_write
    percentilerank(df)
  File "C:\Users\m\Documents\Python\filter_n_split_3.py", line 33, in percentilerank
    frame['percentrank'] = frame.groupby(groupkey).transform(func)
  File "C:\Python33\lib\site-packages\pandas\core\groupby.py", line 1844, in transform
    axis=self.axis, verify_integrity=False)
  File "C:\Python33\lib\site-packages\pandas\tools\merge.py", line 894, in concat
    verify_integrity=verify_integrity)
  File "C:\Python33\lib\site-packages\pandas\tools\merge.py", line 964, in __init__
    self.new_axes = self._get_new_axes()
  File "C:\Python33\lib\site-packages\pandas\tools\merge.py", line 1124, in _get_new_axes
    assert(len(self.join_axes) == ndim - 1)
AssertionError

推荐答案

尝试一下.您的示例是从转换功能返回系列,但应该返回一个值. (这使用PANDAS RANK函数fyi)

In [33]: df
Out[33]: 
                 email  cc  timebucket  total_value
0        john@john.com  us           1       110.50
1  example@example.com  uk           3       208.84
2          foo@foo.com  us           2        50.00

In [34]: df.groupby('cc')['total_value'].apply(lambda x: 100*x.rank()/len(x))
Out[34]: 
0    100
1    100
2     50
dtype: float64

In [35]: df['prank'] = df.groupby('cc')['total_value'].apply(lambda x: 100*x.rank()/len(x))

In [36]: df
Out[36]: 
                 email  cc  timebucket  total_value  prank
0        john@john.com  us           1       110.50    100
1  example@example.com  uk           3       208.84    100
2          foo@foo.com  us           2        50.00     50

本文地址:https://www.itbaoku.cn/post/1727740.html