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()