[Python] Difficulty in merging two dataframes on two columns, ticker and date

I have reviewed numerous responses to this type of question. I am trying to merge two files by columns and the dataframes only share ticker and date, so if I set how='left', it shows data for the df_US[US_columns] and the header columns for df_SEDOL_ESG[ESG_columns] but no data for df_SEDOL_ESG[ESG_columns].

Likewise, if you set how = 'right', it shows the reverse. I have tried setting how = 'outer' and that does not merge the dataframes but lists them separately.

Below the code, I have attached the sample dataframe and dtypes since I wanted to insure that the date column was in a datetime format. Any guidance is appreciated.

import numpy as np
import pandas as pd

path0 = 'K:/QuantTest/Data/ESG/'
path1 = 'K:/QuantTest/Data/US/'


def US_ESG():

df_US = pd.read_csv(path1 + 'df_US_weekly_expectationt.csv', dtype={'ticker':'str'})
df_US.rename(columns = {'Date': 'date'}, inplace = True)
df_US['date'] = pd.to_datetime(df_US['date'], format='%m/%d/%Y', errors = 'coerce')

df_SEDOL_ESG = pd.read_csv(path0 + 'SEDOL_ESGt.csv', dtype = {'ticker':'str'})
df_SEDOL_ESG.rename(columns = {'Ticker':'ticker'}, inplace=True)
df_SEDOL_ESG['date'] = pd.to_datetime(df_SEDOL_ESG['date'], format='%m/%d/%Y', errors = 'coerce')

US_columns = ['ticker', 'date', 'volume', 'closing_price']
ESG_columns = ['ticker', 'date','AllCategories_Insight','AllCategories_CategoryVolumeTTM']

df_US_ESG = df_US[US_columns].merge(df_SEDOL_ESG[ESG_columns], how='left', on = ['ticker', 'date'])

df_US_ESG.to_csv(path0 + 'US_ESGt.csv', index = False)


if __name__ == "__main__":

US_ESG()

Continue reading...