Pandas Crosstab table with Unordered Categorical Variables

user_12933147 0
Viewed 120

For a just-for-fun project, I generated a dataset with all currently extant Pokemon (916, not counting Megas or alternate forms) and collected data on base stats, abilities and types. Now I want to generate a crosstab table showing the distribution of each type-combination. The data is currently coded such that the first and second types are separate variables, Type1 and Type2. This format works well for pd.crosstab(), assuming that the ordering of types is distinct, ('Flying','Normal') is not the same as ('Normal','Flying'); however, the game does not make such a distinction. I want to generate frequency tables that reflect this--essentially folding the pd.crosstab() table in half along the diagonal.

#### For data structured like...
In[1]: dfNatDex[dfNatDex['DexNum']<10]
Out[4]: 
    DexNum        Name  Type1   Type2
0      1.0   Bulbasaur  grass  poison
1      2.0     Ivysaur  grass  poison
2      3.0    Venusaur  grass  poison
3      4.0  Charmander   fire    fire
4      5.0  Charmeleon   fire    fire
5      6.0   Charizard   fire  flying
6      7.0    Squirtle  water   water
7      8.0   Wartortle  water   water
8      9.0   Blastoise  water   water

[10 rows x 16 columns]

#### I am getting...
In[2]: crosstab(dfNatDex['Type2'][...],dfNatDex['Type1'][...])
Out[2]: 
Type1   flying  normal  water
Type2                        
flying       3      26      7
normal       0      69      0
water        1       1     67

#### I want to get...
Type1   flying  normal  water
Type2                        
flying       3      26      8
normal       .      69      1
water        .       .     67

My guess is that, if there isn't a Pandas function for this that I have not found yet, then perhaps I can achieve this through matrix arithmetic. Failing that, there is likely a slow iterative process that I think could achieve this.

pandas categorical-data crosstab python-3.x python
1 Answers

One option would be to use np.sort to sort values on axis=1, then use value_counts to get the counts:

import numpy as np
import pandas as pd

cols = ['Type1', 'Type2']
types_df = pd.DataFrame(
    np.sort(df[cols], axis=1),
    columns=cols
).value_counts().reset_index(name='Count')

types_df:

   Type1   Type2  Count
0  grass  poison      3
1  water   water      3
2   fire    fire      2
3   fire  flying      1

crosstab could be used as well on the sorted values, but there will be a lot of 0 values which could obscure the information trying to be demonstrated:

cols = ['Type1', 'Type2']
types_df = pd.DataFrame(np.sort(df[cols], axis=1), columns=cols)
ct_df = pd.crosstab(types_df['Type2'], types_df['Type1'])

ct_df:

Type1   fire  grass  water
Type2                     
fire       2      0      0
flying     1      0      0
poison     0      3      0
water      0      0      3

Assuming these types:

df[['Type1', 'Type2']]

    Type1   Type2
0  poison   grass  # poison grass
1   grass  poison  # grass poison
2   grass  poison
3    fire    fire
4    fire    fire
5    fire  flying
6   water   water
7   water   water
8   water   water

After sorting:

np.sort(df[['Type1', 'Type2']], axis=1)

[['grass' 'poison']  # grass poison
 ['grass' 'poison']  # grass poison
 ['grass' 'poison']
 ['fire' 'fire']
 ['fire' 'fire']
 ['fire' 'flying']
 ['water' 'water']
 ['water' 'water']
 ['water' 'water']]

This way all types appear in the same order regardless of how they appear in the DataFrame and the value counts will produce the correct values regardless of the order they appear in the columns.

user_154978880
Related