@IvanIvanichNN
Атишничаю потихоньку

Как оптимизировать работу с файлами в Python?

Всем привет! Подскажите кто чем может по следующему вопросу.
Есть 16 тысяч экселек, данные из которых мне нужно будет слить воедино. Однако названия листов несмотря на одинаковое содержание различаются, впрочем как и их порядок. Хочу для начала с помощью Python переименовать листы и привести их названия к единому виду чтобы можно было собирать с них данные по единому ключу. Набросал код, который собирает из файлов уникальные названия листов и выгружает их в файлик для последующей обработки:
def sheet_name_file():
    from openpyxl import load_workbook, Workbook
    import os
    sheetname_set = set()
    for dir in os.listdir('C:\\Users\Иван Данчук\Documents\Тест_shutil'):
        path = f'C:\\Users\Иван Данчук\Documents\Тест_shutil\{dir}'
        for file in os.listdir(path):
            wb = load_workbook(os.path.join(path, file))
            sheetname_set.update(wb.sheetnames[0:10])
            print(file)
        wb2 = Workbook()
        ws = wb2.active
        for row in range(1):
            ws.append(list(sheetname_set))
        wb2.save(f'unique_sheet_name_list_{dir}.xlsx')


Затестил его производительность - за 12 минут он обработал 40 файлов в двух папках! То есть на всю обработку уйдет где то 80 часов.
В связи с этим у меня следующие вопросы:
1) можно ли распараллелить обработку файлов по директориям с помощью библиотеки multiprocessing ? Если можно помогите с примером кода - как это правильно сделать?
2) можно ли этот код оптимизировать для обработки?
3) и стоит ли такие масштабные вещи с Excel'ьками проводить в Python? Или лучше в VBA погрузиться для решения подобных задач?
  • Вопрос задан
  • 85 просмотров
Решения вопроса 2
Или лучше в VBA погрузиться для решения подобных задач?

VBA - однопоточный, расспаралелить не получится.

но если только собрать названия листов, то странно большое время у Вас для такой задачи.
Я тут на коленке набросал код на VBA, так он 200 файлов за 4 минуты обработал, и это без оптимизации кода (excel 32, win 10, 4ядра по 3ГГц).
Можете запустить у себя, для этого открыть новую книгу, открыть редактор макросов, выбрать лист на котором будете работать и вставить следующий код (полной заменой)
Код для сбора списка листов из всех книг директории
Function FilenamesCollection(ByVal FolderPath As String, Optional ByVal Mask As String = "", _
                             Optional ByVal SearchDeep As Long = 999) As Collection
    ' © EducatedFool  excelvba.ru/code/FilenamesCollection
    ' Получает в качестве параметра путь к папке FolderPath,
    ' маску имени искомых файлов Mask (будут отобраны только файлы с такой маской/расширением)
    ' и глубину поиска SearchDeep в подпапках (если SearchDeep=1, то подпапки не просматриваются).
    ' Возвращает коллекцию, содержащую полные пути найденных файлов
    ' (применяется рекурсивный вызов процедуры GetAllFileNamesUsingFSO)

    Set FilenamesCollection = New Collection    ' создаём пустую коллекцию
    Set FSO = CreateObject("Scripting.FileSystemObject")    ' создаём экземпляр FileSystemObject
    GetAllFileNamesUsingFSO FolderPath, Mask, FSO, FilenamesCollection, SearchDeep ' поиск
    Set FSO = Nothing     ' очистка строки состояния Excel
End Function
 
Function GetAllFileNamesUsingFSO(ByVal FolderPath As String, ByVal Mask As String, ByRef FSO, _
                                 ByRef FileNamesColl As Collection, ByVal SearchDeep As Long)
    ' перебирает все файлы и подпапки в папке FolderPath, используя объект FSO
    ' перебор папок осуществляется в том случае, если SearchDeep > 1
    ' добавляет пути найденных файлов в коллекцию FileNamesColl
    On Error Resume Next: Set curfold = FSO.GetFolder(FolderPath)
    If Not curfold Is Nothing Then    ' если удалось получить доступ к папке

        ' раскомментируйте эту строку для вывода пути к просматриваемой
        ' в текущий момент папке в строку состояния Excel
        ' Application.StatusBar = "Поиск в папке: " & FolderPath

        For Each fil In curfold.Files    ' перебираем все файлы в папке FolderPath
            If fil.Name Like "*" & Mask Then FileNamesColl.Add fil.Path
        Next
        SearchDeep = SearchDeep - 1    ' уменьшаем глубину поиска в подпапках
        If SearchDeep Then    ' если надо искать глубже
            For Each sfol In curfold.SubFolders    ' перебираем все подпапки в папке FolderPath
                GetAllFileNamesUsingFSO sfol.Path, Mask, FSO, FileNamesColl, SearchDeep
            Next
        End If
        Set fil = Nothing: Set curfold = Nothing    ' очищаем переменные
    End If
End Function

Sub LoopThroughFiles(ByVal sDirName As String, ByRef lRow As Long, ByVal sMask As String)
   On Error Resume Next
   Dim folder$, coll As Collection
   Dim EX As Excel.Application
   Dim wkb As Workbook
   Dim wks As Worksheet
   Dim file As Variant
   Dim i As Long
   Dim v As Variant
   
 
    folder$ = sDirName
    If Dir(folder$, vbDirectory) = "" Then
        MsgBox "Не найдена папка «" & folder$ & "»", vbCritical
        Exit Sub        ' выход, если папка не найдена
    End If
 
    Set coll = FilenamesCollection(folder$, sMask)        ' получаем список файлов по маске из папки
    If coll.Count = 0 Then
'        MsgBox "В папке «" & Split(folder$, "\")(UBound(Split(folder$, "\")) - 1) & "» нет ни одного подходящего файла!", _
               vbCritical, "Файлы для обработки не найдены"
        Exit Sub        ' выход, если нет файлов
    End If
 
   Set EX = New Application
   EX.Visible = False
   
   ' перебираем все найденные файлы
   For Each file In coll
    
      Cells(lRow, 2) = CStr(file)
      
      Set wkb = EX.Workbooks.Open(Filename:=file)

      ' Если книга не пуста
      If wkb.Sheets.Count > 0 Then
         i = 1
         ReDim v(1 To wkb.Sheets.Count)
         ' Получаем названия листов
         For Each wks In wkb.Sheets
            v(i) = wks.Name
            i = i + 1
         Next wks

      End If

      Cells(lRow, 3) = Join(v, ",")

      wkb.Close False
            
      DoEvents
      
      lRow = lRow + 1
    
      DoEvents
    Next file
    
   Set wks = Nothing: Set wkb = Nothing: Set EX = Nothing
   Set colShts = Nothing
    
End Sub

Sub LoopThroughDirs()
   Dim lLastRow As Long
   Dim lRow As Long
   Dim i As Long
   Dim v As Variant
   Dim dTime As Double

   lRow = 2
   lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
   
   v = Range(Cells(2, 1), Cells(lLastRow, 2))
   
   dTime = Time()
   For i = LBound(v) To UBound(v)
      Application.StatusBar = "Обрабатывается директория " & i & " из " & UBound(v)
      Call LoopThroughFiles(v(i, 1), lRow, "*.xls")
      Call LoopThroughFiles(v(i, 1), lRow, "*.xlsx")
      Call LoopThroughFiles(v(i, 1), lRow, "*.xlsm")
      DoEvents
   Next i
   MsgBox "Готово за " & CStr(CDate(Time() - dTime))
End Sub


для запуска в первой колонке заполнить директории которые он будет смотреть. Просматриваются вложенные директории до 999 грубины.

Запустить процедуру LoopThroughDirs, для этого поставить курсор на название и нажать F5

Результат
5f05adc35867d361594006.png
Ответ написан
SoreMix
@SoreMix
yellow
1) можно ли распараллелить обработку файлов по директориям с помощью библиотеки multiprocessing ? Если можно помогите с примером кода - как это правильно сделать?


Примерно так должно работать, если не напутал. Но нужно будет добавить какое нибудь дополнительное отслеживание прогресса. Например, записывать еще один файл с названиями excel файлов, из которых уже вытащили названия листов. Потому что pool разобьет, и по порядку делать не будет конечно же.
from multiprocessing import Pool

def get_all_files():
    files = []
    for dir in os.listdir('C:\\Users\Иван Данчук\Documents\Тест_shutil'):
        path = f'C:\\Users\Иван Данчук\Documents\Тест_shutil\{dir}'
        for file in os.listdir(path):
            files.append(os.path.join(path, file))
    return files

def get_sheetnames(file_path):
    wb = load_workbook(file_path)
    with open('sheetnames.txt', 'a', encoding='utf-8') as f:
        f.write('\n'.join(wb.sheetnames[0:10])+'\n')

files = get_all_files()
with Pool(5) as p:
    p.map(get_sheetnames, files)


2) можно ли этот код оптимизировать для обработки?

Лучше не создавать новый воркбук, записывать в него данные после каждой операции. Либо сместите влево код, чтобы выполнился только один раз, а лучше всего записать в файл, как я делал выше. Все равно я так понял значение не имеет где эти данные лежат. Если нужно имя из каждой папки - тоже можно разбить либо на разные текстовики, либо сделать словарь, куда заносить имена листов.

3) и стоит ли такие масштабные вещи с Excel'ьками проводить в Python? Или лучше в VBA погрузиться для решения подобных задач?

Вообще, у вас очень много файлов. Время все равно придется потратить.
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
trapwalker
@trapwalker Куратор тега Python
Программист, энтузиаст
Вы можете писать в CSV, прилично сэкономите на этом. CSV-файлы потом легко и быстро конкатенировать системными средствами командной строки. У вас винда, так что COPY, например.
Самое неприятное, что может случиться - это проснуться на утро и увидеть монолитный гигантский файл (результат ночного бдения вашего скрипта), в котором что-то пошло не так где-то после первых 30-60%.

Вообще главное - это вопрос одноразовая ли это задача. Если вы такой объём грузите, скажем, каждые пару дней, то, конечно, нужно юзать мультипроцессинг и настраиваеть нормальный пайплайн с автоматизацией и логами.
Если речь об однократной кастомной обработке в рамках R&D, то не парьтесь, сделайте как проще.

Пишите в CSV. Разбейте объём простым слайсом на несколько кусков и тупо стартуйте отдельными скриптами. Это сэкономит вам время на отладку мультипроцессинга, не надо городить очередей, тасков и прочего.

К вашему коду есть вопросы:
for row in range(1):
            ws.append(list(sheetname_set))

Зачем здесь цикл?
Почему не используете pathlib? Код станет надёжнее и лаконичнее.
Почему не добавили статистику или просто логирование времени работы разных этапов вашего скрипта? Оптимизировать надо начиная с бутылочного горлышка. После первой бутылки задача может показаться не столь актуальной.

Если речь об R&D и вы не знаете что дальше будете делать с данными, то объективно полезные вещи можно вытягивать и сохранять в своеобразные мета-файлы для последующей быстрой обработки. Это выгодно, если бутылочное горлышко застряло в экселе. Вытащите за один проход всё что может пригодиться в sqlite или хотя бы простые json-файлы по одному на каждый документ.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы