Compléter une base de données

L'auteur de ce sujet a trouvé une solution à son problème.
Auteur du sujet

Bonjour,

J'ai une base de données SQLite de la forme suivante :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE IF NOT EXISTS CrudeData(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    cow INTEGER NOT NULL,
    date DATE NOT NULL,
    prod REAL NOT NULL, -- L
    cons REAL NOT NULL, -- kg
    lact INTEGER NOT NULL,
    day INTEGER NOT NULL,
    UNIQUE (cow, date)
);

Cette base a été remplie à partir de fichiers CSV exportés du robot de traite de mon oncle. Seulement, il manque certaines lignes, si je puis dire. J'entends par là que pour une vache $c$ donnée, il existe des dates $d$ (comprises entre la date la plus ancienne et celle la plus récente) telles qu'aucune ligne de la table n'ait pour valeur $c$ dans le champ cow et $d$ dans le champ date.

Je souhaiterais alors remplir ces trous en respectant les contraintes des autres champs. Par exemple :

1
2
3
id   8941   2014-05-05   20.3   11.2   3   200
id   8941   2014-05-07   25.7   14.2   3   202
id   8941   2014-05-09   10.1   12.5   4   2

deviendrait

1
2
3
4
5
id   8941   2014-05-05   20.3   11.2   3   200
id   8941   2014-05-06   0   0   3   201
id   8941   2014-05-07   25.7   14.2   3   202
id   8941   2014-05-08   0   0   4   1
id   8941   2014-05-09   10.1   12.5   4   2

Pour cela, je pense qu'il me faut :

  • Sélectionner les lignes correspondant à une vache donnée ;
  • Classer ces lignes selon le champ date ;
  • Repérer les trous ;
  • Pour un trou donné, déterminer les valeurs des champs lact et day

Actuellement, je ne sais pas trop comment faire le 3è point. J'utilise Python.

Merci.

Édité par Vayel

+0 -0
Staff

Cette réponse a aidé l'auteur du sujet

C'est facile : tu repères la différence de date entre deux lignes consécutives en transformant tes dates en objets datetime.date. Si la différence est strictement supérieure à un jour, c'est qu'il y a un trou.

I was a llama before it was cool

+0 -0
Auteur du sujet

Plus ou moins. Disons que rien ne me dit qu'il ne peut y avoir plusieurs trous consécutifs. J'aurais dû précisé, pardon.

En farfouillant sur le Web, je déniché une solution ressemblant à celle que tu suggères :

  • Stocker mes dates dans un tableau, sous forme d'objets datetime ;
  • Générer l'ensemble des dates comprises entre la plus ancienne et la plus récente ;
  • Faire la différence des ensembles (via des set).

Pour la suite, j'ai pu profiter du fait de ne pas réussir à m'endormir et elle ressemblerait à cela.

Exemple

date lactation jour
2014-05-04 3 2
2014-05-05 3 3
2014-05-07 3 5
2014-05-10 4 2
2014-05-12 4 4

Avec la différence des ensembles, on obtient déniche les trous :

date lactation jour
2014-05-04 3 2
2014-05-05 3 3
2014-05-06 ? ?
2014-05-07 3 5
2014-05-08 ? ?
2014-05-09 ? ?
2014-05-10 ? ?
2014-05-11 4 2
2014-05-12 ? ?
2014-05-13 4 4

Deux problèmes :

  • Il y a un trou non compté au début (la lactation ne commence pas au premier jour)
  • Il y a plusieurs trous consécutifs en fin de lactation, il faut déterminer de combien allonger la 3è et de combien la 4è

De bas en haut

On constate qu'on va avoir un problème si on parcourt nos trous de bas en haut vu que rien ne nous dit quand arrêter la troisième lactation. Si je complète jusqu'à remplir tous les trous, je vais avoir une 4è lactation commençant au deuxième jour :

date lactation jour
2014-05-04 3 2
2014-05-05 3 3
2014-05-06 3 4
2014-05-07 3 5
2014-05-08 3 6
2014-05-09 3 7
2014-05-10 3 8
2014-05-11 4 2
2014-05-12 4 3
2014-05-13 4 4

De haut en bas

Prenons le chemin inverse alors. Je pars du dernier jour de la 4è lactation et remplis tant que je ne suis pas à 1 :

date lactation jour
2014-05-04 3 2
2014-05-05 3 3
2014-05-06 ? ?
2014-05-07 3 5
2014-05-08 ? ?
2014-05-09 ? ?
2014-05-10 4 1
2014-05-11 4 2
2014-05-12 4 3
2014-05-13 4 4

Et là malheur : la date 2014-05-09 appartient à la 3è lactation, mais à quel jour ?

Tant pis, je passe cette série de trous et je continue jusqu'à 1 :

date lactation jour
2014-05-03 3 1
2014-05-04 3 2
2014-05-05 3 3
2014-05-06 3 4
2014-05-07 3 5
2014-05-08 ? ?
2014-05-09 ? ?
2014-05-10 4 1
2014-05-11 4 2
2014-05-12 4 3
2014-05-13 4 4

De bas en haut

Il faut maintenant remonter pour compléter les trous manquant :

date lactation jour
2014-05-03 3 1
2014-05-04 3 2
2014-05-05 3 3
2014-05-06 3 4
2014-05-07 3 5
2014-05-08 3 6
2014-05-09 3 7
2014-05-10 4 1
2014-05-11 4 2
2014-05-12 4 3
2014-05-13 4 4

Au code maintenant. ^^

Édité par Vayel

+0 -0
Auteur du sujet

Le code suivant semble répondre à la problématique :

 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
import datetime as dt
import sqlite3

from mpf.models.db import DBSelector
from mpf.config import DATABASE_PATH


def get_missing_dates(dates):
    date_set = set(
        dates[0] + dt.timedelta(x) 
        for x in range((dates[-1]-dates[0]).days)
    )

    return list(reversed(sorted(date_set - set(dates))))

def main():
    db = DBSelector(DATABASE_PATH)

    q_dates = "SELECT date FROM CrudeData WHERE cow = ? ORDER BY date"
    q_insert = "INSERT INTO CrudeData VALUES (NULL, ?, ?, 0, 0, ?, ?)"
    q_last = "SELECT date, day FROM CrudeData WHERE cow = ? AND lact = ? ORDER BY day DESC LIMIT 1"

    for cow in db.cows():
        dates = [
            dt.datetime.strptime(line[0], "%Y-%m-%d") 
            for line in db.query(q_dates, (cow,))
        ]

        missing = get_missing_dates(dates) 

        if missing:
            for lact in reversed(sorted(db.lacts(cow))):
                # The last date and day in database for this lactation
                last_date_str, last_day = db.query(q_last, (cow, lact))[0]
                last_date = dt.datetime.strptime(last_date_str, "%Y-%m-%d") 

                # We start at the last day of the lactation then add the 
                # missing lines till we reach the first day of the lactation
                while last_day > 1:
                    last_day -= 1
                    last_date -= dt.timedelta(1)

                    if last_date in missing:
                        # If day = 1, we'll have a problem of unique key in 
                        # the second loop
                        missing.remove(last_date)

                        db.query(
                            q_insert, 
                            (cow, last_date.strftime("%Y-%m-%d"), lact, last_day)
                        )

                last_date_str, last_day = db.query(q_last, (cow, lact))[0]
                last_date = dt.datetime.strptime(last_date_str, "%Y-%m-%d") 

                # We start at the last day of the lactation then add the 
                # missing lines till we reach the next lactation or the end of 
                # the data if this lactation is the last one
                while (last_date + dt.timedelta(1)) in missing:
                    last_day += 1
                    last_date += dt.timedelta(1)

                    db.query(
                        q_insert, 
                        (cow, last_date.strftime("%Y-%m-%d"), lact, last_day)
                    )


if __name__ == "__main__":
    main()
+0 -0
Vous devez être connecté pour pouvoir poster un message.
Connexion

Pas encore inscrit ?

Créez un compte en une minute pour profiter pleinement de toutes les fonctionnalités de Zeste de Savoir. Ici, tout est gratuit et sans publicité.
Créer un compte