Power BI: image recognition - Python script and graphics

matplotlib, isomap, seaborn (updateable and sliceable) on Gaussian Na?ve Bayes model - graphics are SVGs in HTML Viewer custom visual, but you could use PNGs in Image custom visual - Python is in SQL Server 2017 stored procedure - results imported into Power BI - uses sp_execute_external_script with @Language = N'Python' - for SQL Server 2016 with @Language = N'R', use the R reticulate package to embed the Python in the R in the SQL - sounds complicated? but takes only 30 seconds to refresh the report.

original Python (which I updated and tweaked) is from Python Data Science Handbook by Jake VanderPlas - if you test the Python in PTVS first, it will suggest updates to deprecated libraries

Here is the script:

CREATE proc [dbo].[NumberRecognition] as

exec sp_execute_external_script

@Language = N'Python',

@Script = N'

import matplotlib.pyplot as plt

import seaborn as sns

from sklearn.datasets import load_digits

digits = load_digits()

fig, axes = plt.subplots(10, 10,

subplot_kw={''xticks'':[], ''yticks'':[]},

gridspec_kw=dict(hspace=0.1, wspace=0.1))

for i, ax in enumerate(axes.flat):

ax.imshow(digits.images[i], cmap=''binary'', interpolation=''nearest'')

ax.text(0.05, 0.05, str(digits.target[i]),

transform=ax.transAxes, color=''blue'')

#SVG method

plt.savefig(''c:\\inetpub\\wwwroot\\1.svg'', bbox_inches=''tight'')

# PNG method

# plt.savefig(''c:\\users\\art\\desktop\\1.png'', dpi = 60)

plt.close(''all'')

X = digits.data

y = digits.target

from sklearn.manifold import Isomap

iso = Isomap(n_components=2)

iso.fit(digits.data)

data_projected = iso.transform(digits.data)

plt.scatter(data_projected[:, 0], data_projected[:, 1], c=digits.target,

edgecolor=''none'', alpha=0.5,

cmap=plt.cm.get_cmap(''nipy_spectral'', 10))

plt.colorbar(label=''numbers by colour'', ticks=range(10))

plt.clim(-0.5, 9.5)

plt.savefig(''c:\\inetpub\\wwwroot\\2.svg'', bbox_inches=''tight'')

plt.close(''all'')

from sklearn.model_selection import train_test_split

Xtrain, Xtest, ytrain, ytest = train_test_split(X, y, random_state=0)

from sklearn.naive_bayes import GaussianNB

model = GaussianNB()

model.fit(Xtrain, ytrain)

y_model = model.predict(Xtest)

from sklearn.metrics import confusion_matrix

mat = confusion_matrix(ytest, y_model)

sns.heatmap(mat, square=True, annot=True, cmap=''Blues'', cbar=False)

plt.xlabel(''predicted'')

plt.ylabel(''actual'')

plt.savefig(''c:\\inetpub\\wwwroot\\3.svg'', bbox_inches=''tight'')

plt.close(''all'')

fig, axes = plt.subplots(10, 10,

subplot_kw={''xticks'':[], ''yticks'':[]},

gridspec_kw=dict(hspace=0.1, wspace=0.1))

test_images = Xtest.reshape(-1, 8, 8)

for i, ax in enumerate(axes.flat):

ax.imshow(test_images[i], cmap=''binary'', interpolation=''nearest'')

ax.text(0.75, 0.00, str(y_model[i]), transform=ax.transAxes, color=''green'' if (ytest[i] == y_model[i]) else ''red'', fontsize = 20, fontweight = ''bold'')

ax.text(0.00, 0.00, str(ytest[i]), transform=ax.transAxes, color=''blue'', fontsize = 20, fontweight = ''bold'')

plt.savefig(''c:\\inetpub\\wwwroot\\4.svg'', bbox_inches=''tight'')

plt.close(''all'')

'

-- #SVG method

select '1' as [Graph], '<img src=https://sql2017:8080/1.svg?' + convert(varchar(255), datepart(ms, SYSDATETIME())) + ' width = 700>' as HTML

union

select '2' as [Graph], '<img src=https://sql2017:8080/2.svg?' + convert(varchar(255), datepart(ms, SYSDATETIME())) + ' width = 600>' as HTML

union

select '3' as [Graph], '<img src=https://sql2017:8080/3.svg?' + convert(varchar(255), datepart(ms, SYSDATETIME())) + ' width = 400>' as HTML

union

select '4' as [Graph], '<img src=https://sql2017:8080/4.svg?' + convert(varchar(255), datepart(ms, SYSDATETIME())) + ' width = 700>' as HTML

-- #PNG base64 method

--SELECT

-- '1' as [Graph], 'data:image/png;base64,' + cast('' as xml).value('xs:base64Binary(sql:column("X.BulkColumn"))', 'varchar(max)') as [Image]