pythonslice_shift_python 数据错位相减，上下两行相减

今天接到一个需求，要求用对数据进行错位相减。感觉写得有点麻烦，如果其他方法，欢迎留言交流

数据说明：有客户、消费日期、消费额度

求解目标：对于同一个客户，对日期升序排序，如果下一个日期的消费额度大于上一个日期的消费额度，则标记，最终取出第一次发生时对应的较大的日期

最简单的方法是使用 pandas.Series.shift 具体可参考官方文档

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.shift.html

方法一是使用pandas.Series.shift，该方法可以方便地对数据进行移动

import pandas as pd

import numpy as np

df = pd.DataFrame({"persion":["A","A","A","A","B","B","B","B","B", "C" ,"D","D","D"]

, "date":[0, 1, 6, 4, 1, 7, 4, 5, 6, 3, 1, 2, 3]

, "price":[1, 5, 3, 0, 2, 4, 5, 8, 3, 5, 7, 7, 7 ]})

# 组内排序

df["sort_id"] = df["date"].groupby(df["persion"]).rank()

df.sort_values(by = ["persion", "sort_id"], axis=0, inplace=True)

df["price_y"] = df["price"].shift(1)

方法二是自己写的完整的数据处理方法，比较复杂，可作参考

import pandas as pd

import numpy as np

df = pd.DataFrame({"persion":["A","A","A","A","B","B","B","B","B", "C" ,"D","D","D"]

, "date":[0, 1, 6, 4, 1, 7, 4, 5, 6, 3, 1, 2, 3]

, "price":[1, 5, 3, 0, 2, 4, 5, 8, 3, 5, 7, 7, 7 ]})

# 组内排序

df["sort_id"] = df["date"].groupby(df["persion"]).rank()

df.sort_values(by = ["persion", "sort_id"], axis=0, inplace=True)

# 添加每个persion的记录数

persion_dt = df["persion"]

persion_u = list(persion_dt.unique())

persion_cout = df["persion"].value_counts().to_dict()

persion_dt_dict = persion_dt.to_dict()

for key, value in persion_dt_dict.items():

persion_dt_dict[key] = persion_cout[value]

df["persion_cout"] = persion_dt_dict.values() # 添加每个id的统计值

# 数据移动，将下一条数据移动到上一行

fill_data = pd.DataFrame({"date":[0], "price":[0]}) # 填充为0, 也可以是其他值

# df.loc[df["persion"]==col_value, ["date", "price"]][1:].append(fill_data) 使移动后的数据行数与移动前一致

slice_data = {col_value: df.loc[df["persion"]==col_value, ["date", "price"]][1:].append(fill_data) for col_value in persion_u }

new_data = pd.DataFrame()

for key, value in slice_data.items():

new_data = new_data.append(value)

new_data.reset_index(drop=True, inplace=True)

# 将移动后的数据与原数据拼接

all_data = pd.merge(df, new_data, how="inner", left_index=True, right_index=True)

all_data["diff"] = all_data["price_y"] - all_data["price_x"]

# 如果persion只有一条记录，保留；如果每个月的price一样，则保留最早的记录

all_data["flag"] = (all_data["persion_cout"]==1) | (all_data["diff"]>=0)

all_data2 = all_data[all_data["flag"] == True]

data_select = all_data2.groupby("persion", as_index=False).first() # 保留第一次取得的最大值

# 将填充的0值改为有效值

data_select.loc[data_select["date_y"]==0, "date_y"] = data_select.loc[data_select["date_y"]==0, "date_x"]

# 删除无用的列

data_select.drop(["sort_id", "persion_cout", "diff", "flag"], axis=1, inplace=True)