Pandas - Create a column by looking up the value of a column in another column

I have the dataframe below

Number Name ID1     ID2    ID3
100000 a1   100000  100000 100000 
200000 a2   200000  200000 200000
101000 a3   100000  101000 101000
201545 a4   200000  201500 201545
101010 a5   100000  101000 101010
201500 a6   200000  201500 201500

And I want to create 3 new columns, and for each column, I need to take the value of the IDX column (x in [1-3]) and search in the Number column and from there return the first value and concatenate with the column value Name.

Number Name ID1     ID2    ID3    id_name1   id_name2   id_name3
100000 a1   100000  100000 100000 100000-a1  100000-a1  100000-a1
200000 a2   200000  200000 200000 200000-a2  200000-a2  200000-a2
101000 a3   100000  101000 101000 100000-a1  101000-a3  101000-a3
201545 a4   200000  201500 201545 200000-a2  201500-a6  201545-a4
101010 a5   100000  101000 101010 100000-a1  101000-a3  101010-a5
201500 a6   200000  201500 201500 200000-a2  201500-a6  201500-a6

That is, for ID1 I need to create an id_name1 column looking for the information contained in ID1 in the Number column (as it may have repeated, only the first one already helps) and thus, bring the value that is on the same line but in the Name column and so on concatenate these values to form the column id_name1.

I tried to do it for loop and loc / iloc but I couldn't do it.Can anyone give me a light?

Edit: I need to get the value of the ID1 column in the Number column and return the value of the Number column and the value of the Name column.

Solution

You can define some mappings and do a for-loop for each each column:

columns = ['ID1', 'ID2', 'ID3']
mappings = dict(zip(df.Number.values, df.Name.values))

for col in columns:
    df['id_name' + col[2]] = df[col].astype(str) + '-' + df[col].map(mappings)

df

#          Name  ID1    ID2     ID3      id_name1   id_name2    id_name3
# Number                            
# 100000    a1  100000  100000  100000   100000-a1  100000-a1   100000-a1
# 200000    a2  200000  200000  200000   200000-a2  200000-a2   200000-a2
# 101000    a3  100000  101000  101000   100000-a1  101000-a3   101000-a3
# 201545    a4  200000  201500  201545   200000-a2  201500-a6   201545-a4
# 101010    a5  100000  101000  101010   100000-a1  101000-a3   101010-a5
# 201500    a6  200000  201500  201500   200000-a2  201500-a6   201500-a6