flatten multiple index into a DataFrame header

Question

Assuming the following DataFrame:

     A    B    C    D    E    F
0   d1   10  d11   10  d21   10
1   d2   30  d12   30  d22   30
2   d3   40  d13   40  d23   40
3   d4  105  d14  105  NaN  NaN
4   d5   10  d15   10  NaN  NaN
5   d6   30  NaN  NaN  NaN  NaN
6   d7   40  NaN  NaN  NaN  NaN
7   d8   10  NaN  NaN  NaN  NaN
8   d9    5  NaN  NaN  NaN  NaN
9  d10   10  NaN  NaN  NaN  NaN

how do i merge all the descriptions into a single header that is associated with the respective value ?

d1  d2  d3  d4  d5  d6  d7  d8  d9  d10 d11 d12 d13 d14 d15 d16 d17 d18 d19 d20 d21 d22 d23 d24 d25 d26 d27 d28 d29 d30
0   10  30  40  105 10  30  40  10  5   10  10  30  40  105 10  30  40  10  5   10  10  30  40  105 10  30  40  10  5   10

take note that some descriptions of the original dataframe could have blank values and descriptions (NaN)

i realised i asked something similar before but after putting it into my code it does not achieve what i needed

Bharath M Shetty · Accepted Answer · 2017-11-12 05:22:58Z

4

We can use pd.concat iterating over column pairs i.e

pairs = list(zip(df.columns,df.columns[1:]))[::2]
# [('A', 'B'), ('C', 'D'), ('E', 'F')]
# iterate over pairs and set the first element of pair as index and rename the column name to 0. Then concat and drop na. 
ndf = pd.concat([df[list(i)].set_index(i[0]).rename(columns={i[1]:0})
                          for i in pairs],0).dropna()

  d1    d2    d3     d4    d5    d6    d7    d8   d9   d10   d11   d12  \
0  10.0  30.0  40.0  105.0  10.0  30.0  40.0  10.0  5.0  10.0  10.0  30.0   

    d13    d14   d15   d21   d22   d23  
0  40.0  105.0  10.0  10.0  30.0  40.0

edited Nov 12, 2017 at 5:22

answered Nov 12, 2017 at 4:33

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

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

2 Comments

user2731915 Over a year ago

it messes up the DataFrame eg v1 d1 v2 d3

Bharath M Shetty Over a year ago

Sure about that? Check the dataframe you have provided

piRSquared · Accepted Answer · 2017-11-12 04:56:43Z

2

r = np.arange(df.shape[1])
a = r % 2
b = r // 2

df.T.set_index([a, b]).T.stack().set_index(0).T

0  d1 d11 d21  d2 d12 d22  d3 d13 d23   d4  d14  d5 d15  d6  d7  d8 d9 d10
1  10  10  10  30  30  30  40  40  40  105  105  10  10  30  40  10  5  10

edited Nov 12, 2017 at 4:56

answered Nov 12, 2017 at 4:42

piRSquared

296k68 gold badges509 silver badges654 bronze badges

6 Comments

user2731915 Over a year ago

i got an error with your solution too .. could be due to the NaN values ValueError: cannot reshape array of size 77 into shape (3,newaxis,11)

Bharath M Shetty Over a year ago

Oh god this is awesome.

piRSquared Over a year ago

Lotsa T (-: !

Bharath M Shetty Over a year ago

I wanted to do the same. I thought of pairing using columns but you went to another level. Math is cool.

piRSquared Over a year ago

I messed with your way as well for a bit.

|

Bharath M Shetty · Accepted Answer · 2017-11-12 05:38:53Z

2

For fun:-)

pd.DataFrame(sum([df1.values.tolist() for _, df1 in df.groupby((df.dtypes=='object').cumsum(),axis=1)],[])).dropna().set_index(0).T

0  d1    d2    d3     d4    d5    d6    d7    d8   d9   d10   d11   d12  \
1  10.0  30.0  40.0  105.0  10.0  30.0  40.0  10.0  5.0  10.0  10.0  30.0

0  d13    d14   d15   d21   d22   d23  
1  40.0  105.0  10.0  10.0  30.0  40.0

edited Nov 12, 2017 at 5:38

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

answered Nov 12, 2017 at 5:12

BENY

324k22 gold badges176 silver badges250 bronze badges

5 Comments

Bharath M Shetty Over a year ago

My thought went to groupby but not in this way really nice.

BENY Over a year ago

@Bharath just for fun :-)

Bharath M Shetty Over a year ago

Also set_index(0) and transpose. Op's requirement so :)

BENY Over a year ago

@Bharath feel free to edit :-) on my phone .right now :-)

user2731915 Over a year ago

the whole dataframe is on a single row after i ran your code hmm

Collectives™ on Stack Overflow

flatten multiple index into a DataFrame header

3 Answers 3

2 Comments

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related