Graphing line segments on top of a line graph with excel pandas xlsxwriter

Question

I have data set A which is time, temp, watts. I am able to generate and display the graph in an excel sheet using python, pandas and xlsxwriter. I now want to add data set B to the graph. This data is just a line segment. so data A is a complete time line measuring values. Data set be is a short event that occurred in that time line. I can get the segment to show on the graph but not at the time occurred.

[![excel graph][1]][1]

How do I get the line graph with the line segments at the correct spot on the x axis?

Data set A

time    temp    watts
8:00:00 45  20
8:00:47 42  20
8:01:12 68  50
8:01:52 72  50
8:02:21 41  20
8:02:58 37  20
8:03:28 70  50
8:04:01 71  50
8:04:37 38  15
8:05:00 36  15

Data set B

Begin   end value
8:01:09 8:02:02 123
8:03:10 8:04:20 147

or as

Begin   value
8:01:09 123
8:02:02 123
8:03:10 147
8:04:20 147

Not sure how to proceed

here is the working code code:

import pandas as pd
import matplotlib.pyplot as plt
import xlsxwriter
import os

ld_col = ['date', 'temp1', 'temp2', 'temp3', 'temp4', 'temp5']
ld = [  
    ['02-02-2024 08:01:00', 64, 58, 26, 21, 57],
    ['02-02-2024 08:01:40', 53, 42, 14, 9, 51],
    ['02-02-2024 08:02:20', 47, 36, 12, 8, 47],
    ['02-02-2024 08:03:03', 52, 38, 11, 9, 52],
    ['02-02-2024 08:03:47', 39, 22, 4, 0, 42],
    ['02-02-2024 08:04:17', 64, 58, 26, 21, 57],
    ['02-02-2024 08:04:57', 53, 42, 14, 9, 51],
    ['02-02-2024 08:05:16', 47, 36, 12, 8, 47],
    ['02-02-2024 08:05:49', 52, 38, 11, 9, 52],
    ['02-02-2024 08:06:12', 39, 22, 4, 0, 42],
    ['02-02-2024 08:07:00', 39, 22, 4, 0, 42]
    ]

ld_df = pd.DataFrame(ld, columns=ld_col)
ld_df['date'] = pd.to_datetime(ld_df['date'])
#ld_df['time'] = pd.to_datetime(ld_df['time'], format = '%H:%M:%S' ).dt.time

tp_col =  ['date', 'val', 'name']
tp = [
    ['02-02-2024 08:01:37', 30, 'ev1'],
    ['02-02-2024 08:02:17', 30, 'ev1'],
    ['02-02-2024 08:03:47', 46, 'ev2'],
    ['02-02-2024 08:04:17', 46, 'ev2'],
    ]

tp_df = pd.DataFrame(tp, columns=tp_col) 
tp_df['date'] = pd.to_datetime(tp_df['date'])
#tp_df['time'] = pd.to_datetime(tp_df['time'], format = '%H:%M:%S' ).dt.time

# Create a Pandas Excel writer using XlsxWriter as the engine.
excel_file = 'scatterchart.xlsx'
sheet_name = 'Sheet1'

writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')
ld_df.to_excel(writer, sheet_name=sheet_name)

tp_df.to_excel(writer, sheet_name=sheet_name, startcol=10, startrow=0)

# Access the XlsxWriter workbook and worksheet objects from the dataframe.
workbook = writer.book
worksheet = writer.sheets[sheet_name]

# Create a chart object.
chart = workbook.add_chart({'type': 'scatter', 'subtype': 'straight_with_markers'})

# Configure the series of the chart from the dataframe data.

row_len = ld_df.shape[0]
col_len = ld_df.shape[1]

for c in range(2,col_len+1):
    chart.add_series({
        'categories': ['Sheet1', 1, 1, row_len, 1],
        'values':     ['Sheet1', 1, c, row_len, c],
        'name':       ['Sheet1', 0, c, 0, c],
        'marker':     {'type': "circle"}
    })
            
row_len = tp_df.shape[0]
col_len = tp_df.shape[1]

for r in range(1, row_len+1, 2):
    #for c in range(12,col_len+1,2):
    chart.add_series({
        'categories': ['Sheet1', r, 11, r+1, 11],
        'values':     ['Sheet1', r, 12, r+1, 12],
        'name':       ['Sheet1', r, 13, r, 13],
        'marker':     {'type': "circle"},
        'y2_axis':    'True'
    })
   

    

chart.set_size({'width': 720, 'height': 576})
chart.set_title({'name': 'CPU % over run'})
# Configure the chart axes.
chart.set_x_axis({'name': 'Time', 'date_axis': True, 'num_format': 'h:mm:ss' })
#chart.set_x_axis({'name': 'Time', 'position_axis': 'on_tick','date_axis': True })
chart.set_y_axis({'name': 'CPU%', 'major_gridlines': {'visible': False}})
chart.set_y2_axis({'name': 'Rate', 'major_gridlines': {'visible': False}})

# Insert the chart into the worksheet.
worksheet.insert_chart('I8', chart)

# Close the Pandas Excel writer and output the Excel file.
writer.save()


exit()

Switching to scatter and changing the date format and it works [1]: https://i.stack.imgur.com/Ul10d.png