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.

Depending on your pandas version, https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.values.html#pandas.DataFrame.values or https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.as_matrix.html should do the trick. (You'll need to subset first, of course.) — Evan, Nov 15 '18 at 23:17

Evan · Accepted Answer · 2018-11-16T00:08:43.553

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

Edited. Portions of your question were already answered elsewhere on SO; as such, your question isn't an exact duplicate, but I think you should spend some time googling SO to see what other people have tried to address smaller segments of the project you're working on. — Evan, Nov 16 '18 at 00:10
Ah, the `()` tripped up the replacement values. If you're dealing with accounting data, I think that might be a negative number... so you'll need to adjust accordingly. See: https://stackoverflow.com/questions/31521526/convert-currency-to-float-and-parentheses-indicate-negative-amounts — Evan, Nov 16 '18 at 00:24

Get a subset of a data frame into a matrix

1 Answers1

Linked