Skip to main content
 首页 » 编程设计

python之循环遍历 python 数组以匹配第二个数组中的多个条件,快速方法

2025年02月15日8zdz8207

我是 Python 的初学者,想知道是否有更快的方法来执行此代码,所以请原谅我的无知。我有 2 个 Excel 工作表:一个 (results) 有大约 30,000 行唯一用户 ID,然后我有 30 列问题,下面的单元格是空的。我的第二张工作表 (answers) 大约有 400,000 行和 3 列。第一列是用户 ID,第二列是提出的问题,第三列是用户对每个相应问题的回答。我想要做的本质上是一个索引匹配数组 excel 函数,我可以在其中通过匹配用户 ID 和询问的问题,用工作表 2 中的答案填充工作表 1 中的空白单元格。

现在我写了一段代码,但只处理工作表 1 中的 4 列就花了大约 2 个小时。我想弄清楚我的做法是否没有充分利用 Numpy 的功能。

import pandas as pd 
import numpy as np 
 
# Need to take in data from 'answers' and merge it into the 'results' data 
# Will requiring matching the data based on 'id' in column 1 of 'answers' and the 
# 'question' in column 2 of 'answers' 
results = pd.read_excel("/Users/data.xlsx", 'Results') 
answers = pd.read_excel("/Users/data.xlsx", 'Answers') 
 
answers_array = np.array(answers) ######### 
 
# Create a list of questions being asked that will be matched to column 2 in answers.  
# Just getting all the questions I want 
column_headers = list(results.columns) 
formula_headers = []              ######### 
for header in column_headers: 
   formula_headers.append(header) 
del formula_headers[0:13] 
 
# Create an empty array with ids in which the 'merged' data will be fed into 
pre_ids = np.array(results['Id']) 
ids = np.reshape(pre_ids, (pre_ids.shape[0], 1)) 
ids = ids.astype(str) 
 
zero_array = np.zeros((ids.shape[0], len(formula_headers))) 
ids_array = np.hstack((ids, zero_array))    ########## 
 
 
for header in range(len(formula_headers)): 
    question_index = formula_headers[header] 
    for user in range(ids_array.shape[0]): 
        user_index = ids_array[user, 0] 
        location = answers_array[(answers_array[:, 0] == int(user_index)) & (answers_array[:, 1] == question_index)] 
        # This location formula is what I feel is messing everything up, 
        # or could be because of the nested loops 
        # If can't find the user id and question in the answers array 
        if location.size == 0: 
            ids_array[user][header + 1] = '' 
        else: 
            row_location_1 = np.where(np.all(answers_array == location[0], axis=1)) 
            row_location = int(row_location_1[0][0]) 
            ids_array[user][header + 1] = answers_array[row_location][2] 
 
print ids_array 

请您参考如下方法:

我们可以只旋转第二个数据框,而不是用第二个数据框的信息填充第一个数据框。

answers.set_index(['id', 'question']).answer.unstack() 

如果您需要行和列与results 数据框中的相同,您可以添加一个reindex_like 方法

answers.set_index(['id', 'question']).answer.unstack().reindex_like(results) 

如果有重复

cols = ['id', 'question'] 
answers.drop_duplicates(cols).set_index(cols).answer.unstack()