Python: объединить два кадра данных в определенных столбцах, используя Excel-эквивалент индекса/сопоставления
Я пытаюсь воспроизвести тип Excel «индекс/соответствие» в Python. У меня есть два dfs.
дф1:
df2: (содержит таблицу поиска)
Желаемый результат: (добавляет результат в df1)
Я пытался
merged_df = df1.merge(df2, left_on=['Sector Code'], right_on=['Code'], how='left').
Это объединило df1 и df2, но дало мне каждое совпадение (SP500, SP400 и SP600), а не только одно совпадение для каждого.
Ответы 5
Вы можете использовать .mask, чтобы удалить значения из объединенных результатов. Заполненное значение столбцов SP500, SP400, SP600 можно использовать в качестве маски для столбцов SP400_Result, SP500_Result, SP600_Result.
В следующем коде предполагается, что в качестве пустых значений в df1 имеются пустые строки.
import pandas as pd
df1 = pd.DataFrame([
{'RIC': 'FI', 'Sector Code': 40, 'SP500': 'SP500', 'SP400': '', 'SP600': ''},
{'RIC': 'BRBR', 'Sector Code': 30, 'SP500': '', 'SP400': 'SP400', 'SP600': ''},
{'RIC': 'XPEL', 'Sector Code': 25, 'SP500': '', 'SP400': '', 'SP600': 'SP600'}])
df2 = pd.DataFrame({
'Code': [25, 30, 10, 40, 35, 20, 45, 15, 50, 55],
'Name': ['Consumer Discretionary', 'Consumer Staples', 'Energy', 'Financials',
'Health Care', 'Industrials', 'Information Technology', 'Materials',
'Telecommunication Services', 'Utilities'],
'SP400': ['.SPMDCD', '.SPMDCS', '.SPMDCE', '.SPMDCF', '.SPMDCA', '.SPMDCI',
'.SPMDCT', '.SPMDCM', '.SPMDCL', '.SPMDCU'],
'SP500': ['.SPSMCD', '.SPSMCS', '.SPSMCE', '.SPSMCF', '.SPSMCA', '.SPSMCI',
'.SPSMCT', '.SPSMCM', '.SPSMCL', '.SPSMCU'],
'SP600': ['.SPLRCD', '.SPLRCS', '.SPNY', '.SPSY', '.SPXHC', '.SPLRCI',
'.SPLRCT', '.SPLRCM', '.SPLRCL', '.SPLRCU']})
merged_df = df1.merge(
df2,
left_on=['Sector Code'],
right_on=['Code'],
how='left',
suffixes=['', '_Result']
).drop(columns=['Code', 'Name'])
# these orders must match
cols_left = [ 'SP500', 'SP400', 'SP600']
cols_right = ['SP500_Result', 'SP400_Result', 'SP600_Result']
merged_df.loc[:, cols_right] = merged_df.loc[:, cols_right].mask(
merged_df.loc[:, cols_left].eq('').values, '')
Ах, порядок столбцов плохой. я починю это
Предполагая, что у вас есть следующие фреймы данных:
df1 = pd.DataFrame({
'RIC':['FI','BRBR','XPEL'],
'Sector Code':[40,30,20],
'SP500':['SP500',np.nan,np.nan],
'SP400':[np.nan,'SP400',np.nan],
'SP600':[np.nan,np.nan,'SP600']
})
и
df2 = pd.DataFrame({
'code':[25,30,10,40,35,20,45,15,50,55],
'Name':['Consumer Discretionary','Consumer Staples','Energy','Financials',
'Health Care','Industrials','Information','Materials',
'Telecommunication Services','Utilities'],
'SP400':['.SPMDCD','.SPMDCS','.SPMDCE','.SPMDCF','.SPMDCA','.SPMDCI',
'.SPMDCT','.SPMDCM','.SPMDCL','.SPMDCU'],
'SP500':['.SPSMCD','.SPSMCS','.SPSMCE','.SPSMCF','.SPSMCA','.SPSMCI',
'.SPSMCT','.SPSMCM','.SPSMCL','.SPSMCU'],
'SP600':['.SPLRCD','.SPLRCS','.SPNY','.SPSY','.SPXHC','.SPLRCI',
'.SPLRCT','.SPLRCM','.SPLRCL','.SPLRCU']
})
Мы начнем с следующей функции для поиска значений из df2. Эта функция принимает на вход строку из df1 и имя столбца SP. Если индекс SP не равен NaN (что указывает на действительный индекс), он ищет соответствующее значение в df2, используя код сектора и индекс SP, и возвращает его. Если индекс SP равен NaN, он возвращает NaN.
def lookup_result(row, sp_col):
sector_code = row['Sector Code']
sp_index = row[sp_col]
if not pd.isna(sp_index):
return df2.loc[df2['code'] == sector_code, sp_index].values[0]
else:
return np.nan
Затем мы перебираем столбцы SP в df1, начиная с третьего столбца (индекс 2), поскольку первые два столбца не являются индексами SP. Для каждого столбца SP мы извлекаем имя индекса SP и применяем функцию Lookup_result к каждой строке df1. Результат сохраняется в соответствующем столбце результатов в df1.
result_columns = ['RESULT SP500', 'RESULT SP400', 'RESULT SP600']
for col in df1.columns[2:]:
sp_col_name = col.split()[-1]
df1[result_columns[df1.columns.get_loc(col)-2]] = df1.apply(lambda row: lookup_result(row, col), axis=1)
В конце у вас должно остаться:
Я бы предпочел не усложнять еще одну функцию. Я очень ценю ваш пост
Похоже, что плавление -> слияние -> поворот поможет, а затем соединение обратно к оригиналу:
out = df1.join(df1
.reset_index().melt(['index', 'RIC', 'Sector Code'])
.merge(df2.melt(['Code', 'Name']),
left_on=['Sector Code', 'value'],
right_on=['Code', 'variable'], how='left')
.pivot(index='index', columns='variable_x', values='value_y')
.add_prefix('RESULT ')
)
Выход:
RIC Sector Code SP500 SP400 SP600 RESULT SP400 RESULT SP500 RESULT SP600
0 FI 40 SP500 NaN NaN NaN .SPSMCF NaN
1 BRBR 30 NaN SP400 NaN .SPMDCS NaN NaN
2 XPEL 25 NaN NaN SP600 NaN NaN .SPLRCD
Вариант, если ваши пустые ячейки представляют собой пустые строки:
out = df1.join(df1
.reset_index().melt(['index', 'RIC', 'Sector Code'])
.merge(df2.melt(['Code', 'Name']),
left_on=['Sector Code', 'value'],
right_on=['Code', 'variable'], how='left')
.pivot_table(index='index', columns='variable_x', values='value_y',
aggfunc='first', fill_value='')
.add_prefix('RESULT ')
)
Выход:
RIC Sector Code SP500 SP400 SP600 RESULT SP400 RESULT SP500 RESULT SP600
0 FI 40 SP500 .SPSMCF
1 BRBR 30 SP400 .SPMDCS
2 XPEL 25 SP600 .SPLRCD
Извини. Я только что понял, что поместил 20 вместо 25 в df1. Другой способ сделать это — пройти через df1.
for index, row in df1.iterrows():
sec_code = row['Sector Code']
sp_code = ','.join([row[col] for col in ['SP500', 'SP400', 'SP600'] if not pd.isnull(row[col])])
df1.loc[index, f'Result_{sp_code}'] = df2[df2['code'] == sec_code][sp_code].values[0]
Здесь я просматриваю каждую строку df1, беру код сектора и сохраняю его в sec_code, а затем беру ненулевую строку в качестве sp_code. затем я использую метод .loc, чтобы назначить новый столбец и новое значение на основе sec_code и sp_code. Я использовал метод join, чтобы он возвращал строку вместо списка, содержащего эту строку (просто для простоты). Надеюсь, это даст желаемый результат.
Если вы хотите удалить NaN, то:
df1 = df1.fillna('')
Между '' вы можете поставить пробел или не поставить его без пробела.
Это было элегантное решение. Спасибо!
Этот код наиболее эффективен для огромных наборов данных.
import pandas as pd
df1 = pd.DataFrame([
{'RIC': 'FI', 'Sector Code': 40, 'SP500': 'SP500', 'SP400': '', 'SP600': ''},
{'RIC': 'BRBR', 'Sector Code': 30, 'SP400': 'SP400', 'SP500': '', 'SP600': ''},
{'RIC': 'XPEL', 'Sector Code': 25, 'SP400': '', 'SP500': '', 'SP600': 'SP600'}])
df2 = pd.DataFrame({
'Code': [25, 30, 10, 40, 35, 20, 45, 15, 50, 55],
'Name': ['Consumer Discretionary', 'Consumer Staples', 'Energy', 'Financials',
'Health Care', 'Industrials', 'Information Technology', 'Materials',
'Telecommunication Services', 'Utilities'],
'SP400': ['.SPMDCD', '.SPMDCS', '.SPMDCE', '.SPMDCF', '.SPMDCA', '.SPMDCI',
'.SPMDCT', '.SPMDCM', '.SPMDCL', '.SPMDCU'],
'SP500': ['.SPSMCD', '.SPSMCS', '.SPSMCE', '.SPSMCF', '.SPSMCA', '.SPSMCI',
'.SPSMCT', '.SPSMCM', '.SPSMCL', '.SPSMCU'],
'SP600': ['.SPLRCD', '.SPLRCS', '.SPNY', '.SPSY', '.SPXHC', '.SPLRCI',
'.SPLRCT', '.SPLRCM', '.SPLRCL', '.SPLRCU']})
"""
print(df1)
RIC Sector Code SP500 SP400 SP600
0 FI 40 SP500
1 BRBR 30 SP400
2 XPEL 25 SP600
print(df2)
Code Name SP400 SP500 SP600
0 25 Consumer Discretionary .SPMDCD .SPSMCD .SPLRCD
1 30 Consumer Staples .SPMDCS .SPSMCS .SPLRCS
2 10 Energy .SPMDCE .SPSMCE .SPNY
3 40 Financials .SPMDCF .SPSMCF .SPSY
4 35 Health Care .SPMDCA .SPSMCA .SPXHC
5 20 Industrials .SPMDCI .SPSMCI .SPLRCI
6 45 Information Technology .SPMDCT .SPSMCT .SPLRCT
7 15 Materials .SPMDCM .SPSMCM .SPLRCM
8 50 Telecommunication Services .SPMDCL .SPSMCL .SPLRCL
9 55 Utilities .SPMDCU .SPSMCU .SPLRCU
"""
sp_cols = [col for col in df2.filter(like='SP')]
res_dict = {}
for col in sp_cols :
res_dict[f'RESULT_{col}'] = df1['Sector Code'].apply(
lambda d : df2.set_index('Code').loc[d,col] if d in df2['Code'].tolist() else np.nan
)
#print(res_dict[f'RESULT_{col}'])
df1_join_res_dict = df1.join(pd.DataFrame(res_dict))
print(df1_join_res_dict.to_string())
"""
RIC Sector Code SP500 SP400 SP600 RESULT_SP400 RESULT_SP500 RESULT_SP600
0 FI 40 SP500 .SPMDCF .SPSMCF .SPSY
1 BRBR 30 SP400 .SPMDCS .SPSMCS .SPLRCS
2 XPEL 25 SP600 .SPMDCD .SPSMCD .SPLRCD
"""
Я очень ценю вашу помощь. Однако merged_df немного не тот. Например, FI находится в SP500 с кодом сектора 40. Он отображается в merged_df в столбце SP400_Results... может ли это быть потому, что порядок в df1 отличается от порядка в df2?