How to visualize multi-indexed series into a heatmap in pandas?

Question

I am trying to turn this kind of a series:

Animal  Idol
50      60      15
        55      14
        81      14
        80      13
        56      11
        53      10
        58       9
        57       9
        50       9
        59       6
        52       6
        61       1
52      52      64
        58      28
        55      21
        81      17
        60      16
        50      16
        56      15
        80      12
        61      10
        59      10
        53       9
        57       4
53      53      27
        56      14
        58      10
        50       9
        80       8
        52       6
        55       6
        61       5
        81       5
        60       4
        57       4
        59       3

Into something looking more like this:

 Animal/Idol  60  55  81  80 ...
    50        15  14  14  13
    52        16  21  17  12
    53        4    6   5   8
    ...

My base for the series here is actually a data frame looking like this (The unnamed values in series being sums of times a pair of animal and idol repeated, and there are many idols to each animal):

     Animal Idol
1058     50   50
1061     50   50
1197     50   50
1357     50   50
1637     50   50
...     ...  ...
2780     81   81
2913     81   81
2915     81   81
3238     81   81
3324     81   81

Sadly, I have no clue how to convert any of this 2 into the desired form. I guess the good name for it is a pivot table, however I could not get the good result using them. How would You transform any of these into the form I need? I would also like to know, how to visualize this kind of a pivot table (if thats a good name) into a heat map, where color for each cell would differ based on the value in the cell (the higher the value, the deeper the colour). Thanks in advance!

Answer 1

i think you are looking for .unstack() (https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.unstack.html) to unstack data.

To visualize you can use multiple tools. I like using holoviews (https://holoviews.org/), hv.Image should be able to plot a 2d array . You can use hv.Image(df.unstack().values) to do that.

Example:

df = pd.DataFrame({'data': np.random.randint(0, 100, 100)}, index=pd.MultiIndex.from_tuples([(i, j) for i in range(10) for j in range(10)]))
df

unstack:

df_unstacked = df.unstack()
df_unstacked

plot:

import holoviews as hv
hv.Image(df_unstacked.values)

or to plot with matplotlib:

import matplotlib
import matplotlib as mpl
import matplotlib.pyplot as plt
fig, ax = plt.subplots()
im = ax.imshow(df_unstacked.values)