import pandas as pd

# Read in endorsements
pp = pd.read_csv("pp_endorsements.csv")
rff = pd.read_csv("rff_endorsements.csv")
now = pd.read_csv("now_endorsements.csv")

# Merge by name
df = pd.merge(pp[["Candidate", "Endorsed by"]], rff[["Candidate", "Endorsed by"]], on = "Candidate", how = "outer")
df = pd.merge(df, now[["Candidate", "Endorsed by"]], on = "Candidate", how = "outer")

# Fill NaN values with an empty string for concatenation
df.fillna('', inplace = True)


# Concatenate the endorsements into a single string
df['Endorsed by'] = df['Endorsed by_x'] + ', ' + df['Endorsed by_y'] + ', ' + df['Endorsed by']
df['Endorsed by'] = df['Endorsed by'].str.strip(', ')
df.drop(columns=["Endorsed by_x", "Endorsed by_y"], inplace = True)

# Write to CSV
df.to_csv("reproductive_endorsements.csv", index = False)