excel,matlab,cluster-analysis,k-means,geo , K-Means Clustering a list of US addresses based on drive time


K-Means Clustering a list of US addresses based on drive time

Question:

Tag: excel,matlab,cluster-analysis,k-means,geo

I have 8 traveling consultants that need to visit 155 groups across the continental united states. Is there a way to find the optimal 8 regions based of drive time using k-means clustering? I see there are some methods implemented already for other data sets, but they are not based off drive time. How will I need to manipulate my data set to make it usable?

Thank you in advance for any feedback. I am by no means a great coder, I have taken only a few introductory courses back in college.


Answer:

I think you are looking for "path planning" rather than clustering. The traveling salesman problem comes to mind

If you want to use clustering to find the individual regions you should find the coordinates for each location with respect to some global frame. One example would be using Latitude and longitude coordinates. Create an array X thats 155x2 where each row is a destination with the columns lat,long Then simply run matlab's kmeans something like

[idx,C] = kmeans(X,8);

should work nicely. This should be enough to get you started.

One issue with this approach is that it will group the sites by geographical location. Which isn't always the same as shortest travel time. For instance,

distance from (site A, site B) = 0.5 miles
distance from (site A, site C) = 2.0 miles

but getting from A-B requires going around a river and actual travel distance is 10 miles, whereas A-C is realistically 2.5 miles, clearly A-C is the better choice, but using global position alone wouldn't take this into account


Related:


Connecting two binary objects in matlab


matlab,image-processing
I have a binary matrix containing several binary objects and I want to bridge between them. Actually I have the following picture: And the result has to be like this: Is there any function or a shortcut way, other than loops, for this problem?...

MATLAB Access Classreg


matlab
So, I want to be able to look at (read: copy) MATLAB's NonLinearModel method of printing the regression results to the screen such as this. Nonlinear regression model: y ~ (alpha1 - alpha2*t^0.5) Estimated Coefficients: Estimate SE tStat pValue alpha1 1.0253 0.0082253 124.66 4.8823e-24 alpha2 0.0061783 0.00073277 8.4314 4.4834e-07 Number...

VBA “Compile Error: Statement invalid outside Type Block”


excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Giving a string variable as a filename in matlab


string,matlab,filenames
I am using the below mentioned code to get the file names of images according to their id's from images_1 text file as strings and use them to read the images from their directory image_count=1; for image_count=1:6 file=fopen('D:\Academics\New folder\CUB_200_2011\images_1.txt','r'); C = textscan(file, '%s'); original_image=imread('D:\Academics\New folder\CUB_200_2011\images\%s','C{1}{2*(image_count)}'); imshow(original_image) end I am able...

function wait to execute


matlab,events,delay
In Matlab functions can be started at events,but occasionally, like with the resize function, the events are called in rapid order and the function is called many times in succession, which can cause weird behavior and lag. Is there a way to have it listen for the event but only...

How do I do to count rows in a sheets with filters? With a suppress lines


excel,vba,filter
I have a sheet with lots of columns, but when I filter and use count = Application.WorksheetFunction.CountA(Range("A:A")) It returns all the rows non Empty. Not only the rows I filtered....

How to insert excel formula to cell in Report Builder 3.0


sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

Interface Controls for DoEvent in Excel


excel,vba,excel-vba,loops,doevents
I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

Create string without repeating the same element jn the string (Matlab)


string,matlab
I have a string "FDFACCFFFBDCGGHBBCFGE" . Could anyone help me to generate a new string with the same order but no element inside repeated twice. Thanks ! The expected output should be like this : "FDACBGHE"...

Matlab — SVM — All Majority Class Predictions with Same Score and AUC = .50


matlab,svm,auc
I'm having a weird problem in training an SVM with an RBF kernel in Matlab. The issue is that, when doing a grid search, using 10-fold cross-validation, for the C and Sigma values I always get AUC values equal to approximately .50 (varying between .48 and .54 depending) -- I...

what does ellipsis mean in a Matlab function's argument list?


matlab
What is the ellipsis for in this Matlab statement? frame = insertObjectAnnotation(frame, 'rectangle', ... bboxes, labels); ...I could not find in their online doc....

Copying sheet to last row of a sheet from another workbook


excel,vba,excel-vba
I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

If cell value starts with a specific set of numbers, replace data


excel,vba,excel-vba
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

Can we add a statement in between MATLAB codes?


matlab
Is it possible to add statements in between the codes. For example: If I have a code like this, r(:,1) = a(:,1) - a(:,2); Then can I write it as, r(:,1) = a(:,1)("this is a constant") - a(:,2)("this is a variable"); ...

How to force my output data in a inputdlg on Matlab be a double?


matlab,typeconverter
I'm currently using a MATLAB to work and I need some help: I need to convert my output data (variable: units) be a double instead of a cell because I must perform a sum: units = inputdlg(question,title); sum = units + i; I've tried this code also but didn't solve...

Excel VBA Loop Delete row does not start with something


excel,excel-vba
I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?


excel
I've got a large spreadsheet that I'm trying to optimise as it has over 12,000 lines of data, with in excess of 28 columns. It currently takes a significant amount of time to execute and I'm therefore starting to pare it down. As part of this I've started looking at...

NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file


java,excel,apache-poi,writing
I am trying to write to an excel(.xlsx) file using Apache poi, I included the apache poi dependencies in my pom.xml file. But I am getting the following exception in execution. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) at java.net.URLClassLoader.defineClass(URLClassLoader.java:449) at...

Identifying cell in Openpyxl


python,excel,openpyxl
I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "x". I've managed to get so far, but I can't extract the location of said cell. This is the code I have come up with: from openpyxl import load_workbook wb...

thicken an object of image to a curve in matlab


matlab,image-processing
I have a labeled matrix containing two objects. How can I thicken an object to a curve? Actually I have the following image: and I want this: Each pixel of the resulting curve is the median of each column. But if you have another idea, it is acceptable, because I...

How to switch Matlab plot tick labels to scientific form?


matlab,plot
I have a semilogarithmic plot which works so far with semilogx. Now I would like to change the tick labels. Now it says 10^8 10^9 ... 10^13, but I would like to see 1e8, 1e9, ... 1e13 on the x-axis. How can I change that? Cheers Manuel...

Why black surf from this Matlab command?


matlab,time-frequency
Code tfr = abs ( tfr ); [row_size, column_size] = size(tfr); tfr = tfr(1:round(row_size/2), 1:row_size); surf(tfr); view(2); I get in R2014b of OSX 10.10.3 Yosemite but rotating around shows that the cells should not be black Why is the output black? I wonder if this is a hardware problem or...

Converting column from military time to standard time


r,excel
I'm trying to convert a column showing the time of road traffic accidents from military time to standard time. The data looks like this: Col1 Time..24hr. 1 1404 2 322 3 1945 4 1005 5 945 I'd then like to convert to 12hr so for '322' I'd like to make...

Plot multiple functions on one figure


matlab,matlab-figure
I'm struggling to plot multiple functions on one figure. Here is the code that I have: syms t a; a=0.9514; F1=0.5*sqrt(3*t^2); F2=-0.28375*t^2+1.155*a*(t-a)+1; F3=1; E1=diff(F1,t); E2=diff(F2,t); E3=diff(F3,t); I want to plot E1, E2 and E3, each only within a certain range, to make a "composite" line. I've tried plotting with ezplot...

solve symbolic system of equations inside an array


matlab,system,equation
sorry if it already has a answer..i tried other links but it didn't understand i have 2 1*63 array .landa and v. each of their members are syms. and each v member is a function of all landa members.i have already calculated v members and they are all symbolic equations...

Using VLOOKUP formula or other function to compare two columns


mysql,excel,vba,date
I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range


excel,vba,excel-vba
I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

adding variables into another variable vba


excel,vba,excel-vba
Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

12 Characters Including leading and following zeros


excel
I am finding this difficult to explain, but ultimately I am wanting a cells value to be 12 characters long including +/- a decimal point and following zeroes. Examples are 1200 would become +1200.000000 -20 would become -20.00000000 99999999 would become +99999999.00 I have tried FIXED, LENGTH, and formatting rules...

matlab plots as movie with legend


matlab,plot,legend,movie
i have a question regarding legend for movies. This is my code: fig = figure(); for i = 1: 70000 plot(signal1) hold on; plot([i,i],[-5,5]) plot(signal2,'r') hold off; title('\fontsize{14} my data'); legend('signal1','signal2'); axis tight; f(i) = getframe(fig); end The legend shows the same colors for the first two things I plot....

VBA - Unable to pass value from Private to Public Sub


excel,vba,excel-vba
I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

Saving images with more than 8 bits per pixel in matlab


image,matlab,image-processing,computer-vision
I need to save a set of pre-processing images in matlab, resulting in grayscale images. The problem is the fact that these pre-processed images have pixel values higher than 255. If I save them with imwrite() as, for instance, .PNG files, does matlab normalize the values to be in [0,255]...

Converting ADODB Loop into DAO


excel,vba,ms-access,ado,dao
Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

Using a stored integer as a cell reference


excel,excel-vba,reference
Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

timestamp SQL to Excel


php,mysql,sql,excel
If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

xcorr function with impulse response


matlab,filtering,convolution
I'm trying to design a Wiener filter in Matlab for a deconvolution problem but I'm having a lot of problems. I have a gaussian white noise process with a variance of 1.2 and a impulse response which has length two. Its values are g(0) = 5 and g(1) = 4....

Excel - select a cell based on adjacent cell value


excel
I have the following excel spreadsheet and I am trying to work out how I can write a formula in order to provide the values in column D. In each row, there is a test date, I am trying to calculate the day difference from each test date to the...

Matlab - Multiply specific entries by a scalar in multidimensional matrix


matlab,matrix,multidimensional-array,scalar
I'm having problems multiplying specific values within my multidimensional matrix by a scalar. My matrix has the following dimension: size(comDatabe) = 5 10 3 397 10 The third dimension is an x-y-z coordinate frame. Something went wrong and now my y-axis is defined upside down for one subject (#8 out...

Reading all the files in sequence in MATLAB


matlab,image-processing
I am trying to read all the images in the folder in MATLAB using this code flst=dir(str_Expfold); But it shows me output like this. which is not the sequence as i want. Can anyone please tell me how can i read all of them in sequence? for giving downmark, please...

Create mask from bwtraceboundary in Matlab


image,matlab,image-processing,mask,boundary
I'm trying to create a mask (or similar result) in order to erase pieces of a binary image that are not attached to the object surrounded by the boundary. I saw this thread (http://www.mathworks.com/matlabcentral/answers/120579-converting-boundary-to-mask) to do this from bwboundaries, but I'm having trouble making suitable changes to it. My goal...

How to access variables in the properties block of a Matlab System Object?


matlab,simulink
I am working on a simple System Object in Matlab/Simulink. It looks like this : classdef realtime_header_detectorSO < matlab.System & matlab.system.mixin.Propagates % correlateHeader % % This template includes the minimum set of functions required % to define a System object with discrete state. properties Header %nrOfBitsInPreviousStep=0; s=100; d=zeros(1,s); end properties...

excel search engine using vba and filters?


excel,vba
I am using the following vba code to filter my rows in excel based on the value in my cell C5 Sub DateFilter() 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 ActiveSheet.Range("C10:AS30").AutoFilter Field:=1, Criteria1:="*" & ActiveSheet.Range("C5").Value & "*" Application.ScreenUpdating = True End...

How to normalise polynomial coefficients in a fraction?


matlab,polynomial-math
I have the following code: syms z Gc=1.582*(1-0.3679*z^-1)/(1+.418*z^-1); Ghp=.3679*(z^-1)*(1+.718*z^-1)/((1-z^-1)*(1-.3679*z^-1)); T=(Gc*Ghp)/(1+Gc*Ghp); clipboard('copy', latex(simplifyFraction(T))); Which results in following for T: How can I normalise coefficients? I.e. I want the z2 in denominator and z in numerator to have the coefficient of 1. Is there any function in Matlab to do so?...

Using a cell's number to insert that many rows (with that row's data)


excel,excel-vba
I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

Animate through multiple 2D Matlab plots


matlab,plot
I have multiple 2D line plots in Matlab (they represent some wave moving through space). Each plot represents the wave at some time t. I want to animate through these plots (i.e. show the first plot for a fraction of a second, then show the next one, and the next,...

Comparing cell contents against string in Excel


string,excel,if-statement,comparison
Following is my table file:*.css file:*.csS file:*.PDF file:*.PDF file:*.ppt file:*.xls file:*.xls file:*.doc file:*.doc file:*.CFM file:*.dot file:*.cfc file:*.CFM file:*.CFC file:*.cfc file:*.DOC I need a formula to populate the H column with True or False if it finds column G in column F (exact case). I used following but nothing seems to...

Excel - Pulling data from one cell within a list


excel,powerpoint,spreadsheet
I use PowerPoint as a graphics template to type up football player names and there squad numbers. It can be a long procedure and so far following YouTube tutorials i have managed to create a form in Excel which can update the text boxes in PowerPoint at the click of...

Interpolation inside a matrix. Matlab


matlab,matrix
I have a matrix looks like: 0 0 0 0 0 1 0 0 0 0 0 2 0 0 0 0 0 2 0 0 0 0 0 1 0 1 0 0 0 1 0 4 0 0 0 0 0 3 0 0 6 0 0 4...

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In


excel,vba,excel-vba,user-defined-functions
I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

Plotting random signal on circle


matlab,plot,signals,circle
I have some random signal (for example sin signal) with the time scale. t=0:0.1:2*pi y=sin(t) plot(t,y) Now I want to draw this signal on this circle. So the time vector actually becomes an envelope of the circle. Envelope of the circle represents "y = 0" in cartesian coordinate system. Here...