Spaces:
Running
Running
| import streamlit as st | |
| import pandas as pd | |
| import numpy as np | |
| import io | |
| # Page Config | |
| st.set_page_config(page_title="All Anomaly Analysis", layout="wide") | |
| st.title('Aplikasi Analisa THC Pinjaman dan Simpanan') | |
| st.markdown(""" | |
| ## File yang dibutuhkan | |
| 1. **Anomali Simpanan.xlsx** | |
| 2. **Anomali Pinjaman.xlsx** | |
| 3. **DbSimpanan.xlsx** | |
| - Hapus bagian header terlebih dahulu | |
| - Nama File harus DbSimpanan.xlsx dan sheet atau lembar nya IA_SimpananDB | |
| """) | |
| def format_no(no): | |
| try: | |
| if pd.notna(no): | |
| return f'{int(no):02d}.' | |
| else: | |
| return '' | |
| except (ValueError, TypeError): | |
| return str(no) | |
| def format_center(center): | |
| try: | |
| if pd.notna(center): | |
| return f'{int(center):03d}' | |
| else: | |
| return '' | |
| except (ValueError, TypeError): | |
| return str(center) | |
| def format_kelompok(kelompok): | |
| try: | |
| if pd.notna(kelompok): | |
| return f'{int(kelompok):02d}' | |
| else: | |
| return '' | |
| except (ValueError, TypeError): | |
| return str(kelompok) | |
| def load_data(uploaded_files): | |
| dfs = {} | |
| for file in uploaded_files: | |
| try: | |
| excel_file = pd.ExcelFile(file, engine='openpyxl') | |
| for sheet_name in excel_file.sheet_names: | |
| df = pd.read_excel(excel_file, sheet_name=sheet_name) | |
| key = f"{file.name}_{sheet_name}" | |
| dfs[key] = df | |
| st.success(f"File {file.name} berhasil diunggah.") | |
| except Exception as e: | |
| st.error(f"Terjadi kesalahan saat memproses file {file.name}: {str(e)}") | |
| return dfs | |
| def process_data(dfs): | |
| try: | |
| # Pinjaman | |
| df_pu = dfs['Anomali Pinjaman.xlsx_Anomali PU'][['ID','CEK KRITERIA']].rename(columns={'CEK KRITERIA':'PU'}) | |
| df_pu['PU'] = df_pu['PU'].replace({True: 0, False: 1}) | |
| df_pmb = dfs['Anomali Pinjaman.xlsx_Anomali PMB'][['ID','CEK KRITERIA']].rename(columns={'CEK KRITERIA':'PMB'}) | |
| df_pmb['PMB'] = df_pmb['PMB'].replace({True: 0, False: 1}) | |
| df_psa = dfs['Anomali Pinjaman.xlsx_Anomali PSA'][['ID','CEK KRITERIA','Sukarela Sesuai','Wajib Sesuai','Pensiun Sesuai']] | |
| df_psa[['CEK KRITERIA', 'Sukarela Sesuai', 'Wajib Sesuai', 'Pensiun Sesuai']] = df_psa[['CEK KRITERIA', 'Sukarela Sesuai', 'Wajib Sesuai', 'Pensiun Sesuai']].replace({True: 0, False: 1}) | |
| df_psa['PSA'] = df_psa[['CEK KRITERIA', 'Sukarela Sesuai', 'Wajib Sesuai', 'Pensiun Sesuai']].sum(axis=1) | |
| df_psa = df_psa[['ID','PSA']] | |
| df_prr = dfs['Anomali Pinjaman.xlsx_Anomali PRR'][['ID','CEK KRITERIA','Sukarela Sesuai','Wajib Sesuai','Pensiun Sesuai']] | |
| df_prr[['CEK KRITERIA', 'Sukarela Sesuai', 'Wajib Sesuai', 'Pensiun Sesuai']] = df_prr[['CEK KRITERIA', 'Sukarela Sesuai', 'Wajib Sesuai', 'Pensiun Sesuai']].replace({True: 0, False: 1}) | |
| df_prr['PRR'] = df_prr[['CEK KRITERIA', 'Sukarela Sesuai', 'Wajib Sesuai', 'Pensiun Sesuai']].sum(axis=1) | |
| df_prr = df_prr[['ID','PRR']] | |
| df_ptn = dfs['Anomali Pinjaman.xlsx_Anomali PTN'][['ID','SEMUA KRITERIA TERPENUHI']].rename(columns={'SEMUA KRITERIA TERPENUHI':'PTN'}) | |
| df_ptn['PTN'] = df_ptn['PTN'].replace({True: 0, False: 1}) | |
| df_arta = dfs['Anomali Pinjaman.xlsx_Anomali ARTA'][['ID','CEK KRITERIA']].rename(columns={'CEK KRITERIA':'ARTA'}) | |
| df_arta['ARTA'] = df_arta['ARTA'].replace({True: 0, False: 1}) | |
| df_dtp = dfs['Anomali Pinjaman.xlsx_Anomali DTP'][['ID','CEK KRITERIA']].rename(columns={'CEK KRITERIA':'DTP'}) | |
| df_dtp['DTP'] = df_dtp['DTP'].replace({True: 0, False: 1}) | |
| # Simpanan | |
| df_sukarela = dfs['Anomali Simpanan.xlsx_Sukarela'][['ID','Transaksi > 0 & ≠ Modus Sukarela']].rename(columns={'Transaksi > 0 & ≠ Modus Sukarela':'SUKARELA'}) | |
| df_sihara = dfs['Anomali Simpanan.xlsx_Sihara'][['ID','TRANSAKSI TIDAK SESUAI']].rename(columns={'TRANSAKSI TIDAK SESUAI':'HARI RAYA'}) | |
| df_pensiun = dfs['Anomali Simpanan.xlsx_Pensiun'][['ID','Anomali']].rename(columns={'Anomali':'PENSIUN'}) | |
| # Merge all data | |
| df_selected_all = dfs['DbSimpanan.xlsx_IA_SimpananDB'][['Client ID','Client Name','Center ID','Group ID','Meeting Day','Officer Name']].rename(columns={ | |
| 'Client ID':'ID', 'Client Name': 'NAMA', 'Center ID': 'CENTER', 'Group ID': 'KELOMPOK', 'Meeting Day': 'HARI', 'Officer Name': 'STAF' | |
| }) | |
| for df in [df_sukarela, df_pensiun, df_sihara, df_pu, df_pmb, df_psa, df_prr, df_ptn, df_arta, df_dtp]: | |
| df_selected_all = df_selected_all.merge(df, on='ID', how='left') | |
| df_selected_all = df_selected_all.fillna(0) | |
| anomali_columns = ['SUKARELA', 'PENSIUN', 'HARI RAYA', 'PU', 'PMB', 'PSA', 'PRR', 'PTN', 'ARTA', 'DTP'] | |
| df_selected_all['TOTAL ANOMALI'] = df_selected_all[anomali_columns].sum(axis=1) | |
| df_selected_all = df_selected_all[['ID', 'NAMA', 'CENTER', 'KELOMPOK', 'HARI', 'STAF'] + anomali_columns + ['TOTAL ANOMALI']] | |
| df_selected_all = df_selected_all.drop_duplicates(subset=['ID', 'NAMA']) | |
| # Pisahkan anomali simpanan dan pinjaman | |
| anomali_simpanan = ['SUKARELA', 'PENSIUN', 'HARI RAYA'] | |
| anomali_pinjaman = ['PU', 'PMB', 'PSA', 'PRR', 'PTN', 'ARTA', 'DTP'] | |
| df_selected_all['Total Anomali Simpanan'] = df_selected_all[anomali_simpanan].sum(axis=1) | |
| df_selected_all['Total Anomali Pinjaman'] = df_selected_all[anomali_pinjaman].sum(axis=1) | |
| # Buat dictionary untuk memetakan hari bahasa Indonesia ke bahasa Inggris | |
| hari_map = { | |
| 'SENIN': 'Monday', | |
| 'SELASA': 'Tuesday', | |
| 'RABU': 'Wednesday', | |
| 'KAMIS': 'Thursday', | |
| 'JUMAT': 'Friday', | |
| 'SABTU': 'Saturday', | |
| 'MINGGU': 'Sunday' | |
| } | |
| # Konversi kolom HARI ke bahasa Inggris | |
| df_selected_all['HARI_EN'] = df_selected_all['HARI'].map(hari_map) | |
| # Buat pivot table untuk setiap hari | |
| pivot_dfs = [] | |
| for day in ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']: | |
| sub_df = df_selected_all[df_selected_all['HARI_EN'] == day] | |
| if not sub_df.empty: | |
| pivot = sub_df.pivot_table( | |
| values=['Total Anomali Simpanan', 'Total Anomali Pinjaman'], | |
| index=['STAF', 'CENTER'], | |
| aggfunc='sum' | |
| ).reset_index() | |
| pivot.columns = [f'{day}_{col}' if col not in ['STAF', 'CENTER'] else col for col in pivot.columns] | |
| pivot_dfs.append(pivot) | |
| # Gabungkan semua pivot table | |
| if pivot_dfs: | |
| result = pivot_dfs[0] | |
| for df in pivot_dfs[1:]: | |
| result = result.merge(df, on=['STAF', 'CENTER'], how='outer') | |
| # Urutkan kolom | |
| column_order = ['STAF', 'CENTER'] | |
| day_map_rev = {'Monday': 'SENIN', 'Tuesday': 'SELASA', 'Wednesday': 'RABU', 'Thursday': 'KAMIS', 'Friday': 'JUMAT', 'Saturday': 'SABTU', 'Sunday': 'MINGGU'} | |
| for day_en, day_id in day_map_rev.items(): | |
| s_col = f'{day_en}_Total Anomali Simpanan' | |
| p_col = f'{day_en}_Total Anomali Pinjaman' | |
| if s_col in result.columns: column_order.append(s_col) | |
| if p_col in result.columns: column_order.append(p_col) | |
| result = result.reindex(columns=column_order) | |
| # Ganti nama kolom kembali ke bahasa Indonesia | |
| new_columns = [] | |
| for col in result.columns: | |
| temp_col = col | |
| for day_en, day_id in day_map_rev.items(): | |
| if day_en in temp_col: | |
| temp_col = temp_col.replace(day_en, day_id) | |
| new_columns.append(temp_col.replace('_', ' ')) | |
| result.columns = new_columns | |
| result = result.fillna(0) | |
| else: | |
| result = pd.DataFrame(columns=['STAF', 'CENTER']) | |
| return df_selected_all, result | |
| except Exception as e: | |
| st.error(f"Terjadi kesalahan saat memproses data: {str(e)}") | |
| return None, None | |
| def main(): | |
| uploaded_files = st.file_uploader("Unggah file Excel", accept_multiple_files=True, type=["xlsx"]) | |
| if uploaded_files: | |
| dfs = load_data(uploaded_files) | |
| required_files = [ | |
| 'Anomali Pinjaman.xlsx_Anomali PU', | |
| 'Anomali Pinjaman.xlsx_Anomali PMB', | |
| 'Anomali Pinjaman.xlsx_Anomali DTP', | |
| 'Anomali Pinjaman.xlsx_Anomali PSA', | |
| 'Anomali Pinjaman.xlsx_Anomali ARTA', | |
| 'Anomali Pinjaman.xlsx_Anomali PRR', | |
| 'Anomali Pinjaman.xlsx_Anomali PTN', | |
| 'Anomali Simpanan.xlsx_Sihara', | |
| 'Anomali Simpanan.xlsx_Pensiun', | |
| 'Anomali Simpanan.xlsx_Sukarela', | |
| 'DbSimpanan.xlsx_IA_SimpananDB' | |
| ] | |
| missing_files = [file for file in required_files if file not in dfs] | |
| if not missing_files: | |
| with st.spinner("Sedang memproses data..."): | |
| df_selected_all, result = process_data(dfs) | |
| if df_selected_all is not None and result is not None: | |
| st.success("Proses selesai!") | |
| tabs = st.tabs(["Data Anomali", "Pivot Analysis"]) | |
| with tabs[0]: | |
| st.write("Data Anomali Gabungan:") | |
| st.dataframe(df_selected_all) | |
| with tabs[1]: | |
| st.write("Analisis Pivot per Hari:") | |
| st.dataframe(result) | |
| st.divider() | |
| # Downloads | |
| col1, col2 = st.columns(2) | |
| with col1: | |
| buffer1 = io.BytesIO() | |
| with pd.ExcelWriter(buffer1, engine='xlsxwriter') as writer: | |
| df_selected_all.to_excel(writer, index=False, sheet_name='All Anomaly') | |
| st.download_button("Unduh Data Anomali.xlsx", buffer1.getvalue(), "Data_Anomali.xlsx") | |
| with col2: | |
| buffer2 = io.BytesIO() | |
| with pd.ExcelWriter(buffer2, engine='xlsxwriter') as writer: | |
| result.to_excel(writer, index=False, sheet_name='Pivot') | |
| st.download_button("Unduh Data Anomali Pivot.xlsx", buffer2.getvalue(), "Data_Anomali_Pivot.xlsx") | |
| else: | |
| st.warning(f"File/Sheet berikut belum lengkap: {', '.join(missing_files)}") | |
| if __name__ == "__main__": | |
| main() | |