Adding a new row to a dataframe with correct mapping in pandas

Something similar to the above question.

      carrier_plan_identifier           ...            hios_issuer_identifier
1                        AUSK           ...                           99806.0
2                        AUSM           ...                           99806.0
3                        AUSN           ...                           99806.0
4                        AUSS           ...                           99806.0
5                        AUST           ...                           99806.0

I need to pick multiple columns, lets say carrier_plan_identifier, wellthie_issuer_identifier and hios_issuer_identifier.

With these 3 columns I need to run a select query, something like ,

select id from table_name where carrier_plan_identifier = 'something' and wellthie_issuer_identifier = 'something' and hios_issuer_identifier = 'something'

I need to add id column back to my existing dataframe

Currently, I am doing something like this,

for index, frame in df_with_servicearea.iterrows():if frame['service_area_id'] and frame['issuer_id']:# reading from medical plans tablemedical_plan_id = getmodeldata.get_medicalplans(sess, frame['issuer_id'], frame['hios_plan_identifier'], frame['plan_year'],frame['group_or_individual_plan_type'])frame['medical_plan_id'] = medical_plan_iddf_with_servicearea.append(frame)

when I do this,frame['medical_plan_id'] = medical_plan_id , nothing is added. But when I do df_with_servicearea['medical_plan_id'] = medical_plan_id only the last value of the loop is added to all the rows. I am not sure if this is the correct way to do this.

Update -:

After using , I am getting 4 rows , instead of 2 rows which should be there.

df_with_servicearea = df_with_servicearea.append(frame)wellthie_issuer_identifier       ...       medical_plan_id
0                   UHC99806       ...                   NaN
1                   UHC99806       ...                   NaN
0                   UHC99806       ...              879519.0
1                   UHC99806       ...              879520.0

Update 2 - Implemented based on Mayank's answer- Hi Mayank , Is something like this you are suggesting.

for index, frame in df_with_servicearea.iterrows():

    if frame['service_area_id'] and frame['issuer_id']:# reading from medical plans tabledf_new = getmodeldata.get_medicalplans(sess, frame['issuer_id'], frame['hios_plan_identifier'], frame['plan_year'],frame['group_or_individual_plan_type'])df_new.columns = ['medical_plan_id', 'issuer_id', 'hios_plan_identifier', 'plan_year','group_or_individual_plan_type']new_df = pd.merge(df_with_servicearea, df_new, on=['issuer_id', 'hios_plan_identifier', 'plan_year', 'group_or_individual_plan_type'], how='left')print new_df

my get_medicalplans function where I am calling the select query.

def get_medicalplans(self,sess, issuerid, hios_plan_identifier, plan_year, group_or_individual_plan_type):try:medical_plan = sess.query(MedicalPlan.id, MedicalPlan.issuer_id, MedicalPlan.hios_plan_identifier,MedicalPlan.plan_year, MedicalPlan.group_or_individual_plan_type).filter(MedicalPlan.issuer_id == issuerid,MedicalPlan.hios_plan_identifier == hios_plan_identifier,MedicalPlan.plan_year == plan_year,MedicalPlan.group_or_individual_plan_type == group_or_individual_plan_type)sess.commit()return pd.read_sql(medical_plan.statement, medical_plan.session.bind) 

The simplest solution to your issue is to change last row into:

    df_with_servicearea = df_with_servicearea.append(frame)

However, if you want to add new column, use:

df_with_servicearea['medical_plan_id'] = df_with_servicearea.apply(lambda row:getmodeldata.get_medicalplans(sess,row['issuer_id'],row['hios_plan_identifier'],row['plan_year'],row['group_or_individual_plan_type'])if row['service_area_id']and row['issuer_id']else np.nan)