我想在我的数据中识别多个客户群体,但我在这方面面临很多困难。数据如下:
date order_id email
2021-01-01 00:20:01.733000+00:00 123 123@gmail.com
2021-01-01 00:21:51.574000+00:00 124 abc@gmail.com
2021-01-01 02:52:50.862000+00:00 125 def@gmail.com
... ... ...
2022-07-21 06:14:13.419000+00:00 999 xyz@gmail.com我希望找到以下指标,并将它们保存在不同的系列中:
老客户:如果他们在过去12个月里有1次购买months
G 212
订单id是表示已经进行销售的一种表示,因此这是用来统计购买量的列。我试着第一次在每个订单之间进行区别。
df['time_diff'] = df.groupby(['email'])['date'].diff()但是我被困住了,不知道前面该怎么做。任何和所有的帮助都将不胜感激。
另外,我还想将每个度量都保存为一个单独的系列,如下所示
new_cust
0 123@outlook.com
1 abc@gmail.com
2 def@gmail.com
Name: email, Length: 3, dtype: object
old_cust
0 xyz@gmail.com
Name: email, Length: 1, dtype: object发布于 2022-08-23 12:01:43
我能看到那里有很多逻辑。我已经为老的,重复的,死去的客户做了一个经过测试的解决方案。由于我没有数据文件,所以我手动创建了数据。还要注意,您必须先格式化日期,就像我的代码一样。请查看完整的解决方案:
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
#creating data
df = pd.DataFrame([["2021-10-01 00:20:01.733000+00:00",123,"user@gmail.com"], ["2021-05-01 00:20:01.733000+00:00",123,"xyz@gmail.com"],
["2022-07-01 00:20:01.733000+00:00",124,"abc@gmail.com"],["2022-07-01 00:20:01.733000+00:00",124,"abc@gmail.com"]],
columns=['date', 'order_id','email'])
#formatting date column
df['date'] = pd.to_datetime(df['date'].str.strip(), format='%Y-%m-%d %H:%M:%S')
df['date'] = df['date'].dt.date
#creating empty lists
old_customer=[]
new_customer=[]
repeat_customer=[]
dead_customer=[]
recovered_customer=[]
for i in df.email.unique():
#filtering time
df_temp=df[(df.date <= pd.to_datetime('now')) &
(df.date >= pd.to_datetime('now')- pd.DateOffset(months=12))]
#filtering user
df_cust=df_temp.loc[df_temp.email==i]
#old customer logic
if len(df_cust)>0:
old_customer.append(i)
#repeat customer logic
if len(df_cust)>1:
repeat_customer.append(i)
#dead customer logic
if len(df_cust)==0:
dead_customer.append(i)
print("old customers list")
print(old_customer)
print("repeat customers list")
print(repeat_customer)
print("dead customers list")
print(dead_customer)输出:
old customers list
['user@gmail.com', 'abc@gmail.com']
repeat customers list
['abc@gmail.com']
dead customers list
['xyz@gmail.com']希望这能帮到你:)
发布于 2022-08-24 11:01:53
下面是上一个解决方案的替代和更快的解决方案。希望这能帮你更多的忙。稍后谢谢我:D
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
#creating data
df = pd.DataFrame([["2021-10-01 00:20:01.733000+00:00",123,"user@gmail.com"], ["2021-05-01 00:20:01.733000+00:00",123,"xyz@gmail.com"],
["2022-07-01 00:20:01.733000+00:00",124,"abc@gmail.com"],["2022-08-01 00:20:01.733000+00:00",124,"abc@gmail.com"],
["2022-08-01 00:20:01.733000+00:00",124,"use102@gmail.com"]],
columns=['date', 'order_id','email'])
#formatting date column
df['date'] = pd.to_datetime(df['date'].str.strip(), format='%Y-%m-%d %H:%M:%S')
df['date'] = df['date'].dt.date
#creating empty lists
old_customer=[]
new_customer=[]
repeat_customer=[]
dead_customer=[]
recovered_customer=[]
#filtering date
df_temp=df[(df.date <= pd.to_datetime('now')) &
(df.date >= pd.to_datetime('now')- pd.DateOffset(months=12))]
#11 months data before current month
df_temp_eleven=df[(df.date <= pd.to_datetime('now')- pd.DateOffset(months=1)) &
(df.date >= pd.to_datetime('now')- pd.DateOffset(months=11))]
#current one month data
df_temp_current=df[(df.date <= pd.to_datetime('now')) &
(df.date >= pd.to_datetime('now')- pd.DateOffset(months=1))]
#all customers
all_customer=df['email'].unique().tolist()
#old customer
old_customer=df_temp['email'].unique().tolist()
#new customer
#Finding users who are in current month but was not 11 months before current month
df_new = df_temp_current.merge(df_temp_eleven.drop_duplicates(), on=['email','email'],
how='left', indicator=True)
df_new=df_new.loc[df_new['_merge']=='left_only']
new_customer=df_new['email'].unique().tolist()
#repeat customer
repeat_user_counts = df_temp['email'].value_counts()
repeat_customer = repeat_user_counts[repeat_user_counts > 1].index.tolist()
#dead cusstomer
dead_customer = list(set(all_customer) - set(old_customer))
print("old customers list")
print(old_customer)
print("new customers list")
print(new_customer)
print("repeat customers list")
print(repeat_customer)
print("dead customers list")
print(dead_customer)输出:
old customers list
['user@gmail.com', 'abc@gmail.com', 'use102@gmail.com']
new customers list
['use102@gmail.com']
repeat customers list
['abc@gmail.com']
dead customers list
['xyz@gmail.com']https://stackoverflow.com/questions/73457567
复制相似问题