GIZ/Development-Project-Synergy-Finder
2
1import streamlit as st2import pandas as pd3import io4import xlsxwriter5from scipy.sparse import load_npz6import pickle7from sentence_transformers import SentenceTransformer8from modules.multimatch_result_table import show_multi_table9from modules.singlematch_result_table import show_single_table10from modules.allprojects_result_table import show_all_projects_table11from functions.filter_multi_project_matching import filter_multi12from functions.filter_single_project_matching import filter_single13from functions.filter_all_project_matching import filter_all_projects14from functions.multi_project_matching import calc_multi_matches15from functions.same_country_filter import same_country_filter16from functions.single_project_matching import find_similar17import gc18 19# Catch DATA20# Load Similarity matrix21@st.cache_data22def load_sim_matrix():23 """24 !!! Similarities when matches between same orgas are allowed25 """26 loaded_matrix = load_npz("src/extended_similarities.npz")27 return loaded_matrix28 29# Load Non Similar Orga Matrix30def load_nonsameorga_sim_matrix():31 """32 !!! Similarities when matches between same orgas are NOT allowed33 """34 loaded_matrix = load_npz("src/extended_similarities_nonsimorga.npz")35 return loaded_matrix36 37# Load Projects DFs38@st.cache_data39def load_projects():40 def fix_faulty_descriptions(description): # In some BMZ projects there are duplicate descriptions 41 if description and ';' in description:42 parts = description.split(';')43 if len(parts) == 2 and parts[0].strip() == parts[1].strip():44 return parts[0].strip()45 return description46 47 orgas_df = pd.read_csv("src/projects/project_orgas.csv")48 region_df = pd.read_csv("src/projects/project_region.csv")49 sector_df = pd.read_csv("src/projects/project_sector.csv")50 status_df = pd.read_csv("src/projects/project_status.csv")51 texts_df = pd.read_csv("src/projects/project_texts.csv")52 53 projects_df = pd.merge(orgas_df, region_df, on='iati_id', how='inner')54 projects_df = pd.merge(projects_df, sector_df, on='iati_id', how='inner')55 projects_df = pd.merge(projects_df, status_df, on='iati_id', how='inner')56 projects_df = pd.merge(projects_df, texts_df, on='iati_id', how='inner')57 58 59 60 # Add regions (should have been done in the preprocessing instead of here, so is just a quick fix to be able to add the region filter)61 region_lookup_df = pd.read_csv('src/codelists/regions.csv', usecols=['alpha-2', 'region', 'sub-region'])62 63 projects_df['country_code'] = projects_df['country'].str.replace(';', '').str.strip()64 # Replace empty values in the 'country_code' column with 'Unknown'65 projects_df['country_code'] = projects_df['country_code'].fillna('Unknown')66 67 region_lookup_df['alpha-2'] = region_lookup_df['alpha-2'].str.strip()68 projects_df = pd.merge(projects_df, region_lookup_df[['alpha-2', 'region', 'sub-region']], left_on='country_code', right_on='alpha-2', how='left')69 70 projects_df.rename(columns={'region': 'continent', 'sub-region': 'region'}, inplace=True)71 projects_df['continent'] = projects_df['continent'].fillna('Unknown')72 projects_df['region'] = projects_df['region'].fillna('Unknown')73 74 75 # Fix faulty descriptions for BMZ projects76 bmz_mask = projects_df['orga_abbreviation'].str.lower() == 'bmz'77 projects_df.loc[bmz_mask, 'description_main'] = projects_df.loc[bmz_mask, 'description_main'].apply(fix_faulty_descriptions)78 79 # Add Project Link column80 projects_df['Project Link'] = projects_df['iati_id'].apply(81 lambda x: f'https://d-portal.org/ctrack.html#view=act&aid={x}'82 )83 84 # Create necessary columns for consistency85 projects_df['crs_3_code_list'] = projects_df['crs_3_name'].apply(86 lambda x: [""] if pd.isna(x) else (str(x).split(";")[:-1] if str(x).endswith(";") else str(x).split(";"))87 )88 projects_df['crs_5_code_list'] = projects_df['crs_5_name'].apply(89 lambda x: [""] if pd.isna(x) else (str(x).split(";")[:-1] if str(x).endswith(";") else str(x).split(";"))90 )91 projects_df['sdg_list'] = projects_df['sgd_pred_code'].apply(92 lambda x: [""] if pd.isna(x) else (str(x).split(";")[:-1] if str(x).endswith(";") else str(x).split(";"))93 )94 95 # Ensure country_flag is set to None if country_name is missing or "NA"96 projects_df['country_flag'] = projects_df.apply(97 lambda row: None if pd.isna(row['country_name']) or row['country_name'] == "NA" else row['country_flag'],98 axis=199 )100 101 iati_search_list = [f'{row.iati_id}' for row in projects_df.itertuples()]102 title_search_list = [f'{row.title_main} ({row.orga_abbreviation.upper()})' for row in projects_df.itertuples()]103 104 return projects_df, iati_search_list, title_search_list105 106 107# Load CRS 3 data108@st.cache_data109def getCRS3():110 # Read in CRS3 CODELISTS111 crs3_df = pd.read_csv('src/codelists/crs3_codes.csv')112 CRS3_CODES = crs3_df['code'].tolist()113 CRS3_NAME = crs3_df['name'].tolist()114 CRS3_MERGED = {f"{name} - {code}": code for name, code in zip(CRS3_NAME, CRS3_CODES)}115 return CRS3_MERGED116 117# Load CRS 5 data118@st.cache_data119def getCRS5():120 # Read in CRS3 CODELISTS121 crs5_df = pd.read_csv('src/codelists/crs5_codes.csv')122 CRS5_CODES = crs5_df['code'].tolist()123 CRS5_NAME = crs5_df['name'].tolist()124 CRS5_MERGED = {code: [f"{name} - {code}"] for name, code in zip(CRS5_NAME, CRS5_CODES)}125 return CRS5_MERGED126 127# Load SDG data128@st.cache_data129def getSDG():130 # Read in SDG CODELISTS131 sdg_df = pd.read_csv('src/codelists/sdg_goals.csv')132 SDG_NAMES = sdg_df['name'].tolist()133 return SDG_NAMES134 135@st.cache_data136def getCountry():137 # Read in countries from codelist138 country_df = pd.read_csv('src/codelists/country_codes_ISO3166-1alpha-2.csv')139 140 # Read in regions from codelist, keeping only the relevant columns141 region_lookup_df = pd.read_csv('src/codelists/regions.csv', usecols=['alpha-2', 'region', 'sub-region'])142 143 # Strip quotes from the 'Alpha-2 code' column in country_df144 country_df['Alpha-2 code'] = country_df['Alpha-2 code'].str.replace('"', '').str.strip()145 146 # Ensure no leading/trailing spaces in the 'alpha-2' column in region_lookup_df147 region_lookup_df['alpha-2'] = region_lookup_df['alpha-2'].str.strip()148 149 # Merge country and region dataframes on 'Alpha-2 code' from country_df and 'alpha-2' from region_lookup_df150 merged_df = pd.merge(country_df, region_lookup_df, how='left', left_on='Alpha-2 code', right_on='alpha-2')151 152 # Handle any missing regions or sub-regions153 merged_df['region'] = merged_df['region'].fillna('Unknown')154 merged_df['sub-region'] = merged_df['sub-region'].fillna('Unknown')155 156 # Extract necessary columns as lists157 COUNTRY_CODES = merged_df['Alpha-2 code'].tolist()158 COUNTRY_NAMES = merged_df['Country'].tolist()159 REGIONS = merged_df['region'].tolist()160 SUB_REGIONS = merged_df['sub-region'].tolist()161 162 # Create the original COUNTRY_OPTION_LIST without regions163 COUNTRY_OPTION_LIST = [f"{COUNTRY_NAMES[i]} ({COUNTRY_CODES[i]})" for i in range(len(COUNTRY_NAMES))]164 165 # Create a hierarchical filter structure for sub-regions166 sub_region_hierarchy = {}167 sub_region_to_region = {}168 for i in range(len(SUB_REGIONS)):169 sub_region = SUB_REGIONS[i]170 country = COUNTRY_CODES[i]171 region = REGIONS[i]172 if sub_region not in sub_region_hierarchy:173 sub_region_hierarchy[sub_region] = []174 sub_region_hierarchy[sub_region].append(country)175 176 # Map sub-regions to regions177 sub_region_to_region[sub_region] = region178 179 # Sort the subregions by regions180 sorted_sub_regions = sorted(sub_region_hierarchy.keys(), key=lambda x: sub_region_to_region[x])181 182 return COUNTRY_OPTION_LIST, sorted_sub_regions183 184# Call the function to load and display the country data185COUNTRY_OPTION_LIST, REGION_OPTION_LIST = getCountry()186 187 188# Load Sentence Transformer Model189@st.cache_resource190def load_model():191 model = SentenceTransformer('all-MiniLM-L6-v2')192 return model193 194# Load Embeddings195@st.cache_data196def load_embeddings_and_index():197 # Load embeddings198 with open("src/embeddings.pkl", "rb") as fIn:199 stored_data = pickle.load(fIn)200 embeddings = stored_data["embeddings"]201 return embeddings202 203# USE CACHE FUNCTIONS204sim_matrix = load_sim_matrix() # For similarities when matches between same orgas are allowed205nonsameorgas_sim_matrix = load_nonsameorga_sim_matrix() #For similarities when matches between same orgas are NOT allowed206projects_df, iati_search_list, title_search_list = load_projects()207 208CRS3_MERGED = getCRS3()209CRS5_MERGED = getCRS5()210SDG_NAMES = getSDG()211 212# LOAD MODEL FROM CACHE FOR SEMANTIC SEARCH213model = load_model()214embeddings = load_embeddings_and_index()215 216 217 218##################################219 220def show_landing_page():221 st.title("Development Project Synergy Finder")222 223 st.subheader("About")224 st.markdown("""225 Multiple international organizations have projects in the same field and region. These projects could collaborate or learn from each other to increase their impact if they were aware of one another. The Project Synergy Finder facilitates the search for similar projects across different development organizations and banks in three distinct ways. Note that this app is a prototype, results may be incomplete or inaccurate. """)226 st.markdown("<br><br>", unsafe_allow_html=True) # Add two line breaks227 228 st.subheader("Pages")229 st.markdown("""230 1. **📊 All Projects**: Displays all projects included in the analysis.231 *Example Use Case*: Show all World Bank and African Development Bank projects in East Africa working towards the Sustainable Development Goal of achieving gender equality.232 233 234 2. **🎯 Single-Project Matching**: Finds the top similar projects to a selected one.235 *Example Use Case*: Show projects in Eastern Europe that are similar to the "Second Irrigation and Drainage Improvement Project" by the World Bank.236 237 238 3. **🔍 Multi-Project Matching**: Searches for matching pairs of projects.239 *Example Use Case*: Show pairs of similar projects in the "Energy Policy" sector from different organizations within the same country.240 """)241 st.markdown("<br><br>", unsafe_allow_html=True) # Add two line breaks242 243 st.subheader("Data")244 st.markdown("""245 **IATI Data**: The data is sourced from the [IATI d-portal](https://d-portal.org/), providing project-level information. The International Aid Transparency Initiative (IATI) aims to enhance transparency and effectiveness in development cooperation by making data publicly accessible.246 247 **Data Update**: The data is updated irregularly, with the last retrieval on 10th May 2024.248 249 **Project Data**: Data from projects labeled as active during the last data retrieval are included. The data includes Project Title, Description, URL, Country, and Sector classification (CRS). The CRS5 and CRS3 classifications organize development cooperation into categories, with the 5-digit level providing more specific details within the broader 3-digit categories.250 251 **Organizations**: The tool currently includes projects from the following organizations:252 - **IAD**: Inter-American Development Bank253 - **ADB**: Asian Development Bank254 - **AfDB**: African Development Bank255 - **EIB**: European Investment Bank256 - **WB**: World Bank257 - **WBTF**: World Bank Trust Fund258 - **BMZ**: Federal Ministry for Economic Cooperation and Development (Germany)259 - **KfW**: KfW Development Bank (Germany)260 - **GIZ**: Deutsche Gesellschaft für Internationale Zusammenarbeit (Germany)261 - **AA**: German Federal Foreign Office (Germany)262 263 **Additional Data**: The Sustainable Development Goals (SDGs) are 17 UN goals aimed at achieving global sustainability, peace, and prosperity by 2030. The SDG categorization in this tool is AI-predicted based on project descriptions and titles using a [SDG Classifier](https://huggingface.co/jonas/bert-base-uncased-finetuned-sdg) trainded on the OSDG dataset.264 """)265 266 267##################################268 269 270def show_all_projects_page():271 # Define the page size at the beginning272 page_size = 30273 274 def reset_pagination():275 st.session_state.current_end_idx_all = page_size276 277 278 col1, col2, col3 = st.columns([10, 1, 10])279 with col1:280 st.subheader("Project Filter")281 282 st.session_state.crs5_option_disabled = True283 col1, col2, col3 = st.columns([10, 1, 10])284 with col1:285 # CRS 3 SELECTION286 crs3_option = st.multiselect(287 'CRS 3',288 CRS3_MERGED,289 placeholder="Select a CRS 3 code",290 on_change=reset_pagination,291 key='crs3_all_projects_page'292 )293 294 # CRS 5 SELECTION295 # Only enable crs5 select field when crs3 code is selected296 if crs3_option:297 st.session_state.crs5_option_disabled = False298 299 # Define list of crs5 codes depending on crs3 codes300 crs5_list = [txt[0].replace('"', "") for crs3_item in crs3_option for code, txt in CRS5_MERGED.items() if str(code)[:3] == str(crs3_item)[-3:]]301 302 # crs5 select field303 crs5_option = st.multiselect(304 'CRS 5',305 crs5_list,306 placeholder="Select a CRS 5 code",307 disabled=st.session_state.crs5_option_disabled,308 on_change=reset_pagination,309 key='crs5_all_projects_page'310 )311 312 # SDG SELECTION313 sdg_option = st.selectbox(314 label='Sustainable Development Goal (AI-predicted)',315 index=None,316 placeholder="Select a SDG",317 options=SDG_NAMES[:-1],318 on_change=reset_pagination,319 key='sdg_all_projects_page'320 )321 322 with col3:323 # REGION SELECTION324 region_option = st.multiselect(325 'Regions',326 REGION_OPTION_LIST,327 placeholder="All regions selected",328 on_change=reset_pagination,329 key='regions_all_projects_page'330 )331 332 # COUNTRY SELECTION333 country_option = st.multiselect(334 'Countries',335 COUNTRY_OPTION_LIST,336 placeholder="All countries selected",337 on_change=reset_pagination,338 key='country_all_projects_page'339 )340 341 # ORGA SELECTION342 orga_abbreviation = projects_df["orga_abbreviation"].unique()343 orga_full_names = projects_df["orga_full_name"].unique()344 orga_list = [f"{orga_full_names[i]} ({orga_abbreviation[i].upper()})" for i in range(len(orga_abbreviation))]345 346 orga_option = st.multiselect(347 'Organizations',348 orga_list,349 placeholder="All organizations selected",350 on_change=reset_pagination,351 key='orga_all_projects_page'352 )353 354 # CRS CODE LIST355 crs3_list = [i[-3:] for i in crs3_option]356 crs5_list = [i[-5:] for i in crs5_option]357 358 # SDG CODE LIST359 if sdg_option is not None:360 sdg_str = sdg_option.split(".")[0]361 else:362 sdg_str = ""363 364 # COUNTRY CODES LIST365 country_code_list = [option[-3:-1] for option in country_option]366 367 # ORGANIZATION CODES LIST368 orga_code_list = [option.split("(")[1][:-1].lower() for option in orga_option]369 370 st.write("-----")371 372 # FILTER DF WITH SELECTED FILTER OPTIONS373 filtered_df = filter_all_projects(projects_df, country_code_list, orga_code_list, crs3_list, crs5_list, sdg_str, region_option)374 if isinstance(filtered_df, pd.DataFrame) and len(filtered_df) != 0:375 # Implement pagination376 if 'current_end_idx_all' not in st.session_state:377 st.session_state.current_end_idx_all = page_size378 379 end_idx = st.session_state.current_end_idx_all380 381 paginated_df = filtered_df.iloc[:end_idx]382 383 col1, col2 = st.columns([7, 3])384 with col1:385 st.subheader("Filtered Projects")386 with col2:387 # Add a download button for the paginated results388 def to_excel(df, sheet_name):389 # Rename columns390 df = df.rename(columns={391 "iati_id": "IATI Identifier",392 "title_main": "Title",393 "orga_abbreviation": "Organization",394 "description_main": "Description",395 "country_name": "Country",396 "sdg_list": "SDG List",397 "crs_3_code_list": "CRS 3 Codes",398 "crs_5_code_list": "CRS 5 Codes",399 "Project Link": "Project Link"400 })401 output = io.BytesIO()402 writer = pd.ExcelWriter(output, engine='xlsxwriter')403 df.to_excel(writer, index=False, sheet_name=sheet_name)404 writer.close()405 processed_data = output.getvalue()406 return processed_data407 408 # Direct download buttons409 columns_to_include = ["iati_id", "title_main", "orga_abbreviation", "description_main", "country_name", "sdg_list", "crs_3_code_list", "crs_5_code_list", "Project Link"]410 411 with st.expander("Excel Download"):412 # First 15 Results Button413 df_to_download_15 = filtered_df[columns_to_include].head(15)414 excel_data_15 = to_excel(df_to_download_15, "Sheet1")415 st.download_button(label="First 30 Projects", data=excel_data_15, file_name="First_15_All_Projects_Filtered.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")416 417 # All Results Button418 df_to_download_all = filtered_df[columns_to_include]419 excel_data_all = to_excel(df_to_download_all, "Sheet1")420 st.download_button(label="All", data=excel_data_all, file_name="All_All_Projects_Filtered.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")421 422 show_all_projects_table(projects_df, paginated_df)423 424 st.write(f"Showing 1 to {min(end_idx, len(filtered_df))} of {len(filtered_df)} projects")425 426 # Center the buttons and place them close together427 col1, col2, col3, col4, col5 = st.columns([2, 1, 1, 1, 2])428 with col2:429 if st.button('Show More', key='show_more'):430 st.session_state.current_end_idx_all = min(end_idx + page_size, len(filtered_df))431 st.experimental_rerun()432 with col4:433 if st.button('Show Less', key='show_less') and end_idx > page_size:434 st.session_state.current_end_idx_all = max(end_idx - page_size, page_size)435 st.experimental_rerun()436 437 else:438 st.write("-----")439 col1, col2, col3 = st.columns([1, 1, 1])440 with col2:441 st.write(" ")442 st.markdown("<span style='color: red'>There are no results for the applied filter. Try another filter!</span>", unsafe_allow_html=True)443 444 del crs3_list, crs5_list, sdg_str, filtered_df445 gc.collect()446 447 448 449##################################450 451def show_single_matching_page():452 # Define the page size at the beginning453 page_size = 15454 455 def reset_pagination():456 st.session_state.current_end_idx_single = page_size457 458 with st.expander("Explanation"):459 st.caption("""460 Single Project Matching enables you to choose an individual project using either the project IATI ID or title, to display projects most similar to it.461 462 **Similarity Score**:463 - Similarity ranges from 0 to 100 (identical projects score 100%), and is calculated based on 464 - Text similarity of project description and title (MiniLMM & Cosine Similiarity).465 - Matching of SDGs (AI-predicted).466 - Matching of CRS-3 & CRS-5 sector codes.467 - Components are weighted to give a normalized score. 468 469 Note that this app is a prototype, results may be incomplete or inaccurate.470 """)471 472 col1, col2, col3 = st.columns([10, 1, 10])473 with col1:474 st.subheader("Reference Project")475 st.caption("""476 Select a reference project either by its title or IATI ID. 477 """)478 with col3:479 st.subheader("Filters for Similar Projects")480 st.caption("""481 The filters are applied to find the similar projects and are independend of the selected reference project.482 """)483 484 col1, col2, col3 = st.columns([10, 1, 10])485 with col1:486 search_option = st.selectbox(487 label='Search with project title or IATI ID',488 index=0,489 placeholder=" ",490 options=["Search with IATI ID", "Search with project title"],491 on_change=reset_pagination,492 key='search_option_single'493 )494 495 if search_option == "Search with IATI ID":496 search_list = iati_search_list497 else:498 search_list = title_search_list499 500 project_option = st.selectbox(501 label='Search for a project',502 index=None,503 placeholder=" ",504 options=search_list,505 on_change=reset_pagination,506 key='project_option_single'507 )508 509 with col3:510 orga_abbreviation = projects_df["orga_abbreviation"].unique()511 orga_full_names = projects_df["orga_full_name"].unique()512 orga_list = [f"{orga_full_names[i]} ({orga_abbreviation[i].upper()})" for i in range(len(orga_abbreviation))]513 514 # REGION SELECTION515 region_option_s = st.multiselect(516 'Regions',517 REGION_OPTION_LIST,518 placeholder="All regions selected",519 on_change=reset_pagination,520 key='regions_single_projects_page'521 )522 523 country_option_s = st.multiselect(524 'Countries ',525 COUNTRY_OPTION_LIST,526 placeholder="All countries selected ",527 on_change=reset_pagination,528 key='country_option_single'529 )530 orga_option_s = st.multiselect(531 'Organizations',532 orga_list,533 placeholder="All organizations selected ",534 on_change=reset_pagination,535 key='orga_option_single'536 )537 538 different_orga_checkbox_s = st.checkbox("Only matches between different organizations ", value=True, on_change=reset_pagination, key='different_orga_checkbox_single')539 540 st.write("-----")541 542 if project_option:543 selected_project_index = search_list.index(project_option)544 country_code_list = [option[-3:-1] for option in country_option_s]545 orga_code_list = [option.split("(")[1][:-1].lower() for option in orga_option_s]546 547 TOP_X_PROJECTS = 1000548 with st.spinner('Please wait...'):549 filtered_df_s = filter_single(projects_df, country_code_list, orga_code_list, region_option_s)550 551 if isinstance(filtered_df_s, pd.DataFrame) and len(filtered_df_s) != 0:552 if different_orga_checkbox_s:553 with st.spinner('Please wait...'):554 top_projects_df = find_similar(selected_project_index, nonsameorgas_sim_matrix, filtered_df_s, TOP_X_PROJECTS)555 else:556 with st.spinner('Please wait...'):557 top_projects_df = find_similar(selected_project_index, sim_matrix, filtered_df_s, TOP_X_PROJECTS)558 559 # Implement show more, show less, and show all functionality560 if 'current_end_idx_single' not in st.session_state:561 st.session_state.current_end_idx_single = page_size562 563 end_idx = st.session_state.current_end_idx_single564 565 paginated_df = top_projects_df.iloc[:end_idx]566 567 # Add a download button for the paginated results568 def to_excel(df, sheet_name):569 # Rename columns570 df = df.rename(columns={571 "similarity": "Similarity Score",572 "iati_id": "IATI Identifier",573 "title_main": "Title",574 "orga_abbreviation": "Organization",575 "description_main": "Description",576 "country_name": "Country",577 "sdg_list": "SDG List",578 "crs_3_code_list": "CRS 3 Codes",579 "crs_5_code_list": "CRS 5 Codes",580 "Project Link": "Project Link"581 })582 output = io.BytesIO()583 writer = pd.ExcelWriter(output, engine='xlsxwriter')584 df.to_excel(writer, index=False, sheet_name=sheet_name)585 writer.close()586 processed_data = output.getvalue()587 return processed_data588 589 # Direct download buttons590 columns_to_include = ["similarity", "iati_id", "title_main", "orga_abbreviation", "description_main", "country_name", "sdg_list", "crs_3_code_list", "crs_5_code_list", "Project Link"]591 592 col1, col2 = st.columns([15, 5])593 with col2:594 with st.expander("Excel Download"):595 # First 15 Results Button596 df_to_download_15 = top_projects_df[columns_to_include].head(15)597 excel_data_15 = to_excel(df_to_download_15, "Sheet1")598 st.download_button(label="Download first 15 projects", data=excel_data_15, file_name="First_15_Single_Project_Matching_Results.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")599 df_to_download_all = top_projects_df[columns_to_include]600 excel_data_all = to_excel(df_to_download_all, "Sheet1")601 st.download_button(label="Download All", data=excel_data_all, file_name="All_Single_Project_Matching_Results.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")602 603 show_single_table(selected_project_index, projects_df, paginated_df)604 605 st.write(f"Showing 1 to {min(end_idx, len(top_projects_df))} of {len(top_projects_df)} projects")606 607 # Center the buttons and place them close together608 col1, col2, col3, col4, col5 = st.columns([2, 1, 1, 1, 2])609 with col2:610 if st.button('Show More'):611 st.session_state.current_end_idx_single = min(end_idx + page_size, len(top_projects_df))612 st.experimental_rerun()613 with col3:614 if st.button('Show Less') and end_idx > page_size:615 st.session_state.current_end_idx_single = max(end_idx - page_size, page_size)616 st.experimental_rerun()617 with col4:618 if st.button('Show All'):619 st.session_state.current_end_idx_single = len(top_projects_df)620 st.experimental_rerun()621 622 else:623 st.write("-----")624 col1, col2, col3 = st.columns([1, 1, 1])625 with col2:626 st.write(" ")627 st.markdown("<span style='color: red'>There are no results for this filter!</span>", unsafe_allow_html=True)628 gc.collect()629 630 631##################################632def show_multi_matching_page():633 # Define the page size at the beginning634 page_size = 30635 636 def reset_pagination():637 st.session_state.current_end_idx_multi = page_size638 639 with st.expander("Explanation"):640 st.caption("""641 Multi-Project Matching enables to find collaboration opportunities by identifying matching (=similar) projects.642 643 **How It Works**:644 - Filter projects by CRS sector, SDG, country, and organization.645 - Each match displays two similar projects side-by-side.646 647 **Similarity Score**:648 - Similarity ranges from 0 to 100 (Identical projects score 100%), and is calculated based on 649 - Text similarity of project description and title (MiniLMM & Cosine Similiarity).650 - Matching of SDGs (AI-predicted).651 - Matching of CRS-3 & CRS-5 sector codes.652 - Components are weighted to give a normalized score.653 654 Note that this app is a prototype, results may be incomplete or inaccurate.655 """)656 col1, col2, col3 = st.columns([10, 1, 10])657 with col1:658 st.subheader("Sector Filters")659 st.caption("""660 At least one sector filter must be applied to see results.661 """)662 with col3:663 st.subheader("Additional Filters")664 665 st.session_state.crs5_option_disabled = True666 col1, col2, col3 = st.columns([10, 1, 10])667 with col1:668 crs3_option = st.multiselect(669 'CRS 3',670 CRS3_MERGED,671 placeholder="Select a CRS 3 code",672 on_change=reset_pagination,673 key='crs3_multi_projects_page'674 )675 676 if crs3_option:677 st.session_state.crs5_option_disabled = False678 679 crs5_list = [txt[0].replace('"', "") for crs3_item in crs3_option for code, txt in CRS5_MERGED.items() if str(code)[:3] == str(crs3_item)[-3:]]680 681 crs5_option = st.multiselect(682 'CRS 5',683 crs5_list,684 placeholder="Select a CRS 5 code",685 disabled=st.session_state.crs5_option_disabled,686 on_change=reset_pagination,687 key='crs5_multi_projects_page'688 )689 690 sdg_option = st.selectbox(691 label='Sustainable Development Goal (AI-predicted)',692 index=None,693 placeholder="Select a SDG",694 options=SDG_NAMES[:-1],695 on_change=reset_pagination,696 key='sdg_multi_projects_page'697 )698 699 query = ""700 701 with col3:702 region_option = st.multiselect(703 'Regions',704 REGION_OPTION_LIST,705 placeholder="All regions selected",706 on_change=reset_pagination,707 key='regions_multi_projects_page'708 )709 country_option = st.multiselect(710 'Countries',711 COUNTRY_OPTION_LIST,712 placeholder="All countries selected",713 on_change=reset_pagination,714 key='country_multi_projects_page'715 )716 717 orga_abbreviation = projects_df["orga_abbreviation"].unique()718 orga_full_names = projects_df["orga_full_name"].unique()719 orga_list = [f"{orga_full_names[i]} ({orga_abbreviation[i].upper()})" for i in range(len(orga_abbreviation))]720 721 orga_option = st.multiselect(722 'Organizations',723 orga_list,724 placeholder="All organizations selected",725 on_change=reset_pagination,726 key='orga_multi_projects_page'727 )728 729 identical_country_checkbox = st.checkbox("Only matches where country is identical", value=True, on_change=reset_pagination, key='identical_country_checkbox_multi')730 different_orga_checkbox = st.checkbox("Only matches between different organizations", value=True, on_change=reset_pagination, key='different_orga_checkbox_multi')731 filtered_country_only_checkbox = st.checkbox("Only matches between filtered countries", value=True, on_change=reset_pagination, key='filtered_country_only_checkbox_multi')732 filtered_orga_only_checkbox = st.checkbox("Only matches between filtered organisations", value=True, on_change=reset_pagination, key='filtered_orga_only_checkbox_multi')733 734 735 # CRS CODE LIST736 crs3_list = [i[-3:] for i in crs3_option]737 crs5_list = [i[-5:] for i in crs5_option]738 739 # SDG CODE LIST740 sdg_str = sdg_option.split(".")[0] if sdg_option else ""741 742 # COUNTRY CODES LIST743 country_code_list = [option[-3:-1] for option in country_option]744 745 # ORGANIZATION CODES LIST746 orga_code_list = [option.split("(")[1][:-1].lower() for option in orga_option]747 748 # Handle case where no organizations are selected but the checkbox is checked749 if filtered_orga_only_checkbox and not orga_code_list:750 orga_code_list = projects_df["orga_abbreviation"].unique().tolist()751 752 # FILTER DF WITH SELECTED FILTER OPTIONS753 TOP_X_PROJECTS = 2000754 filtered_df = filter_multi(projects_df, crs3_list, crs5_list, sdg_str, country_code_list, orga_code_list, region_option, query, model, embeddings, TOP_X_PROJECTS)755 if isinstance(filtered_df, pd.DataFrame) and len(filtered_df) != 0:756 # FIND MATCHES757 # If only same country checkbox is activated758 if filtered_country_only_checkbox:759 with st.spinner('Please wait...'):760 compare_df = same_country_filter(projects_df, country_code_list)761 else:762 compare_df = projects_df763 764 if filtered_orga_only_checkbox:765 compare_df = compare_df[compare_df['orga_abbreviation'].isin(orga_code_list)]766 767 # if show only different orgas checkbox is activated768 with st.spinner('Please wait...'):769 p1_df, p2_df = calc_multi_matches(filtered_df, compare_df, nonsameorgas_sim_matrix if different_orga_checkbox else sim_matrix, TOP_X_PROJECTS, identical_country=identical_country_checkbox)770 771 # Sort by similarity before pagination772 p1_df = p1_df.sort_values(by='similarity', ascending=False)773 p2_df = p2_df.sort_values(by='similarity', ascending=False)774 775 # Implement pagination776 if 'current_end_idx_multi' not in st.session_state:777 st.session_state.current_end_idx_multi = page_size778 779 end_idx = st.session_state.current_end_idx_multi780 781 paginated_p1_df = p1_df.iloc[:end_idx]782 paginated_p2_df = p2_df.iloc[:end_idx]783 784 if not paginated_p1_df.empty and not paginated_p2_df.empty:785 col1, col2 = st.columns([10, 2])786 with col1:787 st.subheader("Matched Projects")788 with col2:789 # Add a download button for the paginated results790 def to_excel(p1_df, p2_df, sheet_name):791 # Rename columns792 p1_df = p1_df.rename(columns={793 "similarity": "Similarity Score",794 "iati_id": "IATI Identifier",795 "title_main": "Title",796 "orga_abbreviation": "Organization",797 "description_main": "Description",798 "country_name": "Country",799 "sdg_list": "SDG List",800 "crs_3_code_list": "CRS 3 Codes",801 "crs_5_code_list": "CRS 5 Codes",802 "Project Link": "Project Link"803 })804 p2_df = p2_df.rename(columns={805 "similarity": "Similarity Score",806 "iati_id": "IATI Identifier",807 "title_main": "Title",808 "orga_abbreviation": "Organization",809 "description_main": "Description",810 "country_name": "Country",811 "sdg_list": "SDG List",812 "crs_3_code_list": "CRS 3 Codes",813 "crs_5_code_list": "CRS 5 Codes",814 "Project Link": "Project Link"815 })816 817 combined_df = pd.concat([p1_df, pd.DataFrame([{}]), p2_df], ignore_index=True)818 combined_df.fillna('', inplace=True)819 820 empty_row = pd.DataFrame([{}])821 combined_df_list = []822 823 for idx in range(0, len(p1_df), 2):824 combined_df_list.append(p1_df.iloc[[idx]])825 combined_df_list.append(p2_df.iloc[[idx]])826 combined_df_list.append(empty_row)827 828 combined_df = pd.concat(combined_df_list, ignore_index=True)829 830 output = io.BytesIO()831 writer = pd.ExcelWriter(output, engine='xlsxwriter')832 combined_df.to_excel(writer, index=False, sheet_name=sheet_name)833 writer.close()834 processed_data = output.getvalue()835 return processed_data836 837 # Direct download buttons838 columns_to_include = ["similarity", "iati_id", "title_main", "orga_abbreviation", "description_main", "country_name", "sdg_list", "crs_3_code_list", "crs_5_code_list", "Project Link"]839 840 with st.expander("Excel Download"):841 # First 15 Results Button842 p1_df_to_download_15 = p1_df[columns_to_include].head(30)843 p2_df_to_download_15 = p2_df[columns_to_include].head(30)844 excel_data_15 = to_excel(p1_df_to_download_15, p2_df_to_download_15, "Sheet1")845 st.download_button(label="First 15 Matches", data=excel_data_15, file_name="First_15_Multi_Projects_Matching_Results.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")846 847 # All Results Button848 p1_df_to_download_all = p1_df[columns_to_include]849 p2_df_to_download_all = p2_df[columns_to_include]850 excel_data_all = to_excel(p1_df_to_download_all, p2_df_to_download_all, "Sheet1")851 st.download_button(label="All", data=excel_data_all, file_name="All_Multi_Projects_Matching_Results.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")852 853 show_multi_table(paginated_p1_df, paginated_p2_df)854 855 st.write(f"Showing 1 to {min(end_idx // 2, len(p1_df) // 2)} of {len(p1_df) // 2} matches")856 857 # Center the buttons and place them close together858 col1, col2, col3, col4, col5 = st.columns([2, 1, 1, 1, 2])859 with col2:860 if st.button('Show More', key='show_more_button'):861 st.session_state.current_end_idx_multi = min(end_idx + page_size, len(p1_df))862 st.experimental_rerun()863 with col3:864 if st.button('Show Less', key='show_less_button') and end_idx > page_size:865 st.session_state.current_end_idx_multi = max(end_idx - page_size, page_size)866 st.experimental_rerun()867 with col4:868 if st.button('Show All', key='show_all_button'):869 st.session_state.current_end_idx_multi = len(p1_df)870 st.experimental_rerun()871 872 del p1_df, p2_df873 else:874 st.write("-----")875 col1, col2, col3 = st.columns([1, 1, 1])876 with col2:877 st.write(" ")878 st.markdown("<span style='color: red'>There are no results for the applied filter. Try another filter!</span>", unsafe_allow_html=True)879 880 else:881 st.write("-----")882 col1, col2, col3 = st.columns([1, 1, 1])883 with col2:884 st.write(" ")885 st.markdown("<span style='color: red'>There are no results for the applied filter. Try another filter!</span>", unsafe_allow_html=True)886 887 del crs3_list, crs5_list, sdg_str, filtered_df888 gc.collect()