'''
Created on July 28, 2016

@author: cnamgoong
'''
import pandas as pd, numpy as np, ConfigParser

#open config file
config = ConfigParser.ConfigParser()
config.read('config.ini')

#in files
in_CTR_folder = config.get('filename','out_folder')
in_CTR_file = config.get('filename','out_file_CTR')
in_VAR_folder = config.get('filename','out_folder')
in_VAR_file = config.get('filename','out_file_VAR')

#load data
df_CTR = pd.read_csv(in_CTR_folder+in_CTR_file)
df_VAR = pd.read_csv(in_VAR_folder+in_VAR_file)
print 'CTR num records: ' + str(len(df_CTR.index))
print 'VAR num records: ' + str(len(df_VAR.index))
print 'CTR num cols: ' + str(len(df_CTR.columns))
print 'VAR num cols: ' + str(len(df_VAR.columns))

#out files
out_file_diff = config.get('filename','out_file_diff')
out_file_diff_first100 = config.get('filename','out_file_diff_first100')
out_file_inVAR_notCTR = config.get('filename','out_file_inVAR_notCTR')
out_file_inCTR_notVAR = config.get('filename','out_file_inCTR_notVAR')
out_file_columns_diff = config.get('filename','out_file_columns_diff')

#output missing rows
df_diff = df_CTR[~np.in1d(df_CTR['Name'],df_VAR['Name'])]
if len(df_diff.index)>0: df_diff.to_csv(in_CTR_folder + out_file_inCTR_notVAR)

df_diff = df_VAR[~np.in1d(df_VAR['Name'],df_CTR['Name'])]
if len(df_diff.index)>0: df_diff.to_csv(in_CTR_folder + out_file_inVAR_notCTR)

#output difference in columns
df_CTR_col = pd.DataFrame({'in_CTR':['Y' for col in df_CTR.columns]},index=df_CTR.columns) 
df_VAR_col = pd.DataFrame({'in_VAR':['Y' for col in df_VAR.columns]},index=df_VAR.columns)
df_merge_col = pd.concat([df_CTR_col,df_VAR_col],axis=1)
df_merge_col.sort_index(axis=1,inplace=True)
df_merge_col.to_csv(in_CTR_folder + out_file_columns_diff)

#drop any rows that are not in common
df_CTR = df_CTR[np.in1d(df_CTR['Name'],df_VAR['Name'])]
df_VAR = df_VAR[np.in1d(df_VAR['Name'],df_CTR['Name'])]
#print len(df_CTR.columns)
#print len(df_VAR.columns)

#drop any columns that are not in common
common_cols = [col for col in df_CTR.columns if col in df_VAR.columns]
for col in [col for col in df_CTR.columns if col not in common_cols]: df_VAR[col]=''
for col in [col for col in df_VAR.columns if col not in common_cols]: df_CTR[col]=''
#df_CTR.drop(labels=[col for col in df_CTR.columns if col not in common_cols],axis=1,inplace=True)
#df_VAR.drop(labels=[col for col in df_VAR.columns if col not in common_cols],axis=1,inplace=True)
print len(df_CTR.columns)
print len(df_VAR.columns)

#set index to Name
df_CTR.set_index('Name',inplace=True)
df_VAR.set_index('Name',inplace=True)

#sort by index
df_CTR.sort_index(axis=0,inplace=True)
df_VAR.sort_index(axis=0,inplace=True)

#reorder columns
df_CTR.sort_index(axis=1,inplace=True)
df_VAR.sort_index(axis=1,inplace=True)

#take difference
difference_locations = np.where((df_CTR != df_VAR) & ~(df_CTR.isnull() & df_VAR.isnull()))
changed_CTR = df_CTR.values[difference_locations]
changed_VAR = df_VAR.values[difference_locations]
changed_index = [df_CTR.index[i] for i in difference_locations[0]] 
changed_col = [df_CTR.columns[i] for i in difference_locations[1]]
df_diff = pd.DataFrame({'name':changed_index,'column':changed_col,'val_CTR': changed_CTR, 'val_VAR': changed_VAR})

print 'num records match: ' + str(len(df_CTR.index))
print 'num columns match: ' + str(len(df_CTR.columns))
print 'num non-null cells to compare: ' + str(len(np.where(~(df_CTR.isnull() & df_VAR.isnull()))[0])) 
print 'num cells diff: ' + str(len(df_diff.index)) 

df_diff.to_csv(in_CTR_folder + out_file_diff)

df_list = []
for col in df_diff.column.unique():
    df_list.append(df_diff[df_diff.column == col].head(100))
df_diff_first100 = pd.concat(df_list,axis=0) 
df_diff_first100.to_csv(in_CTR_folder + out_file_diff_first100)

#df_group = df_diff.groupby(['column']).size()

#print only a subset of the file
#df_diff[df_diff.column=='Asset Notional'].to_csv(in_VAR_folder + out_VAR_file)

print 'done.'
print 'done. write from ' + in_CTR_folder + in_CTR_file
print 'done. write from ' + in_VAR_folder + in_VAR_file
print 'done. write to ' + in_CTR_folder