I have the multiindex dataframe (example below but not complete) named “pivot_dftable” and the code below displaying the analysis I need for it.
-
I would like to understand why in the graph displayed with the code provided, instead of one of the bar labels in the graph to be named “Re-Open” it just show blank in the graph.
-
Also, as you can see, the Queue Category names down below the graph is being cut in the image, how could I display it?
Thank you so much for the help!
pivot_dftable =
Status New
Queue Category Ticket Age - Open Tickets
UAE Queue Older than 3 Months 0.0
Older than 1 Month 1.0
Older than 2 Weeks 1.0
Switzerland Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 1.0
Older than 1 Week 1.0
Less than 1 Week 1.0
HQ Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 1.0
Less than 1 Week 1.0
London Queue Older than 3 Months 0.0
Older than 1 Month 2.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
York Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Denmark Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Finland Queue Older than 3 Months 3.0
Norway Queue Older than 3 Months 0.0
Older than 1 Month 2.0
Older than 2 Weeks 0.0
Sweden Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 1.0
Older than 1 Week 3.0
Less than 1 Week 4.0
France Queue Older than 3 Months 7.0
Older than 1 Month 0.0
Older than 2 Weeks 4.0
Netherlands Queue Older than 3 Months 3.0
Older than 1 Month 14.0
Older than 2 Weeks 14.0
Older than 1 Week 4.0
Less than 1 Week 0.0
Spain Queue Older than 3 Months 2.0
Older than 1 Month 1.0
Older than 2 Weeks 3.0
Czech Queue Older than 3 Months 1.0
Older than 1 Month 0.0
Slovakia Queue Older than 3 Months 1.0
Older than 1 Month 3.0
Older than 2 Weeks 1.0
Portugal Queue Older than 3 Months 0.0
Older than 1 Month 4.0
Older than 2 Weeks 2.0
Older than 1 Week 2.0
Peru Queue Older than 3 Months 16.0
Older than 1 Month 1.0
Older than 2 Weeks 1.0
TMC Queue Older than 3 Months 0.0
Older than 1 Month 1.0
Older than 2 Weeks 5.0
Older than 1 Week 13.0
Less than 1 Week 14.0
CoE - Compensation & Benefits Older than 3 Months 0.0
Older than 1 Month 1.0
CoE - Talent Acquisition Older than 1 Month 0.0
CoE - Learning, Development & Culture Older than 3 Months 0.0
Older than 1 Month 0.0
Global - Hypercare Older than 3 Months 0.0
Older than 1 Month 0.0
Global - HR Business Partners Older than 3 Months 0.0
Older than 1 Month 2.0
Global Central HRIS Team Queue Older than 3 Months 2.0
Older than 1 Month 1.0
Older than 2 Weeks 1.0
Older than 1 Week 5.0
Less than 1 Week 9.0
AMS Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
Less than 1 Week 0.0
Status In Progress
Queue Category Ticket Age - Open Tickets
UAE Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Switzerland Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 2.0
Less than 1 Week 2.0
HQ Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
Less than 1 Week 0.0
London Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
York Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Denmark Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Finland Queue Older than 3 Months 0.0
Norway Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Sweden Queue Older than 3 Months 0.0
Older than 1 Month 2.0
Older than 2 Weeks 3.0
Older than 1 Week 5.0
Less than 1 Week 4.0
France Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Netherlands Queue Older than 3 Months 3.0
Older than 1 Month 3.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
Less than 1 Week 0.0
Spain Queue Older than 3 Months 2.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Czech Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Slovakia Queue Older than 3 Months 1.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Portugal Queue Older than 3 Months 4.0
Older than 1 Month 3.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
Peru Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
TMC Queue Older than 3 Months 4.0
Older than 1 Month 13.0
Older than 2 Weeks 12.0
Older than 1 Week 3.0
Less than 1 Week 0.0
CoE - Compensation & Benefits Older than 3 Months 0.0
Older than 1 Month 0.0
CoE - Talent Acquisition Older than 1 Month 0.0
CoE - Learning, Development & Culture Older than 3 Months 0.0
Older than 1 Month 0.0
Global - Hypercare Older than 3 Months 1.0
Older than 1 Month 1.0
Global - HR Business Partners Older than 3 Months 0.0
Older than 1 Month 0.0
Global Central HRIS Team Queue Older than 3 Months 7.0
Older than 1 Month 26.0
Older than 2 Weeks 12.0
Older than 1 Week 4.0
Less than 1 Week 3.0
AMS Queue Older than 3 Months 2.0
Older than 1 Month 1.0
Older than 2 Weeks 7.0
Older than 1 Week 1.0
Less than 1 Week 6.0
Status Re-Open
Queue Category Ticket Age - Open Tickets
UAE Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Switzerland Queue Older than 3 Months 6.0
Older than 1 Month 5.0
Older than 2 Weeks 1.0
Older than 1 Week 1.0
Less than 1 Week 0.0
HQ Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
Less than 1 Week 0.0
London Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
York Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Denmark Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Finland Queue Older than 3 Months 0.0
Norway Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Sweden Queue Older than 3 Months 0.0
Older than 1 Month 3.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
Less than 1 Week 0.0
France Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Netherlands Queue Older than 3 Months 2.0
Older than 1 Month 0.0
Older than 2 Weeks 1.0
Older than 1 Week 0.0
Less than 1 Week 0.0
Spain Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Czech Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Slovakia Queue Older than 3 Months 1.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Portugal Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
Peru Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
TMC Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 1.0
Older than 1 Week 0.0
Less than 1 Week 0.0
CoE - Compensation & Benefits Older than 3 Months 1.0
Older than 1 Month 0.0
CoE - Talent Acquisition Older than 1 Month 0.0
CoE - Learning, Development & Culture Older than 3 Months 0.0
Older than 1 Month 0.0
Global - Hypercare Older than 3 Months 0.0
Older than 1 Month 0.0
Global - HR Business Partners Older than 3 Months 0.0
Older than 1 Month 0.0
Global Central HRIS Team Queue Older than 3 Months 0.0
Older than 1 Month 3.0
Older than 2 Weeks 1.0
Older than 1 Week 0.0
Less than 1 Week 0.0
AMS Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Older than 2 Weeks 0.0
Older than 1 Week 0.0
Less than 1 Week 0.0
Status Waiting
Queue Category Ticket Age - Open Tickets
UAE Queue Older than 3 Months 0.0
Older than 1 Month 0.0
Code:
import matplotlib.pyplot as plt
import numpy as np
from matplotlib.offsetbox import OffsetImage, AnnotationBbox, TextArea
# Assuming pivot_df is your DataFrame
# Map the "Queue Category" column using the dictionary
pivot_df.index = pivot_dftable.index.set_levels(pivot_dftable.index.levels[0].map(queuetocountry), level=0)
# Filter the DataFrame to include only the specified countries
countrieslist = ['AE', 'CH', 'London', 'York', 'DK', 'FI', 'NO', 'SE', 'FR', 'NL', 'ES', 'CZ', 'SK', 'PT', 'PE', 'TMC', 'HRIS', 'AMS']
filtered_df = pivot_df[pivot_df.index.get_level_values('Queue Category').isin(countrieslist)]
# Print the filtered DataFrame to debug
print(filtered_df)
# Define queue categories and ticket ages
queue_categories = filtered_df.index.get_level_values('Queue Category').unique()
ticket_ages = ['Older than 3 Months', 'Older than 1 Month', 'Older than 2 Weeks', 'Older than 1 Week', 'Less than 1 Week']
statuses = ['New', 'In Progress', 'Re-Open', 'Waiting']
# Plotting
fig, ax = plt.subplots(figsize=(20, 12))
# Define colors for each ticket age category
colors = {
'Older than 3 Months': 'red',
'Older than 1 Month': 'orange',
'Older than 2 Weeks': 'yellow',
'Older than 1 Week': 'green',
'Less than 1 Week': 'blue'
}
width = 0.2 # width of the bars
x = np.arange(len(queue_categories))
n = len(statuses)
# Loop through each status to create the grouped bars
for i, status in enumerate(statuses):
bottom = np.zeros(len(queue_categories))
for ticket_age in ticket_ages:
try:
current_counts = filtered_df.loc[(slice(None), ticket_age), status].reindex(queue_categories, fill_value=0).values
except KeyError as e:
print(f"KeyError for status '{status}' and ticket_age '{ticket_age}': {e}")
current_counts = np.zeros(len(queue_categories))
bars = ax.bar(x + (i - n / 2) * width, current_counts, bottom=bottom, width=width, color=colors[ticket_age], edgecolor='white', label=f'{ticket_age}' if i == 0 else None)
bottom += current_counts
# Add the total value on top of each bar
for bar in bars:
height = bar.get_height()
if height > 0: # Only annotate if the height is greater than 0
ax.annotate(f'{int(height)}',
xy=(bar.get_x() + bar.get_width() / 2, bar.get_y() + height),
xytext=(0, 0), # 3 points vertical offset
textcoords="offset points",
ha='center', va='bottom')
# Set labels and title
ax.set_title('9.1 Open Ticket Ages by Queue Category and Status', fontsize=15)
# Set x-ticks and labels for queue categories
ax.set_xticks(x)
ax.set_xticklabels([]) # Clear the existing labels
# Add status labels below the bars
status_labels = [f'{status}' for status in statuses for _ in range(len(queue_categories))]
status_positions = np.tile(x, n) + np.repeat((np.arange(n) - n / 2) * width, len(queue_categories))
ax.set_xticks(status_positions, minor=True)
ax.set_xticklabels(status_labels, minor=True, rotation=90, fontsize=8, ha='center')
# Move queue category labels down by 2 cm
for i, label in enumerate(queue_categories):
offsetbox = TextArea(label, textprops=dict(rotation=90, ha='center', va='top', fontsize=10))
ab = AnnotationBbox(offsetbox, (x[i], 0), xybox=(0, -60), # Move down by 60 points (approx 5 cm)
xycoords='data', boxcoords="offset points", frameon=False)
ax.add_artist(ab)
# Adjust layout to prevent overlap and increase bottom margin
plt.subplots_adjust(bottom=0.3)
# Create a legend with only the "Ticket Aging" categories
handles, labels = ax.get_legend_handles_labels()
unique_labels = {}
for handle, label in zip(handles, labels):
if label not in unique_labels:
unique_labels[label] = handle
ax.legend(unique_labels.values(), unique_labels.keys(), title='Ticket Age')
plt.show()