Join data from two data frame

October 17, 2023

I have two data frame that I want to join from df2 to df by using values of df2 rows. Something as below:

import pandas as pd
import numpy as np
#create DataFrame
df = pd.DataFrame({'Definition': ['Loan', 'Deposit'], 
                   '20231015': [28, 17],
                   '20231016': [5, 6],
                   '20231017': [10, 13],
                   'Notes':['','']})
df2 = pd.DataFrame({'BR_CD': ['Hanoi', 'Hochiminh'], 
                   'CUS_NM': ['A', 'B'],
                   'AMT': ['2', '3']})
df2['Conclusion'] = "[" + df2['BR_CD'] + "]" + ' ' + df2['CUS_NM'] + ' ' + df2['AMT'] 
x = "[" + df2['BR_CD'] + "]" + ' ' + df2['CUS_NM'] + ' ' + df2['AMT']
for i in x:
    df['Notes'].iloc[1] = i
df

With this code, df['Notes' just take last value of i:

But my goal is show show all values from df2['Conclusion]:

df3 = pd.DataFrame({'Definition': ['Loan', 'Deposit'], 
                   '20231015': [28, 17],
                   '20231016': [5, 6],
                   '20231017': [10, 13],
                   'Notes':['','[Hanoi] A 2;[Hochiminh] B 3']})
df3

If possible I want to down the line for each conclusion. Thank you.

>Solution :

Why not using directly:

df['Notes'].iloc[1] = ';'.join(x)

Or, better:

df.iloc[1, df.columns.get_loc('Notes')] = ';'.join(x)

Output:

  Definition  20231015  20231016  20231017                        Notes
0       Loan        28         5        10                             
1    Deposit        17         6        13  [Hanoi] A 2;[Hochiminh] B 3