[python] How to convert a pandas DataFrame subset of columns AND rows into a numpy array?

I'm wondering if there is a simpler, memory efficient way to select a subset of rows and columns from a pandas DataFrame.

For instance, given this dataframe:

df = DataFrame(np.random.rand(4,5), columns = list('abcde'))
print df

          a         b         c         d         e
0  0.945686  0.000710  0.909158  0.892892  0.326670
1  0.919359  0.667057  0.462478  0.008204  0.473096
2  0.976163  0.621712  0.208423  0.980471  0.048334
3  0.459039  0.788318  0.309892  0.100539  0.753992

I want only those rows in which the value for column 'c' is greater than 0.5, but I only need columns 'b' and 'e' for those rows.

This is the method that I've come up with - perhaps there is a better "pandas" way?

locs = [df.columns.get_loc(_) for _ in ['a', 'd']]
print df[df.c > 0.5][locs]

          a         d
0  0.945686  0.892892

My final goal is to convert the result to a numpy array to pass into an sklearn regression algorithm, so I will use the code above like this:

training_set = array(df[df.c > 0.5][locs])

... and that peeves me since I end up with a huge array copy in memory. Perhaps there's a better way for that too?

This question is related to python arrays numpy pandas scikit-learn

The answer is


.loc accept row and column selectors simultaneously (as do .ix/.iloc FYI) This is done in a single pass as well.

In [1]: df = DataFrame(np.random.rand(4,5), columns = list('abcde'))

In [2]: df
Out[2]: 
          a         b         c         d         e
0  0.669701  0.780497  0.955690  0.451573  0.232194
1  0.952762  0.585579  0.890801  0.643251  0.556220
2  0.900713  0.790938  0.952628  0.505775  0.582365
3  0.994205  0.330560  0.286694  0.125061  0.575153

In [5]: df.loc[df['c']>0.5,['a','d']]
Out[5]: 
          a         d
0  0.669701  0.451573
1  0.952762  0.643251
2  0.900713  0.505775

And if you want the values (though this should pass directly to sklearn as is); frames support the array interface

In [6]: df.loc[df['c']>0.5,['a','d']].values
Out[6]: 
array([[ 0.66970138,  0.45157274],
       [ 0.95276167,  0.64325143],
       [ 0.90071271,  0.50577509]])

Perhaps something like this for the first problem, you can simply access the columns by their names:

>>> df = pd.DataFrame(np.random.rand(4,5), columns = list('abcde'))
>>> df[df['c']>.5][['b','e']]
          b         e
1  0.071146  0.132145
2  0.495152  0.420219

For the second problem:

>>> df[df['c']>.5][['b','e']].values
array([[ 0.07114556,  0.13214495],
       [ 0.49515157,  0.42021946]])

Use its value directly:

In [79]: df[df.c > 0.5][['b', 'e']].values
Out[79]: 
array([[ 0.98836259,  0.82403141],
       [ 0.337358  ,  0.02054435],
       [ 0.29271728,  0.37813099],
       [ 0.70033513,  0.69919695]])

Examples related to python

programming a servo thru a barometer Is there a way to view two blocks of code from the same file simultaneously in Sublime Text? python variable NameError Why my regexp for hyphenated words doesn't work? Comparing a variable with a string python not working when redirecting from bash script is it possible to add colors to python output? Get Public URL for File - Google Cloud Storage - App Engine (Python) Real time face detection OpenCV, Python xlrd.biffh.XLRDError: Excel xlsx file; not supported Could not load dynamic library 'cudart64_101.dll' on tensorflow CPU-only installation

Examples related to arrays

PHP array value passes to next row Use NSInteger as array index How do I show a message in the foreach loop? Objects are not valid as a React child. If you meant to render a collection of children, use an array instead Iterating over arrays in Python 3 Best way to "push" into C# array Sort Array of object by object field in Angular 6 Checking for duplicate strings in JavaScript array what does numpy ndarray shape do? How to round a numpy array?

Examples related to numpy

Unable to allocate array with shape and data type How to fix 'Object arrays cannot be loaded when allow_pickle=False' for imdb.load_data() function? Numpy, multiply array with scalar TypeError: only integer scalar arrays can be converted to a scalar index with 1D numpy indices array Could not install packages due to a "Environment error :[error 13]: permission denied : 'usr/local/bin/f2py'" Pytorch tensor to numpy array Numpy Resize/Rescale Image what does numpy ndarray shape do? How to round a numpy array? numpy array TypeError: only integer scalar arrays can be converted to a scalar index

Examples related to pandas

xlrd.biffh.XLRDError: Excel xlsx file; not supported Pandas Merging 101 How to increase image size of pandas.DataFrame.plot in jupyter notebook? Trying to merge 2 dataframes but get ValueError Python Pandas User Warning: Sorting because non-concatenation axis is not aligned How to show all of columns name on pandas dataframe? Pandas/Python: Set value of one column based on value in another column Python Pandas - Find difference between two data frames Pandas get the most frequent values of a column Python convert object to float

Examples related to scikit-learn

LabelEncoder: TypeError: '>' not supported between instances of 'float' and 'str' UndefinedMetricWarning: F-score is ill-defined and being set to 0.0 in labels with no predicted samples scikit-learn random state in splitting dataset LogisticRegression: Unknown label type: 'continuous' using sklearn in python Can anyone explain me StandardScaler? ImportError: No module named model_selection How to split data into 3 sets (train, validation and test)? How to convert a Scikit-learn dataset to a Pandas dataset? Accuracy Score ValueError: Can't Handle mix of binary and continuous target How can I plot a confusion matrix?