Efficiently create sparse pivot tables in pandas?

by

Here is a method that creates a sparse scipy matrix based on data and indices of person and thing. person_u and thing_u are lists representing the unique entries for your rows and columns of pivot you want to create. Note: this assumes that your count column already has the value you want in it.

from scipy.sparse import csr_matrix

person_u = list(sort(frame.person.unique()))
thing_u = list(sort(frame.thing.unique()))

data = frame['count'].tolist()
row = frame.person.astype('category', categories=person_u).cat.codes
col = frame.thing.astype('category', categories=thing_u).cat.codes
sparse_matrix = csr_matrix((data, (row, col)), shape=(len(person_u), len(thing_u)))

>>> sparse_matrix 
<3x4 sparse matrix of type '<type 'numpy.int64'>'
    with 6 stored elements in Compressed Sparse Row format>

>>> sparse_matrix.todense()

matrix([[0, 1, 0, 1],
        [1, 0, 0, 1],
        [1, 0, 1, 0]])

Based on your original question, the scipy sparse matrix should be sufficient for your needs, but should you wish to have a sparse dataframe you can do the following:

dfs=pd.SparseDataFrame([ pd.SparseSeries(sparse_matrix[i].toarray().ravel(), fill_value=0) 
                              for i in np.arange(sparse_matrix.shape[0]) ], index=person_u, columns=thing_u, default_fill_value=0)

>>> dfs
     a  b  c  d
him  0  1  0  1
me   1  0  0  1
you  1  0  1  0

>>> type(dfs)
pandas.sparse.frame.SparseDataFrame

More Related Contents:

  1. Populate a Pandas SparseDataFrame from a SciPy Sparse Matrix
  2. Pandas sparse dataFrame to sparse matrix, without generating a dense matrix in memory
  3. Label encoding across multiple columns in scikit-learn
  4. ValueError: numpy.ndarray size changed, may indicate binary incompatibility. Expected 88 from C header, got 80 from PyObject
  5. How to do n-D distance and nearest neighbor calculations on numpy arrays
  6. Is there a library function for Root mean square error (RMSE) in python?
  7. Most efficient way to sort an array into bins specified by an index array?
  8. How to convert a Scikit-learn dataset to a Pandas dataset
  9. Selecting Pandas Columns by dtype
  10. Impute categorical missing values in scikit-learn
  11. Can You Consistently Keep Track of Column Labels Using Sklearn’s Transformer API?
  12. Custom transformer for sklearn Pipeline that alters both X and y
  13. Directly use Intel mkl library on Scipy sparse matrix to calculate A dot A.T with less memory
  14. Scikit Learn OneHotEncoder fit and transform Error: ValueError: X has different shape than during fitting
  15. Fastest pairwise distance metric in python
  16. LabelEncoder: TypeError: ‘>’ not supported between instances of ‘float’ and ‘str’
  17. What are the pros and cons between get_dummies (Pandas) and OneHotEncoder (Scikit-learn)?
  18. Run an OLS regression with Pandas Data Frame
  19. T-test in Pandas
  20. TfidfVectorizer in scikit-learn : ValueError: np.nan is an invalid document
  21. Iterating through a scipy.sparse vector (or matrix)
  22. Parse a Pandas column to Datetime when importing table from SQL database and filtering rows by date
  23. pandas columns correlation with statistical significance
  24. How to use sklearn fit_transform with pandas and return dataframe instead of numpy array?
  25. Stratified Sampling in Pandas
  26. Got continuous is not supported error in RandomForestRegressor
  27. How to transform numpy.matrix or array to scipy sparse matrix
  28. sparse 3d matrix/array in Python?
  29. Convert Pandas dataframe to Sparse Numpy Matrix directly
  30. ValueError: Dimension mismatch

Leave a Comment