[Solved] Get a subset of a data frame into a matrix

Question

Given (stealing from an earlier problem today):

"""
IndexID IndexDateTime IndexAttribute ColumnA ColumnB
   1      2015-02-05        8           A       B
   1      2015-02-05        7           C       D
   1      2015-02-10        7           X       Y
"""

import pandas as pd
import numpy as np

df = pd.read_clipboard(parse_dates=["IndexDateTime"]).set_index(["IndexID", "IndexDateTime", "IndexAttribute"])

df:

                                     ColumnA ColumnB
IndexID IndexDateTime IndexAttribute                
1       2015-02-05    8                    A       B
                      7                    C       D
        2015-02-10    7                    X       Y

using

df.values

returns

array([['A', 'B'],
       ['C', 'D'],
       ['X', 'Y']], dtype=object)

To subset, you can use a couple of techniques. Here,

df.loc[:, "ColumnA":"ColumnB"]

Returns all rows and slices from ColumnA to ColumnB. Other options include syntax like df[df["column"] == condition] or df.iloc[1:3, 0:5]; which approach is best more or less depends on your data, how readable you want your code to be, and which is fastest for what you’re trying to do. Using .loc or .iloc is usually a safe bet, in my experience.

In general for pandas problems, it is helpful to post some sample data rather than an image of your dataframe; otherwise, the burden is on the SO users to generate data that mimics yours.

Edit:
To convert currency to float, try this:

df.replace('[\$,]', '', regex=True).astype(float)

So, in a one-liner,

df.loc[:, "ColumnB":"ColumnC"].replace('[\$,]', '', regex=True).values.astype(float)

yields

array([[1.23, 1.23],
       [1.23, 1.23],
       [1.23, 1.23]])

Accepted Answer