import pandas as pd
import os

os.chdir(r'../Data/Data_website/Scrapping')

##PE Firms
df = pd.DataFrame()
cntr = 1
for file in os.listdir('./ESGmetrics/PE Firms'):
    if file.endswith(".pkl"):
        print('PE Firm', cntr, file)
        inputFile = pd.read_pickle('./ESGmetrics/PE Firms/%s' %file, compression='zip').reset_index()
        df = pd.concat([df,inputFile], ignore_index=True)
        cntr = cntr+1
            
df['snapshot_date'] = pd.to_datetime(df['snapshot_date'])
df.to_stata('./ESGmetrics/Final Stata Files/R1_snapshot_data_final.dta', write_index=False, version=118, convert_dates={'snapshot_date':'td', 'snapshot_datetime':'tc'})

##Hedge Fund data
df = pd.DataFrame()
cntr = 1
for file in os.listdir('./ESGmetrics/HF Firms'):
    if file.endswith(".pkl"):
        print('PE Firm', cntr, file)
        inputFile = pd.read_pickle('./ESGmetrics/HF Firms/%s' %file, compression='zip').reset_index()
        df = pd.concat([df,inputFile], ignore_index=True)
        cntr = cntr+1
            
df['snapshot_date'] = pd.to_datetime(df['snapshot_date'])
df.to_stata('./ESGmetrics/Final Stata Files/R1_snapshot_data_final_HF.dta', write_index=False, version=118, convert_dates={'snapshot_date':'td', 'snapshot_datetime':'tc'})