Concat (Merge) Asymmetrical Data frame Table in Python Pandas to Create Relationship Table

So I have two tables, Table1 and Table2

Table1:

Key1 Key2
A1 B1

Table2:

Key3 Key4 Key5
C1 D1 E1
C2 D2 E2
C3 D3 E3

And I want to combine both makings Table 3

Key2 Key4
B1 D1
B1 D2
B1 D3
B1

is there a function in pandas that would make this possible

Thanks.

Update: Using Andrej Kesely’s solution below.

code:

#create relationship tables from dataframes above
userinforel = pd.merge(computerlist,userinfo , how="cross")[["Name","UserInfo.UserName"]] #works
monitorlistrel = pd.merge(computerlist,monitorlist , how="cross")[["Name","SerialNumber"]] #does not work

output:

KeyError: "None of [Index(['Name', 'SerialNumber'], dtype='object')] are in the [columns]"
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
p:Tech SupportLoginReportLoginReport-To-SQLLoginReport-to-SQL.py in <module>
    127 
    128 if __name__ == "__main__":
--> 129     main()

p:Tech SupportLoginReportLoginReport-To-SQLLoginReport-to-SQL.py in main()
    117             json_to_sql(file.path) """
    118 
--> 119     json_to_sql('SK82-081AL101-20210903.0853.json') #loginreport v2
    120     #json_to_sql('SK82-081AL026-20210803.0849.json') #loginreport v1
    121 

p:Tech SupportLoginReportLoginReport-To-SQLLoginReport-to-SQL.py in json_to_sql(JSONFILE)
     80         #create relationship tables from dataframes above
     81         userinforel = pd.merge(computerlist,userinfo , how="cross")[["Name","UserInfo.UserName"]]
---> 82         monitorlistrel = pd.merge(computerlist,monitorlist , how="cross")[["Name","SerialNumber"]]
     83         #printerlistrel = pd.merge(computerlist,printerlist , how="cross")[["Name","ID"]]
     84         #programlistrel = pd.merge(computerlist,programlist , how="cross")[["Name","IDName"]]

~AppDataLocalProgramsPythonPython39libsite-packagespandascoreframe.py in __getitem__(self, key)
   3459             if is_iterator(key):
   3460                 key = list(key)
-> 3461             indexer = self.loc._get_listlike_indexer(key, axis=1)[1]
   3462 
   3463         # take() does not accept boolean indexers

~AppDataLocalProgramsPythonPython39libsite-packagespandascoreindexing.py in _get_listlike_indexer(self, key, axis)
   1312             keyarr, indexer, new_indexer = ax._reindex_non_unique(keyarr)
   1313 
-> 1314         self._validate_read_indexer(keyarr, indexer, axis)
   1315 
   1316         if needs_i8_conversion(ax.dtype) or isinstance(

~AppDataLocalProgramsPythonPython39libsite-packagespandascoreindexing.py in _validate_read_indexer(self, key, indexer, axis)
   1372                 if use_interval_msg:
   1373                     key = list(key)
-> 1374                 raise KeyError(f"None of [{key}] are in the [{axis_name}]")
   1375 
   1376             not_found = list(ensure_index(key)[missing_mask.nonzero()[0]].unique())

KeyError: "None of [Index(['Name', 'SerialNumber'], dtype='object')] are in the [columns]"

Update 2: Turns out there were similar column name as Andrej Kesely’s mentioned below, using suffix to fix the issue.

#create relationship tables from dataframes above
userinforel = pd.merge(computerlist,userinfo, how="cross")[["Name","UserInfo.UserName"]] #works
monitorlistrel = pd.merge(computerlist,monitorlist, how="cross")[["Name_x","SerialNumber_y"]] #works

Answer

You can try cross-merge:

df_out = pd.merge(df1, df2, how="cross")[["Key2", "Key4"]]
print(df_out)

Prints:

  Key2 Key4
0   B1   D1
1   B1   D2
2   B1   D3