It is not much better than the canonical example given in the article. It still has the following usability issues:
-You still need to enumerate and label each new column and their types. This particular problem is fixed by crosstabN().
-You need to know upfront how many columns are created before performing the pivot. In the context of data analysis, this is often dynamic or unknown.
-The input to the function is not a dataframe, but a text string that generates the pre-pivot results. This means your analysis up to this point needs to be converted into a string. Not only does this disrupt the flow of an analysis, you also have to worry about escape characters in your string.
-It is not standard across SQL dialects. This function is specific to Postgres, and other dialects have their own version of this function with their own limitations.
The article contains several examples like this where SQL is much more verbose and brittle than the equivalent pandas code.
-You still need to enumerate and label each new column and their types. This particular problem is fixed by crosstabN().
-You need to know upfront how many columns are created before performing the pivot. In the context of data analysis, this is often dynamic or unknown.
-The input to the function is not a dataframe, but a text string that generates the pre-pivot results. This means your analysis up to this point needs to be converted into a string. Not only does this disrupt the flow of an analysis, you also have to worry about escape characters in your string.
-It is not standard across SQL dialects. This function is specific to Postgres, and other dialects have their own version of this function with their own limitations.
The article contains several examples like this where SQL is much more verbose and brittle than the equivalent pandas code.