Gera Relatório de Aberturas de Entidades por Natureza Jurídica

plot.py

import seaborn as sns
import matplotlib.pyplot as plt
import math

def get_natur_counts_per_year(cod):
    # Query local Clickhouse
    return db.execute(f"""
    SELECT 
        toYear(data_inicio_atividade) AS year, 
        COUNT(*) AS amount
    FROM companies.estabelecimentos
    WHERE toYear(data_inicio_atividade) BETWEEN 2005 AND 2024
    AND identificador_matriz_filial = 1
    AND cnpj_basico IN (
        SELECT cnpj_basico 
        FROM companies.empresas 
        WHERE natureza_juridica = {cod}
    )
    GROUP BY year
    ORDER BY year ASC;
    """)

def get_naturs():
    # Pega Naturezas Jurídicas e Descrições
    result = db.execute("""
        SELECT nj.codigo, nj.descricao
        FROM naturezas_juridica nj
        WHERE nj.codigo > 0
    """)
    return {row[0]: row[1] for row in result}

def plot():
    # Ignorar naturezas jurídicas sem dados relevantes
    codigos_excluidos = {5037, 3298, 3280, 3107, 2275, 2100, 1341, 1198, 1236, 1252, 1139, 1072, 1163, 1058}
    
    valid_entries = []
    for codigo, descricao in get_naturs().items():
        if codigo in codigos_excluidos:
            continue
        else:
            result = get_natur_counts_per_year(codigo)
            if result:
                valid_entries.append((codigo, descricao, result))

    n = len(valid_entries)
    cols = 3
    rows = math.ceil(n / cols)

    fig, axes = plt.subplots(rows, cols, figsize=(cols * 6, rows * 4))
    axes = axes.flatten()

    for idx, (codigo, descricao, result) in enumerate(valid_entries):
        print(f"Processing: {descricao} ({codigo})")
        years, counts = zip(*result)
        ax = axes[idx]
        sns.lineplot(x=years, y=counts, marker='o', color='darkblue', linewidth=2, ax=ax)
        ax.set_title(f"{descricao}")
        ax.set_xlabel("Ano")
        ax.set_ylabel("Empresas")
        ax.set_xticks(years)
        ax.tick_params(axis='x', rotation=45)
        ax.grid(axis='y', linestyle='--', alpha=0.7)

    plt.tight_layout()
    plt.show()

plot()