Using nwimport to import a comma-delimited US state adjacency matrix

[KDA]

Thanks for creating newcommands, Thomas!

How do I use nwimport to import the attached Excel-generated adjacency matrix (in each row, the first entry represents the focal state of interest, and the subsequent entries list its adjacent states. I got this from https://writeonly.wordpress.com/2009/03/20/adjacency-list-of-states-of-the-united-states-us/ )?
 
I tried the command nwimport USStateAdjMat.xlsx, type(matrix) but got the following error:

Loading networks from file USStateAdjMat.xlsx failed
r(6750);

I have attached my Excel file. Any help would be greatly appreciated!

[Thomas Grund]

Hi Anita,

your data is a compressed adjacency list format that nwimport does not natively understand. It wants one edge/arc per line. 

But there is a simply way to load your data with nwfromedge. First, save your .xlsx file as .csv file in Excel. The code below imports the data as csv (you need to change your file destination), reshapes it and runs nwfromedge.

Have fun!

Thomas

*********************

// loads data as .csv => adjust your path

import delimited "/Users/thogr49/Documents/USStateAdjMat.csv", delimiter(comma) clear

// reshapes the data

rename v1 ego

reshape long v, i(ego) j(j)

rename v alter

// needed to deal with isolates

replace alter = ego if alter == ""

nwfromedge ego alter

nwplot network, lab

 

[KDA]

Excellent, and thanks SO much, Thomas!

I look forward to playing further with nwcommands!