Python Pandas merge samed name columns in a dataframe

Question

So I have a few CSV files I'm trying to work with, but some of them have multiple columns with the same name.

For example I could have a csv like this:

ID   Name   a    a    a     b    b
1    test1  1    NaN  NaN   "a"  NaN
2    test2  NaN  2    NaN   "a"  NaN
3    test3  2    3    NaN   NaN  "b"
4    test4  NaN  NaN  4     NaN  "b"

loading into pandasis giving me this:

ID   Name   a    a.1  a.2   b    b.1
1    test1  1    NaN  NaN   "a"  NaN
2    test2  NaN  2    NaN   "a"  NaN
3    test3  2    3    NaN   NaN  "b"
4    test4  NaN  NaN  4     NaN  "b"

What I would like to do is merge those same name columns into 1 column (if there are multiple values keeping those values separate) and my ideal output would be this

ID   Name   a      b  
1    test1  "1"    "a"   
2    test2  "2"    "a"
3    test3  "2;3"  "b"
4    test4  "4"    "b"

So wondering if this is possible?

Answer 1

You could use groupby on axis=1, and experiment with something like

>>> def sjoin(x): return ';'.join(x[x.notnull()].astype(str))
>>> df.groupby(level=0, axis=1).apply(lambda x: x.apply(sjoin, axis=1))
  ID   Name        a  b
0  1  test1      1.0  a
1  2  test2      2.0  a
2  3  test3  2.0;3.0  b
3  4  test4      4.0  b

where instead of using .astype(str), you could use whatever formatting operator you wanted.