pythonpandasmulti-index

How to add a column level to pandas dataframe


I need a way to add a column level to a dataframe.

     | A | B | C | A | B | C |  
date 
2018   0   1   2   1   3   2

And this is what I want to do:

      |  ticker 1 |  ticker 2 |
      | A | B | C | A | B | C |  
date 
2018   0   1   2   1   3   2

Solution

  • Use GroupBy.cumcount for counts duplicates with converted columns to series by Index.to_series, add prefix and last assign back for MultiIndex in columns:

    df = pd.DataFrame({
            'A':list('abcdef'),
             'B':[4,5,4,5,5,4],
             'C':[7,8,9,4,2,3],
             'D':[1,3,5,7,1,0],
             'E':[5,3,6,9,2,4],
             'F':list('aaabbb')
    
    })
    df.columns= list('ABCABC')
    print (df)
       A  B  C  A  B  C
    0  a  4  7  1  5  a
    1  b  5  8  3  3  a
    2  c  4  9  5  6  a
    3  d  5  4  7  9  b
    4  e  5  2  1  2  b
    5  f  4  3  0  4  b
    
    s = df.columns.to_series()
    df.columns = ['ticker ' + s.groupby(s).cumcount().add(1).astype(str), s]
    print (df)
      ticker 1       ticker 2      
             A  B  C        A  B  C
    0        a  4  7        1  5  a
    1        b  5  8        3  3  a
    2        c  4  9        5  6  a
    3        d  5  4        7  9  b
    4        e  5  2        1  2  b
    5        f  4  3        0  4  b