Convert dataframe to numpy matrix where indexes stored in dataframe

numpy, pandas, python

Solution

Consider the dataframe `df`

df = pd.DataFrame(dict(
        time=pd.date_range('2015-08-30', periods=14000, freq='H'),
        usd=(np.random.randn(14000) / 100 + 1.0005).cumprod()
    ))

Then we can set the index with the `date` and `hour` of `df.time` column and `unstack`. We take the `values` of this result in order to access the numpy array.

a = df.set_index([df.time.dt.date, df.time.dt.hour]).usd.unstack().values

Problem

I have a dataframe that looks like so ``` time usd hour day 0 2015-08-30 07:56:28 1.17 7 0 1 2015-08-30 08:56:28 1.27 8 0 2 2015-08-30 09:56:28 1.28 9 0 3 2015-08-30 10:56:28 1.29 10 0 4 2015-08-30 11:56:28 1.29 11 0 14591 2017-04-30 23:53:46 9.28 23 609 ``` Given this how would I go about building a numpy 2d matrix with hour being one axis day being the other axis and then usd being the value stored in the matrix

Original source