data transformation challenge

[Pavan_6]

Dear Geeks, I have a situation that I am trying to figure out how to automate on excel or write a program in python. i have a csv file like this:

> team1 team2   win
> hawks celtics 1
> hawks knicks  1
> hawks 76ers   0
> hawks lakers  0
> 

etc...

i want to transform this data into a matrix

> XXXX hawks celtics knics 76ers lakers
> hawks  0      1      1     0    0 
> celtics x     x      x    x    x etc...
> knics
> 76ers
> lakers
> 

hope i have explained my question clearly. appreciate your help

[J.C. SolvoTerra]

Ok, I thought I'd play with this. I updated your CSV to check the program, My CSV is like this (Baring in mind, this isn't an effective CSV reader as CSV value may contain commas etc"

Heres My Version of the CSV

team1,team2,win
hawks,celtics,1
hawks,knicks,1
hawks,76ers,0
hawks,lakers,0
hawks,celtics,1
76ers,celtics,1
76ers,lakers,1

And Here's the results printed in the console

               hawks    celtics     knicks      76ers     lakers
     hawks         0         2         1         0         0
   celtics         2         0         0         1         0
    knicks         1         0         0         0         0
     76ers         0         1         0         0         1
    lakers         0         0         0         1         0

You will see a lot of

.PadLeft(10, " ")

This has nothing to do with the program, other then formatting the layout in the console window.

Remeber to import System.IO

Here's the Results Class

    Private Class Results
        Public TeamH As String
        Public TeamA As String
        Public Result As String
    End Class

Here's the main code. Pop it into a button, and you're good to go

        'Open A Stream
        Dim sr As StreamReader = New StreamReader("C:\Users\Jay\Documents\Results.txt")

        Dim ResultsList As List(Of Results) = New List(Of Results)
        Dim TeamList As List(Of String) = New List(Of String)

        Dim rParts(2) As String

        'Read the results
        Do While sr.Peek() >= 0
            rParts = Strings.Split(sr.ReadLine(), ",")
            ResultsList.Add(New Results() With {.TeamH = rParts(0).PadLeft(10, " "), .TeamA = rParts(1).PadLeft(10, " "), .Result = rParts(2)})

            If Not (TeamList.Contains(rParts(0).PadLeft(10, " "))) Then TeamList.Add(rParts(0).PadLeft(10, " "))
            If Not (TeamList.Contains(rParts(1).PadLeft(10, " "))) Then TeamList.Add(rParts(1).PadLeft(10, " "))
        Loop

        'Close The Stream
        sr.Close()

        TeamList.Remove("     team1")
        TeamList.Remove("     team2")

        Dim rTable(TeamList.Count - 1, TeamList.Count - 1)

        Dim X As Integer = 0
        Dim Y As Integer = 0

        For Each eResult As Results In ResultsList

            'Location Of Column Team
            X = TeamList.FindIndex(Function(T As String) T = eResult.TeamA)

            'Location Of Row Team
            Y = TeamList.FindIndex(Function(T As String) T = eResult.TeamH)

            'Populate The Scores
            If X >= 0 And Y >= 0 Then
                If rTable(X, Y) = Nothing Then rTable(X, Y) = 0
                'Display Column Results
                rTable(X, Y) = (CInt(rTable(X, Y)) + CInt(eResult.Result)).ToString
                'Display Row Results
                rTable(Y, X) = (CInt(rTable(Y, X)) + CInt(eResult.Result)).ToString
            End If
        Next

        'Write The Column Headers
        Console.WriteLine("          " & Strings.Join(TeamList.ToArray, " "))

        'Write The Scores
        For C As Int16 = 0 To TeamList.Count - 1
            'Write The Row Headers
            Console.Write(TeamList(C).PadLeft(10, " "))
            'Iterate The Scores
            For R As Int16 = 0 To TeamList.Count - 1
                If rTable(C, R) = Nothing Then rTable(C, R) = 0
                Console.Write(rTable(C, R).ToString.PadLeft(10, " "))
            Next
            'NExt Line
            Console.WriteLine("")
        Next

[Pavan_6]

JC Solvo Terra, impressive effort, I was expecting only some minor heads up and thank you for your code.

I am not familiar with C#, I will try to write this in java/python and observe the performance and respond here

[J.C. SolvoTerra]

Darn, i didn't see the python tag. Sorry. This is vb.net source. Again, apologies. Yeah, keep me informed of your progress. Good luck.

[J.C. SolvoTerra]

If you think it will help I can provide you with a textual step by step of my solution. Hopefully you could then translate into java or python... let me know if this will be of use.

[Pavan_6]

Yes it very much will help. Thanks again, god bless you

[J.C. SolvoTerra]

Ok, so normally all these processes could be complete in one single loop, but for ease of reading I am performing each step seperatly. I also havent included things like ArrayName.Count'-1' I'm assuming you're cool with Array bounds, counts, lengths etc. I hope this helps.

Step 1. Create a 2 dimensional array to hold all the results eg

 ResultsArray(N,2)

N is the number of result sets

    N,0 Holds Team 1 Name
    N,1 Holds Team 2 Name
    N,2 Holds Win

Load the CSV data into this Array

Step 2. Create a 1 Dimensional array to hold all the team names eg TeamList (Do not allow duplicates and do not include the values of "team1" and "team2". Note, if you want to change the table index order e.g. you want them alphabetically, set the values in this array accordingly A-Z etc).

Populate the TeamList array whilst reading the results from the CSV or after by looping through the ResultsArray

Step 3. Once you've done that it's time to put the data in your required table\grid format so create a new 2 dimensional array eg ResultsTable(N,N) where N this time is the number of team names eg TeamList.Count

Time for the old switch-a-roo

Step 4. loop through the sets of data in the ResultsArray and return the Team Name index from the TeamList array and store them accordingly Team1 into X and Team2 into Y e.g

    'Not actual Code Just Visual Representation
    For N = 0 to ResultsArray.Count

        X = TeamList.IndexOf(ResultsArray(N,0)) 
        Y = TeamList.IndexOf(ResultsArray(N,1))

    Next

or

    'Not actual Code Just Visual Representation
    for(int N = 0; N < ResultsArray.Count; N++) {

            'X = Index Of TeamName1 In TeamList
            X = TeamList.IndexOf(ResultsArray(N,0)) 
            'Y = Index Of TeamName2 In TeamList
            Y = TeamList.IndexOf(ResultsArray(N,1))

    }

Once we have the indexes for each team in the result set we now set the according Cell or array location to the new score.

Step 5. In the same loop, below the previous code, you need to do something like the following:

    'Not actual code just visual representaion
    'This sets up the score cell for reading Across Then Down
    ResultsTable(X,Y) += ResultsArray(N,2)
    'We Filp The X and Y for the next one to add the score for reading
    'Down then Across
    ResultsTable(Y,X) += ResultsArray(N,2)

Remember to increment the existing value in the ResultTable not to just set the new value.

That's it, Your ResultsArray will now hold all the scores, which can easily be parsed to a table or a grid.

          Tm1   Tm2  Tm3  Tm4
     Tm1   0     0    0    0
     Tm2   0     0    0    0
     Tm3   0     0    0    1
     Tm4   0     0    1    0

Note: The ResultTable doesn't hold the names of the teams but are in the same order going from left -> right and Top to Bottom as the order of your TeamsList.

I hope you can interpret this into code.

Kind Regards,
Jay