Элементы Vlook Up из другого фрейма данных для создания мультииндексного фрейма данных в Python

Итак, если вы не слишком увлечены мультииндексом, вот один из способов получить ваши данные, как вы ожидаете:

import pandas as pd
test= pd.DataFrame({'row':['a','b','c','d'],'col_A' : ["Alexis","Alexi","Peter","Pete"]})
names = pd.DataFrame({'A' : ["Alexis","Alexi","Peter","Pete"],
                    'B' : ["Alexi","Alexis","Pete","Peter"],
                    "similarity" : [0.9,0.9,0.8,0.8]})

Заметьте, я не set_indextest, но вы можете это сделать, это немного изменит следующее (см. Комментарии). Вы можете создать фрейм данных a, например:

import itertools
a = pd.DataFrame([p for p in itertools.combinations(test['col_A'], 2)],columns =['A','B'],
                 index=['%s,%s'%p for p in itertools.combinations(test['row'], r=2)])
# here if you did set_index your test, then replace 
# index=['%s,%s'%p for p in itertools.combinations(test['row'], r=2)] by 
# index=['%s,%s'%p for p in itertools.combinations(test.index, r=2)]

А это выглядит так:

          A      B
a,b  Alexis  Alexi
a,c  Alexis  Peter
a,d  Alexis   Pete
b,c   Alexi  Peter
b,d   Alexi   Pete
c,d   Peter   Pete

Затем вы можете использовать reset_index (чтобы получить текущий индекс в качестве столбца, но это зависит от того, что именно вы хотите) merge с names в столбцах A и B, заполните nan значением 0, отбросьте два столбца A и B и переименуйте (при необходимости) :

a = a.reset_index().merge(names, how = 'left', on = ['A','B']).fillna(0).\
     drop(labels = ['A','B'], axis=1).rename(columns = {'index':'row', 'similarity':'col_A'})

Дай мне знать, сможешь ли ты делать то, что хочешь, после

Обновлено: с новым результатом, который вы ищете, вы можете:

a = pd.DataFrame([p for p in itertools.combinations(test['col_A'], 2)],columns =['A','B'],
                 index=pd.MultiIndex.from_tuples([p for p in itertools.combinations(test.index, r=2)], names=['first', 'second']))

Примечание: itertools генерирует кортежи, которые используются в pd.MultiIndex.from_tuples для определения вашего мультииндексного DF.

Теперь вы можете merge (чтобы сохранить мультииндекс, вам нужно reset_index до и set_index после:

a = a.reset_index().merge(names, how = 'left', on = ['A','B']).fillna(0).\
         drop(labels = ['A','B'], axis=1).set_index(['first', 'second'])

Вот еще один способ сделать это, используя мультииндексы, merge и map:

from itertools import combinations

a = pd.DataFrame(index = pd.MultiIndex.from_tuples(list(combinations(test.col_A,2))))
a = a.merge(names, left_index=True, right_on=['A','B'],how='left').fillna(0)
testmap = test.reset_index().set_index('col_A').squeeze()

a['A'] = a.A.map(testmap)
a['B'] = a.B.map(testmap)
a = a.set_index(['A','B'])
a

Выход:

     similarity
A B            
a b         0.9
  c         0.0
  d         0.0
b c         0.0
  d         0.0
c d         0.8

Подробности

• Создайте MultiIndex, используя комбинации из itertools
• Объедините пустой фрейм данных с мультииндексом с фреймом данных 'names' и заполните NaN нулем
• Используйте set_index, чтобы создать серию для сопоставления col_A со значениями row в тесте