Yesterday I finished Python for Data Analysis, 2nd Edition. A very nice book by Wes McKinney, the creator of panda’s library for Python, that explores basic data manipulation using Python.
For me (a Mathematica enthusiast), it was a great experience to see how data manipulation is handled in other languages. I love the elegance of the Mathematica environment with It clean and sharp syntax, and yes, the plots are incomparable more beautiful, but at the same time, that are some interesting learnings with pandas.
Going straight to the point, if there is a main function from pandas that I miss in Mathematica, It’s the pivot_table function. See how simple is the syntax using python:
As you can see, it’s as simple as dataset.pivot_table(row, columns, value, total option)
I remember this very old post from Stack Exchange from 2011 about this case. IMHO, pivot is a very basic core operation for data manipulation. It would be nice to have an implementation in some future Mathematica versions.
PS: Dataset can pivot very simple cases, but it has an uncontrollable behavior. It’s changes if all the second-level association have the same keys or not, and you can’t choose witch rows and columns you want to Pivot. For example:
titanic = ExampleData[{"Dataset", "Titanic"}]
pivot = titanic[GroupBy["sex"], GroupBy["class"], N@*Mean, "age"]
Will return us:
That’s cool, but if I simulate a missing value in the dataset:
titanic2 = titanic[Select[!(#class == "1st" && #sex == "female")&]];
pivot2 = titanic2[GroupBy["sex"], GroupBy["class"], N@*Mean, "age"]
We get the unpivoted form:
To return the desired result, I need to do something like this:
pivot3 = Normal@pivot2;
pivot3[[All]] = KeyUnion[Values@pivot3, 0 &];
Dataset@pivot3
What I believe is very unintuitive, and don’t solve the case with more Columns and Rows.
UPDATE - strange result
If I remove N from:
titanic2[GroupBy["sex"], GroupBy["class"], N@*Mean, "age"]
to:
titanic2[GroupBy["sex"], GroupBy["class"], Mean, "age"]
We get:
I tried another functions in the 3rd argument, and I changes the pivot form.
