python - 如何对 pandas 数据框进行二进制搜索以查找列值的组合?
问题描述
对不起,如果这是熊猫文档解释的一个简单问题,但我已经尝试搜索如何做到这一点并且没有任何运气。
我有一个带有几列的 pandas 数据名,我希望能够使用二进制搜索来搜索特定的行,因为我的数据集很大并且我将进行大量搜索。
我的数据如下所示:
Name Course Week Grade
------------- ------- ---- -----
Homer Simpson MATH001 1 97
Homer Simpson MATH001 3 85
Homer Simpson CSCI100 1 89
John McGuirk MATH001 2 78
John McGuirk CSCI100 1 100
John McGuirk CSCI100 2 96
我希望能够快速搜索我的数据以查找名称、课程和周数的特定组合。名称、课程和周数的每个不同组合在数据集中将有 0 行或 1 行。如果我正在搜索的名称、课程和星期的组合缺少值,我希望我的搜索返回 0。
例如,我想搜索值(John McGuirk, CSCI100, 1)
有没有内置的方法可以做到这一点,还是我必须编写自己的二进制搜索?
更新:
我尝试使用下面一位评论者建议的内置方式执行此操作,并且我还尝试使用为我的特定数据编写的自定义二进制搜索以及使用递归处理不同列的另一个自定义二进制搜索来执行此操作比我的具体例子。
这些测试的数据框包含 10,000 行。我把时间安排在下面。两种二分搜索的性能都比使用[...]
获取行好。我远非 Python 专家,所以我不确定我的代码优化得有多好。
# Load data
from pandas import DataFrame, read_csv
import math
import pandas as pd
import time
file = 'grades.xlsx'
df = pd.read_excel(file)
# This was suggested by one of the commenters below
def get_grade(name, course, week):
mask = (df.name.values == name) & (df.course.values == course) & (df.week.values == week)
row = df[mask]
if row.empty == False:
return row.grade.values[0]
else:
return 0
# Binary search that is specific to my particular data
def get_grade_binary_search(name, course, week):
lower = 0
upper = len(df.index) - 1
while lower <= upper:
mid = math.floor((lower + upper) / 2)
row_name = df.iat[mid, 0]
if name < row_name:
upper = mid - 1
elif name > row_name:
lower = mid + 1
else:
row_course = df.iat[mid, 1]
if course < row_course:
upper = mid - 1
elif course > row_course:
lower = mid + 1
else:
row_week = df.iat[mid, 2]
if week < row_week:
upper = mid - 1
elif week > row_week:
lower = mid + 1
else:
return df.iat[mid, 3]
return 0
# General purpose binary search
def get_grade_binary_search_recursive(search_value):
lower = 0
upper = len(df.index) - 1
while lower <= upper:
mid = math.floor((lower + upper) / 2)
comparison = compare(search_value, 0, mid)
if comparison < 0:
upper = mid - 1
elif comparison > 0:
lower = mid + 1
else:
return df.iat[mid, len(search_value)]
# Utility method
def compare(search_value, search_column_index, df_value_index):
if search_column_index >= len(search_value):
return 0
if search_value[search_column_index] < df.iat[df_value_index, search_column_index]:
return -1
elif search_value[search_column_index] > df.iat[df_value_index, search_column_index]:
return 1
else:
return compare(search_value, search_column_index + 1, df_value_index)
以下是时间安排。我还打印了每次搜索返回值的总和,以验证是否返回了相同的行。
# Non binary search
sum_of_grades = 0
start = time.time()
for week in range(first_week, last_week + 1):
for name in names:
for course in courses:
val = get_grade(name, course, week)
sum_of_grades += val
end = time.time()
print('elapsed time: ', end - start)
print('sum of grades: ', sum_of_grades)
elapsed time: 26.130020141601562
sum of grades: 498724
# Binary search specific to this data
sum_of_grades = 0
start = time.time()
for week in range(first_week, last_week + 1):
for name in names:
for course in courses:
val = get_grade_binary_search(name, course, week)
sum_of_grades += val
end = time.time()
print('elapsed time: ', end - start)
print('sum of grades: ', sum_of_grades)
elapsed time: 4.4506165981292725
sum of grades: 498724
# Binary search with recursion
sum_of_grades = 0
start = time.time()
for week in range(first_week, last_week + 1):
for name in names:
for course in courses:
val = get_grade_binary_search_recursive([name, course, week])
sum_of_grades += val
end = time.time()
print('elapsed time: ', end - start)
print('sum_of_grades: ', sum_of_grades)
elapsed time: 7.559535264968872
sum_of_grades: 498724
解决方案
推荐阅读
- angular - 在打开多个 MatDialogs 时无法将对话框设置为上方
- javascript - 为什么javascript不能通过存储值操作数组?
- google-api - 如何使用登录密码连接到 Google Drive?
- postgresql - 是否可以使用 GORM 在 Postgres 中同时创建索引?
- flutter - 在 Flutter 的表单中将数据从一个小部件传递到另一个小部件
- javascript - 计算器 JAVASCRIPT:逻辑部分的问题
- php - 我的变形没有返回任何东西 - laravel
- .net - MSBuild.ILMerge.Task 程序集引用冲突
- python - PySpark 失败取决于指令的顺序
- angular - NgRx:如何将 InjectionToken 值加载到元减速器中