Bottom Page

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)
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Filter data based on a value from another dataframe column and create a file using lo pawanmtm 1 107 Yesterday, 06:20 PM
Last Post: pawanmtm
  Assigning Column nunique values to another DataFrame column Pythonito 0 143 Jun-25-2020, 05:04 PM
Last Post: Pythonito
  Pandas - Dynamic column aggregation based on another column theroadbacktonature 0 215 Apr-17-2020, 04:54 PM
Last Post: theroadbacktonature
Question Dividing a single column of dataframe into multiple columns based on char length darpInd 2 317 Mar-14-2020, 09:19 AM
Last Post: scidam
  Merging two DataFrames based on indexes from two other DataFrames lucinda_rigeitti 0 220 Jan-16-2020, 08:36 PM
Last Post: lucinda_rigeitti
  matrixes product paul18fr 0 621 Jul-18-2019, 07:32 PM
Last Post: paul18fr
  Interpolate using multiple dataframes Lastwizzle 0 536 May-29-2019, 05:32 PM
Last Post: Lastwizzle
  Python QtableWidget get text of all cells and headers to dataframe Mady 3 8,074 Dec-15-2018, 06:46 PM
Last Post: Axel_Erfurt
  Create selection box to pass string value based on uniques in Excel column sneakysnek 1 806 Nov-18-2018, 07:29 PM
Last Post: Stefanovietch
  Removing rows at random based on the value of a specific column Mr_Keystrokes 4 939 Aug-24-2018, 11:15 AM
Last Post: Mr_Keystrokes

Forum Jump:


Users browsing this thread: 1 Guest(s)