ENH: Add sparse option in pivot_table / pivot / unstack

[yupbank]

A small, complete example of the issue

# Your code here
sparse_df = df.pivot(index='a', columns='b', values='c', aggfunc=lambda x: len(x), sparse=True)

Expected Output

Output of pd.show_versions()

# Paste the output here

[jreback]

this is possible; would have to be integrated into unstacking proper. We do do something like this in get_dummies, but that is not fully generalizable. So this would be an effort. A community pull-request would be needed here.

[yupbank]

i might try my best for a pull-request :)

[yupbank]

@jreback i noticed that there isn't many options for sparse_matrix. (which in my case want something df.to_csr_matrix) is that ok, i add some sparse_matrix to current limited scipy.coo_matrix only collection ?
or do you suggest i talk to someone who knows the pandas.sparse better?
and also DataFrame.groupby people, cause i notice the actually agg_func/value assign of pivot happened in groupby?

[jreback]

adding sparse means pandas sparse structures: http://pandas.pydata.org/pandas-docs/stable/sparse.html

[jnothman]

I have implemented, I think, most of the logic for sparse unstacking of series (or homogeneous dtypes) at master...jnothman:sparse-unstack

So far I am:

• currently overwriting the existing _Unstacker.get_result() implementation for ease of testing.
• not handling the categorical case yet (cf. Behaviour of Categorical inputs to sparse data structures #19278).
• not currently correctly outputting SparseDataFrame where it would be output
• not adding a sparse parameter to allow the user to switch on sparse output

I'd be very happy for someone else to complete the work!

[jnothman]

Maybe then it can be downgraded to Effort Medium :P

[jnothman]

In pandas 0.25, making a series have a sparse dtype and then unstacking it seems to produce a sparse DataFrame (see https://stackoverflow.com/questions/58617185/converting-a-list-of-counters-to-sparse-pandas-dataframe/58617186). I'm assuming this does not require a dense in-memory structure along the way. Maybe this can be closed?

[GeekLad]

I know this is a really old issue, but I believe there are many scenarios where this functionality would be useful. For instance, in my case, I'm trying to pivot order details (line items, where one order has multiple lines, with different items with different quantities/prices ordered). I want to see if I can develop a predictive model where quantities of particular items predict outcomes for an order, so I need to engineer features to pivot the item numbers into columns.

I have 10 million rows of item detail on 3.3 million orders with about 15K unique items. So the pivot would have 15K columns, with only about 3 columns populated on average. I run out of memory when I try to pivot with Pandas, and I have 64GB of RAM.

[alonba]

I need to pivot a big table (100 million rows, 4 cols). The pivoted table is insanely big. It has to be returned as a sparse matrix, but it isn't.
This feature is extremely needed.
I am currently thinking to use this way, but it is an ugly workaround, if it would even work.

[michelkluger]

it is a hack around (would love to see this ENH become a feature)

https://medium.com/@michelkluger/pivot-to-sparse-dataframe-d0b1759a9d14