Creating Columns In A Pandas Dataframe Based On A Column Value In Other Dataframe

I have two pandas dataframe import pandas as pd import numpy as np import datetime data = {'group' :['A','A','B','B'], 'val': ['AA','AB','B1','B2'], 'cal1'

Solution 1:

Flexible Columns

If you want this works when we add several rows more in df1, you can do this.

combinations = df1.groupby(['group','val'])['cal3'].sum().reset_index()

for index_, row_ in combinations.iterrows():
    for index, row in df2.iterrows():
        if row['flag'] == 1:
            if row['group'] == row_['group']:
                df2.loc[index, row_['val'] + '_new'] = row['var1'] * df1[(df1['group'] == row_['group']) & (df1['val'] == row_['val'])]['cal3'].values[0]

Hard Code

You can use iteration to dataframe and change its specific column in each iteration, you can do something like this (but you need to add new column into your df1 first).

df1['cal3'] = df1['cal1'] * df1['cal2']

for index, row in df2.iterrows():
    if row['flag'] == 1:
        if row['group'] == 'A':
            df2.loc[index, 'AA_new'] = row['var1'] * df1[(df1['group'] == 'A') & (df1['val'] == 'AA')]['cal3'].values[0]
            df2.loc[index, 'AB_new'] = row['var1'] * df1[(df1['group'] == 'A') & (df1['val'] == 'AB')]['cal3'].values[0]

        elif row['group'] == 'B':
            df2.loc[index, 'B1_new'] = row['var1'] * df1[(df1['group'] == 'B') & (df1['val'] == 'B1')]['cal3'].values[0]
            df2.loc[index, 'B2_new'] = row['var1'] * df1[(df1['group'] == 'B') & (df1['val'] == 'B2')]['cal3'].values[0]

This is the result I got.