More than 2 Vertices

Mar 1, 2011 at 10:53 PM

Hi,

I'm new to NodeXL and so far have gone through the Tutorial pdf and played with a small sample set of data, all of which worked like a charm!  I'm not sure if this is possible with social network analysis, but is it possible to have more than 2 vertices?  For example, if Anne is friends with Bob and Cathy, can there be 3 columns showing Vertex 1 = Anne, Vertex 2 = Bob, and Vertex 3 = Cathy?

If not, is there an efficient way (in Excel) to take a row of connected "friends" and transform it into a set of relationship pairs?  That is, taking a row with:

Friend 1 Friend 2 Friend 3 Friend 4
Anne Bob Cathy Derek

and turning it into:

Vertex 1 Vertex 2
Anne Bob
Anne Cathy
Anne Derek

 

Thanks for your help,
Meghan

Mar 2, 2011 at 5:16 AM
Edited Mar 2, 2011 at 5:23 AM

Meghan:

NodeXL works with an edge list, where each edge has two vertices.  So no, NodeXL can't handle your row of connected friends.

However, it can easily be done it VBA, the programming language built into Excel.  Let's say you have a friend list that looks like this:

Anne  Bob  Cathy  Derek
Mark  Bill  Sally

To convert this to an edge list that NodeXL can understand, do the following:

* Create an empty NodeXL workbook.

* From the NodeXL workbook, use Office Button, New, Blank Workbook to create an empty regular Excel workbook.

* Copy your friend list into Sheet1 of the regular Excel workbook.  There can be a different number of friends in each row.

* Right-click the Sheet1 tab at the lower-left corner of Excel and select View Code.  A "Microsoft Visual Basic" window will open.

* Copy and paste the entire contents of my next post into the big empty pane of the Microsoft Visual Basic window.

* In the Microsoft Visual Basic window, select Run, Run Sub/UserForm (or click F5).

* Close the Microsoft Visual Basic window.  You'll see that your friend pairs have been copied to Sheet2 of the regular Excel workbook.

* Switch to the NodeXL workbook.  You can use Ctrl-Tab to switch between workbooks.

* In the NodeXL workbook, select NodeXL, Data, Import, From Open Workbook.

* In the Import from Open Workbook dialog box, select the regular Excel workbook as the "open workbook to import from."  Click Import.

You're done.

-- Tony

Mar 2, 2011 at 5:20 AM
Edited Mar 2, 2011 at 5:21 AM

Sub MakeFriendPairs()

    Dim oSheet1 As Worksheet
    Set oSheet1 = Me
   
    Dim oSheet2 As Worksheet
    Set oSheet2 = Me.Application.ActiveWorkbook.Worksheets("Sheet2")

    Dim oSheet1Cell As Range
    Set oSheet1Cell = oSheet1.Cells(1, 1)
   
    Dim oSheet2Cell As Range
    Set oSheet2Cell = oSheet2.Cells(1, 1)
   
    ' For each row until an empty row is reached.
   
    Do Until IsEmpty(oSheet1Cell.Value)
   
        Dim iColumn As Integer
        iColumn = 2
       
        ' For each column until an empty column is reached.
       
        Do Until IsEmpty(oSheet1Cell.Offset(0, iColumn - 1).Value)
       
            oSheet2Cell.Value = oSheet1Cell.Value
            oSheet2Cell.Offset(0, 1).Value = oSheet1Cell.Offset(0, iColumn - 1).Value
       
            iColumn = iColumn + 1
            Set oSheet2Cell = oSheet2Cell.Offset(1, 0)
           
        Loop
       
        Set oSheet1Cell = oSheet1Cell.Offset(1, 0)
       
    Loop
   
    oSheet2.Activate
   
End Sub

Mar 2, 2011 at 9:09 PM

Tony, that worked perfectly!  Thank you so much for providing the VB code.

- Meghan

Mar 2, 2011 at 9:27 PM

Glad to help.

-- Tony