Pyexcel - comparaison de deux fichiers xls

optimisation

a marqué ce sujet comme résolu.

Bonjour, voilà je me frotte depuis quelques temps à python et j’essaie de me créer quelques utilitaires. J’en ai un qui va parcourir un fichier Excel et qui va regarder, pour chaque cellule, s’il y a une occurence dans le deuxième. Le truc fonctionne, mais c’est lent (il me faut plusieurs minutes pour deux fichiers de 50k cellules occupées chacun). Je me demandais s’il y avait moyen d’optimiser ça. J’ai tenté d’utiliser filter() et intersection(), mais je ne m’en sors pas. Je ne demande pas un code tout fait, mais des pistes seraient bienvenues.

Voilà le code qui fonctionne :

 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
# coding: utf-8

import pyexcel as p

def define_files_to_check():
    file_name1 = input('Entrez le nom du 1er fichier :')
    file_name2 = input('Entrez le nom du 2ème fichier :')

    return {
        'sheet1': p.get_array(file_name=file_name1),
        'sheet2': p.get_array(file_name=file_name2),
    }

def check_if_cells_match(cell1, cell2):
    """ Return True if 2 cells have the same content (not type) """

    if cell1 == cell2 and cell1 != '' and cell2 != '':
        return True


def browse_spreadsheet_rows(sheet1, sheet2):
    """ Loop trough 2 worksheets to compare all cells """

    cells_checked_count = 0
    matches = []

    for row_first_sheet in sheet1:
        for cell_first_sheet in row_first_sheet:
            cells_checked_count += 1
            if cells_checked_count%250 == 0:
                print('({} cells checked)'.format(cells_checked_count))
            for row_second_sheet in sheet2:
                for cell_second_sheet in row_second_sheet:
                    if check_if_cells_match(cell_first_sheet, cell_second_sheet):
                        if cell_first_sheet not in matches:
                            print('MATCH : {} ({} cells checked)'.format(cell_first_sheet, cells_checked_count))
                            matches.append(cell_first_sheet)

    print('FINISHED : {} cells checked'.format(cells_checked_count))

sheets = define_files_to_check()
browse_spreadsheet_rows(sheets['sheet1'], sheets['sheet2'])

Merci pour vos conseils !

+0 -0

Salut,

L’outil le plus rapide en Python pour ça sera effectivement l’intersection de set. J’ai un peu de mal à comprendre ce qui peut te poser problème là-dessus, l’idée est juste de construire deux sets avec le contenu de tes deux fichiers et d’en prendre l’intersection, c’est l’affaire de trois lignes pour définir browse_spreadsheet_rows.

+1 -0

Salut, merci pour ta réponse ! J’ai creusé dans ce sens, et j’ai écrit le code suivant :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
def browse_rows(sheet1, sheet2):

    matches = []

    for row_first_sheet in sheet1:
        row_first_sheet = list(filter(None, row_first_sheet))
        for row_second_sheet in sheet2:
            row_second_sheet = list(filter(None, row_second_sheet))
            result = set(row_first_sheet).intersection(row_second_sheet)
            if result and result not in matches:
                print(result)
                matches.append(result)
    print('FINISHED')

Je ne sais pas s’il y a moyen d’optimiser encore, là je suis passé de plusieurs minutes à environ 12-13 secondes pour deux fichiers de 50k lignes. Un grand merci en tout cas !

+0 -0

Tu peux faire plus simple, il suffit de laisser set faire tout le boulot. Ce sera forcément plus efficace que de boucler à la main ou encore de passer des filter qui vont parcourir toutes tes données une fois pour pas grand chose.

1
2
3
4
def browse_rows(sheet1, sheet2):
    set1 = set(elt for row in sheet1 for elt in row)
    set2 = set(elt for row in sheet2 for elt in row)
    return set1 & set2 - {'', None}

Le & effectue une intersection et le - {'', None} permet de virer les cases vides.

+1 -0

Ton dernier code est effectivement bien plus rapide. Ce que je ne comprends pas bien c’est qu’à l’intérieur des set(), tu parcours chaque ’sheet’, puis chaque ’row’ pour ajouter un ’elt’ (le contenu d’une cellule ?) non ? Ton code produit un résultat équivalent au mien mais en 2 à 3 secondes seulement. Comment est-ce que j’arrive à une telle différence ?

En tout cas merci pour ton aide ! Je suis en train de créer une petite interface graphique avec tkinter et la première version de mon code faisait planter le soft à tous les coups ^^

+0 -0

C’est bien plus rapide car :

  1. Dans ton code pour chaque cellule de la feuille 1 tu va re-parcourir entièrement tout l’autre feuille. Ça fait des tonnes d’itérations alors que dans son code il ne passe qu’une fois sur chaque cellule (pour récupérer le contenu).
  2. Une fois les feuilles parcourus, beaucoup moins de comparaisons seront effectués. En effet en créant des sets tu as déjà viré toutes les cellules identiques. Imagine si ta première feuille a 100 cellules qui contiennent le chiffre "0". Dans ton code, pour chacune d’elles tu va aller parcourir tout l’autre feuille pour le chercher. Si il est a la fin, ça va être long et en plus tu va le faire 100 fois. Dans sa solution, quand le set est créé, il n’y a déjà plus qu’un seul "0". Donc quand il va le rechercher dans le deuxième il ne va le faire qu’une seule fois.
  3. Enfin dernière raisons, les set sont un des conteneurs de bases. Ils sont codés en C et sont très optimisés. Les recherche/comparaison effectués par l’intersection sont réalisés de manière bien plus optimale.

Il y a tout de même une petite différence entre les deux codes. Le tiens était totalement itératif. Celui d’adri1 va mettre, en gros, tout le contenu des deux feuilles en mémoires. Sur des (très) gros tableaux ça peut coûter cher. Le siens sera toujours bien plus rapide mais consomme plus de mémoire a priori. Cependant, d’ici que tu remplisse ta mémoire avec des feuilles Excel…

+1 -0

Ce que je ne comprends pas bien c’est qu’à l’intérieur des set(), tu parcours chaque ’sheet’, puis chaque ’row’ pour ajouter un ’elt’ (le contenu d’une cellule ?) non ?

C’est exactement ça. En fait, (elt for row in sheet1 for elt in row) est un generator écrit en compréhension qui est équivalent à celui-ci :

1
2
3
4
def browse(sheet):
    for row in sheet:
        for elt in row:
            yield elt

La construction du set pourrait être écrite set1 = set(browse(sheet1)).

C’est un objet qui va donc parcourir une fois les données dans sheet1 et les donner une à une à set, qui va se charger de construire un ensemble avec ces données.

Comment est-ce que j’arrive à une telle différence ?

Il y a plein de raisons différente.

La première, c’est que set est énormément optimisé pour construire un ensemble unique à partir de données puis calculer des intersections entre deux sets. Tu auras (et moi aussi je te rassure :) ) déjà du mal à venir avec un algorithme meilleur que celui qui est implémenté dans set. Si ça t’intéresse, set marche avec une hashtable, c’est à dire que pour chaque élément que tu lui donnes, set calcul un numéro qui lui sert à identifier cet élément, pour chaque nouveau élément que tu ajoutes, il suffit de calculer le hash de cet élément et vérifier si il est présent ou non (ce qui est rapide puisque les hash sont ordonnables) pour savoir si il faut l’ajouter au set ou pas. Le même mécanisme rend le calcul de l’intersection beaucoup plus efficace qu’une comparaison élément par élément entre les deux feuilles puis élément par élément entre les éléments identiques et l’intersection déjà trouvée comme tu le faisais. Ton code nécessite 50K*50K comparaisons environ (en comptant un élément par ligne), soit 2.5 milliards contre 100K calculs de hash et environ 200K recherches dans les hashtables.

Ça, ça explique partiellement que mon code soit beaucoup plus rapide que le premier que tu as fait. Une deuxième raison, c’est que set est implémenté en C alors que ton code est implémenté en Python. Ceci implique un surcoût pour des opérations qui ont l’air simples (et le sont en C mais pas en Python) comme la comparaison de deux éléments. Ça n’a pas d’incidence visible sur un faible nombre de comparaisons, mais sur 2.5 milliards, ça va se voir. Même problème dans ton second code, la construction de listes et la comparaison avec les set déjà trouvé sera lente.

Il y a tout de même une petite différence entre les deux codes. Le tiens était totalement itératif. Celui d’adri1 va mettre, en gros, tout le contenu des deux feuilles en mémoires. Sur des (très) gros tableaux ça peut coûter cher. Le siens sera toujours bien plus rapide mais consomme plus de mémoire a priori. Cependant, d’ici que tu remplisse ta mémoire avec des feuilles Excel…

Même pas parce que les arrays de pyexcel sont chargés en mémoire. Et même dans le cas contraire, ce que mon code charge en mémoire serait seulement les ensembles obtenus.

+1 -0

Au passage, leir, ta fonction define_files_to_check n’est pas très pythonic. Quand tu as 2 valeurs a renvoyés (et toujours 2), pas besoin de passer par un dico. En python on peut renvoyer plusieurs variables (en vrai on renvoie un tuple mais peut importe).

Utilise plutôt un truc comme ça :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
def define_files_to_check():
    file_name1 = input('Entrez le nom du 1er fichier :')
    file_name2 = input('Entrez le nom du 2ème fichier :')

    sheet1 = p.get_array(file_name=file_name1)
    sheet2 = p.get_array(file_name=file_name2)
    return sheet1, sheet2

# ...
sheet1, sheet2 = define_files_to_check()
browse_spreadsheet_rows(sheet1, sheet2)

C’est bien plus clair.

+1 -0

Merci à vous deux pour ces explications très complètes ! Cela va grandement m’aider, je peux avoir à traiter des fichiers de 200k à 500k lignes parfois, j’avais besoin d’une bonne méthode (la mienne fonctionnait mais j’imagine qu’avec deux fichiers de 500k lignes, ça aurait été super long ^^).

Et j’ai appris plein de trucs, merci encore ! :) Décidément, j’adore python !

+2 -0
Connectez-vous pour pouvoir poster un message.
Connexion

Pas encore membre ?

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