Correlation analysis with multiple data in a single cell

Question

I have a dataset with some rows containing singular answers and others having multiple answers. Like so:

       year  length  Animation
0      1971     121       1,2,3
1      1939      71       1,3
2      1941       7       0,2
3      1996      70       1,2,0
4      1975      71       3,2,0

With the singular answers I managed to create a heatmap using df.corr(), but I can't figure out what is the best approach for multiple answers rows.

I could split them and add additional columns for each answer like:

           year  length  Animation
    0      1971     121       1
    1      1971     121       2
    2      1971     121       3
    3      1939      71       1
    4      1939      71       3 ...

and then do the exact same dr.corr(), or add additional Animation_01, Animation_02 ... columns, but there must be a smarter way to work around this issue?

EDIT: Actual data snippet

Answer 1

You should compute a frequency table between two categorical variables using pd.crosstab() and perform subsequent analyses based on this table. df.corr(x, y) is NOT mathematically meaningful when one of x and y is categorical, no matter it is encoded into number or not.

N.B.1 If x is categorical but y is numerical, there are two options to describe the linkage between them:

1. Group y into quantiles (bins) and treat it as categorical
2. Perform a linear regression of y against one-hot encoded dummy variables of x

Option 2 is more precise in general but the statistics is beyond the scope of this question. This post will focus on the case of two categorical variables.

N.B.2 For sparse matrix output please see this post.

Sample Solution

Data & Preprocessing

import pandas as pd
import io
import matplotlib.pyplot as plt
from seaborn import heatmap

df = pd.read_csv(io.StringIO("""
       year  length  Animation
0      1971     121       1,2,3
1      1939      71       1,3
2      1941       7       0,2
3      1996      70       1,2,0
4      1975      71       3,2,0
"""), sep=r"\s{2,}", engine="python")

# convert string to list
df["Animation"] = df["Animation"].str.split(',')
# expand list column into new rows
df = df.explode("Animation")
# (optional)
df["Animation"] = df["Animation"].astype(int)

Frequency Table

Note: grouping of length is ignored for simplicity

ct = pd.crosstab(df["Animation"], df["length"])

print(ct)
# Out[65]:
# length     7    70   71   121
# Animation
# 0            1    1    1    0
# 1            0    1    1    1
# 2            1    1    1    1
# 3            0    0    2    1

Visualization

ax = heatmap(ct, cmap="viridis",
             yticklabels=df["Animation"].drop_duplicates().sort_values(),
             xticklabels=df["length"].drop_duplicates().sort_values(),
             )
ax.set_title("Title", fontsize=20)
plt.show()

Example Analysis

Based on the frequency table, you can ask questions about the distribution of y given a certain (subset of) x value(s), or vice versa. This should better describe the linkage between two categorical variables, as the categorical variables have no order.

For example,

Q: What length does Animation=3 produces? 

A: 66.7% chance to give 71
   33.3% chance to give 121
   otherwise unobserved

Answer 2

You want to break Animation (or Preferred_positions in your data snippet) up into a series of one-hot columns, one one-hot column for every unique string in the original column. Every column with have values of either zero or one, one corresponding to rows where that string appeared in the original column.

First, you need to get all the unique substrings in Preferred_positions (see this answer for how to deal with a column of lists).

positions = df.Preferred_positions.str.split(',').sum().unique()

Then you can create the positions columns in a loop based on whether the given position is in Preferred_positions for each row.

for position in positions:
    df[position] = df.Preferred_positions.apply(
        lambda x: 1 if position in x else 0
    )