英文:
Creating a variable based on conditions within a group python
问题
I want to create a variable that shows me whether a customer has reallocated funds after taking out all their money from a position within one of their depots.
I define a reallocation, if customer (main_depot_id) a reduces their position in one fund by 90% (sold 90% == 'Y') and reinvests the same amount +-20% into another fund (different sub_depot_id).
My data looks like this:
df = pd.DataFrame({'main_depot_id':[1, 1, 1, 2, 2, 2, 2],'sub_depot_id': [1, 1, 2, 1, 1, 2, 2],'inflow': [100, 0, 100, 200, 0, 0, 220],'outflow': [0, 100, 0, 0, 0, 200, 0],'sold 90%': ['N', 'Y', 'N', 'N', 'N', 'Y', 'N']})# I omitted a date variable above
Now I am not sure how to proceed. My idea was to group by main_depot id and then possibly use a loop that loops through each id. However, I am really at my wits end on how to define my reallocation variable.
英文:
I want to create a variable that shows me whether a customer has reallocated funds after taking out all their money from a position within one of their depots.
I define a reallocation, if customer (main_depot_id) a reduces their position in one fund by 90% (sold 90% == 'Y') and reinvests the same amount +-20% into another fund (different sub_depot_id).
My data looks like this:
df = pd.DataFrame({'main_depot_id':[1, 1, 1, 2, 2, 2, 2],'sub_depot_id': [1, 1, 2, 1, 1, 2, 2],'inflow': [100, 0, 100, 200, 0, 0, 220],'outflow': [0, 100, 0, 0, 0, 200, 0],'sold 90%': ['N', 'Y', 'N', 'N', 'N', 'Y', 'N']})# I omitted a date variable above
Now I am not sure how to proceed. My idea was to group by main_depot id and then possible use a loop that loops through each id. However, I am really at my wits end on how to define my reallocation variable.
答案1
得分: 2
根据事件按时间排序的假设，您可以使用 groupby.ffill 方法对 "sub_depot_id" 和前一个 "sold 90%" 等于 "Y" 的 "outflow" 进行分组，然后识别 "sub_depot_id" 已更改且 "inflow" 在前向填充的 "outflow" 的20%内的行：
# 按组填充前一个 Y 的值tmp = (df[['sub_depot_id', 'outflow']].where(df['sold 90%'].eq('Y')).groupby(df['main_depot_id']).ffill())# 车站是否已更改？m1 = df['sub_depot_id'].ne(tmp['sub_depot_id'])# 金额是否在 outflow 金额的20%内？m2 = df['inflow'].sub(tmp['outflow']).div(tmp['outflow']).abs().le(0.2)# 如果两个条件都为真，则分配 "Y"，否则分配 "N"df['reallocated'] = np.where(m1 & m2, 'Y', 'N')
输出结果如下：
main_depot_id sub_depot_id inflow outflow sold 90% reallocated0 1 1 100 0 N N1 1 1 0 100 Y N2 1 2 100 0 N Y3 2 1 200 0 N N4 2 1 0 0 N N5 2 2 0 200 Y N6 2 2 220 0 N N
中间结果如下：
main_depot_id sub_depot_id inflow outflow sold 90% reallocated sub_depot_id_ffill outflow_ffill m1 m20 1 1 100 0 N N NaN NaN True False1 1 1 0 100 Y N 1.0 100.0 False False2 1 2 100 0 N Y 1.0 100.0 True True3 2 1 200 0 N N NaN NaN True False4 2 1 0 0 N N NaN NaN True False5 2 2 0 200 Y N 2.0 200.0 False False6 2 2 220 0 N N 2.0 200.0 False True
请注意，这是原始代码和输出的翻译，不包括问题或其他内容。
英文:
Assuming the events are sorted chronologically, you can groupby.ffill the "sub_depot_id" and "outflow" from the previous "sold 90%" equal to Y, then identify the rows for which "sub_depot_id" has changed and "inflow" is within 20% of the forward filled "outflow":
# fill the values from the previous Y per grouptmp = (df[['sub_depot_id', 'outflow']].where(df['sold 90%'].eq('Y')).groupby(df['main_depot_id']).ffill())# has the depot changed?m1 = df['sub_depot_id'].ne(tmp['sub_depot_id'])# is the amount within 20% of the outflow amount?m2 = df['inflow'].sub(tmp['outflow']).div(tmp['outflow']).abs().le(0.2)# if both conditions are true, assign "Y", else assign "N"df['reallocated'] = np.where(m1&m2, 'Y', 'N')
Output:
main_depot_id sub_depot_id inflow outflow sold 90% reallocated0 1 1 100 0 N N1 1 1 0 100 Y N2 1 2 100 0 N Y3 2 1 200 0 N N4 2 1 0 0 N N5 2 2 0 200 Y N6 2 2 220 0 N N
Intermediates:
main_depot_id sub_depot_id inflow outflow sold 90% reallocated sub_depot_id_ffill outflow_ffill m1 m20 1 1 100 0 N N NaN NaN True False1 1 1 0 100 Y N 1.0 100.0 False False2 1 2 100 0 N Y 1.0 100.0 True True3 2 1 200 0 N N NaN NaN True False4 2 1 0 0 N N NaN NaN True False5 2 2 0 200 Y N 2.0 200.0 False False6 2 2 220 0 N N 2.0 200.0 False True
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论