你的问题没那么简单...

• • 拟议守则**

import pandas as pd

# Your dummy dataset
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]
                   })

def get_dict(g):
    return {g['Primary_Location'].iloc[i]:g['Sales'].iloc[i] for i in range(len(g))}
# return these dictionnaries
# {'Chicago': 100, 'Paris': 200}
# {'Chicago': 300, 'Paris': 350}

tot = []
def func(g):
    global df1, tot
    i, somme = [0]*2
    iterdf = df1.iterrows() # Dataframe iterator
    kv = get_dict(g) # Get dicts

    while i < len(df1):
        row = next(iterdf)[1]
        # Select specific df1 rows to modify by ID and Year criteria
        if g['ID'].iloc[1]==row['ID'] and g['Year'].iloc[1]==row['Year']:
            somme = row['Sales']
            for town in row['Secondary_Location'].split(','):
                if town in kv:
                    somme+=kv[town]
            tot.append(somme)
        i+=1

df2.groupby(['ID', 'Year']).apply(lambda g: func(g))
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吗？

这应该行得通：

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(','))
.explode('Secondary_Location'))

(df3[['ID','Year','Secondary_Location']].apply(tuple,axis=1)
.map(df2.set_index(['ID','Year','Primary_Location'])['Sales'])
.groupby(level=0).sum()
.add(df1['Sales']))

原始答复：

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