python - Get a subset of a data frame into a matrix

Question

I have this data frame:

I want just the numbers under August - September to be placed into a matrix, how can I do this?

I tried this cf = df.iloc[:,1:12] which gives me but it gives me the headers as well which I do not need.

I did this

cf = df.iloc[:,1:12]
cf = cf.values
print(cf)

which gives me

[['$0.00 ' '$771.98 ' '$0.00 ' ..., '$771.98 ' '$0.00 ' '$1,543.96 ']
 ['$1,320.83 ' '$4,782.33 ' '$1,320.83 ' ..., '$1,954.45 ' '$0.00 '
  '$1,954.45 ']
 ['$2,043.61 ' '$0.00 ' '$4,087.22 ' ..., '$4,662.30 ' '$2,907.82 '
  '$1,549.53 ']
 ..., 
 ['$427.60 ' '$0.00 ' '$427.60 ' ..., '$427.60 ' '$0.00 ' '$427.60 ']
 ['$868.58 ' '$1,737.16 ' '$0.00 ' ..., '$868.58 ' '$868.58 ' '$868.58 ']
 ['$0.00 ' '$1,590.07 ' '$0.00 ' ..., '$787.75 ' '$0.00 ' '$0.00 ']]

I need these to be of floating types.

Answer 1

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]])