Handling multiple column headers and same column names in csv - pandas/python

Question

I have a csv file that looks like this

        PROD1   PROD1   PROD2   PROD2
        X         Y       X       Y
AA  A   1         2       9       10
BB  B   3         4       11      12
CC  C   5         6       13      14
DD  D   7         8       15      16

The output I am trying to get has to look like this

                X   Y
AA  A   PROD1   1   2
BB  B   PROD1   3   4
CC  C   PROD1   5   6
DD  D   PROD1   7   8
AA  A   PROD2   9   10
BB  B   PROD2   11  12
CC  C   PROD2   13  14
DD  D   PROD2   15  16

I tried transposing the csv read with

data=pd.read_csv('transposedata.csv', header=None).T

But then I lose column info. I also tried this from another solution provided here at stackoverflow

df = pd.read_csv('transposedata.csv', header=[0,1])
a = df.columns.get_level_values(0).to_series()
b = a.mask(a.str.startswith('Unnamed')).ffill().fillna('')
df.columns = [b, df.columns.get_level_values(1)]

I end up with

                                           PROD1    PROD2    
  Unnamed: 0_level_1 Unnamed: 1_level_1     X  Y     X   Y
0                 AA                  A     1  2     9  10
1                 BB                  B     3  4    11  12
2                 CC                  C     5  6    13  14
3                 DD                  D     7  8    15  16

Any Help?

update when I run the solution given

data=pd.read_csv('transposedata1.csv', header=[0,1]).stack(level=0).sort_index(level=1)

i get this

        Unnamed:0_level_1   Unnamed:1_level_1   X   Y
0   PROD1   NaN NaN 1   2
1   PROD1   NaN NaN 3   4
2   PROD1   NaN NaN 5   6
3   PROD1   NaN NaN 7   8
0   PROD2   NaN NaN 9   10
1   PROD2   NaN NaN 11  12
2   PROD2   NaN NaN 13  14
3   PROD2   NaN NaN 15  16
0   Unnamed:0_level_0   AA  NaN NaN NaN
1   Unnamed:0_level_0   BB  NaN NaN NaN
2   Unnamed:0_level_0   CC  NaN NaN NaN
3   Unnamed:0_level_0   DD  NaN NaN NaN
0   Unnamed:1_level_0   NaN A   NaN NaN
1   Unnamed:1_level_0   NaN B   NaN NaN
2   Unnamed:1_level_0   NaN C   NaN NaN
3   Unnamed:1_level_0   NaN D   NaN NaN

Thanks

Serge Ballesta · Accepted Answer · 2020-03-25 17:17:59Z

4

You do not want to transpose the dataframe but stack one column level. Simply you must declare to pandas that the csv file has a 2 rows header:

data=pd.read_csv('transposedata.csv', header=[0,1]).stack(level=0).sort_index(level=2)

It should give:

             X   Y
AA A PROD1   1   2
BB B PROD1   3   4
CC C PROD1   5   6
DD D PROD1   7   8
AA A PROD2   9  10
BB B PROD2  11  12
CC C PROD2  13  14
DD D PROD2  15  16

answered Mar 25, 2020 at 17:17

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dark Knight Over a year ago

serge, I ran your code. I get a data frame with shape 16,13 of which I can get X,Y but the first two columns appear in rows 9-16. What am i missing?

Serge Ballesta Over a year ago

Can you share the dataframe values, so that I can reproduce?

Dark Knight Over a year ago

I share 4 rows at a time since i couldnt not cut and paste the 16 rows? or is there another way to share?

Serge Ballesta Over a year ago

What prevents you from copying 16 or 17 text lines?

Dark Knight Over a year ago

I have added the result i get in my original question. Thanks

|

Collectives™ on Stack Overflow

Handling multiple column headers and same column names in csv - pandas/python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related