Python Forum
Sum product multiple Dataframes based on column headers.
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Sum product multiple Dataframes based on column headers.
#1
I have a data frame with inputs where each group represents multiple inputs values that correspond to that groups respective premium ( script for test data below)

Each value in that row is a key to a table which contains factors for multiple perils (e.g fire, Water Weather).

What I want to do is create a premium column which is the sum product of each perils factors. Example output in premium_output_df.

input_df is an example of what my start data looks like albeit much smaller and with less column fields.

amount_of_insurance_df is a representation of the table which contains the peril factors with each groups corresponding Amount of Insurance value.

territory df is a representation of the table which contains the peril territory factors.

To run through an example, the Premium for group 1 will be the sum product of the perils. (0.1*0.94)+(0.2*0.3)=0.154=Total Premium group1

To give you an idea of the process I used to collect the data.

Each column in input_df represents a table that gets merged to come collect the factors for each peril, please see below for an example of a merge being done for the actual ( not raw) data set. amount_of_insurance=pd.merge(data_for_rater["Enter Amount of Insurance"],df['Amount of Insurance'],how='left',left_on='Enter Amount of Insurance' , right_on='Amount Of Insurance')

My original thought was to concat each side by side, but it was proving to be ineffective due to the sheet amount of tables ( 47).

Thoughts on a better way to proceed?

Test Data


input_df=pd.DataFrame(input_data)

amount_of_insurance_df=pd.DataFrame(data_amount_of_insurance)

territory_df=pd.DataFrame(data_territory)

premium_output_data={'Group':[1,2,3,4,5],'Amount of Insurance':[28000,28000,28000,28000,28000],'Territory':[701,702,703,704,705],'Fire Premium':[0.094,0.19,0.288,0.388,0.49],'Water Weather Premium':[0.06,0.12,0.2,0.3,0.42],'Total Premium':[0.154,0.31,0.488,0.688,0.91]}
premium_output_df=pd.DataFrame(premium_output_data)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Make unique id in vectorized way based on text data column with similarity scoring ill8 0 862 Dec-12-2022, 03:22 AM
Last Post: ill8
  New Dataframe Column Based on Several Conditions nb1214 1 1,783 Nov-16-2021, 10:52 PM
Last Post: jefsummers
  Pandas Data frame column condition check based on length of the value aditi06 1 2,655 Jul-28-2021, 11:08 AM
Last Post: jefsummers
Question [Solved] How to refer to dataframe column name based on a list lorensa74 1 2,239 May-17-2021, 07:02 AM
Last Post: lorensa74
  Add column based on others timste 8 3,954 Apr-03-2021, 07:39 AM
Last Post: devesh_sahu
  Matching multiple conditions and mapping multiple results, between two dataframes daveyg33 2 1,761 Jan-02-2021, 10:32 AM
Last Post: daveyg33
  matching a row from 2 seperate dataframes and minimg a column randor 0 1,518 Sep-03-2020, 08:57 PM
Last Post: randor
  Extracting rows based on condition on one column Robotguy 2 2,172 Aug-07-2020, 02:27 AM
Last Post: Robotguy
  Filter data based on a value from another dataframe column and create a file using lo pawanmtm 1 4,246 Jul-15-2020, 06:20 PM
Last Post: pawanmtm
  Pandas - Dynamic column aggregation based on another column theroadbacktonature 0 3,012 Apr-17-2020, 04:54 PM
Last Post: theroadbacktonature

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020