1
$\begingroup$

I have an excel database of 50 rows and some 28,000 columns. The database provides information of water parameters such as pressure, roughness, pipe length, diameter, valve, pump, flow and so on. I want to apply k-means code on this data to find outliers.

Code

from pandas import read_excel
import numpy as np
from numpy import arange
from sklearn.cluster import KMeans
from sklearn.decomposition import PCA
import plotly.graph_objs as go
from plotly.offline import init_notebook_mode, iplot
init_notebook_mode


dataset = read_excel('D:\M.tech\Dataset\Testing database1 New.xlsx')
#print(dataset.tail())
dataset.fillna(0)
cols=dataset.columns[1:]
#print(cols)
np.isnan(dataset).any()
dataset = dataset.fillna(method='ffill')

clusters=KMeans(n_clusters=4)
dataset["clusters"]=clusters.fit_predict(dataset[cols])
dataset.head(20)

#Principal component separation to create 2 dim picture
pca=PCA(n_components=2)
dataset['x']=pca.fit_transform(dataset[cols])[:,0]
dataset['y']=pca.fit_transform(dataset[cols])[:,1]
dataset=dataset.reset_index()
print(dataset.tail())


trace0= go.Scatter(x=dataset[dataset.clusters == 0]['x'],
                   y=dataset[dataset.clusters == 0]['y'],
                   name="Cluster1",
                   mode ="markers",
                   marker =dict(size=10,color="rgba(15,152,152,0.5)",line=dict(width=1,color="rgb(0,0,0)")))

trace1= go.Scatter(x=dataset[dataset.clusters == 1]['x'],
                   y=dataset[dataset.clusters == 1]['y'],
                   name="Cluster2",
                   mode ="markers",
                   marker =dict(size=10,color="rgba(180,18,180,0.5)",line=dict(width=1,color="rgb(0,0,0)")))

trace2= go.Scatter(x=dataset[dataset.clusters == 2]['x'],
                   y=dataset[dataset.clusters == 2]['y'],
                   name="Cluster3",
                   mode ="markers",
                   marker =dict(size=10,color="rgba(132,132,132,0.8)",line=dict(width=1,color="rgb(0,0,0)")))

trace3= go.Scatter(x=dataset[dataset.clusters == 3]['x'],
                   y=dataset[dataset.clusters == 3]['y'],
                   name="Cluster4",
                   mode ="markers",
                   marker =dict(size=10,color="rgba(230,20,30,0.5)",line=dict(width=1,color="rgb(0,0,0)")))

data =[trace0,trace1,trace2,trace3]

iplot(data)

Above code displays no error but when I run the code in ipython console, it does not display any figure.

How can I do this?

Improve this question
$\endgroup$
1
  • $\begingroup$ Can you be more specific for which part of this task you require assistance? $\endgroup$
    – El Burro
    Jan 16, 2018 at 8:11

1 Answer 1

Reset to default
1
$\begingroup$

First, you can read your Excel File with python to a pandas dataframe as described here: how-can-i-open-an-excel-file-in-python

Second, you can use scikit-learn for the k-means clustering on your imported dataframe as described here:KMeans

Improve this answer
$\endgroup$
3
  • $\begingroup$ Thanks for the answer. I have written a code in jupyter notebook in pycharm. When Iam running the code in ipython console, it does not give any error but does not show any plot $\endgroup$
    – Sheetal
    Jan 18, 2018 at 12:54
  • $\begingroup$ If you use matplotlib then you have to activate the visualisation for iPython with %matplotlib inline. However, I think this would be a new question, even more for Stackoverflow. $\endgroup$
    – Rene B.
    Jan 18, 2018 at 13:27
  • $\begingroup$ If the answer helped you, then you could accept it by clicking the green hook under the rating. $\endgroup$
    – Rene B.
    Jan 19, 2018 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.