|
#!/usr/bin/env python
|
|
# coding: utf-8
|
|
|
|
# In[1]:
|
|
|
|
|
|
import pandas as pd
|
|
|
|
# Caminhos dos arquivos
|
|
caminho_dsv_producao_completa = r"C:\desenvolvimento\projetos\Analise_Extrapolacao_Mac\Fontes\Producao\tb_producao_completa.dsv"
|
|
caminho_xlsx_dados_com_decis_e_formatados = r"C:\desenvolvimento\projetos\Analise_Extrapolacao_Mac2\Fontes\Exportacoes\dados_com_decis_e_formatados.xlsx"
|
|
|
|
# Leitura do arquivo .dsv com especificação de tipo para CO_PROCEDIMENTO como texto
|
|
print('Leitura do arquivo .dsv com especificação de tipo para CO_PROCEDIMENTO como texto')
|
|
df_producao_completa = pd.read_csv(
|
|
caminho_dsv_producao_completa,
|
|
sep=';',
|
|
quotechar='"',
|
|
encoding='latin1',
|
|
dtype={'CO_PROCEDIMENTO': 'str'}, # Garante que CO_PROCEDIMENTO seja texto
|
|
low_memory=False
|
|
)
|
|
|
|
# HIGIENIZAÇÃO DE DADOS
|
|
# Garantir que as colunas sejam strings antes de substituir as vírgulas
|
|
df_producao_completa['NU_QUANTIDADE'] = df_producao_completa['NU_QUANTIDADE'].astype(str).str.replace(',', '.').astype(float)
|
|
df_producao_completa['NU_VALOR'] = df_producao_completa['NU_VALOR'].astype(str).str.replace(',', '.').astype(float)
|
|
|
|
# Ajustar os tipos das demais colunas para strings
|
|
df_producao_completa = df_producao_completa.astype({
|
|
'TP_PRODUCAO': 'str',
|
|
'CO_MUNICIPIO': 'str',
|
|
'CO_MUNICIPIO_ORIGEM': 'str',
|
|
'CO_DOCUMENTO_ORIGEM': 'str',
|
|
'TP_ATENDIMENTO': 'str',
|
|
'TP_INSTRUMENTO_REGISTRO': 'str'
|
|
})
|
|
|
|
# Adicionando a nova coluna CO_PROCEDIMENTO_AH
|
|
df_producao_completa['CO_PROCEDIMENTO_AH'] = df_producao_completa.apply(
|
|
lambda row: f"{row['CO_PROCEDIMENTO']}-A" if row['TP_PRODUCAO'] == "Ambulatorial" else
|
|
f"{row['CO_PROCEDIMENTO']}-H" if row['TP_PRODUCAO'] == "Hospitalar" else None,
|
|
axis=1
|
|
)
|
|
|
|
df_producao_completa['CO_UF_IBGE'] = df_producao_completa['CO_MUNICIPIO'].str.slice(0, 2)
|
|
|
|
# Exibindo os primeiros registros para verificar
|
|
# print(df_dsv[['CO_PROCEDIMENTO', 'TP_PRODUCAO', 'CO_PROCEDIMENTO_AH']].head())
|
|
|
|
# Importação do arquivo Excel (.xlsx)
|
|
df_dados_com_decis_e_formatados = pd.read_excel(caminho_xlsx_dados_com_decis_e_formatados)
|
|
|
|
# Exibindo os primeiros registros de cada dataframe
|
|
# print("Dados do arquivo .dsv:")
|
|
# print(df_dsv.head())
|
|
|
|
# print("\nDados do arquivo .xlsx:")
|
|
# print(df_xlsx.head())
|
|
|
|
# print(df_dsv.columns)
|
|
|
|
populacao_brasil = df_dados_com_decis_e_formatados['QT_POPULACAO'].sum()
|
|
|
|
# Agrupando por CO_PROCEDIMENTO_AH e somando NU_QUANTIDADE e NU_VALOR
|
|
df_producao_completa_agrupado = df_producao_completa.groupby('CO_PROCEDIMENTO_AH').agg(
|
|
NU_QUANTIDADE_TOTAL=('NU_QUANTIDADE', 'sum'),
|
|
NU_VALOR_TOTAL=('NU_VALOR', 'sum')
|
|
).reset_index()
|
|
|
|
# Adicionando a coluna com a população do Brasil
|
|
df_producao_completa_agrupado['POPULACAO_BRASIL'] = populacao_brasil
|
|
|
|
# Agrupar por CO_UF_IBGE e somar a população (QT_POPULACAO)
|
|
df_populacao_por_uf = df_dados_com_decis_e_formatados.groupby('CO_UF_IBGE')['QT_POPULACAO'].sum().reset_index()
|
|
|
|
# Agrupar por CO_MUNICIPIO_IBGE e somar a população (QT_POPULACAO)
|
|
df_populacao_por_municipio = df_dados_com_decis_e_formatados.groupby('CO_MUNICIPIO_IBGE')['QT_POPULACAO'].sum().reset_index()
|
|
|
|
df_populacao_por_uf = df_populacao_por_uf.rename(columns={
|
|
'QT_POPULACAO': 'QT_POPULACAO_UF'
|
|
})
|
|
|
|
df_populacao_por_municipio = df_populacao_por_municipio.rename(columns={
|
|
'QT_POPULACAO': 'QT_POPULACAO_MUNICIPIO'
|
|
})
|
|
|
|
# ------------- TRATAMENTO DE DADOS - PERCAPITA BRASIL
|
|
# Tratar valores de NU_QUANTIDADE_TOTAL para evitar divisões por zero ou NaN
|
|
df_producao_completa_agrupado['PERCAPITA_BRASIL'] = df_producao_completa_agrupado['NU_QUANTIDADE_TOTAL'] / df_producao_completa_agrupado['POPULACAO_BRASIL']
|
|
|
|
# Substituir NaN ou infinito por 0 (caso existam valores inválidos após a divisão)
|
|
df_producao_completa_agrupado['PERCAPITA_BRASIL'] = df_producao_completa_agrupado['PERCAPITA_BRASIL'].fillna(0).replace([float('inf'), -float('inf')], 0)
|
|
|
|
# Arredondar a coluna PERCAPITA_BRASIL para 4 casas decimais
|
|
df_producao_completa_agrupado['PERCAPITA_BRASIL'] = df_producao_completa_agrupado['PERCAPITA_BRASIL'].round(4)
|
|
|
|
# Arredondar a coluna PERCAPITA_BRASIL_1000
|
|
df_producao_completa_agrupado['PROCED_POR_1000_HAB_BRA'] = df_producao_completa_agrupado['PERCAPITA_BRASIL'] * 1000
|
|
|
|
|
|
|
|
|
|
# ------------- TRATAMENTO DE DADOS - PERCAPITA BRASIL FIM
|
|
|
|
|
|
|
|
|
|
# ------------- MERGE DE DADOS
|
|
# Merge para adicionar as colunas agregadas ao df_producao_completa
|
|
df_producao_completa = df_producao_completa.merge(
|
|
df_producao_completa_agrupado[['CO_PROCEDIMENTO_AH', 'NU_QUANTIDADE_TOTAL', 'NU_VALOR_TOTAL', 'POPULACAO_BRASIL', 'PERCAPITA_BRASIL', 'PROCED_POR_1000_HAB_BRA']],
|
|
on='CO_PROCEDIMENTO_AH',
|
|
how='left'
|
|
)
|
|
|
|
# Agrupando por CO_PROCEDIMENTO_AH e CO_UF_IBGE e somando NU_QUANTIDADE e NU_VALOR
|
|
df_agrupado_uf_proced = df_producao_completa.groupby(['CO_PROCEDIMENTO_AH', 'CO_UF_IBGE']).agg(
|
|
NU_QUANTIDADE_TOTAL=('NU_QUANTIDADE', 'sum'),
|
|
NU_VALOR_TOTAL=('NU_VALOR', 'sum')
|
|
).reset_index()
|
|
|
|
# Renomeando as colunas
|
|
df_agrupado_uf_proced = df_agrupado_uf_proced.rename(columns={
|
|
'NU_QUANTIDADE_TOTAL': 'NU_QUANTIDADE_TOTAL_UF',
|
|
'NU_VALOR_TOTAL': 'NU_VALOR_TOTAL_UF'
|
|
})
|
|
|
|
# Realizando o merge para adicionar as colunas de df_agrupado_uf_proced em df_producao_completa
|
|
df_producao_completa = pd.merge(
|
|
df_producao_completa, # dataframe principal
|
|
df_agrupado_uf_proced[['CO_PROCEDIMENTO_AH', 'CO_UF_IBGE', 'NU_QUANTIDADE_TOTAL_UF', 'NU_VALOR_TOTAL_UF']], # dataframe para agregar
|
|
on=['CO_PROCEDIMENTO_AH', 'CO_UF_IBGE'], # colunas chave para a junção
|
|
how='left' # tipo de junção: 'left' mantém todas as linhas de df_producao_completa e preenche com valores de df_agrupado_uf_proced
|
|
)
|
|
|
|
# Garantir que CO_UF_IBGE em ambos os dataframes seja do tipo string (str)
|
|
df_producao_completa['CO_UF_IBGE'] = df_producao_completa['CO_UF_IBGE'].astype(str)
|
|
df_populacao_por_uf['CO_UF_IBGE'] = df_populacao_por_uf['CO_UF_IBGE'].astype(str)
|
|
|
|
# Realizando o merge entre df_producao_completa e df_populacao_por_uf com base na chave CO_UF_IBGE
|
|
df_producao_completa = pd.merge(df_producao_completa, df_populacao_por_uf[['CO_UF_IBGE', 'QT_POPULACAO_UF']],
|
|
on='CO_UF_IBGE', how='left')
|
|
|
|
|
|
# ------------- TRATAMENTO DE DADOS - PERCAPITA UF
|
|
|
|
|
|
# Garantir que não há divisões por zero ou valores nulos em QT_POPULACAO_UF
|
|
df_producao_completa['PERCAPITA_UF'] = df_producao_completa['NU_QUANTIDADE_TOTAL_UF'] / df_producao_completa['QT_POPULACAO_UF']
|
|
|
|
# Substituir valores infinitos ou NaN por 0
|
|
df_producao_completa['PERCAPITA_UF'] = df_producao_completa['PERCAPITA_UF'].fillna(0).replace([float('inf'), -float('inf')], 0)
|
|
|
|
# Converter a coluna PERCAPITA_UF para 4 casas decimais
|
|
df_producao_completa['PERCAPITA_UF'] = df_producao_completa['PERCAPITA_UF'].round(4)
|
|
|
|
# Converter a coluna PERCAPITA_UF para
|
|
df_producao_completa['PROCED_POR_1000_HAB_UF'] = df_producao_completa['PERCAPITA_UF'] * 1000
|
|
|
|
|
|
# ------------- TRATAMENTO DE DADOS - PERCAPITA UF
|
|
|
|
|
|
|
|
|
|
# Certificando-se de que as colunas CO_MUNICIPIO e CO_MUNICIPIO_IBGE têm o mesmo tipo de dados
|
|
df_producao_completa['CO_MUNICIPIO'] = df_producao_completa['CO_MUNICIPIO'].astype(str)
|
|
df_populacao_por_municipio['CO_MUNICIPIO_IBGE'] = df_populacao_por_municipio['CO_MUNICIPIO_IBGE'].astype(str)
|
|
|
|
# Realizando o merge entre df_producao_completa e df_populacao_por_municipio com base na chave CO_MUNICIPIO
|
|
df_producao_completa = pd.merge(df_producao_completa, df_populacao_por_municipio[['CO_MUNICIPIO_IBGE', 'QT_POPULACAO_MUNICIPIO']],
|
|
left_on='CO_MUNICIPIO', right_on='CO_MUNICIPIO_IBGE', how='left')
|
|
|
|
|
|
# ------------- TRATAMENTO DE DADOS - PERCAPITA MUNICIPIO
|
|
|
|
# Garantir que QT_POPULACAO_MUNICIPIO seja do tipo inteiro, substituindo infinitos por 0
|
|
df_producao_completa['QT_POPULACAO_MUNICIPIO'] = df_producao_completa['QT_POPULACAO_MUNICIPIO'].replace([float('inf'), -float('inf')], 0)
|
|
|
|
# Garantir que a coluna 'QT_POPULACAO_MUNICIPIO' não tenha valores nulos
|
|
df_producao_completa['QT_POPULACAO_MUNICIPIO'] = df_producao_completa['QT_POPULACAO_MUNICIPIO'].fillna(0)
|
|
|
|
# Criar a coluna PERCAPITA_MUNICIPIO com a divisão de NU_QUANTIDADE por QT_POPULACAO_MUNICIPIO
|
|
df_producao_completa['PERCAPITA_MUNICIPIO'] = df_producao_completa['NU_QUANTIDADE'] / df_producao_completa['QT_POPULACAO_MUNICIPIO']
|
|
|
|
# Substituir NaN e Inf (se houver) na coluna PERCAPITA_MUNICIPIO por 0
|
|
df_producao_completa['PERCAPITA_MUNICIPIO'] = df_producao_completa['PERCAPITA_MUNICIPIO'].replace([float('inf'), -float('inf')], 0)
|
|
df_producao_completa['PERCAPITA_MUNICIPIO'] = df_producao_completa['PERCAPITA_MUNICIPIO'].fillna(0)
|
|
|
|
# Arredondar os valores da coluna PERCAPITA_MUNICIPIO para 4 casas decimais
|
|
df_producao_completa['PERCAPITA_MUNICIPIO'] = df_producao_completa['PERCAPITA_MUNICIPIO'].round(4)
|
|
|
|
# Converter a coluna PERCAPITA_MUNICIPIO para
|
|
df_producao_completa['PROCED_POR_1000_HAB_MUN'] = df_producao_completa['PERCAPITA_MUNICIPIO'] * 1000
|
|
|
|
# ------------- TRATAMENTO DE DADOS - PERCAPITA MUNICIPIO
|
|
|
|
# Criando a coluna ST_EXCEDE_UF
|
|
df_producao_completa['ST_EXCEDE_UF'] = df_producao_completa['PROCED_POR_1000_HAB_MUN'] > df_producao_completa['PROCED_POR_1000_HAB_UF']
|
|
df_producao_completa['ST_EXCEDE_UF'] = df_producao_completa['ST_EXCEDE_UF'].replace({True: 'S', False: 'N'})
|
|
|
|
# Criando a coluna ST_EXCEDE_BR
|
|
df_producao_completa['ST_EXCEDE_BR'] = df_producao_completa['PROCED_POR_1000_HAB_MUN'] > df_producao_completa['PROCED_POR_1000_HAB_BRA']
|
|
df_producao_completa['ST_EXCEDE_BR'] = df_producao_completa['ST_EXCEDE_BR'].replace({True: 'S', False: 'N'})
|
|
|
|
|
|
# Filtrando o dataframe onde ST_EXCEDE_UF é 'S' ou ST_EXCEDE_BR é 'S'
|
|
# df_filtrado = df_producao_completa[(df_producao_completa['ST_EXCEDE_UF'] == 'S') | (df_producao_completa['ST_EXCEDE_BR'] == 'S')]
|
|
|
|
# Exportando o dataframe filtrado para um arquivo Excel
|
|
# df_filtrado.to_excel('df_producao_completa_filtrado.xlsx', index=False)
|
|
|
|
# Exportando o dataframe filtrado para um arquivo DSV com delimitador ';' e casas decimais ','
|
|
# df_producao_completa.to_csv('df_producao_completa.dsv', sep=';', decimal=',', index=False)
|
|
|
|
# Convertendo ambas as colunas para string
|
|
df_producao_completa['CO_MUNICIPIO'] = df_producao_completa['CO_MUNICIPIO'].astype(str)
|
|
df_dados_com_decis_e_formatados['CO_MUNICIPIO_IBGE'] = df_dados_com_decis_e_formatados['CO_MUNICIPIO_IBGE'].astype(str)
|
|
|
|
print('Realizando o merge entre df_producao_completa e df_dados_com_decis_e_formatados')
|
|
df_producao_completa = pd.merge(
|
|
df_producao_completa,
|
|
df_dados_com_decis_e_formatados,
|
|
left_on='CO_MUNICIPIO',
|
|
right_on='CO_MUNICIPIO_IBGE',
|
|
how='left'
|
|
)
|
|
|
|
print('Criando a PONTUACAO')
|
|
|
|
required_columns = ['TETO_PRODUCAO_MUNICIPAL', 'Perc_Bpac', 'ST_EXCEDE_UF', 'ST_EXCEDE_BR']
|
|
if all(col in df_producao_completa.columns for col in required_columns):
|
|
# Criar a coluna PONTUACAO com base nos critérios
|
|
df_producao_completa['PONTUACAO'] = (
|
|
(df_producao_completa['TETO_PRODUCAO_MUNICIPAL'] > 200) &
|
|
(df_producao_completa['Perc_Bpac'] > 60) &
|
|
((df_producao_completa['ST_EXCEDE_UF'] == 'S') | (df_producao_completa['ST_EXCEDE_BR'] == 'S'))
|
|
).astype(int) # Converte os valores booleanos para 1 (True) ou 0 (False)
|
|
else:
|
|
raise ValueError(f"Faltam colunas no dataframe: {set(required_columns) - set(df_producao_completa.columns)}")
|
|
|
|
print('Filtrando os dados onde PONTUACAO é igual a 1')
|
|
df_pontuacao_1 = df_producao_completa[df_producao_completa['PONTUACAO'] == 1]
|
|
|
|
# Filtrar os 10 CO_PROCEDIMENTO com maior NU_QUANTIDADE para cada CO_MUNICIPIO
|
|
df_top10_procedimentos = (
|
|
df_pontuacao_1
|
|
.sort_values(by=['CO_MUNICIPIO', 'NU_QUANTIDADE'], ascending=[True, False]) # Ordenar por município e NU_QUANTIDADE
|
|
.groupby('CO_MUNICIPIO') # Agrupar por município
|
|
.head(10) # Selecionar os 10 primeiros de cada grupo
|
|
)
|
|
|
|
# Exibir o resultado
|
|
print(df_top10_procedimentos)
|
|
|
|
# Confirmar o tamanho do novo dataframe
|
|
print(f"Número de registros no dataframe final: {len(df_top10_procedimentos)}")
|
|
|
|
|
|
print('exportando para o excel')
|
|
df_top10_procedimentos.to_excel(r'C:\desenvolvimento\projetos\Analise_Municipios_Notificacoes\df_top10_procedimentos.xlsx', index=False)
|
|
|
|
print(f"Dados exportados com sucesso")
|
|
|
|
|
|
|
|
|
|
|
|
# In[ ]:
|
|
|
|
|
|
df_pontuacao_1.columns
|
|
|
|
|
|
# In[ ]:
|
|
|
|
|
|
|
|
|