Feature Request Discussion: a list of `Case` in place of `row` or `column`

[wasabigeek]

Do you think it would be possible to specify a dict of when/then options in place of row/column. Use case below:

Data

customer_name	sales_rep	stage	is_archived
Cust1	Alice	Negotiating	False
Cust2	Bob	Closed	True

Result

sales_rep	Negotiating	Closed	Archived
Alice	1	0	0
Bob	0	0	1

So perhaps something like:

cases = [
    {fieldname: 'Negotiating', when: Q(stage='negotiating') & Q(is_archived=False), then: 1},
    {fieldname: 'Closed', ...},
    {fieldname: 'Archived', ...},
]
pivot_table = pivot(Lead, 'sales_rep', cases, 'id', aggregation=Count)

What do you think?

P.S. Thanks for this package! Was looking for something lightweight for a lightweight use case, and this is great.

[martsberger]

I think you can accomplish want you want already because you can pivot on an annotated column.

from django.db.models import Case, When, CharField

annotation = {
    'pivot_column': Case(When(Q(stage='negotiating', is_archived=False), then=Value('Negotiating')),
                         When(Q(...), then=Value('Closed'),
                         When(Q(...), then=Value('Archived'),
                         default=None,
                         output_field=CharField()
}

queryset = Lead.objects.annotate(**annotation)
pivot_table = pivot(queryset, 'sales_rep', 'pivot_column', 'id', aggregation=Count)

If there is a cleaner API than the above, maybe it's worth having the pivot package expose that. I'm certainly open to making pivoting on an annotated column cleaner, but I'm not thinking of something better off the top of my head. The cases list in your example isn't that different than what's in the annotation.

[martsberger]

@wasabigeek I forgot to say, let me know if the above works for you or not.

[wasabigeek]

@martsberger it works! Just a few typos in the example e.g. When was not closed properly.

I was able to create the "Whens" programmatically and use those :)

whens = [
    When(Q(stage='negotiating', is_archived=False), then=Value('Negotiating')),
    When(Q(...), then=Value('Closed')),
    When(Q(...), then=Value('Archived')),
]
...
queryset = Lead.objects.annotate(pivot_column=Case(*whens, default=None, output_field=CharField())