Pandas DataFrame from MultiIndex and NumPy structured array (recarray)

Question

First I create a two-level MultiIndex:

import numpy as np
import pandas as pd

ind = pd.MultiIndex.from_product([('X','Y'), ('a','b')])

I can use it like this:

pd.DataFrame(np.zeros((3,4)), columns=ind)

Which gives:

     X         Y     
     a    b    a    b
0  0.0  0.0  0.0  0.0
1  0.0  0.0  0.0  0.0
2  0.0  0.0  0.0  0.0

But now I'm trying to do this:

dtype = [('Xa','f8'), ('Xb','i4'), ('Ya','f8'), ('Yb','i4')]
pd.DataFrame(np.zeros(3, dtype), columns=ind)

But that gives:

Empty DataFrame
Columns: [(X, a), (X, b), (Y, a), (Y, b)]
Index: []

I expected something like the previous result, with three rows.

Perhaps more generally, what I want to do is to generate a Pandas DataFrame with MultiIndex columns where the columns have distinct types (as in the example, a is float but b is int).

Andy Hayden · Accepted Answer · 2016-06-09 17:50:14Z

2

This looks like a bug, and worth reporting as an issue github.

A workaround is to set the columns manually after construction:

In [11]: df1 = pd.DataFrame(np.zeros(3, dtype))

In [12]: df1.columns = ind

In [13]: df1
Out[13]:
     X       Y
     a  b    a  b
0  0.0  0  0.0  0
1  0.0  0  0.0  0
2  0.0  0  0.0  0

answered Jun 9, 2016 at 17:50

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

1 Comment

John Zwinck Over a year ago

Thank you. I have reported it as a bug: github.com/pydata/pandas/issues/13415 and your workaround is indeed effective.

piRSquared · Accepted Answer · 2016-06-09 17:51:19Z

pd.DataFrame(np.zeros(3, dtype), columns=ind)

Empty DataFrame
Columns: [(X, a), (X, b), (Y, a), (Y, b)]
Index: []

is just showing the textual representation of the dataframe output.

Columns: [(X, a), (X, b), (Y, a), (Y, b)]

is then just the text representation of the index.

if you instead:

df = pd.DataFrame(np.zeros(3, dtype), columns=ind)

print type(df.columns)

<class 'pandas.indexes.multi.MultiIndex'>

You see it is indeed a pd.MultiIndex

That said and out of the way. What I don't understand is why specifying the index in the dataframe constructor removes the values.

A work around is this.

df = pd.DataFrame(np.zeros(3, dtype))

df.columns = ind

print df

     X       Y   
     a  b    a  b
0  0.0  0  0.0  0
1  0.0  0  0.0  0
2  0.0  0  0.0  0

Collectives™ on Stack Overflow

Pandas DataFrame from MultiIndex and NumPy structured array (recarray)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related