[Dynamo+=Python] Les DataTables (Chapitre 2

Comment Filtrer les DataTables de l'API Microsoft

#DataTable #RevitAPI #DynamoBIM #Datas

if this article is not in your language, use the Google Translate widget ⬈ (bottom of page for Mobile version ⬇)

Cet article fait suite à l'article précédent Les DataTables (Chapitre 1 - Construction)

Ici, nous verrons comment filtrer un DataTable suivant des differentes méthodes.

Bien qu'à ce jour les méthodes d'extension ne soient compatibles avec Python.Net à ce jour, un effort supplémentaire sera fait dans cet article pour traiter les solutions avec IronPython et Python.Net quand cela est possible.

Le code complet figure en fin d'article

Création de la DataTable de Base

À partir d'une maquette numérique de type Bâtiment de Logements collectif, créons un DataTable contenant des données des "Espaces" puis ajoutons-le à un DataSet

Avant l'ajout au DataSet, nous en profitons pour effacer les lignes incomplètes (cellules vides)


## 1/ Get all Spaces ##
		
all_spaces = FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_MEPSpaces).WhereElementIsNotElementType().ToElements()
apartment_nb_level = list(set([(e.LookupParameter("Num_Appt_ou_Chambre").AsString(), e.Level.Name) for e in all_spaces]))
apartment_nb_level = [tple for tple in apartment_nb_level if tple[0] is not None]

## 2/ Create a new DataSet
dtSet = DataSet()

# 3/ Create DataTable for Spaces and add to DataSet
dt = DataTable("SPACES")
dt.Columns.Add('Name', System.String)
dt.Columns.Add('Number', System.String)
dt.Columns.Add('NumberAppt', System.String)
dt.Columns.Add('SpaceLevel', System.String)
dt.Columns.Add('SpaceLevelElevation', System.Double)
dt.Columns.Add('DB_Element', DB.Element)
for space in all_spaces:
    if space.Number.isdigit():
        mumberAppt = space.LookupParameter("Num_Appt_ou_Chambre").AsString()
        if not System.String.IsNullOrEmpty(mumberAppt):
            dt.Rows.Add(get_Name(space), space.Number , mumberAppt, space.Level.Name, space.Level.ProjectElevation, space)

# remove empty rows
filterFunc = System.Func[DataRow, System.Boolean](lambda row : all(str(c).strip() != "" for c in row.ItemArray))
iterDataRows = Enumerable.Where[DataRow]([x for x in dt.Rows], filterFunc )
dt = DataRows_to_DataTable(iterDataRows, "SPACES")
#
# Add dt to the DataSet.
dtSet.Tables.Add(dt)

Dans ce même DataSet ajoutons un nouveau DataTable avec seulement les Numéros des Logements et leur niveau correspondant.
Pour ce faire, nous utilisons ici la méthode GroupBy (utilisé via la méthode d'extension avec IronPython, et via la méthode statique avec Python.Net)


#
if pyEngineName == "ironpython":
    dt_Lgts = DataTable("APPTS")
    for col in dt.Columns:
        dt_Lgts.Columns.Add(col.ColumnName, col.DataType)
        
    List[DataRow](dt.AsEnumerable() \
                .GroupBy(lambda r : r["NumberAppt"]) \
                .Select(lambda g : g.OrderBy( lambda r : r["SpaceLevelElevation"]).First())) \
                .CopyToDataTable(dt_Lgts, LoadOption.Upsert)
else:
    # create DataTable with only appartement number
    # method with PythonNet with static method Linq (no support Linq extension)
    enumeralbleRows = List[DataRow]([x for x in dt.Rows])
    # Groupy by appartement number
    filterFuncA = System.Func[DataRow, str](lambda r : r["NumberAppt"])
    dtgr = Enumerable.GroupBy[DataRow, str](enumeralbleRows, filterFuncA)
    # for each group get the first row
    filterFuncB = System.Func[System.Object, System.Object](lambda g : sorted(g, key = lambda r : r["SpaceLevelElevation"])[0])
    dtrq = Enumerable.Select[System.Object, System.Object](dtgr, filterFuncB)
    # convert in new DataTable
    dt_Lgts = DataRows_to_DataTable(dtrq, "APPTS")
#
# remove some columns 
dt_Lgts = dt_Lgts.DefaultView.ToTable(True, "NumberAppt", "SpaceLevel")
# set Column as PrimaryKey
keys = System.Array[DataColumn]([DataColumn()])
keys[0] = dt_Lgts.Columns["NumberAppt"]
dt_Lgts.PrimaryKey = keys
#
# Add dt to the DataSet.
dtSet.Tables.Add(dt_Lgts)

Le filtrage

Au sein de la première table, nous souhaitons rechercher au travers de diverses méthodes toutes les lignes de données qui concernent les appartements "B1.08 - T2" et / ou "C1.05 - T2"

Exemple 1 avec la méthode SELECT

La méthode DataTable.Select() est utilisée (à l'aide d'opérateurs logique) pour filtrer les données dans un objet DataTable en fonction de critères spécifiques et renvoyer un tableau de lignes qui correspondent à ces critères.

Vous trouverez une documentation plus précise sur cette page
https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression

https://learn.microsoft.com/en-us/dotnet/fundamentals/runtime-libraries/system-data-datacolumn-expression


def DataRows_to_DataTable(lstRows, nameDataTable=""):
    """
    convert enumrable DataRows to DataTable
    with IronPython you can use CopyToDataTable() method extension directly
    lstRows : Enumerable[DataRow]
    nameDataTable : String
    return DataTable
    """
    datatable = DataTable(nameDataTable)
    [datatable.Columns.Add(col.ColumnName, col.DataType) for col in list(lstRows)[0].Table.Columns]
    [datatable.ImportRow(row) for row in lstRows]
    return datatable

searchApptA = "B1.08 - T2"
searchApptB = "C1.05 - T2"
#
# Example : select all Space DataRow with number appartement "B1.08 - T2"
strDataExpression = "[NumberAppt] = '{}'".format(searchApptA)
rows_this_Appt = dt.Select(strDataExpression)
# Example : select the first DataRow Space Rows with number appartement "B1.08 - T2"
first_row_this_Appt = Enumerable.FirstOrDefault[System.Object](dt.Select(strDataExpression))
# Example : select all Space DataRow with number appartement "B1.08 - T2" or "C1.05 - T2"
strDataExpression = "[NumberAppt] IN ('{}','{}')".format(searchApptA, searchApptB)
rows_this_Appt = dt.Select(strDataExpression)
# Example : select the first DataRow Space Rows with number appartement "B1.08 - T2" and Number Space '115'
strDataExpression = "[NumberAppt] = '{}' AND [Number] = '{}'".format(searchApptA, 115)
rows_this_Appt = dt.Select(strDataExpression)
#
dt_space = DataRows_to_DataTable(rows_this_Appt, searchApptA)

Exemple 2 avec les méthodes d'extension LINQ et DataTableExtensions (compatible seulement avec IronPython)


if pyEngineName == "ironpython":
    dt_space = List[DataRow](dt.AsEnumerable().Where(lambda row : row["NumberAppt"] == searchApptB)).CopyToDataTable()
    ShowDataTable(dt_space)
    # Example : select the first DataRow Space Rows with number appartement "B1.08 - T2" and Number Space '115'
    strDataExpression = "[NumberAppt] = '{}' AND [Number] = '{}'".format(searchApptA, 115)
    dt_space = dt.Select(strDataExpression).CopyToDataTable()

Exemple 3 avec une relation entre DataTables

La classe DataRelation dans le framework .NET permet d'établir des relations entre deux objets DataTable, en définissant des clés de relation entre les colonnes, notamment la clé primaire d'une table parente et la clé étrangère d'une table enfant.

Elle facilite la modélisation des structures de données, la navigation entre des données liées, le maintien de la cohérence des données dans des applications .NET, et permet de créer des hiérarchies de données.

En outre, elle joue un rôle essentiel en garantissant la liaison entre ces clés primaires et étrangères, simplifiant ainsi la gestion de données tabulaires complexes.

On établit une relation entre les 2 DataTables en définissant le Numéro de Lgts comme clé commune.
Dans le DataTable "APPTS" nous recherchons la ligne correspondante à l'appartement "B1.08 - T2" grâce à la clé primaire.
Grâce à la relation, nous pouvons ainsi obtenir les lignes de la DataTable enfant "SPACES"


def DataRows_to_DataTable(lstRows, nameDataTable=""):
    """
    convert enumrable DataRows to DataTable
    with IronPython you can use CopyToDataTable() method extension directly
    lstRows : Enumerable[DataRow]
    nameDataTable : String
    return DataTable
    """
    datatable = DataTable(nameDataTable)
    [datatable.Columns.Add(col.ColumnName, col.DataType) for col in list(lstRows)[0].Table.Columns]
    [datatable.ImportRow(row) for row in lstRows]
    return datatable
    
dtRelation = DataRelation("ApptRelation ", dtSet.Tables["APPTS"].Columns["NumberAppt"], dtSet.Tables["SPACES"].Columns["NumberAppt"])
dtSet.Tables["SPACES"].ParentRelations.Add(dtRelation)
# get one APPT by Name
an_apptRow = dtSet.Tables["APPTS"].Rows.Find(searchApptA)

rows_this_Appt = an_apptRow.GetChildRows(dtRelation)
dt_space = DataRows_to_DataTable(rows_this_Appt, searchApptA)

Bonus :

on peut facilement utiliser les données d'une DataTable vers une autre et ajouter/calculer des Colonnes grâce à des simples expressions


# add Number of Rooms per Apartment 
dtSet.Tables["APPTS"].Columns.Add('Number_Rooms', System.Int32)
dtSet.Tables["APPTS"].Columns["Number_Rooms"].Expression = "COUNT(Child.NumberAppt)"  
# add Total Area per Apartment 
dtSet.Tables["APPTS"].Columns.Add('Total_Area', System.Double)
dtSet.Tables["APPTS"].Columns["Total_Area"].Expression = "SUM(Child.Area)"

DataTable par MoonValley. AI

  
import clr
import sys
import System
pyEngineName = sys.implementation.name 
#
clr.AddReference('ProtoGeometry')
from Autodesk.DesignScript.Geometry import *
import Autodesk.DesignScript.Geometry as DS

#import Revit API
clr.AddReference('RevitAPI')
import Autodesk
from Autodesk.Revit.DB import *
import Autodesk.Revit.DB as DB

#import net library
from System import Array
from System.Collections.Generic import List, IList, Dictionary, IEnumerable


clr.AddReference('System.Data')
from System.Data import *

#import transactionManager and DocumentManager (RevitServices is specific to Dynamo)
clr.AddReference('RevitServices')
import RevitServices
from RevitServices.Persistence import DocumentManager
from RevitServices.Transactions import TransactionManager

doc = DocumentManager.Instance.CurrentDBDocument

clr.AddReference("System.Core")
import System.Linq
from System.Linq import *

if pyEngineName == "ironpython":
    clr.ImportExtensions(System.Linq)
    clr.AddReference('System.Data.DataSetExtensions')
    clr.ImportExtensions(System.Data.DataTableExtensions)

clr.AddReference('System.Drawing')
clr.AddReference('System.Windows.Forms')
import System.Drawing
import System.Windows.Forms

from System.Drawing import *
from System.Windows.Forms import *

class ShowDataTable(Form):
    """A Windows Form for displaying a DataTable"""
    def __init__(self, datatable, title=""):
        """
        Initialize the FormDataTable instance.
        
        Args:
            datatable (DataTable): The DataTable to display.
            title (str, optional): The title of the form. Defaults to an empty string.
        """
        self.datatable = datatable
        self.title = title
        self.InitializeComponent()
        self.ShowDialog()

    def InitializeComponent(self):
        """Initialize the components of the FormDataTable"""
        self._dataGridView1 = System.Windows.Forms.DataGridView()
        if pyEngineName == "ironpython":
            self._dataGridView1.BeginInit()
        else:
            System.ComponentModel.ISupportInitialize(self._dataGridView1).BeginInit()
        # Initialize DataGridView
        self.SuspendLayout()
        self._dataGridView1.Anchor = System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left | System.Windows.Forms.AnchorStyles.Right
        self._dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        self._dataGridView1.Location = System.Drawing.Point(12, 33)
        self._dataGridView1.Name = "dataGridView1"
        self._dataGridView1.DataSource = self.datatable
        self._dataGridView1.Size = System.Drawing.Size(523, 467)
        self._dataGridView1.TabIndex = 0
        # Initialize Form
        self.ClientSize = System.Drawing.Size(547, 512)
        self.Controls.Add(self._dataGridView1)
        self.Name = "Form27"
        self.Text = self.title
        if pyEngineName == "ironpython":
            self._dataGridView1.EndInit()
        else:
            System.ComponentModel.ISupportInitialize(self._dataGridView1).EndInit()
        self.ResumeLayout(False)

def get_Name(obj, type = None, i = 0):	
    if isinstance(obj, Autodesk.Revit.DB.Element) and i < 10:
        type = type if type is not None else obj.GetType()
        if type.ToString() == 'Autodesk.Revit.DB.Element': 
            return type.GetProperty('Name').GetValue(obj) 
        else:
            return get_Name(obj, type.BaseType, i + 1)
            
def DataRows_to_DataTable(lstRows, nameDataTable=""):
    """
    convert enumrable DataRows to DataTable
    with IronPython you can use CopyToDataTable() method extension directly
    lstRows : Enumerable[DataRow]
    nameDataTable : String
    return DataTable
    """
    datatable = DataTable(nameDataTable)
    [datatable.Columns.Add(col.ColumnName, col.DataType) for col in list(lstRows)[0].Table.Columns]
    [datatable.ImportRow(row) for row in lstRows]
    return datatable
    
#
#### MAIN START HERE ####
## 1/ Get all Spaces ##
		
all_spaces = FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_MEPSpaces).WhereElementIsNotElementType().ToElements()
apartment_nb_level = list(set([(e.LookupParameter("Num_Appt_ou_Chambre").AsString(), e.Level.Name) for e in all_spaces]))
apartment_nb_level = [tple for tple in apartment_nb_level if tple[0] is not None]

## 2/ Create a new DataSet
dtSet = DataSet()

# 3/ Create DataTable for Spaces and add to DataSet
dt = DataTable("SPACES")
dt.Columns.Add('Name', System.String)
dt.Columns.Add('Number', System.String)
dt.Columns.Add('NumberAppt', System.String)
dt.Columns.Add('Area', System.Double)
dt.Columns.Add('SpaceLevel', System.String)
dt.Columns.Add('SpaceLevelElevation', System.Double)
dt.Columns.Add('DB_Element', DB.Element)
for space in all_spaces:
    if space.Number.isdigit():
        mumberAppt = space.LookupParameter("Num_Appt_ou_Chambre").AsString()
        if not System.String.IsNullOrEmpty(mumberAppt):
            dt.Rows.Add(get_Name(space), 
                        space.Number , 
                        mumberAppt, 
                        space.Area * 0.09290304, # convert to square meter
                        space.Level.Name, 
                        space.Level.ProjectElevation, 
                        space)

# remove empty rows
filterFunc = System.Func[DataRow, System.Boolean](lambda row : all(str(c).strip() != "" for c in row.ItemArray))
iterDataRows = Enumerable.Where[DataRow]([x for x in dt.Rows], filterFunc )
dt = DataRows_to_DataTable(iterDataRows, "SPACES")
#
ShowDataTable(dt)
# Add dt to the DataSet.
dtSet.Tables.Add(dt)
        

#
if pyEngineName == "ironpython":
    dt_Lgts = DataTable("APPTS")
    for col in dt.Columns:
        dt_Lgts.Columns.Add(col.ColumnName, col.DataType)
        
    List[DataRow](dt.AsEnumerable() \
                .GroupBy(lambda r : r["NumberAppt"]) \
                .Select(lambda g : g.OrderBy( lambda r : r["SpaceLevelElevation"]).First())) \
                .CopyToDataTable(dt_Lgts, LoadOption.Upsert)
else:
    # create DataTable with only appartement number
    # method with PythonNet with static method Linq (no support Linq extension)
    enumeralbleRows = List[DataRow]([x for x in dt.Rows])
    # Groupy by appartement number
    filterFuncA = System.Func[DataRow, str](lambda r : r["NumberAppt"])
    dtgr = Enumerable.GroupBy[DataRow, str](enumeralbleRows, filterFuncA)
    # for each group get the first row
    filterFuncB = System.Func[System.Object, System.Object](lambda g : list(g)[0])
    dtrq = Enumerable.Select[System.Object, System.Object](dtgr, filterFuncB)
    # convert in new DataTable
    dt_Lgts = DataRows_to_DataTable(dtrq, "APPTS")
#
# remove some columns 
dt_Lgts = dt_Lgts.DefaultView.ToTable(True, "NumberAppt", "SpaceLevel")
# set Column as PrimaryKey
keys = System.Array[DataColumn]([DataColumn()])
keys[0] = dt_Lgts.Columns["NumberAppt"]
dt_Lgts.PrimaryKey = keys
#
ShowDataTable(dt_Lgts)
#
# Add dt to the DataSet.
dtSet.Tables.Add(dt_Lgts)
# EXERCICE GET ALL SPACE DATA
searchApptA = "B1.08 - T2"
searchApptB = "C1.05 - T2"
#
# ---> METHOD 1 WITH SELECT (like SQL) (PYTHONNET OR IPY2/3)
# https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=net-7.0
# Example : select all Space DataRow with number appartement "B1.08 - T2"
strDataExpression = "[NumberAppt] = '{}'".format(searchApptA)
rows_this_Appt = dt.Select(strDataExpression)
# Example : select the first DataRow Space Rows with number appartement "B1.08 - T2"
first_row_this_Appt = Enumerable.FirstOrDefault[System.Object](dt.Select(strDataExpression))
# Example : select all Space DataRow with number appartement "B1.08 - T2" or "C1.05 - T2"
strDataExpression = "[NumberAppt] IN ('{}','{}')".format(searchApptA, searchApptB)
rows_this_Appt = dt.Select(strDataExpression)
# Example : select the first DataRow Space Rows with number appartement "B1.08 - T2" and Number Space '115'
strDataExpression = "[NumberAppt] = '{}' AND [Number] = '{}'".format(searchApptA, 115)
rows_this_Appt = dt.Select(strDataExpression)
#
dt_space = DataRows_to_DataTable(rows_this_Appt, searchApptA)
#System.Data.DataTable
ShowDataTable(dt_space)
#
# ---> METHOD 2 WITH LINQ EXTENSION ONLY IPY2 OR IPY3
#
if pyEngineName == "ironpython":
    dt_space = List[DataRow](dt.AsEnumerable().Where(lambda row : row["NumberAppt"] == searchApptB)).CopyToDataTable()
    ShowDataTable(dt_space)
    # Example : select the first DataRow Space Rows with number appartement "B1.08 - T2" and Number Space '115'
    strDataExpression = "[NumberAppt] = '{}' AND [Number] = '{}'".format(searchApptA, 115)
    dt_space = dt.Select(strDataExpression).CopyToDataTable()
    ShowDataTable(dt_space)
#
# ---> METHOD 3 WITH DATATABLE RELATION
#
dtRelation = DataRelation("ApptRelation ", dtSet.Tables["APPTS"].Columns["NumberAppt"], dtSet.Tables["SPACES"].Columns["NumberAppt"])
dtSet.Tables["SPACES"].ParentRelations.Add(dtRelation)
# get one APPT by Name
an_apptRow = dtSet.Tables["APPTS"].Rows.Find(searchApptA)

rows_this_Appt = an_apptRow.GetChildRows(dtRelation)

dt_space = DataRows_to_DataTable(rows_this_Appt, searchApptA)
ShowDataTable(dt_space)

# BONUS : COMPLETE THE APPTS DateTable with 2 columuns using the DataTable Relation
# add Number of Rooms per Apartment 
dtSet.Tables["APPTS"].Columns.Add('Number_Rooms', System.Int32)
dtSet.Tables["APPTS"].Columns["Number_Rooms"].Expression = "COUNT(Child.NumberAppt)"  
# add Total Area per Apartment 
dtSet.Tables["APPTS"].Columns.Add('Total_Area', System.Double)
dtSet.Tables["APPTS"].Columns["Total_Area"].Expression = "SUM(Child.Area)"  
ShowDataTable(dtSet.Tables["APPTS"])

OUT = dt.Rows.Count, dt.Columns.Count,an_apptRow, rows_this_Appt

VoltaDynaBim

22 oct. 2023