Iterate on a pandas dataframe column and create a new column based on condition-CodePudding

I am new to Pandas and I have some issues developing my code. I have a pandas dataframe like this:

What I want to do is creating a new column "Weight per meter" and then check each element on "Design Section" column and if this element equals to one of the elements on "Section Name" column, the value of "Weight per meter" column will be the corresponding element in "Weight Per Unit Length". Some thing like this:

How should I do this?

CodePudding user response：

You can do it with a map or merge. If your base dataframe is called df, then:

df['Weight per meter'] = df['Design Section'].map(df[['Section Name','Weight Per Unit Length']].set_index('Section Name').to_dict()['Weight Per Unit Length'])

Or with a merge:

df['Weight per meter'] = df[['Design Section']].merge(df[['Section Name','Weight Per Unit Length']].drop_duplicates(),left_on='Design Section',right_on='Section Name',how='left')['Weight Per Unit Length

For example:

df = pd.DataFrame({'Col 1':['A','B','C','D','E'],
                   'Col 2':[1,2,3,4,5],
                   'Col 3':['G','B','C','D','F']})

df['Col 4'] = df['Col 3'].map(df[['Col 1','Col 2']].set_index('Col 1').to_dict()['Col 2'])

Returns:

  Col 1  Col 2 Col 3  Col 4
0     A      1     G    NaN
1     B      2     B    2.0
2     C      3     C    3.0
3     D      4     D    4.0
4     E      5     F    NaN