How to get data from another dataframe based on key values

I don't know merge very well. Perhaps there is a way to do based on it. Did it differently.

A 'number' column is created. To do this, the rows of the Area-ID-Series column of dataframe df1 are split() into an array by delimiter using split and expand=True is applied to get the columns. The second column is retrieved.

Further, the dataframe is grouped by df1.index, that is, it is called on each row, where the expression df2['Area-ID'] == x['Area-ID'].values[0] is a mask for indexing rows, and x[' number'].values[0] acts as a column name for df2 filtering. Explicit loc indexing is used, where the row indexes are on the left, the column name is on the right. .values[0] is used to extract the value from the list.

import pandas as pd

df1 = pd.read_csv('df1.csv', header=0)
df2 = pd.read_csv('df2.csv', header=0)

df1['number'] = df1['Area-ID-Series'].str.split('-', expand=True)[2].astype(str)


def my_func(x):
    df2.loc[df2['Area-ID'] == x['Area-ID'].values[0], x['number'].values[0]] = x['MODE'].values[0]


df1.groupby(df1.index).apply(my_func)

print(df2)

Output

  Area-ID  900 1800 2100
0   BSD-1  NaN   VM  NaN
1   BSD-2  NaN   VM  NaN
2   BSD-3  NaN   VM  NaN
3   BSE-1   VM  NaN   XM
4   BSE-2   VM  NaN   XM
5   BSE-3   VM  NaN   XM
6   SMR-1  NaN   VM  NaN