To start off with I have two dataframes (taken from excel sheets) that share a field called Policy #. The two sets of data are policy info and claims info.
Policy data - can have multiple rows of the same policy # where all the data is the same except for the premium. I would like to group all the same policy #'s up into a single row per policy # and total their premium (but not total the other columns, like year or limit)
Claims data - each Policy can have one or multiple claims attached to it, so you'll see multiple policy #'s listed. I would like to total the three columns listed below and group together by policy #
Lastly, I want to merge or join (or whatever) the two together so I see one unique policy # row with the totaled up premium and totaled claims values (again I don't want to total year or limit) and all the other columns (there are more I've left out)
Policy data table
Policy # Actual Premium U/W Year Limit
0 MAX7PL0001001 44565.0000 2013 2000
1 MAX7PL0001518 46500.0000 2014 2000
2 MAX7PL0002012 47500.0000 2015 2000
3 MAX7PL0002443 47500.0000 2016 2000
4 MKLV7PL0002898 47500.0000 2017 2000
5 MKLV7PL0003379 50000.0000 2018 2000
6 MKLV7PL0003876 66000.0000 2019 3000
7 MKLV7PL0004395 33741.2600 2020 3000
8 MKLV7PL0004395 66544.7400 2020 5000
9 MKLV7PL0004981 129000.0000 2021 5000
10 MAX7PL0000309 128333.0000 2012 15000
11 MAX7PL0000624 128333.0000 2013 15000
12 MAX7PL0001086 128333.0000 2014 15000
13 MAX7PL0001617 128333.0000 2015 15000
14 MAX7PL0002099 128333.0000 2016 15000
15 MKLV7PL0002508 128333.0000 2017 15000
16 MKLV7PL0002965 128333.0000 2018 15000
17 MKLV7PL0003475 128333.0000 2019 15000
18 MKLV7PL0003984 147000.0000 2020 15000
19 MKLV7PL0004505 176666.6700 2021 15000
20 MKLV7PL0005078 199333.3300 2022 15000
21 MAX7PL0000017 12671.2329 2010 2000
22 MAX7PL0000017 125000.0000 2010 2000
23 MAXA7PL0001008 120000.0000 2011 2000
24 MAXA7PL0001039 140000.0000 2012 2000
25 MAXA7PL0001097 147000.0000 2013 2000
26 MAXA7PL0001158 154500.0000 2014 2000
27 MAXA7PL0001227 225000.0000 2015 2000
28 MAXA7PL0001321 8013.7000 2016 2000
29 MAXA7PL0001321 67191.7800 2016 2000
30 MAXA7PL0001321 149794.5200 2016 2000
31 MAXA7PL0001321 225000.0000 2016 2000
32 MAXA7PL0001321 8013.7000 2017 2000
33 MAXA7PL0001321 67191.7800 2017 2000
34 MAXA7PL0001321 149794.5200 2017 2000
35 MAXA7PL0001321 225000.0000 2017 2000
36 MAX7PL0001966 245000.0000 2015 5000
37 MAXA7PL0001060 265000.0000 2013 5000
38 MAXA7PL0001119 264000.0000 2014 5000
39 MAXA7PL0001181 275000.0000 2015 5000
40 MAXA7PL0001259 268000.0000 2016 5000
41 MKLM7PL0001371 16520.5500 2017 5000
42 MKLM7PL0001371 38875.0200 2017 5000
43 MKLM7PL0001371 72802.2200 2017 5000
44 MKLM7PL0001371 139802.2100 2017 5000
45 MKLM7PL0001502 20157.5300 2018 5000
46 MKLM7PL0001502 47433.3300 2018 5000
47 MKLM7PL0001502 88829.5700 2018 5000
48 MKLM7PL0001502 170579.5700 2018 5000
49 MKLM7PL0001684 345000.0000 2019 5000
50 MKLM7PL0001860 22715.7500 2020 5000
51 MKLM7PL0001860 53453.1500 2020 5000
52 MKLM7PL0001860 100103.0500 2020 5000
53 MKLM7PL0001860 192228.0500 2020 5000
54 MKLM7PL0002063 388500.0000 2021 5000
55 MKLM7PL0002246 35722.6000 2022 5000
56 MAX7PL0001977 400050.0000 2015 5000
57 MAX7PL0002051 43500.0000 2015 2000
58 MAX7PL0002405 100000.0000 2016 2000
59 MAX7PL0002186 72500.0000 2016 4000
Claims Data
Policy # Total Incurred Total Spend ALAE spend
0 MAX7PL0001966 350000.00 49510.26 49510.26
1 MAX7PL0001977 2719346.77 552833.60 552833.60
2 MAX7PL0002051 265000.00 195755.75 85755.75
3 MAX7PL0002099 0.00 0.00 0.00
4 MAX7PL0002186 17425.00 12093.00 12093.00
5 MAX7PL0002405 220000.00 219137.89 189137.89
6 MAX7PL0002443 49916.75 49916.75 49916.75
7 MAXA7PL0001227 237616.00 76147.15 76147.15
8 MAXA7PL0001227 155000.00 38604.82 38604.82
9 MAXA7PL0001227 146489.58 36538.35 36538.35
10 MAXA7PL0001259 2361392.53 2352988.95 352988.95
11 MAXA7PL0001259 1105183.37 451159.62 331244.62
12 MKLM7PL0001371 170000.00 0.00 0.00
13 MKLM7PL0001724 7500.00 0.00 0.00
As you can see in policy Data - an example is the policy ending in 1371 is listed multiple times. That I want rounded up to one row with just the premium summed up
In claims data you see something where the policy ends in 1227, however there's only 1 record of it in policy data, and I want to see the claims columns summed up and attached to that one record.
Any help would be appreciated, I've tried quite a few things already!