Importing Data #316
-
|
Hey, just want to start by saying this is an exciting package. after reserving in excel for years, I am really excited for find a package that handles the data in this way and creates an environment that follows the way that I think about data and process. I am simultaneously working my way up both the Python and the chainladder learning curve, so forgive a noob question I am trying to pull in quarterly development triangles where the format looks like this YYYY-Qq, so for example 2021Q4. chain ladder seems to take the data in, although it does throw a warning about data being out of bounds, but when I try to look at the data, it is giving me garbage. see example. Pulling from Access
|
Beta Was this translation helpful? Give feedback.
Replies: 4 comments 6 replies
-
|
Hi @Obalus2022 , Syntactically, your code looks fine. The warning about some items having some development dates before the origin date indicates the negative development ages, so the data should be checked. Some other possible things to check:
Hope this helps, if not, I may need you to provide a bit more context and/or a reproducible example with fake data. |
Beta Was this translation helpful? Give feedback.
-
|
So this is what a simplified version of my data looks like: AccidentQuarter | ReportedQuarter | RiskCode | PDLALAE | OSLR 2006-Q1 | 2006-Q1 | ARP | 1199961.6 | 0.0 and this is what Triangles is putting out.
|
Beta Was this translation helpful? Give feedback.
-
|
Does this look like what you want? import chainladder as cl
import pandas as pd
from io import StringIO
data = """
AccidentQuarter|ReportedQuarter|RiskCode|PDLALAE|OSLR
2006-Q1|2006-Q1|ARP|1199961.6| 0.0
2006-Q1|2006-Q2|ARP|0.0| 0.0
2006-Q2|2006-Q2|ARP|106595.2| 0.0
2006-Q2|2006-Q3|ARP|481599.2| 0.0
2006-Q3|2006-Q3|ARP|1116264.8| 0.0
2006-Q3|2006-Q4|ARP|217327.6| 0.0
2006-Q3|2007-Q1|ARP|211620.0| 0.0
2006-Q3|2007-Q2|ARP|0.0| 0.0
2006-Q4|2006-Q4|ARP|358700.0| 0.0
2006-Q4|2007-Q1|ARP|56620.0| 0.0
2006-Q4|2007-Q2|ARP|1213920.4| 0.0
2006-Q4|2008-Q1|ARP|908103.6| 0.0
2006-Q4|2008-Q4|ARP|0.0| 0.0"""
triangle = cl.Triangle(
pd.read_csv(StringIO(data), delimiter='|'),
origin='AccidentQuarter', development='ReportedQuarter',
index='RiskCode', columns=['PDLALAE', 'OSLR'],
origin_format='%Y-%q', development_format='%Y-%q',
cumulative=False)
triangle[triangle['RiskCode']=='ARP']['PDLALAE'].sum(axis='index').incr_to_cum() |
Beta Was this translation helpful? Give feedback.
-
|
I think your assuming that data is a text string, Its actually a dataframe.
|
Beta Was this translation helpful? Give feedback.
-
|
Ok, while the data string is only part of a larger dataframe, I've used it to create a reproducible example. Even though that text is not complete, the resultant triangle looks as I'd expect given I don't have all the data: Do you expect something different? |
Beta Was this translation helpful? Give feedback.
-
|
Im a little confused as I see that your method works, but not sure what to do about mine. Are you suggesting that I turn my data frame into a string and then then read it in as a string. That seems to be counterintuitive. Oddly I did try that, but I still got the same result. Could the problem be with my origin and development formats?
|
Beta Was this translation helpful? Give feedback.
-
|
No, I do not suggest you convert your data to a string. Let's look instead at the difference in the code (chainladder syntax only) with expanded commentary: (
triangle[triangle['RiskCode']=='ARP']['PDLALAE'] # Filters for some index and column, Fine
.sum(axis='index') # Deliberately summing the index axis. If there is only one index .e.g. ARP, this is unnecessary.
.incr_to_cum() # This method takes our incremental triangle and makes it cumulative which is what I think you want.
)Your original code with commentary: (
triangle[triangle['RiskCode']=='ARP']['PDLALAE'] # Filters for some index and column, Fine
.sum() # No axis is specified in sum, so this sums the origin axis since we have a (1,1,12,12) shaped triangle. origin is the first axis with length > 1
.to_frame() # this converts it to a pandas dataframe which precludes using the estimators in chainladder.
)It sounds to me like you just need to accumulate your triangle with |
Beta Was this translation helpful? Give feedback.
-
|
Thanks so much appreciate all the help. |
Beta Was this translation helpful? Give feedback.
-
|
So, I have a followup on this, I have been able to get all of my loss data into Triangle and now I am attempting to do the same with premiums which come from a different database. I was able to then get my premium database all the way into Triangles, however when I tried to concat my premium and loss data frames into one dataframe, Triangle didnt like it: Am I doing something wrong?
Is using concat the best way to merge premium and loss data frames, or is there a better way? |
Beta Was this translation helpful? Give feedback.
-
This is exactly what I do when making policy/claim level triangles. There is nothing apparent from the code you show that anything is wrong. In these cases, it is important that you have a reproducible example of the issue. Otherwise it becomes way too time-consuming and often times impossible to trouble shoot. For example, here is an attempt at replicating this workflow with publicly available data: import chainladder as cl
import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/casact/chainladder-python/master/chainladder/utils/data/clrd.csv')
loss = df[['GRCODE', 'LOB', 'AccidentYear', 'DevelopmentYear', 'CumPaidLoss']]
prem = df[['GRCODE', 'LOB', 'AccidentYear', 'DevelopmentYear', 'EarnedPremDIR']]
comb = pd.concat((loss, prem), axis=0)
cl.Triangle(
comb, origin='AccidentYear', development='DevelopmentYear',
columns=['CumPaidLoss', 'EarnedPremDIR'], index=['GRCODE', 'LOB'],
cumulative=True)Unfortunately, I cannot replicate your issue with the above even after this attempt. |
Beta Was this translation helpful? Give feedback.
No, I do not suggest you convert your data to a string. Let's look instead at the difference in the code (chainladder syntax only) with expanded commentary:
( triangle[triangle['RiskCode']=='ARP']['PDLALAE'] # Filters for some index and column, Fine .sum(axis='index') # Deliberately summing the index axis. If there is only one index .e.g. ARP, this is unnecessary. .incr_to_cum() # This method takes our incremental triangle and makes it cumulative which is what I think you want. )Your original code with commentary:
( triangle[triangle['RiskCode']=='ARP']['PDLALAE'] # Filters for some index and column, Fine .sum() # No axis is specified in sum, so this sums the or…