Having issue converting to traingle from long format (first time user of package)

[AmaanAliK]

Hello guys,

I am trying to build a model but keep running into the same issue- I convert my existing triangle into long and then input it into the triangle function but I get the weirdest triangle as an output. I have attached the code and the result, please help me out!!

Get the file path

file_path = os.path.join('C:\Users\akhan\Desktop', 'Data.xlsx')

Import the Excel file

df = pd.read_excel(file_path, sheet_name='Gross Paid')

Convert to Long Form

df = pd.melt(df, id_vars='Loss Origin', value_vars=list(range(1, df.shape[1])),var_name='Period', value_name='Amount')

Remove NA Values

df = df.dropna().reset_index()

Drop Index column if needed + Rename the columns

df = df.drop('index', axis=1)
df.columns = ["origin", "age", "values"]

Change format of Origin + Create valuation column

df['origin'] = pd.to_datetime(df['origin'], format='%Y%m')
df["valuation"] = (df["origin"].dt.year + df["age"] /12+1).astype(int)

tri = cl.Triangle(
df, origin="origin", development="valuation", columns="values", cumulative=True
)

[kennethshsu]

Hi @AmaanAliK, can you share your dataset? Or even just a sample of it?

[AmaanAliK]

DataScaled.xlsx
Here it is! Thank you!!!

[kennethshsu]

From looking at the data, I think we have a Quarter-by-Quarter triangle, is that right? The data, in its current form, is not clear for chainladder and pandas to understand. I try to keep your code as much as I can, but there are a few chagnes that are required. Take a look:

import chainladder as cl
import pandas as pd
import numpy as np

print(cl.__version__)

Be sure that you are on version 0.8.17!

df = pd.read_excel("/Users/kennethhsu/Downloads/DataScaled.xlsx")
df = pd.melt(
    df,
    id_vars="Loss Origin",
    value_vars=list(range(1, df.shape[1])),
    var_name="Period",
    value_name="Amount",
)
df = df.dropna().reset_index()
df = df.drop("index", axis=1)
df.columns = ["origin", "age", "values"]

df["origin_quarterly"] = (
    df["origin"].astype("str").str.strip().str[:4].astype("int") * 100 
    + (df["origin"].astype("str").str.strip().str[-1].astype("int") - 1) * 3 
    + 1
)
df["age_quarterly"] = df["age"] * 3

df["origin_quarterly"] = pd.to_datetime(df["origin_quarterly"], format="%Y%m")
df["valuation"] = df.apply(
    lambda x: x["origin_quarterly"] + pd.offsets.MonthEnd(x["age_quarterly"]), axis=1
)

We need a new df["origin_quarterly"] because our date format in its current form is ambiguous, it looks like they are in months. What we have done here is convert:
201501 -> 201501 (no change)
201502 -> 201504 This is needed because our 02 really means second quarter, not February.

• df["origin"].astype("str").str.strip().str[:4].astype("int") * 100 This takes the first four digits, the year, and multiply it by 100
• (df["origin"].astype("str").str.strip().str[-1].astype("int") - 1) * 3 + 1 This takes the last single digit, the quarter, doing some math to get it in month

df["age_quarterly"] needs to be recalculated because this is our real "monthly" age.

Then, we can convert df["origin_quarterly"] using pandas.to_datetime(), and calculate our df["valuation"] correctly.

All the above should be processed using pandas, as chainladder cannot understand the data in its raw form.

Once everything is good to go, we can then call chainladder.Triangle():

tri = cl.Triangle(
    df,
    origin="origin_quarterly",
    development="valuation",
    columns="values",
    cumulative=True,
)
tri

[AmaanAliK]

Thank you very much Ken! I will run through the code and keep you posted but your reasoning was point. Appreciate the guidance :)

[AmaanAliK]

Update: worked like a charm, thanks again @kennethshsu