Interpoler des valeurs pour des enregistrements vides (données manquantes)

**Dermochelys** · 27/08/2020, 17h20

Bonjour,

J'ai une table par ordre chronologique qui contient des données manquantes (un nombre variable et pas toujours manquants consécutivement comme dans l'exemple ici) comme ceci:
Nom : tempfig.png
Affichages : 451
Taille : 35,2 Ko

Nom : tempfig.png
Affichages : 451
Taille : 35,2 Ko

Comme il s'agit de relevés horaires de températures, je me dit que je ne peux pas "simplement" prendre la dernière valeur précédent les manquants + la première valeur suivant les manquants et en faire une moyenne que j'injecterai à la place des valeurs manquantes.

J'ai pensé à prendre les x valeurs précédents les manquants + les x valeurs suivants ceux-ci et d'en faire une moyenne (ce qui serait plus proche de la valeur moyenne réelle pour la période considérée).
Mais ca me renvoie à :
- comment faire pour déterminer facilement combien j'ai de valeurs manquantes consécutives? (je pensais parcourir un recordset et détecter le premier enregistrement sans données, puis compter le nombre de lignes consécutives Y sans données, retourner au début de la zone et prendre les Y enregistrements, aller a la fin de la zone et prendre en compte les Y enregistrements ce qui me donne 2Y données dont je fais la moyenne, que j'utilise pour remplir les valeurs vides)... Rien qu'à me relire, je trouve ça lourd...

- est-ce qu'il n'y a pas une meilleure façon d'interpoler ces valeurs manquantes que de faire une moyenne (vu que c'est des variations des températures heure par heure)? Et si oui, comment faire...

En espérant qu'une bonne âme se penche sur mon cas ;-)
Un grand merci d'avance :-)

**nico84** · 28/08/2020, 10h25

Bonjour,

Une t° heure par heure suit globalement une sinusoide de 24h de période qui se décale chaque jour plus ou moins (et avec des anomalies en fonction de la météo du jour). Une moyenne est donc possible entre 2 heures mais complètement fausse au-delà

Ce n'est pas un problème de programmation mais d'algèbre, il faut poser la question sur un forum de forts en math

**Dermochelys** · 28/08/2020, 13h04

Bonjour Nico84,

Je suis à 100% d'accord avec toi pour la sinusoïde quotidienne :-)

Mais en final, ce que je dois calculer est une température moyenne quotidienne (je fais une moyenne simple des température d'une journée). Ce qui reviens à dire que si j'ai des données qui manquent pour un jour complet, au pire je fais la moyenne du jour avant avec celui qui suit et ça me donne une estimation du jour manquant.

Mais je suis sûr qu'il doit y avoir d'autres méthodes plus précises pour interpoler cela et permettant de gérer des plages de valeurs manquantes de tailles variables....

Un forum de math... bonne idée, si tu as une suggestion d'un bon, je suis preneur :-)

Belle journée à toi^^

**User** · 28/08/2020, 14h10

Bonjour à vous,

C'est vrai qu'il vaut mieux demander à des spécialises du domaine pour choisir la bonne méthode, ici par exemple.

Mais tu peux déjà regarder comment mettre en place ces méthodes dans Access.

L'idée c'est d'ouvrir un recordset basé sur ta table, puis de le transformer en un tableau à 2 dimensions, avec la méthode getRows :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
n = rs.RecordCount ' nombre d'enregistrements
t = rs.GetRows(n) ' on transforme le recordset en un tableau à 2 dimension t(0..rs.Fields.Count-1,0..n-1)

Ensuite, c'est plus facile de travailler sur des tableaux d'indices pour calculer les valeurs d'interpolations.

Maintenant, tu peux peut-être déjà essayer avec une méthode pas trop compliquer à implémenter, comme celle-ci :

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
Public Function InterpolationLagrange(t As Variant, indiceX As Long, indiceY As Long, x As Double) As Double
    ' indiceX : indice du champ date
    ' indiceY : indice du champ température
    Dim p As Double, u As Double
    Dim i As Long, j As Long, n As Long
 
    ' formule = p1*y1 + p2*y2 +...+ pn*yn + ..
 
    n = UBound(t) ' nombre d'enregistrements
 
    ' formule devient = p0*t(indiceY,0) + p1**t(indiceY,1) +...+ p(n-1)*t(indiceY,n)
 
    InterpolationLagrange = 0
 
    For i = 0 To n ' on parcourt les lignes du tableau
        p = 1
 
        If Not IsNull(t(indiceY, i)) Then ' si la valeur de température n'est pas nulle alors on la prend en compte dans le calcul d'intepolation
 
            For j = 0 To n  ' on parcourt les lignes du tableau pour calculer le coefficient
 
                If j <> i And Not IsNull(t(indiceY, j)) Then
                    p = p * (x - t(indiceX, j)) / (t(indiceX, i) - t(indiceX, j))
                End If
 
            Next j
 
            InterpolationLagrange = InterpolationLagrange + (p * t(indiceY, i)) ' on ajoute à la formule (p * t(indiceY, i))
 
        End If
 
    Next i
 
End Function
 
Public Function MajTemperatures(nomTable As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset, x As Double, y As Double, bk As Variant
    Dim t As Variant ' variable utilisée comme tableau à 2 dimension : 1 dimension pour les champs du recordset et une autre pour les enregistrements
    Dim i As Long
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(nomTable, dbOpenDynaset)
 
    rs.MoveLast
    rs.MoveFirst
 
    t = rs.GetRows(rs.RecordCount) ' on transforme le recordset en un tableau à 2 dimension t(0..rs.Fields.Count-1,0..n-1)
    i = 0
    rs.MoveFirst
    Do Until rs.EOF
 
        If IsNull(t(2, i)) Then ' si pas de valeur de température pour cet enregistrement
            x = CDbl(rs!DateTemperature) ' on convertit la date en un numérique : valeur d'abscisse x
            y = InterpolationLagrange(t, 1, 2, x) ' calcul de la température correspondant à la date : valeur d'ordonnée y
            rs.Edit
            rs!ValeurTemperature = y ' on copie la valeur de l'interpolation
            rs.Update
 
        End If
        rs.MoveNext ' on passe à l'enregistrement suivant
        i = i + 1
 
    Loop
 
    rs.Close
    Set rs = Nothing
 
    Set db = Nothing
 
End Function

tu peux tester en faisant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? MajTemperatures("NomTable")

Attention aux indices X et Y qui correspondent aux indices du champ date et du champ température (0...nombre champs -1)

Sans garantie au niveau du choix de la méthode, car il faut bien voir les critères de choix sur le forum algorithme et maths, mais ça peut déjà te donner une idée pour l'implémenter dans Access.

Cdlt,

**Dermochelys** · 28/08/2020, 15h19

Bonjour User,

Un grand merci pour ta contribution!! Je ne connaissais pas du tout l'interpolation Lagrange :-)

Je vais travailler cela sur base de mes données et reviens sur le post quand j'aurai des soucis ou, mieux, quand j'aurai aboutis au résultat :-)

Encore merci! Et un très bon Weekend à vous deux!

**User** · 28/08/2020, 15h22

J'avais fait une erreur dans mon code, j'ai corrigé dans mon dernier message et je l'ai testé

Bon week-end également

**micniv** · 28/08/2020, 16h31

Bravo, User : a priori les résultats sont remarquables et rapides

Micniv

**User** · 28/08/2020, 18h03

Merci à toi micniv, c'est plutôt rassurant

**nico84** · 28/08/2020, 19h20

Lagrange c'est une interpolation polynomiale et non sinusoidale, ça peut être instable s'il manque beaucoup de points

**User** · 28/08/2020, 19h57

Envoyé par nico84

Lagrange c'est une interpolation polynomiale et non sinusoidale, ça peut être instable s'il manque beaucoup de points

Tu dois avoir raison, peut-être faudrait-il faire une interpolation de lagrange sur une période, ou alors approcher les valeurs par une série de Fourier, mais ça me semble plus compliqué à mettre en oeuvre.

Bon week-end à toi

**nico84** · 29/08/2020, 10h06

A une époque j'avais une bibliothèque mathématique qui faisait des analyses de fourier mais c'était en fortran

**User** · 29/08/2020, 17h31

Envoyé par nico84

A une époque j'avais une bibliothèque mathématique qui faisait des analyses de fourier mais c'était en fortran

En effet, ça nous rajeunit pas

De nos jours ça semble être disponible en VBA pour Excel.

Cdlt,

**Dermochelys** · 31/08/2020, 08h29

Bonjour à tous,

Merci pour vos discussions, remarques, corrections ;-) et surtout participation

Du Fortran, j'en ai fait un tout petit peu à l'époque aussi... :-)

Ce weekend, j'ai un peu cherché au delà de Lagrange et suis tombé sur ceci https://www.mrexcel.com/board/thread...5/post-3553373
Ca me semble intéressant comme méthode et pourrait être plus robuste que l'interpolation de Lagrange si pas mal de valeurs sont manquantes.
Vous en pensez-quoi?

Quant à une interpolation de Fourier, j'avoue que je ne vois pas encore très bien de quoi il s'agit :-) mais je vais me renseigner :-)

Belle semaine à tous et merci !

**nico84** · 31/08/2020, 10h07

"cubic spline" est le nom anglais pour l'interpolation polynomiale apparemment donc ça serait du Lagrange pareil

L'analyse de Fourrier est utilisée principalement pour étudier des signaux répétitifs (sons, vibrations mécaniques...) : on cherche à remplacer la courbe par une somme de sinusoides de fréquence et d'amplitude variables. Dans ton cas ça serait une sinusoïde journalière + une annuelle mais en pratique la météo introduit une variation aléatoire et donc ça marcherait assez mal aussi (sauf si on dit qu'il pleut tous les dimanche) !

En fait tout dépend de la précision que tu recherches et du nombre de valeurs potentiellement manquantes. Si c'est important il vaudrait mieux fiabiliser l'acquisition

**Dermochelys** · 31/08/2020, 12h49

Bonjour Nico84,

Mais, Lagrange est donc partout !!

Au niveau de l'analyse de Fourier, je ne pense pas que ce soit adapté, sauf peut-être si on limite l'interpolation des températures sur des données manquantes pendant 2-3 jours au max.

Je suis tout à fait d'accord avec toi sur la qualité d'acquisition, mais cela ne dépends pas de moi. Les données dans lesquelles il y a des manques viennent des stations météo officielles (manques causé par bugs, pannes, etc...). On doit faire avec...

J'explique un peu ici ce que je vise comme résultat.
Pour l'instant je n'ai pas de retour des matheux (sans que ce soit péjoratif, car les maths sont partout) quant à programmer un GAM en VBA... Si tu as des idées ou des sources traduisibles en VBA, je suis preneur ;-)
Mais déjà au niveau de l'interpolation, je vais pouvoir avancer :-)

Un grand merci en tout cas!

**Dermochelys** · 01/09/2020, 09h40

Bonjour User,

Je viens de tester ton code, en remplaçant ce que devais l'être pour coller a ma situation.
Voici les codes modifiés

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Public Function MajTemperatures(nomTable As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset, x As Double, y As Double, bk As Variant
    Dim t As Variant ' variable utilisée comme tableau à 2 dimension : 1 dimension pour les champs du recordset et une autre pour les enregistrements
    Dim i As Long
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(nomTable, dbOpenDynaset)
 
    rs.MoveLast
    rs.MoveFirst
 
    t = rs.GetRows(rs.RecordCount) ' on transforme le recordset en un tableau à 2 dimension t(0..rs.Fields.Count-1,0..n-1)
    i = 0
    rs.MoveFirst
    Do Until rs.EOF
        If IsNull(t(2, i)) Then ' si pas de valeur de température pour cet enregistrement
            x = CDbl(rs!DateFull) ' on convertit la date en un numérique : valeur d'abscisse x
            'pour l'interpolation ci-dessous, les
            'indices X et Y correspondent aux indices du champ date et du champ température
            y = InterpolationLagrange(t, 2, 9, x) ' calcul de la température correspondant à la date : valeur d'ordonnée y
            MsgBox "y =" & y
            rs.Edit
            rs!TempExtrapTE = y ' on copie la valeur de l'interpolation
            rs.Update
        End If
        rs.MoveNext ' on passe à l'enregistrement suivant
        i = i + 1
    Loop
MsgBox "fini"
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
End Function
 
Public Function InterpolationLagrange(t As Variant, indiceX As Long, indiceY As Long, x As Double) As Double
    ' indiceX : indice du champ date
    ' indiceY : indice du champ température
    Dim p As Double, u As Double
    Dim i As Long, j As Long, n As Long
 
    ' formule = p1*y1 + p2*y2 +...+ pn*yn + ..
    n = UBound(t) ' nombre d'enregistrements
 
    ' formule devient = p0*t(indiceY,0) + p1**t(indiceY,1) +...+ p(n-1)*t(indiceY,n)
    InterpolationLagrange = 0
 
    For i = 0 To n ' on parcourt les lignes du tableau
        p = 1
        If Not IsNull(t(indiceY, i)) Then ' si la valeur de température n'est pas nulle alors on la prend en compte dans le calcul d'intepolation
            For j = 0 To n  ' on parcourt les lignes du tableau pour calculer le coefficient
                If j <> i And Not IsNull(t(indiceY, j)) Then
                    p = p * (x - t(indiceX, j)) / (t(indiceX, i) - t(indiceX, j))
                End If
            Next j
            InterpolationLagrange = InterpolationLagrange + (p * t(indiceY, i)) ' on ajoute à la formule (p * t(indiceY, i))
        End If
    Next i
End Function

Je le lance par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MajTemperatures ("TempStationExtrap")

. Mais ca ne modifie rien dans ma table. Du coup en mettant quelques msgbox pour voir le deroulement, on dirait qu'il ne rentre jamais dans la boucle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
        If IsNull(t(2, i)) Then ' si pas de valeur de température pour cet enregistrement
            x = CDbl(rs!DateFull) ' on convertit la date en un numérique : valeur d'abscisse x
            'pour l'interpolation ci-dessous, les
            'indices X et Y correspondent aux indices du champ date et du champ température
            y = InterpolationLagrange(t, 2, 9, x) ' calcul de la température correspondant à la date : valeur d'ordonnée y ; DateFull d'indiceX= 2 ; TempExtrapTE d'indiceY = 9
            MsgBox "y =" & y
            rs.Edit
            rs!TempExtrapTE = y ' on copie la valeur de l'interpolation
            rs.Update
        End If
        rs.MoveNext ' on passe à l'enregistrement suivant
        i = i + 1
    Loop

Ma table a la même structure que celle que j'illustrai dans mon message d'origine.
As-tu une idée? Vais continuer a chercher de mon côté...

Bonne journée

**Dermochelys** · 01/09/2020, 09h56

Re,

Bon en effet l'entrée dans la boucle prenait en compte les dates et pas la valeur (possiblement) nulle de la température
En modifiant cela ainsi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If IsNull(t(9, i)) Then

ca me rempli bien le champ là où il y a des valeurs manquantes
Mais avec des valeurs ... étranges

Nom : tempfig.png
Affichages : 277
Taille : 25,6 Ko

Il doit y avoir un soucis dans la formule de Lagrange

Vais essayer de voir où, mais j'ai peur que ce soit hors de mes compétences en math...

A bientôt!

**User** · 01/09/2020, 11h58

Bonjour,

Il y a peut-être divergence de la méthode à cause du grand nombre de valeurs manques. Peux-tu poster tes données pour que je teste de mon côté.

Cdlt,

**Dermochelys** · 01/09/2020, 13h53

Bonjour User,

Je te joins ci attaché le tableau concerné (dont les données ont été copiées dans ce fichier excel).

Les colonnes qui m'intéressent sont DateFull et TempExtrapTE.

J'ai aussi fait des tests en me basant sur l'ID à l place de DateFull pour l'axe des x, mais ce n'est pas mieux.

Merci pour ton aide!

Cdlt

PS: je réfléchissais mais ca me semble bizarre que les valeurs calculées pour l’interpolation soient des exposants+18 ou du même genre... Ce qui me fait dire que ça ne doit pas être lié au nombre de données manquantes. Peut-être que c'est plutôt lié au nombre de données totales?
Tu verras qu'il y a un gros bloc manquant vers 26-27 juillet. il y a aussi une valeur manquante le 8 aout. Dans les deux cas, l'interpolation remplis les champs avec des valeurs calculées beaucoup trop grande.

**Dermochelys** · 01/09/2020, 14h04

re

En me basant sur l'article ici et sur le code là, j'ai modifié le code pour l'adapter à mon cas en VBA Access.

Il fonctionne mais pose le même problème que l'interpolation de Lagrange (les valeurs retournées sont aberrantes).

Je le poste au cas où ça pourrait servir d'inspiration à quelqu'un plus tard:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
'Fonction vue ici https://www.business-spreadsheets.com/forum.asp?t=120
'et adaptée pour access
'
Public Function spline(t As Variant, indiceX As Long, indiceY As Long, x As Double) As Double
Dim val1 As Integer
Dim val2 As Integer
Dim n As Integer
Dim i As Integer, k As Integer
Dim p As Single, qn As Single, sig As Single, un As Single
Dim u() As Single
Dim yt() As Single
Dim y As Double
Dim klo As Integer, khi As Integer
Dim h As Single, b As Single, a As Single
 
val1 = UBound(t) - 1
val2 = UBound(t)
n = UBound(t)
 
ReDim u(val1)
ReDim yt(val2)
 
yt(1) = 0
u(1) = 0
 
For i = 2 To n - 1
    sig = (t(indiceX, i) - t(indiceX, i - 1)) / (t(indiceX, i + 1) - t(indiceX, i - 1))
    p = sig * yt(i - 1) + 2
    yt(i) = (sig - 1) / p
    u(i) = (t(indiceY, (i + 1)) - t(indiceY, i)) / (t(indiceX, (i + 1)) - t(indiceX, i)) - (t(indiceY, i) - t(indiceY, (i - 1))) / (t(indiceX, i) - t(indiceX, (i - 1)))
    u(i) = (6 * u(i) / (t(indiceX, (i + 1)) - t(indiceX, (i - 1))) - sig * u(i - 1)) / p
Next i
 
qn = 0
un = 0
yt(n) = (un - qn * u(n - 1)) / (qn * yt(n - 1) + 1)
For k = n - 1 To 1 Step -1
    yt(k) = yt(k) * yt(k + 1) + u(k)
Next k
 
klo = 1
khi = n
Do
    k = khi - klo
        If t(indiceX, k) > x Then
            khi = k
        Else
            klo = k
        End If
    k = khi - klo
Loop While k > 1
 
h = t(indiceX, khi) - t(indiceX, klo)
a = (t(indiceX, khi) - x) / h
b = (x - t(indiceX, klo)) / h
y = a * t(indiceY, klo) + b * t(indiceY, khi) + ((a ^ 3 - a) * yt(klo) + (b ^ 3 - b) * yt(khi)) * (h ^ 2) / 6
 
spline = y
 
End Function