<jupyter_start><jupyter_text>Подключаем необходимые библиотеки<jupyter_code>import os
import datetime
import numpy as np
import pandas as pd
from sqlalchemy import create_engine<jupyter_output><empty_output><jupyter_text>Настраиваем подключение к БД<jupyter_code>engine = create_engine("postgresql://postgres:@{}:{}".format(os.environ["POSGRES_HOST"], os.environ['POSGRES_PORT']))<jupyter_output><empty_output><jupyter_text>Запрос 2: Подсчитать общее количест получателей<jupyter_code>emailreceivers = pd.read_sql("select * from emailreceivers", engine)
req2_result = emailreceivers["personid"].drop_duplicates().count()
print(req2_result)
pd.DataFrame(
    [req2_result],
     columns=['count']).to_csv(os.environ['PANDAS_EXPORT_FOLDER'] + 'req2.csv')<jupyter_output>418<jupyter_text>Запрос 3: Подсчитать количество отправленных писем за 2012<jupyter_code>emails = pd.read_sql("select * from emails", engine)
req3_result = emails[
    (emails["metadatadatesent"] >= np.datetime64('2012-01-01')) &
    (emails["metadatadatesent"] < np.datetime64('2013-01-01'))
]["id"].count()
print(req3_result)
pd.DataFrame(
    [req3_result],
     columns=['emails']).to_csv(os.environ['PANDAS_EXPORT_FOLDER'] + 'req3.csv')<jupyter_output>1500<jupyter_text>Запрос 5:Вывести список писем в следующем формате:Отправитель письма, получатель письма, тема письма,и отсортированы по теме письма.<jupyter_code>emails = pd.read_sql("select * from emails", engine)
req5_df = emails[
    ["metadatafrom", "metadatato", "metadatasubject"]
].sort_values(by=["metadatasubject"])
print(req5_df.head())
req5_df.head().to_csv(os.environ['PANDAS_EXPORT_FOLDER'] + 'req5.csv')<jupyter_output>metadatafrom         metadatato        metadatasubject
35711          <NAME>                  H   - MORE WHEN WE SPEAK
3951           <NAME>                  H   - MORE WHEN WE SPEAK
1528   <EMAIL>  <EMAIL>   - MORE WHEN WE SPEAK
17504  <EMAIL>  <EMAIL>   - MORE WHEN WE SPEAK
25355  <EMAIL>  <EMAIL>   - MORE WHEN WE SPEAK<jupyter_text>Запрос 6:Вывести среднюю длинну сообщения<jupyter_code>emails = pd.read_sql("select * from emails", engine)
req6_result = emails["extractedbodytext"].str.len().mean()
print(req6_result)
pd.DataFrame(
    [req6_result],
     columns=['avg']).to_csv(os.environ['PANDAS_EXPORT_FOLDER'] + 'req6.csv')<jupyter_output>533.1624147137348<jupyter_text>Запрос 7:Вывести количество писем, в которых содержится подстрока UNCLASSIFIED<jupyter_code>emails = pd.read_sql("select * from emails", engine)
req7_result = emails[
    emails["extractedbodytext"].str.contains("UNCLASSIFIED") == True
]["extractedbodytext"].count()

print(req7_result)
pd.DataFrame(
    [req7_result],
    columns=['text']).to_csv(os.environ['PANDAS_EXPORT_FOLDER'] + 'req7.csv')<jupyter_output>78
{'fraction_non_alphanumeric': 0.13647912885662433, 'fraction_numerical': 0.035208711433756805, 'mean_word_length': 6.158441558441559, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '2127', 'license': 'MIT', 'n_tokens_mistral': 1141, 'n_tokens_neox': 1116, 'n_words': 164, 'path': 'project1_sql/scripts/requests.ipynb', 'repo_name': 'mikhailbadin/ds_couse_homeworks', 'size': '2851'}