Merge or append 2 dataframes row wise and add a check in a separate column determining which one it came from

I have the following 2 dataframes, df1,

import pandas as pd

data = {
    'commonshortname': ['SNX.US', '002400.CH', 'CDW.US', 'CEC.GR', '300002.CH'],
    'altshortname': ['SNX.US', '002400.SHE', 'CDW.US', 'CEC.XETRA', '300002.SHE'],
    'Code': ['SNX', '002400', 'CDW', 'CEC', '300002', ...],
    'Type': ['Common Stock', 'Common Stock', 'Common Stock', 'Common Stock', 'Common Stock'],
    'common': [1, 1, 1, 1, 1]
}

df1 = pd.DataFrame(data)

and df2 which looks like this,

data = {'altshortname': ['SEDG.US', 'MHLD.US', 'CDW.US', 'POLA.US', 'PHASQ.US'],
        'Code': ['SEDG', 'MHLD', 'CDW', 'POLA', 'PHASQ'],
        'Type': ['Common Stock', 'Common Stock', 'Common Stock', 'Common Stock', 'Common Stock'],
        'alt': [1, 1, 1, 1, 1]}

df2 = pd.DataFrame(data)

This is what they look like in dataframe form,

     commonshortname altshortname  Code           Type   common
0          SNX.US       SNX.US      SNX   Common Stock     1
1       002400.CH    002400.SHE  002400  Common Stock      1
2          CDW.US       CDW.US      CDW   Common Stock     1
3          CEC.GR     CEC.XETRA     CEC  Common Stock      1
4       300002.CH    300002.SHE  300002  Common Stock      1
...           ...          ...     ...           ...  ...

and

     altshortname    Code         Type         alt
0         SEDG.US    SEDG  Common Stock          1
1         MHLD.US    MHLD  Common Stock          1
2          CDW.US     CDW  Common Stock          1
3         POLA.US    POLA  Common Stock          1
4        PHASQ.US   PHASQ  Common Stock          1

I want to merge these 2 row wise, so that if they exist in both, the data from the top dataframe is taken and a 1 is added into the alt column for it.

The final frame should look like this,

     commonshortname altshortname  Code           Type   common   alt
0          SNX.US       SNX.US      SNX   Common Stock     1
1       002400.CH    002400.SHE  002400  Common Stock      1
2          CDW.US       CDW.US      CDW   Common Stock     1       1
3          CEC.GR     CEC.XETRA     CEC  Common Stock      1
4       300002.CH    300002.SHE  300002  Common Stock      1
0                      SEDG.US    SEDG  Common Stock               1
1                      MHLD.US    MHLD  Common Stock               1
3                      POLA.US    POLA  Common Stock               1
4                     PHASQ.US   PHASQ  Common Stock               1

Basically, if the data came from df1, there will be a 1 in the common column, if it came from df2, there will be a 1 in the alt column, and if it came from both, there will be a 1 in both columns.

Can this be done in pandas?

I tried to do a merge, but it keeps joining it column wise and I end up with millions of rows.

merged_df = pd.merge(df1, df2, on=['altshortname', 'Code', 'Type'], how='outer')

IIUC what you need is a concat and drop_duplicates

out = pd.concat([df1, df2], ignore_index=True).drop_duplicates(
    ["altshortname", "Code", "Type"], ignore_index=True
)

Here is a possible solution:

merged_df = pd.merge(df1, df2, on=['altshortname', 'Code', 'Type'], how='outer')
merged_df.fillna(0, inplace=True)

merged_df[['common', 'alt']] = merged_df[['common', 'alt']].astype(int)
merged_df.replace(0, '', inplace=True)
print(merged_df)

  commonshortname altshortname    Code          Type common alt
0          SNX.US       SNX.US     SNX  Common Stock      1    
1       002400.CH   002400.SHE  002400  Common Stock      1    
2          CDW.US       CDW.US     CDW  Common Stock      1   1
3          CEC.GR    CEC.XETRA     CEC  Common Stock      1    
4       300002.CH   300002.SHE  300002  Common Stock      1    
5                      SEDG.US    SEDG  Common Stock          1
6                      MHLD.US    MHLD  Common Stock          1
7                      POLA.US    POLA  Common Stock          1
8                     PHASQ.US   PHASQ  Common Stock          1