Pandas Scatter Plot Filtered by Multiple Row Conditions and X, Y Column Values

Question

Thank you for your ideas - I have been trying to make a scatter plot using a loop to filter for unique (2) row values for x values (column data) and y values (column data). The column data for the scatter plot is made when the 2 row conditions are met. My data looks like this:

site_name   power_1   wind_speed   month   year   day   hour   power_2
A           50        5.5          1       2021   2     5      60
A           75        5.9          2       2021   8    17      70
A           40        7.3          5       2021  11    20      85
B           80        8.1          4       2021   1     4      90
B           84        8.2          7       2021  18     5      92
B           46        6.1         10       2021  23    11      41

I am trying to plot each site in a separate scatter plot with x = wind speed and y = power_1 and each hour a different color. Ultimately, I need 2 scatter plots (A, B) for wind speed and power and then 3 different color points for the x, y values. I hope this makes sense.

I have tried using a 2-loop structure - 1 outer loop for the sites (A, B) and an inner loop for the colors of the x, y values.

My actual code to a much larger dataset than I show above resembles below and I get a blank plot when I use this:

#PLOT ALL HOURS OF THE MONTHS/YEARS - WIND SPEED vs  POWER
sites = (dfc1.plant_name.unique())
sites = sites.tolist()
import matplotlib.patches
from scipy.interpolate import interp1d
levels, categories = pd.factorize(dfc1.hour.unique())
colors = [plt.cm.Paired(k) for k in levels] 
handles = [matplotlib.patches.Patch(color=plt.cm.Paired(k), label=c) for k, c in enumerate(categories)]
#fig, ax = plt.subplots(figsize=(10,4))

for i in range(len(sites)):
    #fig = plt.figure()
    for j in np.arange(0,24): #24 HOURS AND 1 COLOR FOR EACH UNIQUE HOUR
        
        x = dfc1.loc[dfc1['plant_name']==sites[i]].groupby(['hour']).wind_speed_ms
        y = dfc1.loc[dfc1['plant_name']==sites[i]].groupby(['hour']).power_kwh
        plt.scatter(x,y, edgecolors=colors[0:j],marker='o',facecolors='none')
        site = str(sites[i])
        plt.title(site + (' ')  + str(dfc1.columns[5]) + (' ') + ('vs') + (' ') + str(dfc1.columns[3]) )
        plt.xlabel('Wind Speed'); plt.ylabel('Power')
        plt.legend(handles=handles, title="Month",loc='center left', bbox_to_anchor=(1,0.5),edgecolor='black')
    #plt.plot(mwsvar.iloc[-1,4], mpvar.iloc[-1,4], c='orange',linestyle=(0,()),marker="o",markersize=7)
    plt.legend()
    plt.show()

Answer 1

I think you're very close, here's a solution using matplotlib which is kind of long and unwieldy but I think it's the correct solution. Then I also show using a different library called seaborn which makes plots like this much easier

import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt

df = pd.DataFrame({
    'site_name': ['A', 'A', 'A', 'B', 'B', 'B'],
    'power_1': [50, 75, 40, 80, 84, 46],
    'wind_speed': [5.5, 5.9, 7.3, 8.1, 8.2, 6.1],
    'month': [1, 2, 5, 4, 7, 10],
    'year': [2021, 2021, 2021, 2021, 2021, 2021],
    'day': [2, 8, 11, 1, 18, 23],
    'hour': [5, 17, 20, 4, 5, 11],
    'power_2': [60, 70, 85, 90, 92, 41],
})

#Matplotlib approach
cmap = mpl.cm.get_cmap('Blues')
hour_colors = {h+1:cmap(h/24) for h in range(24)} #different color for each hour

for site_name,site_df in df.groupby('site_name'):
    fig, ax = plt.subplots()
    for hour,hour_df in site_df.groupby('hour'):
        x = hour_df['wind_speed']
        y = hour_df['power_1']
        color = hour_colors[hour]
        ax.scatter(x, y, color=color, label=f'Hour {hour}')
        
    ax.legend()
    plt.title(f'Station {site_name}')
    plt.xlabel('Wind speed')
    plt.ylabel('Power 1')
    plt.show()
    plt.close()

#Seaborn approach (different library)
import seaborn as sns
sns.relplot(
    x = 'wind_speed',
    y = 'power_1',
    col = 'site_name',
    hue = 'hour',
    data = df,
)
plt.show()
plt.close()