Суммировать строки из двух разных фреймов данных на основе значения столбцов

Не так уж и прост твой вопрос...

Предлагаемый сценарий

import pandas as pd

df1 = pd.DataFrame({'ID': [11, 11, 11, 12, 12, 12],
                   'Year': [2023, 2023, 2023, 2022, 2022, 2022],
                   'Primary_Location': ['NewYork', 'Lyon', 'Berlin', 'Newyork', 'Lyon', 'Berlin'],
                   'Secondary_Location': ['Chicago', 'Chicago,Paris', 'Paris', 'Chicago', 'Chicago,Paris', 'Paris'],
                   'Sales': [100, 200, 300, 150, 250, 400]
                   })

df2 = pd.DataFrame({'ID': [11, 11, 12, 12],
                   'Year': [2023, 2023, 2022, 2022],
                   'Primary_Location': ['Chicago', 'Paris', 'Chicago', 'Paris'],
                   'Sales': [150, 200, 300, 350]
                   })

tot = []
def func(g, iterdf, len_df1, i = 0):
    global tot
    kv = {g['Primary_Location'].iloc[i]:g['Sales'].iloc[i] for i in range(len(g))}
    while i < len_df1:
        row = next(iterdf)[1]
        # Select specific df1 rows to modify by ID and Year criteria
        if g['ID'][g.index[0]]==row['ID'] and g['Year'][g.index[0]]==row['Year']:
            tot.append(row['Sales'] + sum([kv[town] for town in row['Secondary_Location'].split(',') if town in kv]))
        i+=1

df2.groupby(['ID', 'Year'], sort=False).apply(lambda g: func(g, df1.iterrows(), len(df1)))
df1['Sales'] = tot
print(df1)

Результат :

   ID  Year Primary_Location Secondary_Location  Sales
0  11  2023          NewYork            Chicago    250
1  11  2023             Lyon      Chicago,Paris    550
2  11  2023           Berlin              Paris    500
3  12  2022          Newyork            Chicago    450
4  12  2022             Lyon      Chicago,Paris    900
5  12  2022           Berlin              Paris    750

Вы уверены в результате в строке 3, мой скрипт нашел 450, а не 400?

Объяснение :

1 - group(...).apply(...) отправляет две группы из df2 одну за другой в func() :

   ID  Year Primary_Location  Sales
0  11  2023          Chicago    150
1  11  2023            Paris    200

   ID  Year Primary_Location  Sales
2  12  2022          Chicago    300
3  12  2022            Paris    350

2 - kv возвращает словари из df2 следующим образом:

(каждая итерация соответствует группе, т.е. ID + год)

call 1 - {'Chicago': 100, 'Paris': 200}
call 2 - {'Chicago': 300, 'Paris': 350}

3 - Функция while, за которой следует использование next(iterator), позволяет исследовать строки в g (группе) одну за другой:

while i < len_df1:
    row = next(iterdf)[1]
...
    i+=1

4 - Условие if в цикле while позволяет отфильтровать df1 строк таким образом, чтобы ID и Year соответствовали строкам df2.

И для каждой корреспонденции добавить в глобальный список tot df1 и df2 значения продаж

5 — tot — это глобальный список для запоминания значений, который передается в df1 для создания столбца Sales:

df1['Sales'] = tot

Результат с новым образцом фреймов данных:

   ID  Year Primary_Location Secondary_Location  Sales
0  11  2023          NewYork            Chicago    250
1  11  2023           Berlin            Chicago    450
2  11  2022          Newyork            Chicago    450
3  11  2022           Berlin            Chicago    700

Это должно работать:

s = 'Secondary_Location'
(df1.assign(Secondary_Location = lambda x: x[s].str.split(','))
.explode(s)
.join(df2.set_index(['ID','Year','Primary_Location'])['Sales'].rename('Sales_2'),on = ['ID','Year',s])
.groupby(level=0)['Sales_2'].sum()
.add(df1['Sales']))

Или

df3 = (df1.assign(Secondary_Location = df1['Secondary_Location'].str.split(',')) #split Secondary_Location column into list and explode it so each row has one value
.explode('Secondary_Location'))


(df3[['ID','Year','Secondary_Location']].apply(tuple,axis=1) #create a series where ID, Year and Secondary_Location are a combined into a tuple so we can map our series created below to bring in the values needed.
.map(df2.set_index(['ID','Year','Primary_Location'])['Sales']) #create a series with lookup values in index, and make a series by selecting Sales column
.groupby(level=0).sum() #when exploding the column above, the index was repeated, so groupby(level=0).sum() will combine back to original form.
.add(df1['Sales'])) #add in original sales column

Оригинальный ответ:

s = 'Secondary_Location'
(df.assign(Secondary_Location = lambda x: x[s].str.split(','))
.explode(s)
.join(df2.set_index(['ID','Year','Primary_Location'])['Sales'].rename('Sales_2'),on = ['ID','Year',s])
.groupby(level=0)
.agg({**dict.fromkeys(df,'first'),**{s:','.join,'Sales_2':'sum'}})
.assign(Sales = lambda x: x['Sales'] + x['Sales_2'])
.drop('Sales_2',axis=1))

Выход:

   ID  Year Primary_Location Secondary_Location  Sales
0  11  2023          NewYork            Chicago    250
1  11  2023             Lyon      Chicago,Paris    550
2  11  2023           Berlin              Paris    500
3  12  2022          Newyork            Chicago    450
4  12  2022             Lyon      Chicago,Paris    900
5  12  2022           Berlin              Paris    750