<jupyter_start><jupyter_text>Generate CSV-files from database Excel filesThis scripts converts the Excel files that are extracted from the original rodent basal ganglia database into CSV files that are used by the notebook that generates the database, `1. Create the Rodent Basal Ganglia Graph.ipynb`.The project provides these files, so it is not necessary to run these scripts<jupyter_code>## REGIONS
import pandas as pd

files = [
    ("../Data/xlsx/basal_ganglia_regions.xlsx", "regions_other", "../Data/csvs/basal_ganglia/regions/regions_other.csv", {}),
    ("../Data/xlsx/basal_ganglia_regions.xlsx", "region_records", "../Data/csvs/basal_ganglia/regions/region_records.csv", {"Original_framework":int, "Documentation_score":int}),
    ("../Data/xlsx/basal_ganglia_regions.xlsx", "original_region_records", "../Data/csvs/basal_ganglia/regions/original_region_records.csv", {}),
    ("../Data/xlsx/basal_ganglia_regions.xlsx", "regions", "../Data/csvs/basal_ganglia/regions/regions.csv", {"Nomenclature":int}),
    ("../Data/xlsx/basal_ganglia_regions.xlsx", "region_zones", "../Data/csvs/basal_ganglia/regions/region_zones.csv", {}),    
    ("../Data/xlsx/basal_ganglia_regions.xlsx", "nomenclatures", "../Data/csvs/basal_ganglia/regions/nomenclatures.csv",  {"Version":int, "Published":int}),    
    ("../Data/xlsx/basal_ganglia_regions.xlsx", "nomenclatures_other", "../Data/csvs/basal_ganglia/regions/nomenclatures_other.csv", {"Version":int, "Published":int, "Strain": int}),    
    ("../Data/xlsx/basal_ganglia_regions.xlsx", "BAMS_region_mapping", "../Data/csvs/basal_ganglia/regions/BAMS_region_mapping.csv", {}),    
]

for xlsx, sheet, csv, converters in files:
    data_xls = pd.read_excel(xlsx, sheet, index_col=None, converters = converters)
    data_xls.to_csv(csv, encoding='utf-8')
    
print("Converted all regions from xlsx to csv")


## Csvs of brain region, remove prefix in names
import pandas as pd
import re 

root = "../Data/csvs/basal_ganglia/regions"

# Load region csvs
df_region = pd.read_csv(root + "/regions.csv", dtype="object")
df_region_other = pd.read_csv(root + "/regions_other.csv", dtype="object")

# Remove the prefix of the name as it only adds a relation to the nomenclature which we again add later
df_region["Region_name"] = [re.sub(r'\w*\_','', str(x)) for x in df_region['Region_name']]
df_region_other["Region_name"] = [re.sub(r'\w*\_','', str(x)) for x in df_region_other['Region_name']]

df_region["Region_name"] = [ (x[0].upper() + x[1:]) for x in df_region["Region_name"]]

# Store in common csv
df_region.to_csv(root + "/regions.csv", encoding='utf-8')
df_region_other.to_csv(root + "/regions_other.csv", encoding='utf-8')

print("Csv with all regions fixed for regions.csv and regions_other.csv")
## SUBJECTS
import pandas as pd

files = [
    ("../Data/xlsx/basal_ganglia_subjects.xlsx", "Age_categories", "../Data/csvs/basal_ganglia/subjects/age_categories.csv", {}),
    ("../Data/xlsx/basal_ganglia_subjects.xlsx", "Sex", "../Data/csvs/basal_ganglia/subjects/sex.csv", {}),
    ("../Data/xlsx/basal_ganglia_subjects.xlsx", "Species", "../Data/csvs/basal_ganglia/subjects/species.csv", {}),
    ("../Data/xlsx/basal_ganglia_subjects.xlsx", "Specimens", "../Data/csvs/basal_ganglia/subjects/specimens.csv", {"Experiment_ID":int}),
    ("../Data/xlsx/basal_ganglia_subjects.xlsx", "Strains", "../Data/csvs/basal_ganglia/subjects/strains.csv", {}),    
    ("../Data/xlsx/basal_ganglia_subjects.xlsx", "Substrains", "../Data/csvs/basal_ganglia/subjects/substrains.csv", {"Strain_name":int}),    
    ("../Data/xlsx/basal_ganglia_subjects.xlsx", "Transgenic_lines", "../Data/csvs/basal_ganglia/subjects/transgenic_lines.csv", {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "derived_ddr_specimen", "../Data/csvs/basal_ganglia/subjects/derived_ddr_specimen.csv", {"analysisId": int, "SpecimenId":int, "Species":int, "Strain":int, "Substrain":int, "Transgenic_line_name":int, "Sex":int, "ageId":int})
]

for xlsx, sheet, csv, converters in files:
    data_xls = pd.read_excel(xlsx, sheet, index_col=None, converters = converters)
    data_xls.to_csv(csv, encoding='utf-8')
    
print("Converted all subjects from xlsx to csv")
## EXPERIMENTS
import pandas as pd

files = [
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Calculations", "../Data/csvs/basal_ganglia/experiments/calculations.csv", {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Cell_morphologies", "../Data/csvs/basal_ganglia/experiments/cell_morphologies.csv", {"Region_zone":int}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Derived_data_EMdetails", "../Data/csvs/basal_ganglia/experiments/derived_data_EMdetails.csv", {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Derived_data_LFMdetails", "../Data/csvs/basal_ganglia/experiments/derived_data_LFMdetails.csv", {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Derived_data_records", "../Data/csvs/basal_ganglia/experiments/derived_data_records.csv", {"Number_of_animals":int, "Cell_type_putative":int, "Object_of_interest":int, "Visualization_method":int }),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Distributions", "../Data/csvs/basal_ganglia/experiments/distributions.csv",  {"Stereology_details_record":int, "Related_quantitation":int, "Software":int, "Cellular_region":int}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Electron_microscopy_details", "../Data/csvs/basal_ganglia/experiments/electron_microscopy_details.csv", {"Magnification":int}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Experiments", "../Data/csvs/basal_ganglia/experiments/experiments.csv",  {"Strain":int, "Substrain":int, "Transgenic_line_name":int, "Sex":int, "Age_lower_limit":int, "Age_upper_limit":int, "Age":int, "Weight_lower_limit":int, "Weight_upper_limit":int, "Anaesthetic":int, "Perfusion_fix_medium":int}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Light_fluorescence_microscopy_d", "../Data/csvs/basal_ganglia/experiments/light_fluorescence_microscopy_details.csv",  {}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Microscopes", "../Data/csvs/basal_ganglia/experiments/microscopes.csv",  {}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Quantitations", "../Data/csvs/basal_ganglia/experiments/quantitations.csv",  {"Region_zone":int,"Cellular_target_region":int,"Cellular_target_ID":int, "Software":int,"Stereology_details_record":int}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Reporter_incubations", "../Data/csvs/basal_ganglia/experiments/reporter_incubations.csv",  {}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Reporter_labels", "../Data/csvs/basal_ganglia/experiments/reporter_labels.csv",  {}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Reporter_targets", "../Data/csvs/basal_ganglia/experiments/reporter_targets.csv",  {}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "ReporterType", "../Data/csvs/basal_ganglia/experiments/reporter_types.csv",  {}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Reporters", "../Data/csvs/basal_ganglia/experiments/reporters.csv",  {"Target":int, "Label":int}),    
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Sectioning_details", "../Data/csvs/basal_ganglia/experiments/sectioning_details.csv",  {"Section_thickness":int,"Sectioning_instrument":int}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Sectioning_instruments", "../Data/csvs/basal_ganglia/experiments/sectioning_instruments.csv",  {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Single_cell_labelings", "../Data/csvs/basal_ganglia/experiments/single_cell_labelings.csv",  {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Softwares", "../Data/csvs/basal_ganglia/experiments/softwares.csv",  {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Solutions", "../Data/csvs/basal_ganglia/experiments/solutions.csv",  {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Specimen_treatments", "../Data/csvs/basal_ganglia/experiments/specimen_treatments.csv",  {"Specimen_ID":int,"Solution":int}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Stereology_details", "../Data/csvs/basal_ganglia/experiments/stereology_details.csv",  {"Disector_height":int, "Investigated_sections":int, "Investigated_fields":int, "Counted_objects":int}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "Visualization_protocols", "../Data/csvs/basal_ganglia/experiments/visualization_protocols.csv",  {}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "derived_ddr_reporters", "../Data/csvs/basal_ganglia/experiments/derived_ddr_reporters.csv", {"analysisId": int, "SpecimenId":int, "reporterId":int}), 
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "derived_ddr_sectioning_details", "../Data/csvs/basal_ganglia/experiments/derived_ddr_sectioning_details.csv", {"analysisId": int, "SpecimenId":int, "sectioningDetailsId":int}),
    ("../Data/xlsx/basal_ganglia_experiments.xlsx", "derived_ddr_experiments", "../Data/csvs/basal_ganglia/experiments/derived_ddr_experiments.csv", {"analysisId": int, "specimenId":int, "analysisId":int})
]

for xlsx, sheet, csv, converters in files:
    data_xls = pd.read_excel(xlsx, sheet, index_col=None, converters = converters)
    data_xls.to_csv(csv, encoding='utf-8')
    
print("Converted all experiments from xlsx to csv")
## SOURCES
import pandas as pd

files = [
    ("../Data/xlsx/basal_ganglia_sources.xlsx", "Sources", "../Data/csvs/basal_ganglia/sources/sources.csv", {"Source_publication_year":int, "Source_origin":int}),
    ("../Data/xlsx/basal_ganglia_sources.xlsx", "Source_origins_lookup", "../Data/csvs/basal_ganglia/sources/source_origins_lookup.csv", {}),
    ("../Data/xlsx/basal_ganglia_sources.xlsx", "Considered_papers", "../Data/csvs/basal_ganglia/sources/considered_papers.csv", {"Published":int, "Journal":int}),
    ("../Data/xlsx/basal_ganglia_sources.xlsx", "Considered_papers_desicions", "../Data/csvs/basal_ganglia/sources/considered_papers_desicions.csv", {}),
     ("../Data/xlsx/basal_ganglia_sources.xlsx", "Journals", "../Data/csvs/basal_ganglia/sources/journals.csv", {}),
     ("../Data/xlsx/basal_ganglia_sources.xlsx", "Repositories", "../Data/csvs/basal_ganglia/sources/repositories.csv", {}),
]

for xlsx, sheet, csv, converters in files:
    data_xls = pd.read_excel(xlsx, sheet, index_col=None, converters = converters)
    data_xls.to_csv(csv, encoding='utf-8')

print("Converted all sources from xlsx to csv")
## CELLS
import pandas as pd

files = [
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "Cell_phenotype_categories", "../Data/csvs/basal_ganglia/cells/cell_phenotype_categories.csv", {}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "Cell_phenotypes", "../Data/csvs/basal_ganglia/cells/cell_phenotypes.csv", {"Phenotype_category":int}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "Cell_type_classifications", "../Data/csvs/basal_ganglia/cells/cell_type_classifications.csv", {}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "Cell_types", "../Data/csvs/basal_ganglia/cells/cell_types.csv", {"Cell_class_membership":int}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "Cell_classes", "../Data/csvs/basal_ganglia/cells/cell_classes.csv", {"Cell_group_membership":int}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "Cell_groups", "../Data/csvs/basal_ganglia/cells/cell_groups.csv", {}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "Cellular_regions", "../Data/csvs/basal_ganglia/cells/cellular_regions.csv", {}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "Objects_of_interest", "../Data/csvs/basal_ganglia/cells/objects_of_interest.csv", {}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "cell_phenotype_type_categories", "../Data/csvs/basal_ganglia/cells/cell_phenotype_type_categories.csv", {}),
    ("../Data/xlsx/basal_ganglia_cells.xlsx", "celltypes_phenotypes", "../Data/csvs/basal_ganglia/cells/celltypes_phenotypes.csv", {}),
]

for xlsx, sheet, csv, converters in files:
    data_xls = pd.read_excel(xlsx, sheet, index_col=None, converters = converters)
    data_xls.to_csv(csv, encoding='utf-8')

print("Converted all Cells from xlsx to csv")
## Analysis similarities
import pandas as pd

files = [
    ("../Data/xlsx/analyses_similarity.xlsx", "rat", "../Data/csvs/graph/analyses_similarity_rat.csv", {"id1":int, "id2":int, "similarity":float}),
    ("../Data/xlsx/analyses_similarity.xlsx", "mouse", "../Data/csvs/graph/analyses_similarity_mouse.csv", {"id1":int, "id2":int, "similarity":float}),
    ("../Data/xlsx/analyses_similarity.xlsx", "rat2", "../Data/csvs/graph/analyses_similarity_rat_2.csv", {"id1":int, "id2":int, "similarity":float}),
    ("../Data/xlsx/analyses_similarity.xlsx", "rat_all", "../Data/csvs/graph/analyses_similarity_rat_all.csv", {"id1":int, "id2":int, "similarity":float}),
    ("../Data/xlsx/analyses_similarity.xlsx", "mouse_all", "../Data/csvs/graph/analyses_similarity_mouse_all.csv", {"id1":int, "id2":int, "similarity":float}),

]

for xlsx, sheet, csv, converters in files:
    data_xls = pd.read_excel(xlsx, sheet, index_col=None, converters = converters)
    data_xls.to_csv(csv, encoding='utf-8')

print("Converted all Cells from xlsx to csv")<jupyter_output>Converted all Cells from xlsx to csv
{'fraction_non_alphanumeric': 0.18355813427774847, 'fraction_numerical': 0.0017331022530329288, 'mean_word_length': 5.4458474987858185, 'pattern_counts': {'":': 85, '<': 4, '<?xml version=': 0, '>': 4, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '667391', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 5145, 'n_tokens_neox': 4921, 'n_words': 593, 'path': '3. Initial file generation of CSVs for the graph generation/basal_ganglia_xlsx_to_csv.ipynb', 'repo_name': 'marenpg/jupyter_basal_ganglia', 'size': '13271'}