Merge

Multiple.py

merged = pd.merge(df1, df2, on='C1', how='left')

# Grouping by 'C1' and checking for multiple occurrences
grouped = merged.groupby('C1')

# Creating the new DataFrame with custom values for multiple occurrences
df3 = pd.DataFrame(columns=merged.columns)
for name, group in grouped:
    if len(group) > 1:
        df3 = df3.append({col: 'multiple' if col == 'C2' else 'MULTIPLE' for col in merged.columns}, ignore_index=True)
    else:
        df3 = df3.append(group.iloc[0], ignore_index=True)

print(df3)