Блог

Проблема, которую я пытаюсь решить, состоит в том, чтобы согласовать ставки, применяемые к фактическим счетам, показанным в «Таблице счетов«, с тем, что должно быть установлено в «Таблице ставок«. Для каждой Учетной записи ее ставка может быть установлена на разных уровнях, либо на уровне Учетной записи, либо на Родительском уровне. Несколько учетных записей могут быть связаны с одним и тем же родителем, но они будут отличаться, например, в зависимости от валюты. Хотя я могу заставить его сравнивать, мое решение включает в себя много кода, который повторяется и не масштабируется, где в этом примере рассматриваются только 2 разные группировки, где я мог бы сравнить до 9 различных группировок.

Вот пример таблицы учета:

 import pandas as pd import numpy as np AccountTable = pd.DataFrame([[1234567890,456,'EUR',3.5],  [7854567890,15,'USD',2.7],  [9632587415,56,'GBP',1.4]], columns = ['Account','ParentID','Cur','Rate']) AccountTable  

Выход:

 Account ParentID Cur Rate 0 1234567890 456 EUR 3.5 1 7854567890 15 USD 2.7 2 9632587415 56 GBP 1.4  

Вот таблица ставок:

 RateTable = pd.DataFrame([['Account',1234567890,'EUR',3.5], # Rate set at account level and shuold return a match  ['ParentID',456,'EUR',3.5], # should be Unused as match found at account level  ['ParentID',15,'USD',2.7],# rate set at account level and matches   ['ParentID',15,'CAD',1.5],# CAD not in Account Table therfore unused   ['Account',9876542190,'EUR',3.5], # Account Table therfore unused   ['ParentID',56,'GBP',1.5]], # rate set on parent level but rates don't match so return should be mismatch here columns = ['Level_Type','ID','Cur','Set_Rate'])  

Выход:

 Level_Type ID Cur Set_Rate 0 Account 1234567890 EUR 3.5 1 ParentID 456 EUR 3.5 2 ParentID 15 USD 2.7 3 ParentID 15 CAD 1.5 4 Account 9876542190 EUR 3.5 5 ParentID 56 GBP 1.5  

Мои решения заключаются в следующем: я разделяю таблицу ставок на несколько фреймов данных на основе разных уровней. В данном случае 2 — Уровень учетной записи и Родительский уровень. Затем я оставил их независимо присоединяться к таблице учетных записей с помощью функции Groupby и сравнивать ставки.

 option1 = ['Account'] option2 = ['ParentID'] AccountView = RateTable[RateTable['Level_Type'].isin(option1)] ParentView = RateTable[RateTable['Level_Type'].isin(option2)] AccountView = AccountView.rename(columns={'Set_Rate':'Account_Set_Rate'}) ParentView = ParentView.rename(columns={'Set_Rate':'Parent_Set_Rate'}) AccountView = AccountView.rename(columns={'ID':'Account_ID'}) ParentView = ParentView.rename(columns={'ID':'Parent_ID'}) # new view to identify matches at Account level Only  df = pd.merge(AccountTable, AccountView, left_on=['Account','Cur'], right_on=['Account_ID','Cur'], how='left') df['Account_level_RateMatch'] = np.where(df['Rate'] == df['Account_Set_Rate'],'1','0').astype(int)   

 Account ParentID Cur Rate Level_Type Account_ID Account_Set_Rate Account_level_RateMatch 0 1234567890 456 EUR 3.5 Account 1.234568e 09 3.5 1 1 7854567890 15 USD 2.7 NaN NaN NaN 0 2 9632587415 56 GBP 1.4 NaN NaN NaN 0  

Вышесказанное повторяется, но теперь совпадает на родительском уровне:

 df = pd.merge(AccountTable, ParentView, left_on=['ParentID','Cur'], right_on=['Parent_ID','Cur'], how='left') df['Parent_level_RateMatch'] = np.where(df['Rate'] == df['Parent_Set_Rate'],'1','0').astype(int) # compare rates   

Выход:

 Account ParentID Cur Rate Level_Type Parent_ID Parent_Set_Rate Parent_level_RateMatch 0 1234567890 456 EUR 3.5 ParentID 456 3.5 1 1 7854567890 15 USD 2.7 ParentID 15 2.7 1 2 9632587415 56 GBP 1.4 ParentID 56 1.5 0  

Мне нужен лучший способ сравнить ставки по счетам с таблицей ставок, а не делать отдельные просмотры. Кроме того, логика должна быть такой, если совпадение найдено на первом уровне «Уровень учетной записи», оно останавливается на этом, и не нужно проверять следующий уровень, т. е. родительский уровень, например, в строке # 1, он совпадает как на уровне учетной записи, так и на родительском уровне.

Любые мысли или решения будут высоко оценены.

Желаемый Результат:

 Account ParentID Cur Rate IsMatch LevelFound 0 1234567890 456 EUR 3.5 1 Account 1 7854567890 15 USD 2.7 1 Parent 2 9632587415 56 GBP 1.4 0 Parent  

ОТРЕДАКТИРУЙТЕ решение, аналогичное оригинальному, но лучше соответствующее ожидаемому результату операции

 #define the order of the levels ordered_levels = ['Account','ParentID']   # fnd all the matching rates res = (  pd.concat(  [AccountTable  .merge(RateTable.loc[RateTable['Level_Type'].eq(lvl),#row with good level  # columns for comparision with AccountTable  ['ID','Cur','Set_Rate']]  .rename(columns={'ID':lvl, 'Cur':f'Cur_opt'}),   on=lvl, how='inner')  .query('Cur == Cur_opt') #EDIT to query same cur  .assign(LevelFound=lvl,   #EDIT if rate not the same then 0  Is_Match=lambda x: x['Rate'].eq(x['Set_Rate']).astype(int))   for lvl in ordered_levels]) # do the merge operation on each level  #EDIT for selecting first 1 if any, then first 0  .sort_values('Is_Match', ascending=False)   # keep the first matched per initial AccountTable or higher level non-match  .drop_duplicates(ordered_levels)   [AccountTable.columns.tolist()   ['LevelFound','Is_Match']]  ) print(res) # Account ParentID Cur Rate LevelFound Is_Match # 0 1234567890 456 EUR 3.5 Account 1 # 1 7854567890 15 USD 2.7 ParentID 1 # 3 9632587415 56 GBP 1.4 ParentID 0  

Оригинальное решение

Вот решение, сначала вам нужно определить порядок уровней, затем вы можете перебрать каждый из них, выбрать нужные строки RateTable , затем merge с учетной записью и сохранить только соответствующие cur и rate ( query ). concat все совпадающие данные и сохраняйте только первое совпадение для каждой учетной записи в начальной строке.

 #define the order of the levels ordered_levels = ['Account','ParentID']   # fnd all the matching rates matched = (  pd.concat(  [AccountTable  .merge(RateTable.loc[RateTable['Level_Type'].eq(lvl),#row with good level  # columns for comparision with AccountTable  ['ID','Cur','Set_Rate']]   .rename(columns={'ID':lvl, 'Cur':f'Cur_opt'}),   on=lvl, how='inner')  # keep only the matching data  .query('Cur == Cur_opt and Rate == Set_Rate')  # add the two columns for the ouput  .assign(LevelFound=opt, Is_Match=1)  for lvl in ordered_levels]) # do the merge operation on each level  .drop_duplicates(ordered_levels) # keep the first matched per initial AccountTable  [AccountTable.columns.tolist()   ['LevelFound','Is_Match']]  ) print(matched) # note that the row wihtout match is missing # Account ParentID Cur Rate LevelFound Is_Match # 0 1234567890 456 EUR 3.5 Account 1 # 1 7854567890 15 USD 2.7 ParentID 1   

Если вы хотите добавить строки без совпадения, то вы можете сделать

 res = AccountTable.merge(matched, how='left') print(res) # Account ParentID Cur Rate LevelFound Is_Match # 0 1234567890 456 EUR 3.5 Account 1.0 # 1 7854567890 15 USD 2.7 ParentID 1.0 # 2 9632587415 56 GBP 1.4 NaN NaN