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
  Creating A List of DataFrames & Manipulating Columns in Each DataFrame firebird 1 203 Jul-31-2019, 04:04 AM
Last Post: scidam
  Compare between 2 DataFrames Nidhesh 2 213 Jul-26-2019, 08:16 AM
Last Post: Nidhesh
  matrixes product paul18fr 0 439 Jul-18-2019, 07:32 PM
Last Post: paul18fr
  Giving index when joining dataframes kw42chan 1 352 Jul-06-2019, 06:19 AM
Last Post: kw42chan
  Could anyone help me get the jaccard distance between my dataframes please? :) a_real_phoenix 0 418 Jun-27-2019, 06:01 PM
Last Post: a_real_phoenix
  Two dataframes merged Ecniv 10 745 Jun-16-2019, 09:10 PM
Last Post: Ecniv
  Statistical analysis of two dataframes zhl 1 548 Jun-11-2019, 07:26 PM
Last Post: Ecniv
  Interpolate using multiple dataframes Lastwizzle 0 298 May-29-2019, 05:32 PM
Last Post: Lastwizzle
  How to delete column if entire column values are "nan" Sri 4 599 Apr-13-2019, 12:16 PM
Last Post: Sri
  Python QtableWidget get text of all cells and headers to dataframe Mady 3 3,730 Dec-15-2018, 06:46 PM
Last Post: Axel_Erfurt

Forum Jump:


Users browsing this thread: 1 Guest(s)