[FIXED] How to iteratively plot custom groups of dataframes

Issue

I have multiple dataframes which I have read from an excel sheet as follows -

A = pd.read_excel("sample.xlsx", usecols="A:B", sheet_name="A")
B = pd.read_excel("sample.xlsx", usecols="A:B", sheet_name="B")
...
...

Each dataframe has their own unique x, and y values and I am able to make a combined plot using the following code -

ax.plot(A.iloc[:, 0], A.iloc[:, 1], color=colors(0), label='A')
ax.plot(B.iloc[:, 0], B.iloc[:, 1], color=colors(1), label='B')
...
...

I now want to retain this original plot in an initial figure, but also additionally make multiple plots using the same data such that I am able to group some of these plots into a group and assign a common label for them. A very rudimentary code I can think of for it is as follows -

ax.plot(A.iloc[:, 0], A.iloc[:, 1], color=colors(0), label='A, B')
ax.plot(B.iloc[:, 0], B.iloc[:, 1], color=colors(0), label='A, B')
...
...

However, this has a drawback. If I do this, I will have two legend entries with the labels [A, B]. In addition, I have close to 50 such data frames which I have extracted from multiple sources and it will be a cumbersome process to constantly change all colors and labels for these plots.

Is there a way I can group these dataframes as I wish and label them as a grouped entity? I am imagining something like the following -

ax.plot(A.iloc[:, 0], A.iloc[:, 1], color=colors(0), label='A')
ax.plot(B.iloc[:, 0], B.iloc[:, 1], color=colors(1), label='B')
...
...
set1 = [A, B, C]
set2 = [G, E, F]
ax.legend((set1), ('Legend for Set1'), color=colors(0))
ax.legend((set2), ('Legend for Set2'), color=colors(2))

These groupings will also change such as, in subsequent plots, I will want to group (A, C, E) together, for example. Is there an easy way to achieve this in Python and Matplotlib? I am new to this community, so please let me know if I should frame my questions differently next time.

Ps - I am also fine if I have to manually place my groups in individual labels like

ax.legend((A, B, C), ('Legend for Set1'), color=colors(0))

Solution

• Use the parameter sheet_name=None in pandas.read_excel to create a dict of dataframes, where each sheet name is a key and the dataframe for the sheet, is the value.
  • df_dict = pd.read_excel("sample.xlsx", usecols="A:B", sheet_name=None)
  • This will make it easier to iteratively create plots of each dataframe, or custom groups of dataframes.

Plotting all dataframes to a single figure

• To plot each dataframe, iterate through the key value pairs with df_dict.items().
• color=colors[i], can be removed from ax.plot(...), as the plot API will specify unique colors, providing there are not more plots than unique colors in the palette.
• This demonstrates how to plot all of the dataframes into a single figure.

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns  # just using it for the color palette
import numpy as np  # for test data

# synthetic dict of dataframes used for plot example
df_dict = dict()
for i in range(1, 4):
    rads = np.arange(0, 2*np.pi, 0.01)
    data = np.sin(i*rads)
    df_dict[f'freq: {i}x'] = pd.DataFrame({'x': rads, 'y': data})

# In your case, create a dict of dataframes by using the parameter sheet_name=None
df_dict = pd.read_excel("sample.xlsx", usecols="A:B", sheet_name=None)

# create colors from a palette; creates a list of colors based on the number of keys in df_dict
colors = sns.color_palette('husl', n_colors=len(df_dict.keys()))

# create a plot figure
fig, ax = plt.subplots(figsize=(8, 6))

# iterate through the dict and enumerate with i, i is used to index the colors
for i, (k, v) in enumerate(df_dict.items()):

    # plot each dataframe, v, and use the key, k, to create a legend label
    ax.plot(v.iloc[:, 0], v.iloc[:, 1], color=colors[i], label=f'{k}')

# place the legend outside the plot figure
plt.legend(bbox_to_anchor=(1.05, 1), loc='upper left')

---

Plotting Custom Groups

• Using df_dict from above
• All of the groups of dataframes to be plotted together, must be defined.
  • In this case, a list of tuples is used to define the groups to plot together.

# define the groups
groups = [('freq: 1x', 'freq: 2x'), ('freq: 1x', 'freq: 3x')]

# iterate through each group
for i, g in enumerate(groups, 1):
    
    # create a plot figure for the group
    fig, ax = plt.subplots(figsize=(8, 6))
    
    # plot each dataframe in the group
    for key in g:
        
        # get the value for the key
        v = df_dict[key]
        
        # plot each dataframe, v, and use the key to create a legend label
        ax.plot(v.iloc[:, 0], v.iloc[:, 1], label=f'{key}')

    # place the legend outside the plot figure
    plt.legend(bbox_to_anchor=(1.05, 1), loc='upper left')

    # set the title
    plt.title(f'Plot of group {i}')

Answered By - Trenton McKinney