Process pandas dataframe into violinplot

You may use seaborn. In this case, import seaborn and then use violin plot to visualize the scenarios.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

# load data into a dataframe
df = pd.read_excel('Modeling analysis charts.xlsx',
                   sheetname='lmps',
                   parse_cols=[7,12],
                   skiprows=0,
                   header=1)
fontsize = 10

fig, axes = plt.subplots()
# plot violin. 'Scenario' is according to x axis, 
# 'LMP' is y axis, data is your dataframe. ax - is axes instance
sns.violinplot('Scenario','LMP', data=df, ax = axes)
axes.set_title('Day Ahead Market')

axes.yaxis.grid(True)
axes.set_xlabel('Scenario')
axes.set_ylabel('LMP ($/MWh)')

plt.show()

You need to be careful how to create the dataset to plot. In the code from the question you have a list of lists of one dataframe. However you need simply a list of one-column dataframes.

You would therefore also need to take only the "LMP" column from the filtered dataframes, otherwise the violinplot wouldn't know which column to plot.

Here is a working example which stays close to the original code:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt


x = np.random.poisson(lam =3, size=100)
y = np.random.choice(["S{}".format(i+1) for i in range(6)], size=len(x))
df = pd.DataFrame({"Scenario":y, "LMP":x})

fig, axes = plt.subplots()

axes.violinplot(dataset = [df[df.Scenario == 'S1']["LMP"].values,
                           df[df.Scenario == 'S2']["LMP"].values,
                           df[df.Scenario == 'S3']["LMP"].values,
                           df[df.Scenario == 'S4']["LMP"].values,
                           df[df.Scenario == 'S5']["LMP"].values,
                           df[df.Scenario == 'S6']["LMP"].values ] )

axes.set_title('Day Ahead Market')
axes.yaxis.grid(True)
axes.set_xlabel('Scenario')
axes.set_ylabel('LMP ($/MWh)')

plt.show()