How to make combo charts inside subplots using a For loop in Python

Question

I'm trying to programmatically create a combo of lineplots and barbplots inside a series of subplots using a For Loop in Python.

Here is reproductible version of the code I tried so far:

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

df_q = pd.DataFrame.from_dict ({'Year': [2020,2021,2022,2020,2021,2022,2020,2021,2022], 
                                'Category': ['A','A','A','B','B','B','C','C','C'], 
                                'Requested': [5,8,3,15,6,9,11,14,9],
                                'Allowed':   [5,6,2,10,6,7,5,11,9]})
                                
df_a = pd.DataFrame.from_dict ({'Year': [2020,2021,2022,2020,2021,2022,2020,2021,2022], 
                                'Category': ['A','A','A','B','B','B','C','C','C'], 
                                'Amount': [4,10,3,12,6,11,7,5,12]})
    
df_qt = df_q.melt(id_vars=['Year', 'Category'], 
                   value_vars=['Requested', 'Allowed' ], ignore_index=True)   
 
catgs = df_a['Category'].unique()
    
fig, ax = plt.subplots(nrows=len(catgs), figsize=(20,len(catgs)*6))
  
for i, cat in enumerate(catgs):
    filt_q = df_qt.loc [df_qt['Category'] == cat]
    filt_a= df_a.loc [df_a['Category'] == cat]
      
    sns.barplot(data = filt_q, x='Year', y='value', hue= 'variable', alpha=0.7, ax=ax[i])
    
    sns.lineplot(data =filt_a['Amount'], linewidth = 1.5, markers=True,
                 marker="o",markersize=10, color='darkred', label='Amount', ax=ax[i])
    
    ax[i].set_title('Category: {}'.format(cat), size=25)

However, there seems to be a lag between the lineplot and barplot starting from the second subplot. See screenshot below.

Any idea what I'm doing wrong ?

Answer 1

Try this:

filt_a= df_a.loc[df_a['Category'] == cat].reset_index()

The reason you see the drift in Category B and C were due to the x-coordinate of the bar chart:

# The x-coordinates are not 2020, 2021, 2022.
# They are 0, 1, 2. The *x-labels* are 2020, 2021, 2022
sns.barplot(data=filt_q, x='Year', y='value', ...)

# The x-coordinates are the index of the filt_a dataframe
# Calling reset_index resets it to 0, 1, 2, ...
sns.lineplot(data=filt_a['Amount'], ...)