我有一个数据框看起来像这样:
indx user_id type date
0 123 A Level-1 2021-01-15
1 123 A Level-1 2021-01-10
2 123 A Level-2 2021-01-10
3 123 B Level-2 2021-01-11
4 123 not_ctrgzd 2021-01-10
5 124 A Level-2 2021-02-11
6 124 B Level-1 2021-01-21
7 124 B Level-1+ 2021-02-11
8 125 not_ctrgzd 2021-01-31
9 126 A Level-1 2021-02-02
...
我需要为每种唯一类型获取具有最新日期的行,即
indx user_id type date
0 123 A Level-1 2021-01-15
2 123 A Level-2 2021-01-10
3 123 B Level-2 2021-01-11
4 123 not_ctrgzd 2021-01-10
5 124 A Level-2 2021-02-11
6 124 B Level-1 2021-01-21
7 124 B Level-1+ 2021-02-11
8 125 not_ctrgzd 2021-01-31
9 126 A Level-1 2021-02-02
然后下面的代码块这样做
idx = df.groupby(['user_id','type'])['date'].transform(max) == df['date']
df[idx]
现在,我不能为每种类型(等)获取具有最大类型值的行A
,B
以便最终,数据帧看起来像这样。
indx user_id type date
2 123 A Level-2 2021-01-10
3 123 B Level-2 2021-01-11
4 123 not_ctrgzd 2021-01-10
5 124 A Level-2 2021-02-11
7 124 B Level-1+ 2021-02-11
8 125 not_ctrgzd 2021-01-31
9 126 A Level-1 2021-02-02
因为B Level-1 +大于B Level-1,而A Level-2大于A Level-1,依此类推。请注意,某些行没有归类的type(no_ctgrzd
),无论如何都不应将其包括在最终数据框中。请不要犹豫,更正标题看起来不合理的任何部分:)。谢谢!
您可以使用pd.CategoricalDtype这样:
#Create a catoregy and order for type
catTypeDtype = pd.CategoricalDtype(['1','1+','2'], ordered=True)
#Split the type into two helper columns to sort on category
df[['t1','t2']] = df['type'].str.extract('(?P<t1>[AB]|(?:.*))(?P<t2>.*)')
#change dtype from string to categorical
df['t2'] = df['t2'].astype(catTypeDtype)
#Sort dataframe on categorical data and date
dfs = df.sort_values(['t2','date'], ascending=[False, False])
#Groupby and take the first record after sorting
df_out = dfs.groupby(['user_id','t1'], group_keys=False, as_index=False).first()\
.drop(['t1','t2'], axis=1)
df_out
输出:
user_id indx type date
0 123 2 A2 2021-01-10
1 123 3 B2 2021-01-11
2 123 4 not_ctrgzd 2021-01-10
3 124 5 A2 2021-02-11
4 124 6 B2 2021-01-21
5 125 8 not_ctrgzd 2021-01-31
6 126 9 A1 2021-02-02
catTypeDtype = pd.CategoricalDtype(['1','1+','2'], ordered=True)
df[['t1','t2']] = df['type'].str.extract('(?P<t1>[AB]|(?:.*))(?:\sLevel-)?(?P<t2>.*)')
# df
df['t2'] = df['t2'].astype(catTypeDtype)
dfs = df.sort_values(['t2','date'], ascending=[False, False])
df_out = dfs.groupby(['user_id','t1'], group_keys=False, as_index=False).first()\
.drop(['t1','t2'], axis=1)
输出:
user_id indx type date
0 123 2 A Level-2 2021-01-10
1 123 3 B Level-2 2021-01-11
2 123 4 not_ctrgzd 2021-01-10
3 124 5 A Level-2 2021-02-11
4 124 7 B Level-1+ 2021-02-11
5 125 8 not_ctrgzd 2021-01-31
6 126 9 A Level-1 2021-02-02
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句