Data Analysis IV - Merging and Reshaping Data

## View list of tabs including hidden tab
df = pd.ExcelFile('data/analysis4/Premium_Loss.xlsx')
print ('df.sheet_names=%s' % df.sheet_names)

## Count how many tabs in the Excel file 
number_tab = len(df.sheet_names)
print("There are %s tab(s) in the file." % (number_tab)) 

## Detect duplicated data from the first tab -"premium"
df_premium = pd.read_excel('data/analysis4/Premium_Loss.xlsx', sheet_name = "premium")
print("There are %s policies" % (df_premium.shape[0]))  

df_prem_dupes=df_premium[df_premium.duplicated()]
df_prem_dupes.head()
print("There are %s duplicate policies" % (df_prem_dupes.shape[0])) 

## View data from the second tab -"loss"
df_loss = pd.read_excel('data/analysis4/Premium_Loss.xlsx', sheet_name = "loss") 

## Slicing can achieve the same results as head()
df_loss[0:5]  


Count_Row=df_loss.shape[0] 
print("There are %s claims" % (Count_Row)) 

## Merge tabs - Inner Join
df_premium = df.parse("premium")
df_loss = df.parse("loss")
df_merge = pd.merge(df_premium, df_loss, on='Policy Number')
print("There are %s policies with incurred losses - here are the first 2:" % (df_merge.shape[0]))  
df_merge.head(2)

## Save the merge file to Excel
df_merge.to_excel('data/analysis4/MergePremiiumLoss.xlsx') 

## Combined tabs - Outer Join
df_combine = pd.merge(df_premium, df_loss, on='Policy Number', how='outer')
print("Here are the listing of %s policies incurred losses history - here are the first 2:" % (df_combine.shape[0])) 
df_combine.head(5)

## Save the combine file to Excel
df_combine.to_excel('data/analysis4/CombinePremiiumLoss.xlsx') 

df_combine.duplicated().head(10)

## View duplicated data after merging
df_dupes=df_combine[df_combine.duplicated()]
print("There are %s duplicate policies - here is the list:" % (df_dupes.shape[0])) 
df_dupes

## Drop duplicated data after merging 
df_merge_clean=df_combine.drop_duplicates()
df_merge_clean.head(2)

df_loss_1M = df_loss[df_loss['Loss'] > 1000000]

print("There are %s claims with losses > 1M - here they are:" % (df_loss_1M.shape[0]))  
df_loss_1M[['Claim Number','Loss']]

## Sepaprated loss into bins and print out first five rows
bins = [1, 50000, 75000, 500000, 1000000,20000000]
cats = pd.cut(df_loss['Loss'], bins)

## Count how many claims in each bins - ie. there are 150 claims in the loss range from $75K to $500K
pd.value_counts(cats)

## Show first 3 claims according to the matching discrete loss bin categories
pd.cut(df_loss['Loss'], bins, right=False).head(3)

## Group claim data into discrete loss bins by assigning group names, ie. first loss is a Clash coverage etc.
group_names = ['Layer1', 'Layer2', 'Layer3', 'XOL','Clash']
pd.cut(df_loss['Loss'], bins, labels=group_names).head()

## Example file is a list of claims data
df_claims = pd.read_excel('data/analysis4/Claim.xlsx')
df_claims[0:2]

## Group claims by Line of Business LOB and set Loss_Date as index
df_claims.set_index('Loss_Date', inplace=True)
df_claims_by_lob = df_claims.groupby('LOB')

## View claim by LOB - Nurse 
df_claims_by_lob.get_group('Nurse').head(2)

## Maximum value by LOB
df_claims_by_lob.max()

## Traspose the describe 
transport= df_claims_by_lob.describe().T
transport.head()

## Save the describe result to excel
df_claims_by_lob.describe().T.to_excel('data/analysis4/claim_distribution.xlsx')

## How to add dollars signs to the y-axis
## ref: https://stackoverflow.com/questions/40566413/matplotlib-pyplot-auto-adjust-unit-of-y-axis
from matplotlib.ticker import FuncFormatter
def y_fmt(y, pos):
    decades = [1e9, 1e6, 1e3, 1e0, 1e-3, 1e-6, 1e-9 ]
    suffix  = ["G", "M", "k", "" , "m" , "u", "n"  ]
    if y == 0:
        return str(0)
    for i, d in enumerate(decades):
        if np.abs(y) >=d:
            val = y/float(d)
            signf = len(str(val).split(".")[1])
            if signf == 0:
                return '{val:d} {suffix}'.format(val=int(val), suffix=suffix[i])
            else:
                if signf == 1:
#                     print(val, signf)
                    if str(val).split(".")[1] == "0":
                       return '{val:d} {suffix}'.format(val=int(round(val)), suffix=suffix[i]) 
                tx = "{"+"val:.{signf}f".format(signf = signf) +"} {suffix}"
                return tx.format(val=val, suffix=suffix[i])

                #return y
    return y

def y_fmt2(x, y):
    return '{:2.2e}'.format(x).replace('e', 'x10^')


ax = df_claims_by_lob['Loss'].plot(legend=True)

ax[0].yaxis.set_major_formatter(FuncFormatter(y_fmt))
# ax[0].yaxis.set_major_formatter(FuncFormatter(y_fmt2))
plt.show()