查找之前不存在的 customer_id

Question

我想过滤掉 customer_id's以前数据中不存在的那些，所以所有 new_customer_ids这些都是 2020-01-10 的新数据，而不是 2020-01-01 的

Main_df

date          customer_id   amount_spent 

2020-01-01    24            123

2020-01-10    24            145

2020-01-01    58             89

2020-01-10    58             67

2020-01-01    98             34

2020-01-10    99             86

2020-01-10    67            140

2020-01-10    32            321

2020-01-10    75             76

输出_df

new_customer_id  amount_spent 

32           321

75            76

67           140

我曾尝试在 Pandas 中使用 shift 功能，但这对我不起作用

编辑

df = pd.DataFrame([["2020-01-01",24,123],

["2020-01-10",24,145],

["2020-01-01",58,89],

["2020-01-10",58,67],

["2020-01-01",98,34],

["2020-01-10",98,86],

["2020-01-10",67,140],

["2020-01-10",32,321],

["2020-01-10",75,76]],columns = ["date","customer_id","amount_spent" ])

Answer 1

这是另一个解决方案，import numpy as npmask = df.groupby('customer_id').transform(np.size).eq(1)    date  amount_spent0  False         False1  False         False2  False         False3  False         False4   True          True5   True          True6   True          True7   True          True8   True          Truedf[mask['date'] & df.date.eq('2020-01-10')]         date  customer_id  amount_spent5  2020-01-10           99            866  2020-01-10           67           1407  2020-01-10           32           3218  2020-01-10           75            76

Answer 2

如果您正在寻找通用方法，这可能是一个解决方案：df = pd.DataFrame({    'date':['2020-01-01','2020-01-10','2020-01-01','2020-01-10','2020-01-01','2020-01-10','2020-01-10','2020-01-10','2020-01-10'],    'customer_id':[24,24,58,58,98,99,67,32,75],    'amount_spent':[123,145,89,67,34,86,140,321,76]})print(df)         date  customer_id  amount_spent0  2020-01-01           24           1231  2020-01-10           24           1452  2020-01-01           58            893  2020-01-10           58            674  2020-01-01           98            345  2020-01-10           99            866  2020-01-10           67           1407  2020-01-10           32           3218  2020-01-10           75            76您正在查找最后两个日期，因为您的数据集可能看起来不同，而且您不知道要查找的日期。所以现在你应该找到最后两个日期。df=df.sort_values(by='date')take_last_dates = df.drop_duplicates(subset='date').sort_values(by='date')take_last_dates = take_last_dates.date.tolist()print(take_last_dates)['2020-01-01', '2020-01-10']现在您需要为这两个日期创建两个 DF，以查看客户的差异：df_prev = df[    df.date==take_last_dates[0]]print(df_prev)         date  customer_id  amount_spent0  2020-01-01           24           1232  2020-01-01           58            894  2020-01-01           98            34df_current = df[    df.date==take_last_dates[1]]print(df_current)         date  customer_id  amount_spent1  2020-01-10           24           1453  2020-01-10           58            675  2020-01-10           99            866  2020-01-10           67           1407  2020-01-10           32           3218  2020-01-10           75            76所以最后你可以通过使用这两个 df 得到你的结果：new_customers = df_current[    ~df_current.customer_id.isin(df_prev.customer_id.tolist())]print(new_customers)         date  customer_id  amount_spent5  2020-01-10           99            866  2020-01-10           67           1407  2020-01-10           32           3218  2020-01-10           75            76

Answer 3

假设您的示例中有错字（99 是 98）。您可以执行以下操作：df = pd.DataFrame([["2020-01-01",24,123],["2020-01-10",24,145],["2020-01-01",58,89],["2020-01-10",58,67],["2020-01-01",98,34],["2020-01-10",98,86],["2020-01-10",67,140],["2020-01-10",32,321],["2020-01-10",75,76]],columns = ["date","customer_id","amount_spent" ])df["order"] = df.groupby("customer_id").cumcount()df[(df["date"] == "2020-01-10") & (df["order_x"]==0)]输出：    date        customer_id amount_spent    order_x order_y6   2020-01-10  67          140             0       07   2020-01-10  32          321             0       08   2020-01-10  75          76              0       0这将需要根据您的 df 的复杂性进行编辑

Answer 4

这就是你注意到的。不确定您的示例数据和输出是否如您所想。我在 2020-01-10 将客户 99 更改为 98创建一个掩码，它是您所需日期之前/之后的行选择切换日期之后（包括切换日期）的行，减去切换日期之前存在的客户isin()import datetime as dtdf = pd.read_csv(io.StringIO("""date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; customer_id&nbsp; &nbsp;amount_spent&nbsp;2020-01-01&nbsp; &nbsp; 24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1232020-01-10&nbsp; &nbsp; 24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1452020-01-01&nbsp; &nbsp; 58&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;892020-01-10&nbsp; &nbsp; 58&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;672020-01-01&nbsp; &nbsp; 98&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;342020-01-10&nbsp; &nbsp; 98&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;862020-01-10&nbsp; &nbsp; 67&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1402020-01-10&nbsp; &nbsp; 32&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3212020-01-10&nbsp; &nbsp; 75&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;76"""), sep="\s+")df["date"] = pd.to_datetime(df["date"])mask = df["date"] < dt.datetime(2020,1,10)dfnew = df[~mask & ~df["customer_id"].isin(df.loc[mask,"customer_id"])].groupby("customer_id").sum()print(dfnew.to_string())输出&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;amount_spentcustomer_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;32&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32167&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14075&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;76

Answer 5

IIUC 你可以customer_id在 中获取礼物2020-01-01，然后过滤掉它们：s = df.loc[df["date"]=="2020-01-01", "customer_id"]print (df[~df["customer_id"].isin(s)])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;date&nbsp; customer_id&nbsp; amount_spent5&nbsp; 2020-01-10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;99&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 866&nbsp; 2020-01-10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;67&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1407&nbsp; 2020-01-10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;32&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3218&nbsp; 2020-01-10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;75&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 76