Power BI Python visual - Is my time data wrong or am I formatting it the wrong way?

Question

I'm trying to make a scatter map using the Python visual since this feature is not present out of the box for date or time data. R is not an option since it is not possible to view these visuals on the web service. The set of visuals available on the marketplace does not give the expected result.

This is the first time I try to create a Python visual and a first visual with time values.

I have read the Power BI documentation on Python. I read the documentation on Pandas and Matplotlib.

But, I can't figure out why my graphic displays 00:00 on the y axis. So I come to question my data.

My data was initially generated in Excel and then imported into Power BI. Here a sample :

Day_of_the_week,Time
Monday,1899-12-30 08:15:00
Monday,1899-12-30 14:15:00
Tuesday,1899-12-30 16:15:00
Wednesday,1899-12-30 08:15:00
Thursday,1899-12-30 11:30:00
Thursday,1899-12-30 14:15:00
Thursday,1899-12-30 16:15:00
Friday,1899-12-30 11:30:00

Result in Power BI :

Python code for visual :

import matplotlib.pyplot as plt
import matplotlib.dates as mdates

plt.scatter(dataset.Day_of_the_week,dataset.Time)
date_format = mdates.DateFormatter('%H:%M')
plt.gca().yaxis.set_major_formatter(date_format)

plt.show()

Any hint on what I'm doing wrong?

Answer 1

Your Time column was not recognized as a datetime object. You can convert the column to datetime like so:

dataset.Time = pd.to_datetime(dataset.Time, format='%Y-%m-%d %H:%M:%S')

full code:

import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import pandas as pd
from io import StringIO

d='''Day_of_the_week,Time
Monday,1899-12-30 08:15:00
Monday,1899-12-30 14:15:00
Tuesday,1899-12-30 16:15:00
Wednesday,1899-12-30 08:15:00
Thursday,1899-12-30 11:30:00
Thursday,1899-12-30 14:15:00
Thursday,1899-12-30 16:15:00
Friday,1899-12-30 11:30:00'''
dataset = pd.read_csv(StringIO(d), header=0)

dataset.Time = pd.to_datetime(dataset.Time, format='%Y-%m-%d %H:%M:%S')

plt.figure()
plt.scatter(dataset.Day_of_the_week,dataset.Time)
date_format = mdates.DateFormatter('%H:%M')
plt.gca().yaxis.set_major_formatter(date_format)
plt.tight_layout()
plt.show()