Menu
  • HOME
  • TAGS

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

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.

Best How To :

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

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

excel,excel-vba

This will do what you want, it polls through from the bottom up, if it encounters a number in C and it is > 1 then it will insert the number of rows equal to column C number - 1 then copy the data from the host row. This will...

How to insert excel formula to cell in Report Builder 3.0

sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2

Excel Formulas support as ended since SSRS 2008 (see Breaking Changes in SQL Server Reporting Services). No Formula Support in Excel In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel,...

Why black surf from this Matlab command?

matlab,time-frequency

My bet is that trf is a very large matrix. In these cases, the surface has so many edges (coloured black by default) that they completely clutter the image, and you don't see the surface patches One solution for that is to remove the edges: surf(trf, 'edgecolor', 'none'). Example: with...

How to normalise polynomial coefficients in a fraction?

matlab,polynomial-math

You can extract the numerator and denominator with numden, then get their coefficiens with coeffs, normalize the polynomials, and divide again. [n,d] = numden(T); cn = coeffs(n); cd = coeffs(d); T = (n/cn(end))/(d/cd(end)); The output of latex(T) (note: no simplifyFraction now; it would undo things): Of course this isn't equal...

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

java,excel,apache-poi,writing

I think You'r missing some classes "UnsupportedFileFormatException" try to change the poi versions to the same and dont use the 3.11-beta2 You can use both in version 3.12 http://mvnrepository.com/artifact/org.apache.poi...

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

matlab,svm,auc

Unless you have some implementation bug (test your code with synthetic, well separated data), the problem might lay in the class imbalance. This can be solved by adjusting the missclassification cost (See this discussion in CV). I'd use the cost parameter of fitcsvm to increase the missclassification cost of the...

Identifying cell in Openpyxl

python,excel,openpyxl

cell.row and cell.column should give you the addresses you need. Here is what I got from the documentation: https://openpyxl.readthedocs.org/en/latest/api/openpyxl.cell.html?highlight=.row#module-openpyxl.cell.cell It gives you the list of attributes of a cell object.

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

matlab,simulink

You should be able to use properties s = 100; d = zeros(1,100); end right? If you already have the 100 as a default for s, you should also be able to provide this as part of the default for d. I'm guessing that you're trying to avoid doing that...

Converting column from military time to standard time

r,excel

Given your criteria -- that 322 is represented as 3 and 2045 is 20 -- how about dividing by 100 and then rounding towards 0 with trunc(). time_24hr <- c(1404, 322, 1945, 1005, 945) trunc(time_24hr / 100) ...

function wait to execute

matlab,events,delay

The "weird behavior and lag" you see is almost always a result of callbacks interrupting each other's execution, and repeated unnecessary executions of the same callbacks piling up. To avoid this, you can typically set the Interruptible property of the control/component to 'off' instead of the default 'on', and set...

Plotting random signal on circle

matlab,plot,signals,circle

You need to add "noise" to the radius of the circle, roughly around r=1: th = linspace( 0, 2*pi, N ); %// N samples noise = rand( 1, N ) * .1; %// random noise in range [0..0.1] r = 1+noise; %// add noise to r=1 figure; plot( r.*cos(th), r.*sin(th)...

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

matlab,typeconverter

inputdlg returns a cell array of strings. You can convert to double with str2double: units = str2double(inputdlg(question, title)); ...

Connecting two binary objects in matlab

matlab,image-processing

Morphological operations are suitable but i would suggest line structuring element as your arrangement is horizontal and you do not want overlaps between lines: clear clc close all BW = im2bw(imread('Silhouette.png')); BW = imclearborder(BW); se = strel('line',10,0); dilateddBW = imdilate(BW,se); img= imerode(BW,se); figure; imshow(img) ...

12 Characters Including leading and following zeros

excel

User the formula =LEFT(TEXT(A1,"+0.000000000;-0.000000000"),12) where A1 contains your number. This will be a text cell type but will format the cell exactly how you want it....

Animate through multiple 2D Matlab plots

matlab,plot

I assume with "2d-line" you mean a 2d-plot. This is done by the plot-function, so there is no need of surf or mesh. Sorry, when I got you wrong. The following code does what I think you asked for: % Generate some propagating wave n = 20; t = linspace(0,10,100);...

Matlab - Multiply specific entries by a scalar in multidimensional matrix

matlab,matrix,multidimensional-array,scalar

Errr, why you multiply indexes instead of values? I tried this: comDatabe(:,:,[1 2 3],:,8) = comDatabe(:,:,[1 2 3],:,8)*-1 And it worked....

xcorr function with impulse response

matlab,filtering,convolution

The following code implements only a part of what I can see in the description. It generates the noise processes and does what is described in the first part. The autocorrelation is not calculated with the filter coefficients but with the actual signal. % generate noise process y y =...

Can we add a statement in between MATLAB codes?

matlab

You need to comment those statements like this r(:,1) = a(:,1) ... % this is a constant - a(:,2); % this is a variable for more information read this ...

Comparing cell contents against string in Excel

string,excel,if-statement,comparison

We need an Array formula. In G2 enter: =NOT(ISERROR(MATCH(1,--EXACT(F$2:F$7,E2),0))) and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. Note: The curly brackets that appear in the Formula Bar should not be typed....

MATLAB Access Classreg

matlab

You can read the file using: >> open classreg.regr.modelutils.tstats This will open "tstats.m". The path of that file on your drive can be a acccessed using: >> which classreg.regr.modelutils.tstats In this folder there are all the other m-files which belong to this class....

Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba

This is what I mentioned in my comment Note: in future, you can using for loop to go through the column index. Option Explicit Dim WB1 As Workbook Dim ws1 As Worksheet Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long Dim i As Integer...

Using a stored integer as a cell reference

excel,excel-vba,reference

You have to find a suitable formula for entering in the target cell. Then you would build such formula with string concatenation, etc., for entering it via VBA. One option for the formula is to use OFFSET, as in =SUM(OFFSET($A$1,D3-1,COLUMN()-1):OFFSET($A$1,ROW()-3-1,COLUMN()-1)) This sums all values from Cell1 to Cell2, in the...

solve symbolic system of equations inside an array

matlab,system,equation

Generally this is done (if the eq is in the format you have) with an Ax=b system. Let me show you how to do it with a simple example of 2 eq with 2 unknowns. 3*l1-4*l2=3 5*l1 -3*l2=-4 You can build the system as: x (unknowns) will be a unknowns...

Excel - select a cell based on adjacent cell value

excel

The logic here is: (1) Find the date for each subject that is the principal date, and return it for each row; and (2) subtract this date from the current date in col B. (2) is easy, but (1) requires a way to match the value in B on both...

Reading all the files in sequence in MATLAB

matlab,image-processing

From the Matlab forums, the dir command output sorting is not specified, but it seems to be purely alphabetical order (with purely I mean that it does not take into account sorter filenames first). Therefore, you would have to manually sort the names. The following code is taken from this...

Interpolation inside a matrix. Matlab

matlab,matrix

M =[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 0 0 3 0 0...

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date

If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....

Giving a string variable as a filename in matlab

string,matlab,filenames

You would need to change your back slashes \ to forward slashes /, otherwise some \ followed by a letter may be commands in the sprintffunction, like for example \N or \a. See sprintf documentation in the formatSpecarea for more information. original_image=imread(sprintf('D:/Academics/New folder/CUB_200_2011/images/%s', C{1}{2*(image_count)})); ...

Plot multiple functions on one figure

matlab,matlab-figure

I think you are missing the x limit. xlim([0 2.5*a]) ...

Create mask from bwtraceboundary in Matlab

image,matlab,image-processing,mask,boundary

It's very simple. I actually wouldn't use the code above and use the image processing toolbox instead. There's a built-in function to remove any white pixels that touch the border of the image. Use the imclearborder function. The function will return a new binary image where any pixels that were...

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

excel,vba,excel-vba

Use the LEFT() function, as shown below: lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If LEFT(c.Value,3) = "614" _ Or LEFT(c.Value,3) = "626" _ Or LEFT(c.Value,3) = "618" _ Or LEFT(c.Value,3) = "609" _ Or LEFT(c.Value,3) = "605" Then...

VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010

You have: Dim RangeNOut as Double Dim RangeNOut as Integer While the IF statements in there are a nice idea, VBA will not allow you to do that. It doesn't do conditional 'compilation' since it isn't a compiled language. When VBA runs your code, all your variables are declared (no...

Excel VBA Loop Delete row does not start with something

excel,excel-vba

It's because your moving forward through the rows - if you delete row 4 then row 5 becomes row 4 and the code will jump to the new row 5 - which is in fact row 6. Hope that made sense. :) The solution will be to use a For...

How to switch Matlab plot tick labels to scientific form?

matlab,plot

You can change the XTickLabels property using your own format: set(gca,'XTickLabels',sprintfc('1e%i',0:numel(xt)-1)) where sprintfc is an undocumented function creating cell arrays filled with custom strings and xt is the XTick you have fetched from the current axis in order to know how many of them there are. Example with dummy data:...

Interface Controls for DoEvent in Excel

excel,vba,excel-vba,loops,doevents

How about changing your 'do until' loop to a 'for next' loop? Something like?... Sub rowinput() Dim lngInputStartRow As Long Dim lngInputEndRow As Long Dim row_number As Long lngInputStartRow = Range("A1").Value 'specify your input cell here lngInputEndRow = Range("A2").Value For row_number = lngInputStartRow To lngInputEndRow DoEvents Next row_number 'Then a...

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

string,matlab

Use unique with the 'stable'option: str = 'FDFACCFFFBDCGGHBBCFGE'; result = unique(str, 'stable'); If you want something more manual: use bsxfun to build a logical index of the elements that haven't appeared (~any(...)) before (triu(..., 1)): result = str(~any(triu(bsxfun(@eq, str, str.'), 1))); ...

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

excel,vba,filter

Try the following which uses the SpecialCells() method to select only cells that are currently visible on screen (i.e. not filtered out). count = Application.WorksheetFunction.CountA(Range("A:A").SpecialCells(xlCellTypeVisible)) ...

Excel - Pulling data from one cell within a list

excel,powerpoint,spreadsheet

If you have two columns of the shirt numbers and the corresponding player names then vlookup() will do this, but a warning : are shirt numbers unique i.e. one player one number... With a list of numbers in D2 to D8 and corresponding names in E2 to E8, then =VLOOKUP(A2,D2:E8,2,0)...

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

excel,vba,excel-vba,user-defined-functions

You need to use Application.Caller. This will return the value in cell A1 of the sheet the function is entered to: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Cells(1, 1) End Function This will return the name of the calling sheet: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Name End...

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

matlab

http://www.mathworks.com/help/matlab/matlab_prog/continue-long-statements-on-multiple-lines.html Continue Long Statements on Multiple Lines This example shows how to continue a statement to the next line using ellipsis (...). s = 1 - 1/2 + 1/3 - 1/4 + 1/5 ... - 1/6 + 1/7 - 1/8 + 1/9; ...

thicken an object of image to a curve in matlab

matlab,image-processing

This is called "skeletonization" and you can do it with the function bwmorph: bwmorph(Img, 'skel', Inf); Best...

Saving images with more than 8 bits per pixel in matlab

image,matlab,image-processing,computer-vision

You can use the bitdepth parameter to set that. imwrite(img,'myimg.png','bitdepth',16) Of course, not all image formats support all bitdepths, so make sure you are choosing the the right format for your data....

VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba

In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...

excel search engine using vba and filters?

excel,vba

In order to filter for "any" column, you could combine a Find result and Filter like this: Sub DateFilter() Dim nRow As Range Dim toSearch As Range 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 Set toSearch = Range("A1:C4") 'detect row that...

Converting ADODB Loop into DAO

excel,vba,ms-access,ado,dao

DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once. Sub test() Dim vaIds As Variant Dim sSql As String vaIds = Split("1 2 4 7 200 205 654", Space(1)) sSql = "SELECT [Sales]...

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

excel,vba,excel-vba

If cell.Value2 = "FOUND THE CELL" Then cell.Offset(0, 1).Value2 = "changed the next right side cell" cell.Offset(0, 2).Value2 = "changed the second right side cell" End If ...

timestamp SQL to Excel

php,mysql,sql,excel

The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...

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

excel

A boolean would most likely not yield better performance than integers, since the Excel formula engine is dynamically typed. To significantly improve the performance of your spreadsheet, you should probably consider other options. Excel PowerPivot comes to mind, as it can easily handle millions of records with hundreds of calculations,...

adding variables into another variable vba

excel,vba,excel-vba

I can't see anything wrong with the code, as long as your text is in column C, and the values are in column H I've also taken the liberty of rewriting the code to make it clearer: Sub test() Dim x As Long Dim y As Long Dim TotalValue As...

matlab plots as movie with legend

matlab,plot,legend,movie

The strings defined in the legend command are assigned in order of the plots being generated. This means that your first string 'signal1' is assigned to the plot for signal1 and the second string 'signal2' is assigned to the vertical line. You have two possibilities to fix this problem. Execute...