How to iterate over columns and connacenate two columns into one

November 29, 2021

I have a dataframe:

               Border #1 [from] Border #1 [to]  Border #2 [from] Border #2 [to]
index                                                                  
0                   BE           BE_AL                 PL              SK
1                   BE           BE_AL                 PL              SK

And I want to connect every two columns into one (I have many more columns), the desired result:

                   Border #1                Border #2
index                                                                  
0                   BE_BE_AL                 PL_SK
1                   BE_BE_AL                 PL_SK

For one column I could do:

df['Border#1']=df['Border #1 [from]']+'_'+df['Border #1 [to]']

but how can I do it for multiple columns?

>Solution :

Create MutliIndex by split by [ with space, so possible select both levels by DataFrame.xs and join by +:

df.columns = df.columns.str.strip(']').str.split('\s+\[', expand=True)
print (df)
  Border #1        Border #2    
       from     to      from  to
0        BE  BE_AL        PL  SK
1        BE  BE_AL        PL  SK

print (df.columns)
MultiIndex([('Border #1', 'from'),
            ('Border #1',   'to'),
            ('Border #2', 'from'),
            ('Border #2',   'to')],
           )

df = df.xs('from', axis=1, level=1) +'_'+ df.xs('to', axis=1, level=1)
print (df)
  Border #1 Border #2
0  BE_BE_AL     PL_SK
1  BE_BE_AL     PL_SK