transform scipy sparse csr to pandas?

Question

I have used the

sklearn.preprocessing.OneHotEncoder

to transform some data the output is scipy.sparse.csr.csr_matrix how can I merge it back into my original dataframe along with the other columns?

I tried to use pd.concat but I get

TypeError: cannot concatenate a non-NDFrame object

Thanks

Solution 1

If A is csr_matrix, you can use .toarray() (there's also .todense() that produces a numpy matrix, which is also works for the DataFrame constructor):

df = pd.DataFrame(A.toarray())

You can then use this with pd.concat().

A = csr_matrix([[1, 0, 2], [0, 3, 0]])
    
  (0, 0)    1
  (0, 2)    2
  (1, 1)    3

<class 'scipy.sparse.csr.csr_matrix'>

pd.DataFrame(A.todense())

   0  1  2
0  1  0  2
1  0  3  0

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 3 columns):
0    2 non-null int64
1    2 non-null int64
2    2 non-null int64

In version 0.20, pandas introduced sparse data structures, including the SparseDataFrame.

In pandas 1.0, SparseDataFrame was removed:

In older versions of pandas, the SparseSeries and SparseDataFrame classes were the preferred way to work with sparse data. With the advent of extension arrays, these subclasses are no longer needed. Their purpose is better served by using a regular Series or DataFrame with sparse values instead.

The migration guide shows how to use these new data structures.

For instance, to create a DataFrame from a sparse matrix:

from scipy.sparse import csr_matrix

A = csr_matrix([[1, 0, 2], [0, 3, 0]])

df = pd.DataFrame.sparse.from_spmatrix(A, columns=['A', 'B', 'C'])

df

   A  B  C
0  1  0  2
1  0  3  0

df.dtypes
A    Sparse[float64, 0]
B    Sparse[float64, 0]
C    Sparse[float64, 0]
dtype: object

Alternatively, you can pass sparse matrices to sklearn to avoid running out of memory when converting back to pandas. Just convert your other data to sparse format by passing a numpy array to the scipy.sparse.csr_matrix constructor and use scipy.sparse.hstack to combine (see docs).

Solution 2

UPDATE for Pandas 1.0+

Per the Pandas Sparse data structures documentation, SparseDataFrame and SparseSeries have been removed.

Sparse Pandas Dataframes

Previous Way

pd.SparseDataFrame({"A": [0, 1]})

New Way

pd.DataFrame({"A": pd.arrays.SparseArray([0, 1])})

Working with SciPy sparse csr_matrix

Previous Way

from scipy.sparse import csr_matrix
matrix = csr_matrix((3, 4), dtype=np.int8)
df = pd.SparseDataFrame(matrix, columns=['A', 'B', 'C'])

New Way

from scipy.sparse import csr_matrix
import numpy as np
import pandas as pd

matrix = csr_matrix((3, 4), dtype=np.int8)
df = pd.DataFrame.sparse.from_spmatrix(matrix, columns=['A', 'B', 'C', 'D'])
df.dtypes

Output:

A    Sparse[int8, 0]
B    Sparse[int8, 0]
C    Sparse[int8, 0]
D    Sparse[int8, 0]
dtype: object

---

Conversion from Sparse to Dense

df.sparse.to_dense()                                                                                                                                                                            

Output:

   A  B  C  D
0  0  0  0  0
1  0  0  0  0
2  0  0  0  0

---

Sparse Properties

df.sparse.density                                                                                                                                                                           

Output:

0.0

Solution 3

You could also avoid getting back a sparse matrix in the first place by setting the parameter sparse to False when creating the Encoder.

The documentation of the OneHotEncoder states:

sparse : boolean, default=True

Will return sparse matrix if set True else will return an array.

Then you can again call the DataFrame constructor to transform the numpy array to a DataFrame.

Comments

[user77005]

What can I do if my A.toarray() leads to a MemoryError? Is there any way to create the Dataframe without converting it back to a ndarray?

[Stefan]

You may want to take a look at pandas sparse data structures

[Anne]

Is there any chance I can pass my values as labels for the new dataframe? E.g., if the hot encoder had values given from column 'letter' with 'a a b b c a' that my new dataframe would be headed by letter_a, letter_b etc, much like with the dummy-encoder?

[Anne]

Solved this by passing different arguments to the dummy encoder

[CKM]

Caveat: if sparse matrix is too big, it will throw memory error since .toarray() creates a dense metrix.

[cangrejo]

It's more than a caveat. This is completely useless with any sparse matrix that has any business being such.

[Stefan]

@cangrejo how is .todense() useless if OP wants to incorporate sparse matrix into DataFrame and how is SparseDataFrame() useless as an alternative given memory constraints?

[cangrejo]

@Stefan Sparse formats are often employed to store matrices too large to fit in memory.

[Stefan]

@cangrejo OP explicitly intended to combine a sparse matrix with a DataFrame. The answer demonstrates how to do this. How is this completely useless, rather than not applicable in case the data does not fit into memory, however frequent these cases may be?

[JulesDoe]

Outdated scince pandas 1.0