How to convert a pandas MultiIndex DataFrame into a 3D array

Question

Suppose I have a MultiIndex DataFrame:

                                c       o       l       u
major       timestamp                       
ONE         2019-01-22 18:12:00 0.00008 0.00008 0.00008 0.00008 
            2019-01-22 18:13:00 0.00008 0.00008 0.00008 0.00008 
            2019-01-22 18:14:00 0.00008 0.00008 0.00008 0.00008 
            2019-01-22 18:15:00 0.00008 0.00008 0.00008 0.00008 
            2019-01-22 18:16:00 0.00008 0.00008 0.00008 0.00008

TWO         2019-01-22 18:12:00 0.00008 0.00008 0.00008 0.00008 
            2019-01-22 18:13:00 0.00008 0.00008 0.00008 0.00008 
            2019-01-22 18:14:00 0.00008 0.00008 0.00008 0.00008 
            2019-01-22 18:15:00 0.00008 0.00008 0.00008 0.00008 
            2019-01-22 18:16:00 0.00008 0.00008 0.00008 0.00008

I want to generate a NumPy array from this DataFrame with a 3-dimensional, given the dataframe has 15 categories in the major column, 4 columns and one time index of length 5. I would like to create a numpy array with a shape of (4,15,5) denoting (columns, categories, time_index) respectively.

should create an array:

array([[[8.e-05, 8.e-05, 8.e-05, 8.e-05, 8.e-05],
        [8.e-05, 8.e-05, 8.e-05, 8.e-05, 8.e-05]],

       [[8.e-05, 8.e-05, 8.e-05, 8.e-05, 8.e-05],
        [8.e-05, 8.e-05, 8.e-05, 8.e-05, 8.e-05]],

       [[8.e-05, 8.e-05, 8.e-05, 8.e-05, 8.e-05],
        [8.e-05, 8.e-05, 8.e-05, 8.e-05, 8.e-05]],

       [[8.e-05, 8.e-05, 8.e-05, 8.e-05, 8.e-05],
        [8.e-05, 8.e-05, 8.e-05, 8.e-05, 8.e-05]]])

One used to be able to do this with pd.Panel:

panel = pd.Panel(items=[columns], major_axis=[categories], minor_axis=[time_index], dtype=np.float32)
...

How would I be able to most effectively accomplish this with a multi index dataframe? Thanks

unutbu · Accepted Answer · 2019-02-10 11:52:12Z

17

Since df.values is a (15*100, 4)-shaped array, you can call reshape to make it a (15, 100, 4)-shaped array:

arr = df.values.reshape(15, 100, 4)

Then call transpose to rearrange the order of the axes:

arr = arr.transpose(2, 0, 1)

Now arr has shape (4, 15, 100).

Using reshape/transpose is ~960x faster than to_xarray().to_array():

In [21]: df = pd.DataFrame(np.random.randint(10, size=(15*100, 4)), index=pd.MultiIndex.from_product([range(15), range(100)], names=['A','B']), columns=list('colu'))

In [22]: %timeit arr = df.values.reshape(15, 100, 4).transpose(2, 0, 1)
3.31 µs ± 23.2 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)

In [24]: %timeit df.to_xarray().to_array()
3.18 ms ± 24.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [25]: 3180/3.31
Out[25]: 960.7250755287009

edited Feb 10, 2019 at 11:52

answered Feb 10, 2019 at 11:32

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

2 Comments

g.a Over a year ago

Note, that this only works for fully orthogonal cases, ie when you have all combinations of the multi-index, and that is sorted in the proper order. Xarray is much slower, but also more robust.

g.a Over a year ago

The to_xarray function got reimplemented in xarray 0.16.2 . It is now orders of magnitude faster than it was before.

Josh Friedlander · Accepted Answer · 2019-02-10 11:40:50Z

12

How about using xarray?

res = df.to_xarray().to_array()

Result is an array of shape (4, 15, 5)

In fact the docs now recommend this as an alternative to pandas Panel. Note that you must have the xarray package installed.

answered Feb 10, 2019 at 11:40

Josh Friedlander

11.8k7 gold badges42 silver badges89 bronze badges

3 Comments

g.a Over a year ago

Note, that this method may be very slow for large dataframes in some cases. I tried this with a dataframe with millions of cells. Depending on which variable I had in the column and which in the rows the time was a few seconds in one case, but I stopped the execution after ~20 minutes in the other.

Jinhua Wang Over a year ago

This is the best solution by far.

g.a Over a year ago

The to_xarray function got reimplemented in xarray 0.16.2 . It is now orders of magnitude faster than it was before.

aEgoist · Accepted Answer · 2020-06-24 10:37:15Z

2

In case you have different length for minor axis, you may try this:

df.unstack().ffill().bfill().stack().values.reshape(*df.index.levshape,-1)

still seems awkward through, why Panel was deprecated anyway?

answered Jun 24, 2020 at 10:37

aEgoist

313 bronze badges

1 Comment

JoseOrtiz3 Over a year ago

Panel was deprecated because the ppl behind pandas only wanted two classes to maintain, Dataframe and Series, instead of three, and they figured multi-indexed Dataframes could do anything Panels could do in theory (in practice there's definitely a learning curve to map Panels operations to their equivalent multi-indexed Dataframe operations).

Collectives™ on Stack Overflow

How to convert a pandas MultiIndex DataFrame into a 3D array

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related