How to add multiple labels on x-axis of groupby plot of pandas dataframe

Question

I have an Excel sheet shared here: https://docs.google.com/spreadsheets/d/1WolE-TpyEXtv1rlr3xusESke46UMlRurAEH1D1Lsyss/edit?usp=sharing

I am trying to create work progress graph and here is my code:

import datetime
from datetime import date
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline

df = pd.read_excel('Test Execution Progress.xlsx')

dp = df.groupby(by=['Project ID', 'Release Name', 'Cycle Name'],
                  as_index=False).plot(x=['Test Execution Date'],
                                       y=['Planned', 'Commulative Tested', 'Commulative Passed'],
                                       figsize=(16, 6), linestyle='dashed',marker='D', markersize=5,
                                       title='Trest Exection Progress', legend=True, colormap='jet')

The target is to:

Group the dataframe by Project ID, Release Name and Cycle Name
For Each group draw a plot with Test Execution Date on x-axis and "Cumulative Tested" and "Cumulative Passed" on y-axis showing the progress of test cases execution for each group.
Label x-axis with the respective group of Project ID, Release Name and Cycle Name
label y-axis with the text, "Count of Test Cases"

Help in this regard is highly appreciated.

Edit I found an example from here: enter link description here

And after making some changes, I managed to almost get what I wanted as follows:

Now, I just need to:

Remove the parenthesis from the tuples on the top of each plot (example: ('BIC', 'R5', 'C5') on the top of the first plot) which I think that I might be able to do myself.
Remove the duplicate yticks on the left of each of the plot. I don't have any idea for this.

Could you please provide a [**minimum** working example](https://stackoverflow.com/help/mcve) describing only a relevant subset? — Nico Albers, Mar 04 '18 at 18:18
I don't even understand the problem. Apart from a [mcve] this question also lacks a clear problem description. What is the desired outcome? How should the final plot look like? What hinders you obtaining that outcome? — ImportanceOfBeingErnest, Mar 04 '18 at 18:23
The only thing I am looking for is to add x-axis label as Project ID, Release Name and Cycle Name — novastar, Mar 04 '18 at 18:24
You're saying you did not find a way to produce an xlabel? Like `plt.xlabel("mylabel")`? Serious? — ImportanceOfBeingErnest, Mar 04 '18 at 18:52
Just edited all the problem statement and minimal code. Yes, I have tried code similar to plt.xlabel("mylabel") but all it does is print the label only once. I need to have the x-axis label for each and every sub-plot with corresponding group (Project ID, Release Name and Cycle Name) — novastar, Mar 04 '18 at 18:58
This is not a [mcve]. If you say that nobody has ever asked a question here about labeling a groupby plot you need to make this reproducible for others. — ImportanceOfBeingErnest, Mar 04 '18 at 21:20
@ImportanceOfBeingErnest. Thank you so much. I used an example presented by yourself and almost completed what I wanted. Can you look at my edits and suggest how can I remove the duplicate y-ticks? — novastar, Mar 04 '18 at 21:40
As long as you do not understand the concept of a [mcve] I can't do anything more for you. — ImportanceOfBeingErnest, Mar 04 '18 at 21:42

score 2 · Answer 1 · answered Mar 05 '18 at 19:19

Finally, I resolved all issues and managed to get an desired output. Here is the code:

grouped = dfm.groupby(by=['Project ID', 'Release Name', 'Cycle Name'], as_index=False)
# Calculate the height of table to be displayed above the Testing Progress charts
l = len(dfp['Cycle Name']) + 2

# Create a row_num variable for locating the charts in Excel sheet
row_num = l

# Create a for loop to create charts grouped 
for name, group in grouped:
    image_data = BytesIO()
    fig = plt.figure(figsize=(12, 4), dpi=None, facecolor='white')    
    ax1 = fig.add_subplot(111, facecolor='aliceblue')
    group.plot.line(ax=ax1, color='purple', x='Test Execution Date',
                    y='Planned', linestyle='dashdot', marker='o', markersize=5)
    ax1.set_ylabel('Test Cases Count') 
    ax2 = ax1.twinx()
    group.plot.line(ax=ax1,color='blue',x='Test Execution Date',
                    y='Commulative Tested', linestyle='dotted', marker='o', markersize=5)
#     ax2.set_ylabel('Test Cases Count')
    group.plot.line(ax=ax1, color='green', x='Test Execution Date',
                    y='Commulative Passed', linestyle='dashed' ,marker='o',markersize=5)
    fig.suptitle('Overall Release Progress', fontsize=12, fontweight='bold', x=0.5, y =1.01)
    plt.title(' => '.join([str(i) for i in name]), fontsize=10, loc='center')
    ax1.grid(b=True, which='major', color='grey', linestyle='dotted')
    plt.yticks([])
    ax1.invert_yaxis()
    fig.savefig( image_data, format="png", dpi=100, facecolor='aliceblue', bbox_inches='tight', pad_inches=0.4)
    worksheet1.insert_image('A' + str(row_num), "", {'image_data': image_data})
    row_num += 23

# Rearrange the sheets
sheet_names = ['Test Execution Progress', 'Test Execution by Date']
workbook.worksheets_objs.sort(key=lambda x: sheet_names.index(x.name))

# Save the workbook
writer.save()

Here is the output:

Note: Names of the Project, Release Name and Cycle Names masked.

How to add multiple labels on x-axis of groupby plot of pandas dataframe

1 Answers1