使用pandas根据查找表为列赋值

Question

我有以下矩阵：

destinations = ["DC","NY","SF","AL"]

workinDays = [[3, 5, 7, 7], 
                [5, 5, 7, 7],
                [7, 7, 7, 7],
                [7, 7, 7, 7]]

working_days_df = pd.DataFrame(data=workinDays, columns=destinations,
                 index=destinations).astype(str) + " working days"

基于上述矩阵（当您运行上述代码时，您将得到一个数据集形式的矩阵），我想为另一个数据集other_df赋值，该数据集最多有100行：

dest1   dest2
DC      DC
NY      AL
...

所以我想添加一个新列，从上面的矩阵中读取正确的值。例如，在第2行dest1是NY，addest2是AL，所以根据矩阵，它的值应该是7。我该怎么做

Answer 1

进行以下工作—

df1 = df.unstack().reset_index()
df1.columns = ['dest1', 'dest2', 'workingdays']

#the second dataframe that you mention is the other_df
pd.merge(other_df,df1,how='left',on = ['dest1', 'dest2'])

dest1   dest2   workingdays
0   DC  DC  3 working days
1   DC  NY  5 working days
2   DC  SF  7 working days
3   DC  AL  7 working days
4   NY  DC  5 working days
5   NY  NY  5 working days
6   NY  SF  7 working days
7   NY  AL  7 working days
8   SF  DC  7 working days
9   SF  NY  7 working days
10  SF  SF  7 working days
11  SF  AL  7 working days
12  AL  DC  7 working days
13  AL  NY  7 working days
14  AL  SF  7 working days
15  AL  AL  7 working days

Answer 2

IIUC，您可以执行查找：

df_other['new'] = working_days_df.lookup(df_other['dest1'], df_other['dest2'])

这里，working_days_df是您的矩阵数据帧，而df_other是您要查找值的数据帧