<jupyter_start><jupyter_text>NECESSARY IMPORTS<jupyter_code>#NECESSARY IMPORTS
import json
import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns
import numpy as np
from datetime import datetime

#To show all columns
pd.options.display.max_columns = None
!pip install nltk
import nltk
#To import all functions from the functions_t.ipnyb created file
!pip install nbimporter
import nbimporter
from functions_t import *<jupyter_output>Requirement already satisfied: nbimporter in c:\users\usuario\anaconda3\lib\site-packages (0.3.4)<jupyter_text>IMPORTING INITIATIVES DATAFRAMES  FROM THE THREE PLATFORMS<jupyter_code>INPUT_FILENAME = "./EU_projects.xlsx"

EU_initiatives = pd.read_excel(INPUT_FILENAME)
#EU_initiatives
INPUT_FILENAME = "./SP_initiatives.xlsx"

SP_initiatives = pd.read_excel(INPUT_FILENAME)
#SP_initiatives
INPUT_FILENAME = "./dataframe_BCN.xlsx"

BCN_initiatives = pd.read_excel(INPUT_FILENAME)
#BCN_initiatives<jupyter_output><empty_output><jupyter_text>COMPARING PROJECTS THAT ARE SHOWN IN MORE THAN ONE PLATFORM Print project names that are repeated in two of the platforms<jupyter_code>#See the projects that are posted in both European and Spanish platforms
repeated_projects_names_EU_SP_europename=[]
repeated_projects_names_EU_SP_spanishname=[]

for project_name in EU_initiatives.clean_name:
    for project_name_2 in SP_initiatives.clean_name:
        if project_name in project_name_2 and project_name_2!="Observado" :#since observado is present in more than one project name, and it is not the correct name of the original project, that is why it's skipped
            repeated_projects_names_EU_SP_spanishname.append(project_name_2)
            #print(project_name)
            #print(project_name_2)
repeated_projects_names_EU_SP_spanishname
#See the projects that are posted in both European and BCN platforms
repeated_projects_names_EU_BCN_europename=[]
repeated_projects_names_EU_BCN_bcnname=[]

for project_name in EU_initiatives.clean_name:
    for project_name_2 in BCN_initiatives.name:
        if project_name in project_name_2:
            repeated_projects_names_EU_BCN_europename.append(project_name)
            repeated_projects_names_EU_BCN_bcnname.append(project_name_2)
            #print(project_name)
            #print(project_name_2)
        
repeated_projects_names_EU_BCN_europename
#See the projects that are posted in both Spanish and BCN platforms
repeated_projects_names_SP_BCN_spanishname=[]
repeated_projects_names_SP_BCN_bcnname=[]
for project_name in SP_initiatives.clean_name:
    for project_name_2 in BCN_initiatives.name:
        if project_name in project_name_2 and project_name!="Observado" :
            repeated_projects_names_SP_BCN_spanishname.append(project_name)
            repeated_projects_names_SP_BCN_bcnname.append(project_name_2)
            #print(project_name)
            #print(project_name_2)
repeated_projects_names_SP_BCN_bcnname
#See the projects that are posted in the three platforms
repeated_projects_names_3_europeanname=[]
repeated_projects_names_3_spanishname=[]
repeated_projects_names_3_bcnname=[]
        
for project_name in EU_initiatives.clean_name:
    for project_name_2 in SP_initiatives.clean_name:
        for project_name_3 in BCN_initiatives.name:
            if project_name_2 in project_name_3 and project_name in project_name_3 and project_name_2!="Observado":
                repeated_projects_names_3_europeanname.append(project_name)
                repeated_projects_names_3_spanishname.append(project_name_2)
                repeated_projects_names_3_bcnname.append(project_name_3)
repeated_projects_names_3_bcnname<jupyter_output><empty_output><jupyter_text>Compare the information that is presented for each platform<jupyter_code>pd.options.display.max_colwidth=100

#SHOW HOW PROJECTS ARE POSTED IN THE EUROPEAN EU-CITIZEN.SCIENCE PLATFORM 
projects_repeated_EU=[]

for index, row in EU_initiatives.iterrows():
    if row['clean_name'] in repeated_projects_names_3_europeanname:
        print(row['clean_name'])
        projects_repeated_EU.append(row)
        
repeated_eu=pd.DataFrame(projects_repeated_EU)
repeated_eu.sort_values('clean_name')
projects_repeated_SP=[]

for index, row in SP_initiatives.iterrows():
    if row['clean_name'] in repeated_projects_names_3_spanishname:
        print(row['clean_name'])
        projects_repeated_SP.append(row)
        
repeated_sp=pd.DataFrame(projects_repeated_SP)
repeated_sp.sort_values('clean_name')
projects_repeated_BCN=[]

for index, row in BCN_initiatives.iterrows():
    if row['name'] in repeated_projects_names_3_bcnname:
        print(row['name'])
        projects_repeated_BCN.append(row)
        
repeated_bcn=pd.DataFrame(projects_repeated_BCN)
repeated_bcn.sort_values('name')<jupyter_output>Mosquito Alert
RiuNet
WeCount<jupyter_text>JOINING ALL PROJECTS TO GET A GLOBAL OVERVIEW<jupyter_code>MIX_projects = EU_initiatives[["name", "start_date", "end_date", "country", "description_resultwords", "needed_list", "topics_list", "status_", "topic_social", "topic_physical", "topic_life", "topic_applied", "duration_years", "education_in_text", "education_in_keywords_topic", "clean_name"]]
MIX_projects.insert(len(MIX_projects.columns), "origin_platform", "EU-citizen.science")
#ADD SPANISH PROJECTS
for index, row in SP_initiatives.iterrows():
    if row["clean_name"] not in repeated_projects_names_EU_SP_spanishname:
        new_row = {"name": row["name"], "start_date": row["start_date"], "end_date": row["end_date"],
                   "country": "ES", "description_resultwords": row["description_resultwords"], 
                   "needed_list": row["needed_list"], "topics_list": row["keywords_list"], "status_": None, "topic_social": row["topic_social"], 
                   "topic_physical": row["topic_physical"], "topic_life": row["topic_life"], "topic_applied": row["topic_applied"], "duration_years": row["duration_years"],  
                   "education_in_text": row["education_in_text"], "education_in_keywords_topic": (row["education_in_keywords"] |row["education_in_impact"]), "clean_name": row["clean_name"],
                  "origin_platform": "Observatorio CC"}
        MIX_projects=MIX_projects.append(new_row, ignore_index=True)
#ADD BARCELONIAN PROJECTS
for index, row in BCN_initiatives.iterrows():
    if row["name"] not in repeated_projects_names_SP_BCN_bcnname and row["name"] not in repeated_projects_names_EU_BCN_bcnname:
        if row["phone"]==True:
            needed="phone"
        else:
            needed=None
            
        if row["topic"]=="social":
            social=True
            physical=False
            life=False
            applied=False
        elif row["topic"]=="salud":
            social=False
            physical=False
            life=False
            applied=True
        elif row["topic"]=="ambiental" or  row["topic"]=="ambiental, salud":
            social=False
            physical=False
            life=True
            applied=False
        else:
            social=False
            physical=False
            life=False
            applied=False
            
            
        new_row = {"name": row["name"], "start_date": None, "end_date": None,
                   "country": "ES", "description_resultwords": row["description_resultwords"], 
                   "needed_list": needed, "topics_list": row["topic"], "status_": row["status"], "topic_social": social, 
                   "topic_physical": physical, "topic_life": life, "topic_applied": applied, "duration_years": None,  
                   "education_in_text": None, "education_in_keywords_topic": None, "clean_name": row["name"],
                  "origin_platform": "Oficina de CC"}
        MIX_projects=MIX_projects.append(new_row, ignore_index=True)
#EXPORT TO EXCEL
with pd.ExcelWriter('MIX_projects.xlsx', options={'strings_to_urls': False}) as writer:
        MIX_projects.to_excel(writer, 'MIX_projects')
MIX_projects[:5]<jupyter_output><empty_output><jupyter_text>JOINING ALL RESOURCES TO GET A GLOBAL OVERVIEW<jupyter_code>INPUT_FILENAME = "./mixed_resources.xlsx"

EU_resources = pd.read_excel(INPUT_FILENAME)
EU_resources[:3]
INPUT_FILENAME = "./SP_mix3.xlsx"

SP_resources = pd.read_excel(INPUT_FILENAME)
SP_resources[:3]
MIX_resources = EU_resources[["name", "category_", "related_Initiative"]]
MIX_resources.insert(len(MIX_resources.columns), "origin_platform", "EU-citizen.science")

#ADD SPANISH RESOURCES
for index, row in SP_resources.iterrows():
    new_row = {"name": row["name"], "category_": row["type"], "related_Initiative": row["related_Initiative"], "origin_platform": "Observatorio CC"}
    MIX_resources=MIX_resources.append(new_row, ignore_index=True)
#EXPORT TO EXCEL
with pd.ExcelWriter('MIX_resources.xlsx', options={'strings_to_urls': False}) as writer:
        MIX_resources.to_excel(writer, 'MIX_resources')
MIX_resources[:5]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09612802148612355, 'fraction_numerical': 0.004811996418979409, 'mean_word_length': 4.642045454545454, 'pattern_counts': {'":': 42, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '538969', 'license': 'MIT', 'n_tokens_mistral': 2990, 'n_tokens_neox': 2879, 'n_words': 556, 'path': 'scripts/analysing/Comparing_platforms.ipynb', 'repo_name': 'nvarasp/TFG_CS_Analysis', 'size': '8936'}