Блог

Я финансовый аналитик с опытом работы с Python около двух месяцев, и я работаю над проектом с использованием Python и SQL для автоматизации составления отчета. Процесс включает в себя доступ к изменяющемуся количеству файлов Excel, сохраненных на общем диске, извлечение двух вкладок из каждого (сводка и цитата) и объединение наборов данных в две большие таблицы «Цитата» и «Сводка». Следующий шаг — извлечь различные столбцы из каждого, объединить, вычислить и т.д.

Проблема в том, что набор данных в конечном итоге состоит из 3,4 мм строк и около 30 столбцов. Программа, которую я написал ниже, работает, но на проработку первой части (создание списка фреймов данных) ушло 40 минут, и еще 4,5 часа на создание базы данных и экспорт данных, не говоря уже об использовании БОЛЬШОГО объема памяти.

Я знаю, что должен быть лучший способ добиться этого, но у меня нет опыта работы с CS. Буду признателен за любую помощь.

 import os
import pandas as pd
from datetime import datetime
import sqlite3
from sqlalchemy import create_engine
from playsound import playsound

reportmonth = '2020-08'
month_folder = r'C:syncedSharePointFolder'
os.chdir(month_folder)

starttime = datetime.now()
print('Started', starttime)

c = 0

tables = list()
quote_combined = list()
summary_combined = list()

# Step through files in synced Sharepoint directory, select the files with the specific
# name format. For each file, parse the file name and add to 'tables' list, then load
# two specific tabs as pandas dataframes.  Add two columns, format column headers, then 
# add each dataframe to the list of dataframes. 

for xl in os.listdir(month_folder):
    if '-Amazon' in xl:
        ttime = datetime.now()
        table_name = str(xl[11:-5])
        tables.append(table_name)
        quote_sheet = pd.read_excel(xl, sheet_name='-Amazon-Quote')
        summary_sheet = pd.read_excel(xl, sheet_name='-Amazon-Summary')
        
        quote_sheet.insert(0,'reportmonth', reportmonth)
        summary_sheet.insert(0,'reportmonth', reportmonth)
        quote_sheet.insert(0,'source_file', table_name)
        summary_sheet.insert(0,'source_file', table_name)
        quote_sheet.columns = quote_sheet.columns.str.strip()
        quote_sheet.columns = quote_sheet.columns.str.replace(' ', '_')
        summary_sheet.columns = summary_sheet.columns.str.strip()
        summary_sheet.columns = summary_sheet.columns.str.replace(' ', '_')
        
        quote_combined.append(quote_sheet)
        summary_combined.append(summary_sheet)
        
        c = c   1
        
        print('Step', c, 'complete: ', datetime.now() - ttime, datetime.now() - starttime)

# Concatenate the list of dataframes to append one to another.  
# Totals about 3.4mm rows for August

totalQuotes = pd.concat(quote_combined)
totalSummary = pd.concat(summary_combined)     

# Change directory, create Sqlite database, and send the combined dataframes to database

os.chdir(r'H:AaronSDatabases')
conn = sqlite3.connect('AMZN-Quote-files_'   reportmonth)
cur = conn.cursor()
engine = create_engine('sqlite:///AMZN-Quote-files_'   reportmonth   '.sqlite', echo=False)
sqlite_connection = engine.connect()

sqlite_table = 'totalQuotes'
sqlite_table2 = 'totalSummary'

totalQuotes.to_sql(sqlite_table, sqlite_connection, if_exists = 'replace')    
totalSummary.to_sql(sqlite_table2, sqlite_connection, if_exists = 'replace')  
     
print('Finished. It took: ', datetime.now() - starttime)
'''
  

Я вижу несколько вещей, которые вы могли бы сделать. Во-первых, поскольку ваш первый шаг — это просто перенести данные в вашу базу данных SQL, вам не обязательно добавлять все файлы друг к другу. Вы можете просто решать проблему по одному файлу за раз (что означает, что вы можете выполнять несколько процессов!) — тогда любые вычисления, которые необходимо выполнить, могут быть выполнены позже. Это также приведет к сокращению использования оперативной памяти, поскольку, если у вас в папке 10 файлов, вы не загружаете все 10 одновременно.
Я бы рекомендовал следующее:

1. Создайте массив имен файлов, к которым вам нужен доступ
2. Напишите функцию-оболочку, которая может принимать имя файла, открывать анализировать файл и записывать содержимое в вашу базу данных MySQL
3. Используйте многопроцессорную обработку Python.Класс пула для их одновременной обработки. Например, если вы запустите 4 процесса, ваша задача станет в 4 раза быстрее! Если вам нужно произвести вычисления на основе этих данных и, следовательно, их агрегировать, пожалуйста, сделайте это, как только данные будут в базе данных MySQL. Это будет намного быстрее.
4. Если вам нужно определить некоторые вычисления на основе агрегированных данных, сделайте это сейчас, в базе данных MySQL. SQL — невероятно мощный язык, и там есть команда практически для всего!

Я добавил короткий фрагмент кода, чтобы показать вам, о чем я говорю 🙂

 from multiprocessing import Pool

PROCESSES = 4

FILES = []

def _process_file(filename):
    print("Processing: " filename)

pool = Pool(PROCESSES)
pool.map(_process_file, FILES)
  

Уточнение по SQL: Вам не нужна независимая таблица для каждого файла, который вы перемещаете в SQL! Вы можете создать таблицу на основе заданной схемы, а затем добавить данные из ВСЕХ ваших файлов в эту таблицу, строка за строкой. По сути, это то, что делает функция, которую вы используете для перехода от фрейма данных к таблице, но она создает 10 разных таблиц. Вы можете посмотреть некоторые примеры вставки строки в таблицу здесь.

Однако в конкретном случае использования, который у вас есть, установка if_exists параметра на "append" должна работать, как вы упомянули в своем комментарии. Я просто добавил предыдущие ссылки, потому что вы упомянули, что вы довольно новичок в Python, и многие мои друзья из финансовой индустрии сочли получение немного более тонкого понимания SQL чрезвычайно полезным.

Попробуйте это, здесь больше всего времени уходит на загрузку данных из Excel в Dataframe. Я не уверен, что следующий сценарий сократит время до нескольких секунд, но это уменьшит объем оперативной памяти, что, в свою очередь, может ускорить процесс. Это потенциально сократит время как минимум на 5-10 минут. Поскольку у меня нет доступа к данным, я не могу быть уверен. Но вы должны попробовать это

 import os
import pandas as pd
from datetime import datetime
import sqlite3
from sqlalchemy import create_engine
from playsound import playsound

os.chdir(r'H:AaronSDatabases')
conn = sqlite3.connect('AMZN-Quote-files_'   reportmonth)

engine = create_engine('sqlite:///AMZN-Quote-files_'   reportmonth   '.sqlite', echo=False)
sqlite_connection = engine.connect()

sqlite_table = 'totalQuotes'
sqlite_table2 = 'totalSummary'


reportmonth = '2020-08'
month_folder = r'C:syncedSharePointFolder'
os.chdir(month_folder)

starttime = datetime.now()
print('Started', starttime)


c = 0
tables = list()

for xl in os.listdir(month_folder):
    if '-Amazon' in xl:
        ttime = datetime.now()
        
        table_name = str(xl[11:-5])
        tables.append(table_name)
        
        quote_sheet = pd.read_excel(xl, sheet_name='-Amazon-Quote')
        summary_sheet = pd.read_excel(xl, sheet_name='-Amazon-Summary')
        
        quote_sheet.insert(0,'reportmonth', reportmonth)
        summary_sheet.insert(0,'reportmonth', reportmonth)
        
        quote_sheet.insert(0,'source_file', table_name)
        summary_sheet.insert(0,'source_file', table_name)
        
        quote_sheet.columns = quote_sheet.columns.str.strip()
        quote_sheet.columns = quote_sheet.columns.str.replace(' ', '_')
        
        summary_sheet.columns = summary_sheet.columns.str.strip()
        summary_sheet.columns = summary_sheet.columns.str.replace(' ', '_')
        
        quote_sheet.to_sql(sqlite_table, sqlite_connection, if_exists = 'append')    
        summary_sheet.to_sql(sqlite_table2, sqlite_connection, if_exists = 'append')  
        
        c = c   1
        print('Step', c, 'complete: ', datetime.now() - ttime, datetime.now() - starttime)