Python Pandas-将groupby函数的结果返回到父表 [英] Python Pandas - returning results of groupby function back to parent table
问题描述
[使用Python3]我正在使用熊猫读取csv文件，对数据框进行分组，对分组的数据应用函数并将这些结果添加回原始数据框中.
[Using Python3] I'm using pandas to read a csv file, group the dataframe, apply a function to the grouped data and add these results back to the original dataframe.
我的输入看起来像这样:
My input looks like this:
email cc timebucket total_value
john@john.com us 1 110.50
example@example.com uk 3 208.84
... ... ... ...
基本上我正在尝试按cc分组，并计算该组中total_value中每个值的百分位等级.其次，我想对这些结果应用流程说明.我需要将这些结果添加回原始/父DataFrame中.这样看起来像这样:
Basically I'm trying to group by cc and calculate the percentile rank for each value in total_value within that group. Secondly I want to apply a flow statement to these results. I need these results to be added back to the original/parent DataFrame. Such that it would look something like this:
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还是很陌生，所以这可能会相互解释.
The code below gives me an AssertionError and I cannot figure out why. I'm very new to Python and pandas, so that might explain one and another.
代码:
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)
根据要求，这是回溯日志:
As requested, this is the traceback log:
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
推荐答案
尝试一下.您的示例从转换函数返回了一个Series，但是应该已经返回了一个值. (这使用了熊猫排名函数FYI)
try this. Your example was returning a Series from the transformation function but should have returned a single value. (and this uses pandas rank function 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
这篇关于Python Pandas-将groupby函数的结果返回到父表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
