File size: 7,054 Bytes
4b68381
 
c318f99
 
be5a04c
fba8193
71166ce
4b68381
18438e9
527c7ca
 
 
 
 
 
18438e9
17249dc
df022b3
3910b68
df022b3
 
 
 
3910b68
df022b3
3910b68
df022b3
 
 
 
 
 
3910b68
df022b3
 
 
a268f6c
 
 
 
 
 
 
 
 
 
 
3910b68
a8d856c
cfc693b
3910b68
bd326b8
c318f99
4b68381
a8d856c
da0d4b5
 
d2e8577
fba8193
098ca40
d2e8577
 
576a621
ad5d544
 
098ca40
b654e25
 
 
 
 
 
 
 
 
 
 
 
ad5d544
b654e25
ad5d544
b654e25
ad5d544
098ca40
b654e25
ad5d544
b654e25
098ca40
a2fbaf9
 
 
 
 
 
 
 
 
 
 
d283271
a2fbaf9
 
 
 
9f3b801
d283271
9f3b801
a2fbaf9
ad5d544
a2fbaf9
 
098ca40
78b5466
1cee5ae
f17ebf3
78b5466
6761cfe
78b5466
bdd90e4
 
78b5466
bdd90e4
78b5466
 
bdd90e4
 
 
 
78b5466
2864c62
 
 
8b09f68
67830d9
 
2864c62
 
 
67830d9
 
7b357aa
67830d9
14a3d68
 
 
 
2864c62
 
 
78b5466
8b09f68
6e99dec
bdd90e4
 
78b5466
 
8a769c2
 
 
 
 
 
 
 
6e99dec
7d074fa
8b09f68
6e99dec
7d074fa
78b5466
8b09f68
 
 
6e99dec
 
8b09f68
 
 
6182642
 
527c7ca
78b5466
1cee5ae
78b5466
 
4eb045d
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
import streamlit as st
import pandas as pd
import numpy as np

#st.set_page_config(layout="wide")

df = pd.read_csv('last_results_8.csv')

temp_data = pd.read_csv('temp_data(2).csv')
temp_data['Data_Completa'] = pd.to_datetime(temp_data['Data_Completa'])
temp_data.sort_values(['Instituição', 'Conta', 'Data_Completa'], inplace=True)
temp_data['Últimos 12 meses'] = temp_data.groupby(['Instituição', 'Conta'])['Valor'].transform(lambda x: x.rolling(window=12, min_periods=1).sum())
last_dates = temp_data.groupby(['Instituição', 'Conta'])['Data_Completa'].transform(max)
last_rows = temp_data[temp_data['Data_Completa'] == last_dates]
ultimo_ano = last_rows[['Instituição', 'Conta', 'Últimos 12 meses']]

image1 = 'images/rs_pmpa.PNG'

title_html = """
    <style>
        @font-face {
            font-family: 'Quicksand';
            src: url('font/Quicksand-VariableFont_wght.ttf') format('truetype');
        }
        body {
            font-family: 'Quicksand', sans-serif;
        }
        .custom-title {
            color: darkgreen;
            font-size: 30px;
            font-weight: bold;
        }
    </style>
    <span class='custom-title'>PREVISÕES DE RECEITAS</span>
"""

# Set a fixed width for the sidebar
st.markdown(
    """
    <style>
        .sidebar .sidebar-content {
            width: 300px;
        }
    </style>
    """,
    unsafe_allow_html=True
)

with st.sidebar:
    st.image(image1, use_column_width=True)
    st.markdown(title_html, unsafe_allow_html=True)
    selected_instituicao = st.selectbox('Seleciona Instituição', df['Instituição'].unique())
    selected_conta = st.selectbox('Seleciona Conta', df['Conta'].unique())

# Filter the DataFrame based on selected values
filtered_df = df[(df['Instituição'] == selected_instituicao) & (df['Conta'] == selected_conta)]

#col1, col2, col3 = st.columns(3) # This divides the page into three equal parts

# Set custom width for columns
col1_width = 400
col2_width = 400
col1, col2 = st.columns([col1_width, col2_width])

# Display the Forecasts values in the first column
col1.header('Valores previstos')

if not filtered_df.empty:
    data_string = filtered_df['Forecasts'].iloc[0]

    # Split the string into lines
    lines = data_string.split('\n')

    # Iterate through the lines and extract the values
    for line in lines[:-2]:
        period, value = line.split()
        num_float = float(value)
        monetary_value = f'R$ {num_float:,.2f}'  # Adding commas for thousands separator
        col1.write(f"Período {period}: {monetary_value}")
else:
    col1.warning('No data available for the selected filters.')

# Display the Forecasts values as line plots in the second column
col2.header('Gráfico com previsões')

if not filtered_df.empty:
    data_string = filtered_df['Forecasts'].iloc[0]

    # Create a list to store data for each period
    data = []

    # Split the string into lines
    lines = data_string.split('\n')

    # Iterate through the lines and extract the values
    for line in lines[:-2]:
        period, value = line.split()
        num_float = float(value)
        monetary_value = f'R$ {num_float:,.2f}'  # Adding commas for thousands separator
        data.append({'Period': int(period), 'Monetary Value': num_float})

    # Create a DataFrame from the list
    chart_data = pd.DataFrame(data)

    # Sort the DataFrame by 'Period'
    chart_data = chart_data.sort_values(by='Period')

    # Display line chart with "period" on X-axis and "Monetary Value" on Y-axis
    col2.line_chart(chart_data.set_index('Period'))

else:
    col2.warning('No data available for the selected filters.')

# Display the table in the third column
#col3 = st.columns(1)  # You can use st.columns(1) to create a single column layout

#col3.header('Resultados')
if not filtered_df.empty:
    # Filter the DataFrame for the selected institution
    tab_df = df[df['Instituição'] == selected_instituicao]

    # Create an empty list to store data
    data = []

    # Iterate through each unique 'Conta' in the filtered DataFrame
    for conta in tab_df['Conta'].unique():
        # Filter the DataFrame for the current 'Conta'
        conta_df = tab_df[tab_df['Conta'] == conta]

        # Initialize a variable to store the sum for the current 'Conta'
        conta_sum = 0.0

        # Take the first 'Modelo' for simplicity
        modelo = conta_df['Modelo'].iloc[0]

        # Iterate over each row in the filtered DataFrame for the current 'Conta'
        for _, row in conta_df.iterrows():
            lines = row['Forecasts'].split('\n')
            for line in lines[:-1]:  # Skip the summary line
                if line.strip():
                    parts = line.split()
                    value = parts[-1]
                    try:
                        conta_sum += float(value)
                    except ValueError:
                        print(f"Skipping line unable to convert to float: {line}")

        # Format the sum as a monetary value
        monetary_value = f'R$ {conta_sum:,.2f}'

        # Append the data to the list
        data.append({'Conta': conta, 'Modelo': modelo, 'Próximos 12 meses': monetary_value})

    # Convert the list to a DataFrame
    table_data = pd.DataFrame(data)

    last_df = ultimo_ano[ultimo_ano['Instituição'] == selected_instituicao]
    last_df.drop(['Instituição'], axis=1, inplace=True)
    def format_currency(x):
        return "R${:,.2f}".format(x)
    last_df['Últimos 12 meses'] = last_df['Últimos 12 meses'].apply(format_currency)
    table_data = pd.merge(table_data, last_df)

        # Calculate the grand total sum of all 'Conta' values
    total_sum = sum(float(row['Próximos 12 meses'].replace('R$ ', '').replace(',', '')) for row in data)

    # Append the "Total" row
    total_row = pd.DataFrame({'Conta': ['TOTAL (RLIT)'], 'Modelo': [''], 'Próximos 12 meses': [f'R$ {total_sum:,.2f}']})
    table_data = pd.concat([table_data, total_row], ignore_index=True)

    # Calculate and append the rows for "Saúde (12% da RLIT)" and "Educação (25% da RLIT)"
    saude_value = total_sum * 0.15
    educacao_value = total_sum * 0.25
    saude_row = pd.DataFrame({'Conta': ['Saúde (15% da RLIT)'], 'Modelo': [''], 'Próximos 12 meses': [f'R$ {saude_value:,.2f}']})
    educacao_row = pd.DataFrame({'Conta': ['Educação (25% da RLIT)'], 'Modelo': [''], 'Próximos 12 meses': [f'R$ {educacao_value:,.2f}']})

    # Append these rows to the table data
    table_data = pd.concat([table_data, saude_row, educacao_row], ignore_index=True)

    table_data.fillna('-', inplace=True)
    
    # Display the table
    st.table(table_data)

else:
    col3.warning('No data available for the selected filters.')


st.markdown("""
    <b>Observação:</b> Previsões realizadas com dados extraídos do Relatório Resumido de Execução Orçamentária (RREO) até o 6º bimestre de 2023 no Sistema de Informações Contábeis e Fiscais do Setor Público Brasileiro (SICONFI). 
    [Link](https://siconfi.tesouro.gov.br/)
    """, unsafe_allow_html=True)