I have pyspark Data frame for which want to calculate summary statistics (count of all unique categories in that column) and crossTabulation with one fixed column for all string columns. For Example: My df is like this
| col1 | col2 | col3 |
|---|---|---|
| Cat1 | XYZ | A |
| Cat1 | XYZ | C |
| Cat1 | ABC | B |
| Cat2 | ABC | A |
| Cat2 | XYZ | B |
| Cat2 | MNO | A |
I want something like this
| VarNAME | Category | Count | A | B | C |
|---|---|---|---|---|---|
| col1 | Cat1 | 3 | 1 | 1 | 1 |
| col1 | Cat2 | 3 | 2 | 0 | 1 |
| col2 | XYZ | 3 | 1 | 1 | 1 |
| col2 | ABC | 2 | 1 | 1 | 0 |
| col2 | MNO | 1 | 1 | 0 | 0 |
| col3 | A | 3 | 3 | 0 | 0 |
| col3 | B | 2 | 0 | 2 | 0 |
| Col3 | C | 1 | 0 | 0 | 1 |
So, Basically, I want cross-tabulation for all individual columns with col3 and the total count. I can do it in Python using a loop but the loop is somewhat different in pyspark.