问题

 Race_ID 21 21 21 21 25 25 25 3 3 3 3 14 14 85 85 85  我  Race_ID 21 21 21 21 25 25 25 3 3 3 3 14 14 85 85 85  非

英文:

I have the following large dataset recording color:#272822;">我有以下的大型数据集，记录了数学竞赛结果，按日期降序排列：例如，学生1在比赛1中获得第三名，而学生3在比赛2中获得第一名，依此类推。 Date Student_ID Rank 1/1/2023 1 3 1/1/2023 2 2 1/1/2023 3 1 1/1/2023 4 4 11/9/2022 1 2 11/9/2022 2 3 11/9/2022 3 1 17/4/2022 5 4 17/4/2022 2 1 17/4/2022 3 2 17/4/2022 4 3 1/3/2022 1 1 1/3/2022 2 2 1/1/2021 1 2 1/1/2021 2 3 1/1/2021 3 1 想创建一个名为```Last_win```的新列，它返回学生上次获胜的```Race_ID```（即排名第一的比赛）。所以结果应该如下： Date Student_ID Rank Last_win 1/1/2023 1 3 14 1/1/2023 2 2 3 1/1/2023 3 1 25 1/1/2023 4 4 NaN 11/9/2022 1 2 14 11/9/2022 2 3 3 11/9/2022 3 1 85 17/4/2022 5 4 NaN 17/4/2022 2 1 NaN 17/4/2022 3 2 85 17/4/2022 4 3 NaN 1/3/2022 1 1 NaN 1/3/2022 2 2 NaN 1/1/2021 1 2 NaN 1/1/2021 2 3 NaN 1/1/2021 3 1 NaN 常感谢您的提前帮助。 the result of a math competition among students in descending order of date: So for example, student 1 comes third in Race 1 while student 3 won Race 2, etc.


Race_ID   Date           Student_ID      Rank  
21         1/1/2023       1               3     
21         1/1/2023       2               2     
21         1/1/2023       3               1     
21         1/1/2023       4               4     
25         11/9/2022      1               2     
25         11/9/2022      2               3     
25         11/9/2022      3               1     
3          17/4/2022      5               4     
3          17/4/2022      2               1     
3          17/4/2022      3               2     
3          17/4/2022      4               3     
14         1/3/2022       1               1     
14         1/3/2022       2               2     
85         1/1/2021       1               2     
85         1/1/2021       2               3     
85         1/1/2021       3               1     

And I want to create a new column called Last_win which returns the Race_ID of the last time that student won (i.e. rank number 1). So the outcome should look like
Race_ID   Date           Student_ID      Rank    Last_win
21         1/1/2023       1               3       14
21         1/1/2023       2               2       3
21         1/1/2023       3               1       25
21         1/1/2023       4               4       NaN
25         11/9/2022      1               2       14
25         11/9/2022      2               3       3
25         11/9/2022      3               1       85
3          17/4/2022      5               4       NaN
3          17/4/2022      2               1       NaN
3          17/4/2022      3               2       85
3          17/4/2022      4               3       NaN
14         1/3/2022       1               1       NaN
14         1/3/2022       2               2       NaN
85         1/1/2021       1               2       NaN
85         1/1/2021       2               3       NaN
85         1/1/2021       3               1       NaN

Thank you so much inadvacne.


答案1
得分: 2
保留Rank为1的行，隐藏其他行，然后按学生分组。最后，向后填充Race_ID，不要忘记移动结果：
# 如果需要，将Date列转换为DatetimeIndex
# df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)

df['Last_win'] = (df.where(df['Rank'] == 1)
                    .groupby(df['Student_ID'])['Race_ID']
                    .transform(lambda x: x.bfill().shift(-1)))
print(df)

# 输出
    Race_ID       Date  Student_ID  Rank  Last_win
0        21 2023-01-01           1     3      14.0
1        21 2023-01-01           2     2       3.0
2        21 2023-01-01           3     1      25.0
3        21 2023-01-01           4     4       NaN
4        25 2022-09-11           1     2      14.0
5        25 2022-09-11           2     3       3.0
6        25 2022-09-11           3     1      85.0
7         3 2022-04-17           5     4       NaN
8         3 2022-04-17           2     1       NaN
9         3 2022-04-17           3     2      85.0
10        3 2022-04-17           4     3       NaN
11       14 2022-03-01           1     1       NaN
12       14 2022-03-01           2     2       NaN
13       85 2021-01-01           1     2       NaN
14       85 2021-01-01           2     3       NaN
15       85 2021-01-01           3     1       NaN


英文:
Keep rows where Rank is 1 and hide others then group by Student. Finally, fill backward Race_ID and don't forget to shift the result:
# Convert Date column to DatetimeIndex if needed
# df[&#39;Date&#39;] = pd.to_datetime(df[&#39;Date&#39;], dayfirst=True)
df[&#39;Last_win&#39;] = (df.where(df[&#39;Rank&#39;] == 1)
.groupby(df[&#39;Student_ID&#39;])[&#39;Race_ID&#39;]
.transform(lambda x: x.bfill().shift(-1)))
print(df)
# Output
Race_ID       Date  Student_ID  Rank  Last_win
0        21 2023-01-01           1     3      14.0
1        21 2023-01-01           2     2       3.0
2        21 2023-01-01           3     1      25.0
3        21 2023-01-01           4     4       NaN
4        25 2022-09-11           1     2      14.0
5        25 2022-09-11           2     3       3.0
6        25 2022-09-11           3     1      85.0
7         3 2022-04-17           5     4       NaN
8         3 2022-04-17           2     1       NaN
9         3 2022-04-17           3     2      85.0
10        3 2022-04-17           4     3       NaN
11       14 2022-03-01           1     1       NaN
12       14 2022-03-01           2     2       NaN
13       85 2021-01-01           1     2       NaN
14       85 2021-01-01           2     3       NaN
15       85 2021-01-01           3     1       NaN


答案2
得分: 1
使用 merge_asof 来合并先前日期，通过参数 allow_exact_matches=False：
df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)

df = df.sort_values('Date')

df1 = (df.loc[df['Rank'].eq(1), ['Date','Student_ID','Race_ID']]
         .rename(columns={'Race_ID':'Last_win'}))

df = (pd.merge_asof(df.reset_index(), 
                    df1, on='Date', allow_exact_matches=False, by='Student_ID')
        .sort_values('index', ignore_index=True)
        .drop('index', axis=1))

print(df)

输出结果如下：
    Race_ID       Date  Student_ID  Rank  Last_win
0        21 2023-01-01           1     3      14.0
1        21 2023-01-01           2     2       3.0
2        21 2023-01-01           3     1      25.0
3        21 2023-01-01           4     4       NaN
4        25 2022-09-11           1     2      14.0
5        25 2022-09-11           2     3       3.0
6        25 2022-09-11           3     1      85.0
7         3 2022-04-17           5     4       NaN
8         3 2022-04-17           2     1       NaN
9         3 2022-04-17           3     2      85.0
10        3 2022-04-17           4     3       NaN
11       14 2022-03-01           1     1       NaN
12       14 2022-03-01           2     2       NaN
13       85 2021-01-01           1     2       NaN
14       85 2021-01-01           2     3       NaN
15       85 2021-01-01           3     1       NaN


英文:
Use merge_asof for merge previous datetimes by parameter allow_exact_matches=False:
df[&#39;Date&#39;] = pd.to_datetime(df[&#39;Date&#39;], dayfirst=True)
df = df.sort_values(&#39;Date&#39;)
df1 = (df.loc[df[&#39;Rank&#39;].eq(1), [&#39;Date&#39;,&#39;Student_ID&#39;,&#39;Race_ID&#39;]]
.rename(columns={&#39;Race_ID&#39;:&#39;Last_win&#39;}))
df = (pd.merge_asof(df.reset_index(), 
df1, on=&#39;Date&#39;, allow_exact_matches=False, by=&#39;Student_ID&#39;)
.sort_values(&#39;index&#39;, ignore_index=True)
.drop(&#39;index&#39;, axis=1))
print (df)
Race_ID       Date  Student_ID  Rank  Last_win
0        21 2023-01-01           1     3      14.0
1        21 2023-01-01           2     2       3.0
2        21 2023-01-01           3     1      25.0
3        21 2023-01-01           4     4       NaN
4        25 2022-09-11           1     2      14.0
5        25 2022-09-11           2     3       3.0
6        25 2022-09-11           3     1      85.0
7         3 2022-04-17           5     4       NaN
8         3 2022-04-17           2     1       NaN
9         3 2022-04-17           3     2      85.0
10        3 2022-04-17           4     3       NaN
11       14 2022-03-01           1     1       NaN
12       14 2022-03-01           2     2       NaN
13       85 2021-01-01           1     2       NaN
14       85 2021-01-01           2     3       NaN
15       85 2021-01-01           3     1       NaN







通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。






						
	

点赞		

					https://go.coder-hub.com/75384112.html
复制链接
复制链接
		


go

检索符合条件的最后一行数据。

问题

答案1

答案2

从另一个模块导入的访问节点在Python 3中

创建DataFrame，使用嵌套字典和列表。

Python为什么将13/5返回为0？（正在解决逆波兰表达式问题）

Working with csv file in pandas to create a new csv file from old csv file after checking if values of two fields are not zero

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论