arrays - Accumulating data based on two criteria

Question

I am trying to write a VB.NET program that reads data from a file and does a count for each column as shown in the format below and also writes to an output file.

I am able to do the count but I am unable to write the output per restaurant and day.

From what I have, I can only write the total sum from the array index.

Here is the code I have so far:

Dim IntSubjectArray(23) As String

OpenFileDialog1.ShowDialog()
strInputPath = OpenFileDialog1.FileName
FileOpen(IntInputFileName, strInputPath, OpenMode.Input)
Do While Not EOF(IntInputFileName)
    Dim StrReadLine As String = LineInput(IntInputFileName)
    Dim StrSplitRecord() As String = Split(StrReadLine, ",")
    IntRestaurant = StrSplitRecord(0)
    IntDay = StrSplitRecord(1)
    Meal1 = StrSplitRecord(2)
    Meal2 = StrSplitRecord(3)
    Meal3 = StrSplitRecord(4)
    If SaveDay <> IntDay Then
        IntMealArray(meal1) += 1
        IntMealArray(meal2) += 1
        IntMealArray(meal3) += 1
        SaveDay = IntDay
        SaveDay = 0
    End If
    savetown = IntExamTown
Loop
Call WriteOutputArray()
FileClose(IntInputFileName)
MessageBox.Show("File written to specified location")

Public Sub WriteOutputArray()
    IntOutputFileName = FreeFile()
    For Each Array As String In IntMealArray
        FileOpen(IntOutputFileName, "C:\Users\ireport\foodCount.txt", OpenMode.Append)
        WriteLine(IntOutputFileName, IntMealArray(Array))
        FileClose(IntOutputFileName)
    Next
End Sub

File format is

001,1,5,6,21
001,1,5,6,21
001,1,5,6,21
001,1,10,12,18
001,2,5,6,19
001,2,8,9,19
001,2,6,19,21
001,2,5,6,21
001,3,7,12,18
001,3,8,12,19
001,3,7,12,18
040,4,7,12,18
040,4,7,12,18
040,4,7,12,18
040,4,9,12,19

Key:
The format is 001 is restaurant 1, then day 1, then foods eaten by a particular customer (there are to 23 different kinds of food), with each kind of meal represented by a code 1 to 23 as in the file.

Expected output is count of food eaten in each resturant in each day by a customer e.g.:

Rest day Rice   Beans Yam  Meat  Snack coke Burger Meal8 Meal9  Meal10  M11  M12
001   1    0     0     0    0     3      3     0     0     0      1      0    1
001   2    0     0     0    0     2      3     0     1     1      0      0    0
001   3    0     0     0    0     0      0     2     1     0      0      0    3
040   4    0     0     0    0     0      0     3     0     1      0      0    4

标签： arraysvb.net

Answer 1

首先，您需要将数据转换为某种格式，这样可以更容易地在代码中查看哪个部分是哪个部分。一种简单的方法是创建一个具有有意义名称的属性的类。

然后您可以按餐厅对数据进行分组，对于每个餐厅，您可以对每个日期的数据进行分组。

由于输出在食物名称宽度的列中，因此在格式化输出时需要考虑这些名称。

为简单起见，我创建了一个控制台应用程序而不是 Windows 窗体应用程序。此外，如果我这样做不仅仅是为了概念验证，我会将其拆分为更多方法。

Imports System.IO
Imports System.Text

Module Module1

    Dim Foods As Dictionary(Of String, String)

    Class Datum
        Property Restaurant As String
        Property Day As Integer
        Property FoodCodes As List(Of String)

        Public Overrides Function ToString() As String
            ' Useful for debugging.
            Return $"{Restaurant} {Day} " & String.Join(",", FoodCodes)
        End Function

    End Class

    Sub LoadFoods()
        ' Generate some food names. The first food name has a code of "1".
        Foods = New Dictionary(Of String, String)
        Dim names = {"Rice", "Beans", "Banana", "Meat", "Snacks", "Potato", "Spinach",
            "Fish", "Aubergine", "Peas", "Egg", "Chicken", "Cheese", "Onion",
            "Carrots", "Brocolli", "Asparagus", "Garlic", "Cabbage", "Coconut", "Yam",
            "Naan", "Lentils"}

        For i = 1 To names.Count
            Foods.Add(i.ToString(), names(i - 1))
        Next

    End Sub

    Sub Main()
        LoadFoods()

        Dim src = "C:\temp\FoodRecords.txt"
        Dim dest = "C:\temp\meals.txt"

        Dim data As New List(Of Datum)

        For Each line In File.ReadLines(src)
            Dim parts = line.Split({","c})
            If parts.Count = 5 Then
                Dim d As New Datum With {.Restaurant = parts(0),
                                         .Day = Integer.Parse(parts(1)),
                                         .FoodCodes = parts.Skip(2).OrderBy(Function(s) s).ToList()}
                data.Add(d)

            End If
        Next

        ' Prepare information on the widths of the columns to be output...
        Dim colWidths As New List(Of Integer)
        colWidths.Add(-("Restaurant".Length))
        colWidths.Add(-("Day".Length))

        For Each food In Foods
            colWidths.Add(food.Value.Length)
        Next

        ' Group the data by restaurant...
        Dim restaurantData = From d In data
                             Group By RestCode = d.Restaurant
                             Into RestData = Group


        Using sw As New StreamWriter(dest)
            sw.Write("Restaurant Day ")
            sw.WriteLine(String.Join(" ", Foods.Select(Function(f) f.Value)))

            For Each x In restaurantData
                'Console.WriteLine(x.RestCode & " " & String.Join(",", x.RestData))

                ' Get each day of data for this restaurant
                Dim restaurantDay = From y In x.RestData
                                    Group By Day = y.Day
                                    Into DayData = Group


                For Each rd In restaurantDay

                    ' Count the occurrences of food codes for this day...
                    Dim dayFoodCounts As New Dictionary(Of String, Integer)

                    For Each fd In rd.DayData
                        For Each fc In fd.FoodCodes
                            If dayFoodCounts.ContainsKey(fc) Then
                                dayFoodCounts(fc) += 1
                            Else
                                dayFoodCounts.Add(fc, 1)
                            End If
                        Next

                    Next

                    ' Generate the first two columns
                    Dim sb As New StringBuilder()
                    Dim fmt = "{0," & colWidths(0) & "}"
                    sb.AppendFormat(fmt, x.RestCode)
                    sb.Append(" ")
                    fmt = "{0," & colWidths(1) & "}"
                    sb.AppendFormat(fmt, rd.Day)
                    sb.Append(" ")

                    ' Generate the columns with food consumption counts
                    Dim n = 0
                    For Each kvp In Foods
                        If dayFoodCounts.ContainsKey(kvp.Key) Then
                            sb.Append(String.Format("{0," & colWidths(n + 2) & "}", dayFoodCounts(kvp.Key)) & " ")
                        Else
                            ' no count for this food item, so fill it with spaces
                            sb.Append(New String(" "c, colWidths(n + 2) + 1))
                        End If

                        n += 1
                    Next

                    sw.WriteLine(sb.ToString())

                Next

            Next

        End Using

        Console.WriteLine("Done.")
        Console.ReadLine()

    End Sub

End Module

给定问题中的示例数据，上面的代码会生成一个包含以下内容的文件：

Restaurant Day Rice Beans Banana Meat Snacks Potato Spinach Fish Aubergine Peas Egg Chicken Cheese Onion Carrots Brocolli Asparagus Garlic Cabbage Coconut Yam Naan Lentils
001        1                               3      3                           1           1                                              1                   3              
001        2                               2      3            1         1                                                                       3           2              
001        3                                              2    1                          3                                              2       1                          
040        4                                              3              1                4                                              3       1