B4A Library [B4X][LOA] ListOfArrays - lightweight, powerful and flexible collection

ListOfArrays (LOA) is a simple list where each item is an array of objects, and each array represents a row. As LOA stores tabular data, all rows have the same length.
LOA is lightweight and a list can be converted to a LOA and vice versa, with (almost) no overhead.
It is loosely inspired by Python DataFrames.

It packs all kinds of useful features, including:

• Stores arbitrary object types, including UI elements and class instances.
• Lightweight conversion between List and ListOfArrays with almost no overhead.
• Useful for datasets ranging from a few rows to hundreds of thousands of rows.
• Optional header row, allowing columns to be accessed by name or ordinal.
• Fast row, column, and cell access.
• Add, insert, replace, and delete rows and columns.
• Extract, reorder, duplicate, merge, and reshape columns and rows in multiple ways.
• Sorting by a column.
• GroupBy support for splitting rows by column values.
• Row index creation with duplicate-handling options.
• Row lookup helpers for finding rows by column value.
• Conversion to and from string arrays, mainly useful for CSV.
• Shallow and deep cloning.
• Custom iterator designed for filtering and collecting matching row indices.
• Useful string representation.
• Cross platform.

Current version is considered a beta version.

Updates:
v0.93 - Column matching is now case insensitive. New methods: ToListOfMaps and LOAUtils.CreateFromListOfMaps.
v0.92 - Better support for Null values and new methods: LOASet.GetValueDefault, LOA.GetRowIndicesNonNull and LOA.Clear.
v0.91 - API changes in LOASet. New methods: OrRowsSelections, AndRowsSelections and GetRowIndicesByValue
Examples were also updated.

This thread is locked to keep it concise. Please start a new thread for questions or feedback.

 

[Erel]

First example:

Dim table As ListOfArrays = LOAUtils.CreateEmpty((Array("Animal type", "Name", "Age", "Weight")))
table.AddRow(Array("Dog", "Buddy", 6, 18.5))
table.AddRow(Array("Cat", "Luna", 2, 4.1))
table.AddRow(Array("Dog", "Max", 5, 22.0))
table.AddRow(Array("Cat", "Milo", 1, 3.7))
table.AddRow(Array("Rabbit", "Coco", 3, 2.4))
table.AddRow(Array("Dog", "Rocky", 8, 25.6))
table.AddRow(Array("Parrot", "Kiwi", 7, 0.9))
table.AddRow(Array("Hamster", "Nibbles", 1, 0.2))
table.AddRow(Array("Goat", "Daisy", 4, 35.0))
table.AddRow(Array("Cat", "Shadow", 9, 5.2))
Log("******  First 5 rows:")
Log(table.ToString(5))
Log("***** First name:")
Log(table.GetValue(0, "Name"))
'or
Log(table.GetValue(0, 1)) '1 is the column ordinal. Better to use the header.
Log("***** Milo:")
Dim row() As Object = table.GetRow(table.GetRowIndexByValue("Name", "Milo"))
Log("Milo age: " & row(table.ColumnIndexToOrdinal("Age")))
Log("***** Sort by age:")
table.Sort("Age", True)
Log(table.ToString(5))
Log("***** Get cats:")
Dim loa As ListOfArrays = table.GetRowsByValue("Animal type", "Cat")
Log(loa.ToString(0))
Log("***** Group by animal type:")
Dim om As B4XOrderedMap = table.GroupBy("Animal type")
Log("Keys: " & om.Keys)
For Each loa As ListOfArrays In om.Values
    Log(loa.ToString(2))
    Log("***")
Next
Log("***** Filter by weight > 10")
Dim ls As LOASet = table.CreateLOASet
Do While ls.NextRow
    'select all rows where weight > 10
    If ls.GetValue("Weight") > 10 Then ls.CollectIndex
Loop
Dim OverweightIndices As List = ls.CollectedValues
Dim loa As ListOfArrays = table.GetRows(OverweightIndices)
Log(loa.ToString(2))
Log("***** Filter by weight <= 10 using negation")
Dim loa As ListOfArrays = table.GetRows(table.NegateRowsSelection(OverweightIndices))
Log(loa.ToString(2))
Log("***** Filter by weight > 10 AND animal type = dog")
Dim DogsIndices As List = table.GetRowIndicesByValue("Animal type", "Dog")
Dim loa As ListOfArrays = table.GetRows(table.AndRowsSelections(OverweightIndices, DogsIndices))
Log(loa.ToString(2))

Output tables:

First 5 rows:

Animal type	Name	Age	Weight
Dog	Buddy	6	18.5
Cat	Luna	2	4.1
Dog	Max	5	22.0
Cat	Milo	1	3.7
Rabbit	Coco	3	2.4

Sort by age:

Animal type	Name	Age	Weight
Cat	Milo	1	3.7
Hamster	Nibbles	1	0.2
Cat	Luna	2	4.1
Rabbit	Coco	3	2.4
Goat	Daisy	4	35.0

Get cats:

Animal type	Name	Age	Weight
Cat	Milo	1	3.7
Cat	Luna	2	4.1
Cat	Shadow	9	5.2

Group by:

Animal type	Name	Age	Weight
Cat	Milo	1	3.7
Cat	Luna	2	4.1

Animal type	Name	Age	Weight
Hamster	Nibbles	1	0.2

...

Filter by weight > 10:

Animal type	Name	Age	Weight
Goat	Daisy	4	35.0
Dog	Max	5	22.0

Filter by weight > 10 AND animal type = dog

Animal type	Name	Age	Weight
Dog	Max	5	22.0
Dog	Buddy	6	18.5

 

[Erel]

Example of using LOA to manage UI elements.

A table with buttons and properties is created:

ButtonsTable = LOAUtils.CreateEmpty(Array("button", "value", "color"))
For i = 0 To 9
    Dim value As Int = Rnd(1, 20)
    Dim clr As Int = Colors1.Get(i Mod Colors1.Size)
    Dim b As Button
    #if B4i
    b.InitializeCustom("b", xui.Color_Black, xui.Color_White)
    #else
    b.Initialize("b")
    #End If
    b.Text = value
    b.As(B4XView).Color = clr
    ButtonsTable.AddRow(Array(b, value, clr))
Next

Showing the list of buttons is done with:

Private Sub ShowButtons (loa As ListOfArrays)
    Panel1.RemoveAllViews
    Dim ls As LOASet = loa.CreateLOASet
    Do While ls.NextRow
        Panel1.AddView(ls.GetValue("button"), 20dip, 10dip + 50dip * ls.CurrentIndex, 80dip, 40dip)
    Loop
End Sub

We can then filter the buttons easily:

Private Sub btnShowGreen_Click
    Dim loa As ListOfArrays = ButtonsTable.GetRowsByValue("color", xui.Color_Green)
    ShowButtons(loa)
End Sub

'Combining selections
Private Sub btnBlackWhite_Click
    Dim Black As List = ButtonsTable.GetRowIndicesByValue("color", xui.Color_Black)
    Dim White As List = ButtonsTable.GetRowIndicesByValue("color", xui.Color_White)
    ShowButtons(ButtonsTable.GetRows(ButtonsTable.OrRowsSelections(Black, White)))
End Sub

More examples will be added.

Please start a new thread for any question.

 

[Erel]

v0.91 - API changes in LOASet. New methods: OrRowsSelections, AndRowsSelections and GetRowIndicesByValue
Examples were also updated.

 

[Erel]

CSV Example:

StringUtils.LoadCSV returns a list with arrays of strings. We create a LOA based on this list and then call LOA.ConvertTypes to convert it to a list of arrays based on the set types.

'Column types: Int, Double, Long, Other
table.ConvertTypes(Array("Population"), Array("Pop. Density"), Null, Array("Country", "Region"))

We then add another column based on the population and density:

Dim ls As LOASet = table.CreateLOASet
Do While ls.NextRow
    Dim PopDensity As Double = ls.GetValue("Pop. Density")
    Dim Population As Int = ls.GetValue("Population")
    ls.CollectValue(IIf(PopDensity > 0, Population / PopDensity, 0))
Loop
table.AddColumn("Area", ls.CollectedValues)

And save the table back to CSV using table.ToListOfStrings.

 

[Erel]

v0.92 improved the support for Null values. Some examples:

This is our data:

Dim table As ListOfArrays = LOAUtils.CreateEmpty((Array("Animal type", "Name", "Age", "Weight")))
table.AddRow(Array("Dog", "Buddy", 6, 18.5))
table.AddRow(Array("Cat", "Luna", 2, Null))
table.AddRow(Array("Dog", "Max", Null, 22.0))
table.AddRow(Array("Cat", "Milo", Null, Null))
table.AddRow(Array("Parrot", "Kiwi", 7, 0.9))

Getting all rows without nulls in the "age" column:

Dim TableNonNull As ListOfArrays = table.GetRows(table.GetRowIndicesNonNull("Age"))

Iterating over values and replacing Nulls with a default value:

Dim ls As LOASet = table.CreateLOASet
Do While ls.NextRow
    Dim weight As Double = ls.GetValueDefault("Weight", 0)
    '...
Loop

And last example - sorting based on column with nulls. We need to sort the non-null rows and then combine them together:

Public Sub SortWithNulls(loa As ListOfArrays, Column As Object, Ascending As Boolean, NullsFirst As Boolean)
    Dim NullIndices As List = loa.GetRowIndicesByValue(Column, Null)
    Dim NonNullIndices As List = loa.NegateRowsSelection(NullIndices)
    Dim NullList As ListOfArrays = loa.GetRows(NullIndices)
    Dim NonNullList As ListOfArrays = loa.GetRows(NonNullIndices)
    NonNullList.Sort(Column, Ascending)
    loa.Clear
    If NullsFirst Then
        loa.AddRows(NullList)
        loa.AddRows(NonNullList)
    Else
        loa.AddRows(NonNullList)
        loa.AddRows(NullList)
    End If
End Sub

 

[Erel]

Saving and loading:

Assuming that all values are serializable with B4XSerializator (RandomAccessFile library) then:

Dim ser As B4XSerializator
Dim b() As Byte = ser.ConvertObjectToBytes(table.mInternalArray)
'use File.WriteBytes / ReadBytes
'loading:
Dim NewList As List = ser.ConvertBytesToObject(b)
Dim NewTable As ListOfArrays = LOAUtils.WrapWithHeader(NewList)

Another simple option with json. It requires replacing the lists with arrays:

Dim Json As String = table.mInternalArray.As(JSON).ToCompactString
'use File.WriteString / ReadString
Dim NewTable As ListOfArrays = JsonToLOA(Json)

Private Sub JsonToLOA(JsonString As String) As ListOfArrays
    Dim l1 As List = JsonString.As(JSON).ToList
    For i = 0 To l1.Size - 1
        Dim r As List = l1.Get(i)
        l1.Set(i, LOAUtils.ListToArray(r))
    Next
    Return LOAUtils.WrapWithHeader(l1)
End Sub

 

[Erel]

On the reusing of rows

Objects in B4X are passed by reference, and arrays are objects. By design, LOA reuses the rows arrays whenever possible.

This is our data:

Dim table As ListOfArrays = LOAUtils.CreateEmpty((Array("Id", "Animal type", "Name", "Age", "Weight")))
table.AddRow(Array(1, "Rabbit", "Coco", 3, 2.4))
table.AddRow(Array(2, "Cat", "Luna", 2, 4.1))
table.AddRow(Array(3, "Dog", "Rocky", 8, 25.6))
table.AddRow(Array(4, "Parrot", "Kiwi", 7, 0.9))
table.AddRow(Array(5, "Hamster", "Nibbles", 1, 0.2))
table.AddRow(Array(6, "Goat", "Daisy", 4, 35.0))
table.AddRow(Array(7, "Cat", "Shadow", 9, 5.2))

And we create a new LOA with the cats rows:

Dim cats As ListOfArrays = table.GetRowsByValue("Animal type", "Cat")

Now we update the name of the first cat:

Dim row() As Object = cats.GetRow(0) 'first row
row(1)

Will it affect the cat in the original table? Yes.

Log(table.GetRowsByValue("Id", 2).ToString(0))

Output:

Id    Animal type    Name    Age    Weight (#rows=1, #cols=5)
-----------------------------
2    New name    Luna    2    4.1

If we want to create an independent of cats:

Dim cats As ListOfArrays = table.GetRowsByValue("Animal type", "Cat")
cats = cats.DeepClone

LOA.DeepClone returns a new ListOfArrays with new arrays of rows.

Any method that affects the number of columns, will also result in the creation of new arrays of rows. Example:

cats.AddColumn("ttt", Array("aaa", "bbb"))

Now the cats table no longer shares rows with the original table.

The list of methods that will result in a new set of arrays: DeepClone, AddColumn, Merge, RemoveColumn, ToListOfArrays (creates a new LOA based on selected columns) and ConvertTypes.

 

[Erel]

v0.93 - Column matching is now case insensitive. New methods: ToListOfMaps and LOAUtils.CreateFromListOfMaps.

Dim maps As List = table.ToListOfMaps
Log(maps.As(JSON).ToString) 'json library

Output:
[
{
"Id": 1,
"Animal type": "Rabbit",
"Name": "Coco",
"Age": 3,
"Weight": 2.4
},
{
"Id": 2,
"Animal type": "Cat",
"Name": "Luna",
"Age": 2,
"Weight": 4.1
},
{
"Id": 3,
"Animal type": "Dog",
"Name": "Rocky",
"Age": 8,
"Weight": 25.6
}
]


And back to LOA:

Dim t As ListOfArrays = LOAUtils.CreateFromListOfMaps(maps, Null)

The headers are taken by default from the first row. In B4i, the order inside the map isn't preserved so the columns order will be "random". We can set it explicitly:

Dim t As ListOfArrays = LOAUtils.CreateFromListOfMaps(maps, Array("Id", "Animal type", "Name")) 'and exclude Age and Weight