GUBRA_output_dens_Dan_Excel.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "48c9d2b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing all libraries needed\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "import time\n",
    "import operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8e8990e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Stanting timming process\n",
    "start = time.time()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "768a5c8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# saving the main directory\n",
    "main_directory = os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b764c18a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# subfolder_name = [f.name for f in os.scandir(main_directory) if f.is_dir()]\n",
    "subfolder_with_path = [\"\" for i in range(4)]\n",
    "subfolder_with_path[0] = main_directory + '/'+'01Activity_files'\n",
    "subfolder_with_path[1] = main_directory + '/'+'02Volume_files'\n",
    "subfolder_with_path[2] = main_directory + '/'+'03Volume_in_tissue_files'\n",
    "subfolder_with_path[3] = main_directory + '/'+'zzz_input_and_reference_files'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7927d0cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "###############################################################################################\n",
    "# Importing information from \"samples overview\", \"intensisties template\" and \"brain gubra map\"\n",
    "###############################################################################################\n",
    "\n",
    "# Changing directory to the \"zzz_input_and_reference_files\" folder\n",
    "os.chdir(subfolder_with_path[-1])\n",
    "\n",
    "\n",
    "# Reading \"sample_overview.csv\" file\n",
    "samples_overview = pd.read_csv(\"sample_overview.csv\", \n",
    "                               dtype={\"SAMPLE\": int,\n",
    "                                      \"hemisphere\": \"string\",\"marker\": \"string\",\n",
    "                                      \"TX_GROUP\": \"string\"})\n",
    "\n",
    "# Reading \"gubra_intensities_template.csv\" file\n",
    "gubra_intensities_template = pd.read_csv(\"gubra_intensities_template.csv\",\n",
    "                                   dtype={\"IDPath\": \"string\", \n",
    "                                          \"LabelID\": int, \n",
    "                                          \"raw\": \"string\", \n",
    "                                          \"LabelAbrv\": \"string\"})\n",
    "# Reading \"brain_allen_map.csv\" file\n",
    "brain_gubra_map = pd.read_csv(\"brain_gubra_map_fixed.csv\", \n",
    "                               dtype={\"IDPath\": \"string\", \"LabelAbrv\": \"string\",\n",
    "                                      \"raw\": \"string\",\"allen_1\": \"string\",\n",
    "                                      \"allen_2\": \"string\",\"allen_3\": \"string\",\n",
    "                                      \"allen_4\": \"string\",\"allen_5\": \"string\",\n",
    "                                      \"allen_6\": \"string\",\"allen_7\": \"string\",\n",
    "                                      \"fine\": \"string\",\"medium\": \"string\",\n",
    "                                      \"coarse\": \"string\", \"all\":\"string\"})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dcf1cc23",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Saving drugs and markers' names from the \"sample_overview\" file\n",
    "\n",
    "drugs = samples_overview['TX_GROUP'].unique()\n",
    "markers = samples_overview['marker'].unique()\n",
    "\n",
    "d = 0\n",
    "m = 0\n",
    "counters_names = []\n",
    "while (m < len(markers)):\n",
    "    while (d < len(drugs)):\n",
    "        counters_names.append(drugs[d] + '-' + markers[m])\n",
    "        d += 1\n",
    "    m += 1\n",
    "    d = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "10ad342d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Changing directory to the \"02Volume_files\" folder\n",
    "os.chdir(subfolder_with_path[1])\n",
    "\n",
    "#Counting number of files on \"02Volume_files\" folder\n",
    "list = os.listdir(subfolder_with_path[1]) \n",
    "number_files = len(list)\n",
    "number_volume_files = number_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4192ae1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using and timing  a loop to process all files and populate the main data-frame\n",
    "\n",
    "volume_data = [] #This array will contain df as elements\n",
    "\n",
    "#Staring counters\n",
    "\n",
    "i = 0 #General loop counter\n",
    "\n",
    "#Zeroing the counters \n",
    "counters = np.zeros(len(counters_names))\n",
    "\n",
    "#Creating counters array to contain sample's names as elements\n",
    "counters_samples = [[] for j in range(len(counters_names))]\n",
    "\n",
    "while i < number_files:\n",
    "\n",
    "    ##############################################\n",
    "    # Processing each file\n",
    "    ##############################################\n",
    "    \n",
    "    file = list[i]\n",
    "    # Saving sample's number\n",
    "    filename_segmented = file.split('_')\n",
    "    sample = filename_segmented[1]\n",
    "\n",
    "\n",
    "    # Reading file's data\n",
    "    header = [\"PIXEL_COUNT\"]\n",
    "    pixels_count_volume = pd.read_csv(file, names = header)\n",
    "    pixels_count_volume.insert(0, column = 'INTENSITY', value = range(0, len(pixels_count_volume))) \n",
    "\n",
    "    # Adding activity columns to intensitites template data-frame\n",
    "    output = gubra_intensities_template.merge(pixels_count_volume, how='left', left_on='LabelID', right_on='INTENSITY')\n",
    "    output = output.drop(columns=['INTENSITY'])\n",
    "    volume_label_name = 'VOLUME'\n",
    "    output = output.rename(columns = {'PIXEL_COUNT':volume_label_name})\n",
    "    output['VOLUME'] = output['VOLUME'].div(1000000).round(7)\n",
    "    \n",
    "    # Checking to which hemisphere the sample belongs to and erasing activity that belong to the other hemisphere\n",
    "    hemisphere = samples_overview['hemisphere'].loc[samples_overview['SAMPLE'] == int(sample)]\n",
    "    hemisphere = hemisphere.array\n",
    "    if hemisphere[0] == 'left':\n",
    "        output = output[output['LabelID'] > 20000]\n",
    "    if hemisphere[0] == 'right':\n",
    "        output = output[output['LabelID'] < 20000]\n",
    "\n",
    "    # Adding a column of the sample number\n",
    "    sample_column = np.empty(len(output))\n",
    "    sample = int(sample)\n",
    "    sample_column.fill(sample)\n",
    "    output['SAMPLE'] = sample_column\n",
    "    \n",
    "    # Merging activity and sample overview data-frames\n",
    "    output= pd.merge(output,samples_overview[['SAMPLE',\n",
    "                                             'hemisphere','marker',\n",
    "                                             'TX_GROUP']],on='SAMPLE',how='inner')\n",
    "    \n",
    "    #Adding a column with the sample's nickname\n",
    "    counters_index = counters_names.index(output['TX_GROUP'].iloc[0] + '-' + output['marker'].iloc[0])\n",
    "    if output['SAMPLE'].iloc[0] not in counters_samples[counters_index]:\n",
    "        counters[counters_index] += 1\n",
    "        counters_samples[counters_index].append(output['SAMPLE'].iloc[0])\n",
    "        nickname = 'v-' + output['TX_GROUP'].iloc[0] + '*' + str(int(counters[counters_index]))\n",
    "\n",
    "    nickname_column = np.empty(len(output))    \n",
    "    output['nickname'] = nickname_column\n",
    "    output['nickname'] = nickname\n",
    "    \n",
    "    # In case the sample has both hemispheres\n",
    "    if hemisphere[0] == 'both':\n",
    "        output['nickname'] = output['nickname'] + '-' + output[\"LabelAbrv\"].str[0]\n",
    "    \n",
    "    # Erasing the hemisphere label form the \"raw\" and the and \"labelabrv\" columns\n",
    "    output[\"raw\"] = output[\"raw\"].str.replace('left_','')\n",
    "    output[\"raw\"] = output[\"raw\"].str.replace('right_','')\n",
    "    output[\"LabelAbrv\"] = output[\"LabelAbrv\"].str.replace('L','',1)\n",
    "    output[\"LabelAbrv\"] = output[\"LabelAbrv\"].str.replace('R','',1)\n",
    "    \n",
    "    ##########################################################################################################################\n",
    "    \n",
    "    # store DataFrame in list\n",
    "    volume_data.append(output)\n",
    "    \n",
    "    i += 1\n",
    "    \n",
    "#Joinning all elements of the array in a dataframe\n",
    "volume_data = pd.concat(volume_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "28579936",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Changing directory to the \"03Volume_in_tissue_files\" folder\n",
    "os.chdir(subfolder_with_path[2])\n",
    "\n",
    "#Counting number of files on \"03Volume_in_tissue_files\" folder\n",
    "list = os.listdir(subfolder_with_path[2]) \n",
    "number_files = len(list)\n",
    "number_volintissue_files = number_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "531a8f72",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using and timing  a loop to process all files and populate the main data-frame\n",
    "\n",
    "volume_intissue_data = [] #This array will contain df as elements\n",
    "\n",
    "#Staring counters\n",
    "\n",
    "i = 0 #General loop counter\n",
    "\n",
    "#Zeroing the counters \n",
    "counters = np.zeros(len(counters_names))\n",
    "\n",
    "#Creating counters array to contain sample's names as elements\n",
    "counters_samples = [[] for j in range(len(counters_names))]\n",
    "\n",
    "    ##############################################\n",
    "    # Processing each file\n",
    "    ##############################################\n",
    "\n",
    "while i < number_files:\n",
    "\n",
    "\n",
    "    file = list[i]\n",
    "    # Saving sample's number \n",
    "    filename_segmented = file.split('_')\n",
    "    sample = filename_segmented[1]\n",
    "\n",
    "    # Reading file's data\n",
    "    header = [\"PIXEL_COUNT\"]\n",
    "    pixels_count_volume = pd.read_csv(file, names = header)\n",
    "    pixels_count_volume.insert(0, column = 'INTENSITY', value = range(0, len(pixels_count_volume))) \n",
    "\n",
    "    # Adding activity columns to intensitites template data-frame\n",
    "    output = gubra_intensities_template.merge(pixels_count_volume, how='left', left_on='LabelID', right_on='INTENSITY')\n",
    "    output = output.drop(columns=['INTENSITY'])\n",
    "    volume_label_name = 'VOLUME_IN_TISSUE'\n",
    "    output = output.rename(columns = {'PIXEL_COUNT':volume_label_name})\n",
    "    output['VOLUME_IN_TISSUE'] = output['VOLUME_IN_TISSUE'].div(1000000).round(6)\n",
    "    \n",
    "    # Checking to which hemisphere the sample belongs to and erasing activity that belong to the other hemisphere\n",
    "    hemisphere = samples_overview['hemisphere'].loc[samples_overview['SAMPLE'] == int(sample)]\n",
    "    hemisphere = hemisphere.array\n",
    "    if hemisphere[0] == 'left':\n",
    "        output = output[output['LabelID'] > 20000]\n",
    "    if hemisphere[0] == 'right':\n",
    "        output = output[output['LabelID'] < 20000]\n",
    "\n",
    "    # Adding a column of the sample number\n",
    "    sample_column = np.empty(len(output))\n",
    "    sample = int(sample)\n",
    "    sample_column.fill(sample)\n",
    "    output['SAMPLE'] = sample_column\n",
    "    \n",
    "    # Merging activity and sample overview data-frames\n",
    "    output= pd.merge(output,samples_overview[['SAMPLE',\n",
    "                                             'hemisphere','marker',\n",
    "                                             'TX_GROUP']],on='SAMPLE',how='inner')\n",
    "    \n",
    "    #Adding a column with the sample's nickname\n",
    "\n",
    "    counters_index = counters_names.index(output['TX_GROUP'].iloc[0] + '-' + output['marker'].iloc[0])\n",
    "    if output['SAMPLE'].iloc[0] not in counters_samples[counters_index]:\n",
    "        counters[counters_index] += 1\n",
    "        counters_samples[counters_index].append(output['SAMPLE'].iloc[0])\n",
    "        nickname = 'vit-' + output['TX_GROUP'].iloc[0] + '-' + str(int(counters[counters_index]))\n",
    "\n",
    "    nickname_column = np.empty(len(output))    \n",
    "    output['nickname'] = nickname_column\n",
    "    output['nickname'] = nickname\n",
    "    \n",
    "    # In case the sample has both hemispheres\n",
    "    if hemisphere[0] == 'both':\n",
    "        output['nickname'] = output['nickname'] + '-' + output[\"LabelAbrv\"].str[0]\n",
    "    \n",
    "    # Erasing the hemisphere label form the \"raw\" and the and \"labelabrv\" columns\n",
    "    output[\"raw\"] = output[\"raw\"].str.replace('left_','')\n",
    "    output[\"raw\"] = output[\"raw\"].str.replace('right_','')\n",
    "    output[\"LabelAbrv\"] = output[\"LabelAbrv\"].str.replace('L','',1)\n",
    "    output[\"LabelAbrv\"] = output[\"LabelAbrv\"].str.replace('R','',1)\n",
    "    \n",
    "    ##########################################################################################################################\n",
    "    \n",
    "    # store DataFrame in list\n",
    "    volume_intissue_data.append(output)\n",
    "    \n",
    "    i += 1\n",
    "    \n",
    "#Joinning all elements of the array in a dataframeo\n",
    "volume_intissue_data = pd.concat(volume_intissue_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a24e99db",
   "metadata": {},
   "outputs": [],
   "source": [
    "#calculating volume ratio\n",
    "volumes_data = pd.merge(volume_intissue_data,volume_data[['raw','VOLUME','SAMPLE']],on=('raw','SAMPLE'),how='left')\n",
    "volumes_data['volumes_ratio'] = volumes_data['VOLUME_IN_TISSUE'] / volumes_data['VOLUME'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "87d4edc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Changing directory to the \"01Activity_files\" folder\n",
    "os.chdir(subfolder_with_path[0])\n",
    "\n",
    "#Counting number of files on \"01Activity_files\" folder\n",
    "list = os.listdir(subfolder_with_path[0]) \n",
    "number_files = len(list)\n",
    "number_activity_files = number_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "de9ab5f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using and timing  a loop to process all files and populate the main data-frame\n",
    "\n",
    "activity_data = [] #This array will contain df as elements\n",
    "\n",
    "#Staring counters\n",
    "\n",
    "i = 0 #General loop counter\n",
    "\n",
    "#Zeroing the counters \n",
    "counters = np.zeros(len(counters_names))\n",
    "\n",
    "#Creating counters array to contain sample's names as elements\n",
    "counters_samples = [[] for j in range(len(counters_names))]\n",
    "\n",
    "\n",
    "    ##############################################\n",
    "    # Innitiates loop to process each file\n",
    "    ##############################################\n",
    "\n",
    "\n",
    "while i < number_files:\n",
    "\n",
    "    # Working with each file at a time\n",
    "    file = list[i]\n",
    "    \n",
    "    # Saving sample's number\n",
    "    filename_segmented = file.split('_')\n",
    "    sample = filename_segmented[1]\n",
    "\n",
    "    # Reading file's data\n",
    "    regions_count_activity_fracc = pd.read_csv(file,low_memory=False)\n",
    "    regions_count_activity_fracc = regions_count_activity_fracc[regions_count_activity_fracc.PIXEL_COUNT != \"PIXEL_COUNT\"]        \n",
    "\n",
    "    # Adding-up regions' fracctions\n",
    "    regions_fracc_1 = regions_count_activity_fracc[[\"INTENSITY_1\", \"INTENSITY_1_PERC\"]]\n",
    "    regions_fracc_2 = regions_count_activity_fracc[[\"INTENSITY_2\", \"INTENSITY_2_PERC\"]]\n",
    "    regions_fracc_3 = regions_count_activity_fracc[[\"INTENSITY_3\", \"INTENSITY_3_PERC\"]]\n",
    "    regions_fracc_1 = regions_fracc_1.rename(columns={\"INTENSITY_1\": \"INTENSITY\",\"INTENSITY_1_PERC\" : \"COUNTS\"})\n",
    "    regions_fracc_2 = regions_fracc_2.rename(columns={\"INTENSITY_2\": \"INTENSITY\",\"INTENSITY_2_PERC\" : \"COUNTS\"})\n",
    "    regions_fracc_3 = regions_fracc_3.rename(columns={\"INTENSITY_3\": \"INTENSITY\",\"INTENSITY_3_PERC\" : \"COUNTS\"})\n",
    "    total_region_activity = regions_fracc_1.append(regions_fracc_2, \n",
    "                                                   ignore_index=True).append(regions_fracc_3, ignore_index=True)\n",
    "    total_region_activity[\"COUNTS\"] = total_region_activity[\"COUNTS\"].astype(float)\n",
    "    total_region_activity = total_region_activity.groupby(['INTENSITY']).agg('sum').reset_index()\n",
    "#     total_region_activity[\"COUNTS\"] = total_region_activity[\"COUNTS\"].round(0)\n",
    "    total_region_activity[\"INTENSITY\"] = total_region_activity[\"INTENSITY\"].astype(int)\n",
    "        \n",
    "    # Adding activity columns to intensitites template data-frame\n",
    "    output = gubra_intensities_template.merge(total_region_activity, how='left', left_on='LabelID', right_on='INTENSITY')\n",
    "    output = output.drop(columns=['INTENSITY'])\n",
    "    activity_label_name = 'ACTIVITY'\n",
    "    output = output.rename(columns = {'COUNTS':activity_label_name})\n",
    "    \n",
    "    # Checking to which hemisphere the sample belongs to and erasing activity that belong to the other hemisphere\n",
    "    hemisphere = samples_overview['hemisphere'].loc[samples_overview['SAMPLE'] == int(sample)]\n",
    "    hemisphere = hemisphere.array\n",
    "    if hemisphere[0] == 'left':\n",
    "        output = output[output['LabelID'] > 20000]\n",
    "    if hemisphere[0] == 'right':\n",
    "        output = output[output['LabelID'] < 20000]\n",
    "\n",
    "    # Adding a column of the sample number\n",
    "    sample_column = np.empty(len(output))\n",
    "    sample = int(sample)\n",
    "    sample_column.fill(sample)\n",
    "    output['SAMPLE'] = sample_column\n",
    "    \n",
    "    # Merging activity and sample overview data-frames\n",
    "    output= pd.merge(output,samples_overview[['SAMPLE',\n",
    "                                             'hemisphere','marker',\n",
    "                                             'TX_GROUP']],on='SAMPLE',how='inner')\n",
    "    \n",
    "    \n",
    "    #Adding a column with the sample's nickname\n",
    "\n",
    "    counters_index = counters_names.index(output['TX_GROUP'].iloc[0] + '-' + output['marker'].iloc[0])\n",
    "    if output['SAMPLE'].iloc[0] not in counters_samples[counters_index]:\n",
    "        counters[counters_index] += 1\n",
    "        counters_samples[counters_index].append(output['SAMPLE'].iloc[0])\n",
    "        nickname = output['TX_GROUP'].iloc[0] + '-' + str(int(counters[counters_index]))\n",
    "\n",
    "    nickname_column = np.empty(len(output))    \n",
    "    output['nickname'] = nickname_column\n",
    "    output['nickname'] = nickname\n",
    "    \n",
    "    # In case the sample has both hemispheres\n",
    "    if hemisphere[0] == 'both':\n",
    "        output['nickname'] = output['nickname'] + '-' + output[\"LabelAbrv\"].str[0]\n",
    "\n",
    "    # Erasing the hemisphere label form the \"raw\" and the and \"labelabrv\" columns\n",
    "    output[\"raw\"] = output[\"raw\"].str.replace('left_','')\n",
    "    output[\"raw\"] = output[\"raw\"].str.replace('right_','')\n",
    "    output[\"LabelAbrv\"] = output[\"LabelAbrv\"].str.replace('L','',1)\n",
    "    output[\"LabelAbrv\"] = output[\"LabelAbrv\"].str.replace('R','',1)\n",
    "    \n",
    "    ##########################################################################################################################\n",
    "    \n",
    "    # store DataFrame in list\n",
    "    activity_data.append(output)\n",
    "    \n",
    "    i += 1\n",
    "    \n",
    "#Joinning all elements of the array in a dataframe\n",
    "activity_data = pd.concat(activity_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "681d6fbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replacing empty cells with zeros\n",
    "activity_data['ACTIVITY'].fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f0952787",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating density dataframe\n",
    "##Merging activity, volume and volume in tissue dataframes\n",
    "density = pd.merge(activity_data,\n",
    "                                  volumes_data[['raw','SAMPLE','VOLUME']],\n",
    "                                  on=['raw','SAMPLE'],\n",
    "                                  how='left')\n",
    "density = pd.merge(density,\n",
    "                           volume_intissue_data[['raw','SAMPLE','VOLUME_IN_TISSUE']],\n",
    "                           on=['raw','SAMPLE'],\n",
    "                           how='left')\n",
    "#Creating the density nickname (d_nickname)\n",
    "density['DENSITY'] = density['ACTIVITY'] / density['VOLUME_IN_TISSUE']\n",
    "\n",
    "# Changing directory to the main path\n",
    "os.chdir(main_directory)\n",
    "#Printing all columns\n",
    "density.to_csv('GUBRA_output_all_columns.csv', index=False)\n",
    "\n",
    "density['d_nickname'] = 'd-' + density['nickname']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "36b91efc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Changing directory to the main path\n",
    "os.chdir(main_directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "49c6bccf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\greci\\anaconda3\\lib\\site-packages\\pandas\\core\\reshape\\merge.py:648: UserWarning: merging between different levels can give an unintended result (1 levels on the left,2 on the right)\n",
      "  warnings.warn(msg, UserWarning)\n",
      "C:\\Users\\greci\\anaconda3\\lib\\site-packages\\pandas\\core\\generic.py:4153: PerformanceWarning: dropping on a non-lexsorted multi-index without a level parameter may impact performance.\n",
      "  obj = obj._drop_axis(labels, axis, level=level, errors=errors)\n"
     ]
    }
   ],
   "source": [
    "########Creating output for Dan's Excel analysis\n",
    "\n",
    "\n",
    "#Chaging SAMPLE elements to integer and then to string when adding the letter \"S\"\n",
    "density['SAMPLE_STR'] = density['SAMPLE'].astype(int)\n",
    "density['SAMPLE_STR'] = density['SAMPLE_STR'].astype(str)\n",
    "density['SAMPLE_STR'] = 'S' + density['SAMPLE_STR']\n",
    "\n",
    "#Pivoting dataframe to have sample as columns\n",
    "pivoted_density_Dan = pd.pivot_table(density, values='DENSITY', \n",
    "                               index=['raw','hemisphere',\n",
    "                                      'marker','SAMPLE'],\n",
    "                                      columns=['SAMPLE_STR','d_nickname'], aggfunc=np.sum)\n",
    "\n",
    "#Restarting index to activate'hemisphere','marker' as columns\n",
    "pivoted_density_Dan = pivoted_density_Dan.reset_index()\n",
    "\n",
    "#Adding GUBRA map columns\n",
    "gubra_and_density_Dan = pd.merge(brain_gubra_map,pivoted_density_Dan,on='raw',how='right')\n",
    "\n",
    "#Editing output\n",
    "gubra_and_density_Dan.rename(columns={('SAMPLE', ''): 'SAMPLE',\n",
    "                                       ('hemisphere', ''): 'hemisphere',\n",
    "                                       ('marker', ''): 'marker',\n",
    "                                       'raw': 'LabelName'}, inplace=True)\n",
    "gubra_and_density_Dan = gubra_and_density_Dan.drop(columns=['allen_1', 'allen_2',\n",
    "                                     'allen_3', 'allen_4',\n",
    "                                     'allen_5', 'allen_6',\n",
    "                                     'allen_7', 'fine',\n",
    "                                     'medium', 'coarse','all',\n",
    "                                    'SAMPLE', 'hemisphere','marker'])\n",
    "gubra_and_density_Dan = gubra_and_density_Dan.groupby('IDPath').apply(lambda x: x\\\n",
    "                                                                        .fillna(method='ffill')\\\n",
    "                                                                        .fillna(method='bfill')\\\n",
    "                                                                        .drop_duplicates()\\\n",
    "                                                                        .reset_index(drop=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f5aa4107",
   "metadata": {},
   "outputs": [],
   "source": [
    "#editing columns names from ('sample43', 'psilocybin') to S43_psilocybin\n",
    "columns_name_array = gubra_and_density_Dan.columns.values\n",
    "#dividing into two arrays, one with str and one with tuple\n",
    "#\"cna\" = column_name+array\n",
    "number_of_cols = columns_name_array.size\n",
    "cna_1 = columns_name_array[0:3]\n",
    "cna_2 = columns_name_array[-(number_of_cols - 3):]\n",
    "str = ''\n",
    "for item in cna_2:\n",
    "    str = str +'_'.join(item) + ' ' \n",
    "str = str.split(' ')[:-1]\n",
    "new_columns_name = np.concatenate([cna_1, str]) \n",
    "new_columns_name\n",
    "#Asingning new column names to the dataframe\n",
    "gubra_and_density_Dan = gubra_and_density_Dan.set_axis(new_columns_name, axis=1, inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "57b679af",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Ordering columns so all saline samples are fisrt\n",
    "cols = gubra_and_density_Dan.columns.tolist()\n",
    "cols_first_3 = cna_1\n",
    "cols_samples = cols[-(number_of_cols - 3):]\n",
    "\n",
    "def w_len(e):\n",
    "    len_e = len(e)\n",
    "    if operator.contains(e, \"aline\"):\n",
    "        len_e = 1\n",
    "    return len_e\n",
    "\n",
    "cols_samples.sort(key=w_len)\n",
    "new_cols = np.concatenate((cols_first_3, cols_samples), axis=None)\n",
    "\n",
    "gubra_and_density_Dan = gubra_and_density_Dan[new_cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1383a46a",
   "metadata": {},
   "outputs": [],
   "source": [
    "gubra_and_density_Dan.to_csv('GUBRA_output_dens_Dan_Excel.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6005378b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "106  Files processed \n",
      " \n",
      " Execution time: 130.29 seconds\n"
     ]
    }
   ],
   "source": [
    "#Ending timming process\n",
    "end = time.time()\n",
    "\n",
    "# Printing how many files were processed and how much time the process took\n",
    "files_processed = number_activity_files = number_volume_files + number_volintissue_files + number_activity_files\n",
    "print(files_processed,' Files processed ')\n",
    "print(' ')\n",
    "print(' Execution time:', round((end - start),2), 'seconds') "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}