[FIXED] Python: Plotting comma separated values within two different columns of a single row (Pandas)

Issue

Say I have a dataframe structured like so:

Name        x        y
Joe       0,1,5    0,3,8
Sue       0,2,8    1,9,5
...
Harold    0,5,6    0,7,2

I'd like to plot the values in the x and y axis on a line plot based on row. In reality, there are many x and y values, but there is always one x value for every y value in these columns. The name of the plot would be the value in "name".

I've tried to do this by first converting x and y to lists in their own separate columns like so:

df['xval'] = df.['x'].str.split(',')
df['yval'] = df.['y'].str.split(',')

And then passing them to seaborn:

ax = sns.lineplot(x=df['xval'], y=df['yval'], data=df)

However, this does not work because 1) I recieve an error, which I presume is due to attempting to pass a list from a dataframe, claiming:

TypeError: unhashable type: 'list'

And 2) I cannot specify the value for df['name'] for the specific line plot. What's the best way to go about solving this problem?

Solution

Data and imports:

import pandas as pd
import seaborn as sns
from matplotlib import pyplot as plt

df = pd.DataFrame({
    'name': ['joe', 'sue', 'mike'],
    'x': ['0,1,5', '0,2,8', '0,4'],
    'y': ['0,3,8', '1,9,5', '1,6']
})

We should convert df into a useable format for plotting. This makes all plotting eaiser. We can take advantage of the fact that x and y have a 1-to-1 relationship. Notice I've added a third name with a 2 xy value as opposed to 3 to show this method will work for varied amounts of x and y per name as long as each row has equal numbers of x and y values.

---

Creating the plot_df:

# Grab Name Column to Start Plot DF with
plot_df = df.loc[:, ['name']]
# Split X column
plot_df['x'] = df['x'].str.split(',')
# Explode X into Rows
plot_df = plot_df.explode('x').reset_index(drop=True)
# Split and Series Explode y in one step
# This works IF AND ONLY IF a 1-to-1 relationship for x and y
plot_df['y'] = df['y'].str.split(',').explode().reset_index(drop=True)
# These need to be numeric to plot correctly
plot_df.loc[:, ['x', 'y']] = plot_df.loc[:, ['x', 'y']].astype(int)

plot_df:

   name  x  y
0   joe  0  0
1   joe  1  3
2   joe  5  8
3   sue  0  1
4   sue  2  9
5   sue  8  5
6  mike  0  1
7  mike  4  6

References to the methods used in creating plot_df:

1. DataFrame.loc to subset the dataframe
2. Series.str.split to split the comma separated values into a list
3. DataFrame.explode to upscale the DataFrame based on the iterable in x
4. DataFrame.reset_index to make index unique again after exploding
5. Series.explode to upscale the lists in the Series y.
6. Series.reset_index to make index unique again after exploding
7. DataFrame.astype since the values are initially strings just splitting and exploding is not enough. Will need to convert to a numeric type for them to plot correctly

---

Plotting (Option 1)

# Plot with hue set to name.
sns.lineplot(data=plot_df, x='x', y='y', hue='name')
plt.show()

References for plotting separate lines:

1. sns.lineplot to plot. Note the hue argument to create separate lines based on name.
2. pyplot.show to display.

---

Plotting (Option 2.a) Subplots:

sns.relplot(data=plot_df, x='x', y='y', col='name', kind='line')
plt.tight_layout()
plt.show()

Plotting (Option 2.b) Subplots:

# Use Grouper From plot_df
grouper = plot_df.groupby('name')

# Create Subplots based on the number of groups (ngroups)
fig, axes = plt.subplots(nrows=grouper.ngroups)

# Iterate over axes and groups
for ax, (grp_name, grp) in zip(axes, grouper):
    # Plot from each grp DataFrame on ax from axes
    sns.lineplot(data=grp, x='x', y='y', ax=ax, label=grp_name)

plt.show()

References for plotting subplots:

2.a

1. relplot the row or col parameter can be used to create subplots in a similar way to how hue creates multiple lines. This will return a seaborn.FacetGrid so post processing will be different than lineplot which returns matplotlib.axes.Axes

2.b

1. groupby to create iterable that can be used to plot subplots.
2. pyplot.subplots to create subplots to plot on.
3. groupby.ngroup to count number of groups.
4. zip to iterate over axes and groups simultaneously.
5. sns.lineplot to plot. Note label is needed to have legends. grp_name contains the current key that is common in the current grp DataFrame.
6. pyplot.show to display.

---

Plotting option 3 (separate plots):

# Plot from each grp DataFrame in it's own plot
for grp_name, grp in plot_df.groupby('name'):
    fig, ax = plt.subplots()
    sns.lineplot(data=grp, x='x', y='y', ax=ax)
    ax.set_title(grp_name)
    fig.show()

joe plot	mike plot	sue plot

References for plotting separate plots:

1. groupby to create iterable that can be used to plot each name separately.
2. pyplot.subplots to create separate plot to plot on.
3. sns.lineplot to plot. Note label is needed to have legends. grp_name contains the current key that is common in the current grp DataFrame.
4. pyplot.show to display.

Answered By - Henry Ecker