asnr_data_management.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ASNR Reliablility and Reproducibility in Neurorehabilitation Research\n",
    "# Data Management Workshop\n",
    "# Sook-Lei Liew, PhD, OTR/L\n",
    "# sliew@usc.edu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# jupyter notebook shortcuts: http://maxmelnick.com/2016/04/19/python-beginner-tips-and-tricks.html\n",
    "# press \"escape\" to move from edit to command mode (green to blue cells)\n",
    "# press \"shift + enter\" to execute sections\n",
    "# pandas quick review: https://towardsdatascience.com/a-quick-introduction-to-the-pandas-python-library-f1b678f34673"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if errors, google how to install the libraries below (e.g., \"install os\" or \"install pandas\" or \"install numpy\")\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# initialize directories, variables\n",
    "\n",
    "#rootdir='/Users/sliew/data/20191016_asnr/'\n",
    "db_file='ATLAS_data.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Review Data Quality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#change directory to root dir\n",
    "#os.chdir(rootdir)\n",
    "df = pd.read_csv(db_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(229, 15)\n"
     ]
    }
   ],
   "source": [
    "#print dimensions of dataframe (check rows and columns)\n",
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INDI_Site_ID</th>\n",
       "      <th>INDI_Subject_ID</th>\n",
       "      <th>Session</th>\n",
       "      <th>Num_Strokes_LH_Cortical</th>\n",
       "      <th>Num_Strokes_LH_subcortical</th>\n",
       "      <th>Num_Strokes_RH_Cortical</th>\n",
       "      <th>Num_Strokes_RH_subcortical</th>\n",
       "      <th>Other_Location</th>\n",
       "      <th>Stroke_type</th>\n",
       "      <th>Primary_Stroke_Location</th>\n",
       "      <th>Primary_Stroke_Location_Hemisphere</th>\n",
       "      <th>Vascular_territory</th>\n",
       "      <th>PVH</th>\n",
       "      <th>DWMH</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31768</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Basal ganglia, corona radiata</td>\n",
       "      <td>Left</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31769</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Basal ganglia</td>\n",
       "      <td>Bilateral</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31770</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Frontal centrum semiovale, corona radiata</td>\n",
       "      <td>Left</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31771</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Corona radiata, basal ganglia</td>\n",
       "      <td>Right</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31772</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Corona radiata, basal ganglia</td>\n",
       "      <td>Right</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  INDI_Site_ID  INDI_Subject_ID Session  Num_Strokes_LH_Cortical  \\\n",
       "0        Site1            31768     t01                        0   \n",
       "1        Site1            31769     t01                        0   \n",
       "2        Site1            31770     t01                        0   \n",
       "3        Site1            31771     t01                        0   \n",
       "4        Site1            31772     t01                        0   \n",
       "\n",
       "   Num_Strokes_LH_subcortical  Num_Strokes_RH_Cortical  \\\n",
       "0                           2                        0   \n",
       "1                           1                        0   \n",
       "2                           1                        0   \n",
       "3                           0                        0   \n",
       "4                           0                        0   \n",
       "\n",
       "   Num_Strokes_RH_subcortical  Other_Location Stroke_type  \\\n",
       "0                           0               0     Embolic   \n",
       "1                           1               0     Embolic   \n",
       "2                           0               0     Embolic   \n",
       "3                           1               0     Embolic   \n",
       "4                           1               0     Embolic   \n",
       "\n",
       "                     Primary_Stroke_Location  \\\n",
       "0              Basal ganglia, corona radiata   \n",
       "1                              Basal ganglia   \n",
       "2  Frontal centrum semiovale, corona radiata   \n",
       "3              Corona radiata, basal ganglia   \n",
       "4              Corona radiata, basal ganglia   \n",
       "\n",
       "  Primary_Stroke_Location_Hemisphere Vascular_territory  PVH  DWMH  Notes  \n",
       "0                               Left            Lacunar    2     2    NaN  \n",
       "1                          Bilateral            Lacunar    2     2    NaN  \n",
       "2                               Left            Lacunar    2     3    NaN  \n",
       "3                              Right            Lacunar    1     2    NaN  \n",
       "4                              Right            Lacunar    1     1    NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#do a basic check of data columns and values\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INDI_Subject_ID</th>\n",
       "      <th>Num_Strokes_LH_Cortical</th>\n",
       "      <th>Num_Strokes_LH_subcortical</th>\n",
       "      <th>Num_Strokes_RH_Cortical</th>\n",
       "      <th>Num_Strokes_RH_subcortical</th>\n",
       "      <th>Other_Location</th>\n",
       "      <th>PVH</th>\n",
       "      <th>DWMH</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>229.000000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>31880.602620</td>\n",
       "      <td>0.179039</td>\n",
       "      <td>0.689956</td>\n",
       "      <td>0.135371</td>\n",
       "      <td>0.628821</td>\n",
       "      <td>0.139738</td>\n",
       "      <td>1.480349</td>\n",
       "      <td>1.414847</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>64.264081</td>\n",
       "      <td>0.447505</td>\n",
       "      <td>0.813771</td>\n",
       "      <td>0.355431</td>\n",
       "      <td>0.770765</td>\n",
       "      <td>0.359876</td>\n",
       "      <td>0.752661</td>\n",
       "      <td>0.809997</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>31768.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>31825.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>31882.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>31939.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>31987.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       INDI_Subject_ID  Num_Strokes_LH_Cortical  Num_Strokes_LH_subcortical  \\\n",
       "count       229.000000               229.000000                  229.000000   \n",
       "mean      31880.602620                 0.179039                    0.689956   \n",
       "std          64.264081                 0.447505                    0.813771   \n",
       "min       31768.000000                 0.000000                    0.000000   \n",
       "25%       31825.000000                 0.000000                    0.000000   \n",
       "50%       31882.000000                 0.000000                    1.000000   \n",
       "75%       31939.000000                 0.000000                    1.000000   \n",
       "max       31987.000000                 2.000000                    5.000000   \n",
       "\n",
       "       Num_Strokes_RH_Cortical  Num_Strokes_RH_subcortical  Other_Location  \\\n",
       "count               229.000000                  229.000000      229.000000   \n",
       "mean                  0.135371                    0.628821        0.139738   \n",
       "std                   0.355431                    0.770765        0.359876   \n",
       "min                   0.000000                    0.000000        0.000000   \n",
       "25%                   0.000000                    0.000000        0.000000   \n",
       "50%                   0.000000                    1.000000        0.000000   \n",
       "75%                   0.000000                    1.000000        0.000000   \n",
       "max                   2.000000                    5.000000        2.000000   \n",
       "\n",
       "              PVH        DWMH  Notes  \n",
       "count  229.000000  229.000000    0.0  \n",
       "mean     1.480349    1.414847    NaN  \n",
       "std      0.752661    0.809997    NaN  \n",
       "min      0.000000    0.000000    NaN  \n",
       "25%      1.000000    1.000000    NaN  \n",
       "50%      1.000000    1.000000    NaN  \n",
       "75%      2.000000    2.000000    NaN  \n",
       "max      3.000000    3.000000    NaN  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine basic data stats\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "INDI_Site_ID                            0\n",
       "INDI_Subject_ID                         0\n",
       "Session                                 0\n",
       "Num_Strokes_LH_Cortical                 0\n",
       "Num_Strokes_LH_subcortical              0\n",
       "Num_Strokes_RH_Cortical                 0\n",
       "Num_Strokes_RH_subcortical              0\n",
       "Other_Location                          0\n",
       "Stroke_type                             0\n",
       "Primary_Stroke_Location                 0\n",
       "Primary_Stroke_Location_Hemisphere      0\n",
       "Vascular_territory                      0\n",
       "PVH                                     0\n",
       "DWMH                                    0\n",
       "Notes                                 229\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check for missing values\n",
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop missing values for important colunns\n",
    "df=df.dropna(subset=['Stroke_type'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#fill remaining missing values with 'NA'\n",
    "df=df.fillna('NA')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#removing duplicate columns\n",
    "df=df.loc[:,~df.columns.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#sort the data by a column's value\n",
    "df=df.sort_values(by=['INDI_Subject_ID'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INDI_Site_ID</th>\n",
       "      <th>INDI_Subject_ID</th>\n",
       "      <th>Session</th>\n",
       "      <th>Num_Strokes_LH_Cortical</th>\n",
       "      <th>Num_Strokes_LH_subcortical</th>\n",
       "      <th>Num_Strokes_RH_Cortical</th>\n",
       "      <th>Num_Strokes_RH_subcortical</th>\n",
       "      <th>Other_Location</th>\n",
       "      <th>Stroke_type</th>\n",
       "      <th>Primary_Stroke_Location</th>\n",
       "      <th>Lesion_Hemisphere</th>\n",
       "      <th>Vascular_territory</th>\n",
       "      <th>PVH</th>\n",
       "      <th>DWMH</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31768</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Basal ganglia, corona radiata</td>\n",
       "      <td>Left</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31769</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Basal ganglia</td>\n",
       "      <td>Bilateral</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31770</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Frontal centrum semiovale, corona radiata</td>\n",
       "      <td>Left</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31771</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Corona radiata, basal ganglia</td>\n",
       "      <td>Right</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31772</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Corona radiata, basal ganglia</td>\n",
       "      <td>Right</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  INDI_Site_ID  INDI_Subject_ID Session  Num_Strokes_LH_Cortical  \\\n",
       "0        Site1            31768     t01                        0   \n",
       "1        Site1            31769     t01                        0   \n",
       "2        Site1            31770     t01                        0   \n",
       "3        Site1            31771     t01                        0   \n",
       "4        Site1            31772     t01                        0   \n",
       "\n",
       "   Num_Strokes_LH_subcortical  Num_Strokes_RH_Cortical  \\\n",
       "0                           2                        0   \n",
       "1                           1                        0   \n",
       "2                           1                        0   \n",
       "3                           0                        0   \n",
       "4                           0                        0   \n",
       "\n",
       "   Num_Strokes_RH_subcortical  Other_Location Stroke_type  \\\n",
       "0                           0               0     Embolic   \n",
       "1                           1               0     Embolic   \n",
       "2                           0               0     Embolic   \n",
       "3                           1               0     Embolic   \n",
       "4                           1               0     Embolic   \n",
       "\n",
       "                     Primary_Stroke_Location Lesion_Hemisphere  \\\n",
       "0              Basal ganglia, corona radiata              Left   \n",
       "1                              Basal ganglia         Bilateral   \n",
       "2  Frontal centrum semiovale, corona radiata              Left   \n",
       "3              Corona radiata, basal ganglia             Right   \n",
       "4              Corona radiata, basal ganglia             Right   \n",
       "\n",
       "  Vascular_territory  PVH  DWMH Notes  \n",
       "0            Lacunar    2     2    NA  \n",
       "1            Lacunar    2     2    NA  \n",
       "2            Lacunar    2     3    NA  \n",
       "3            Lacunar    1     2    NA  \n",
       "4            Lacunar    1     1    NA  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#renaming columns \n",
    "df.rename(columns={'Primary_Stroke_Location_Hemisphere':'Lesion_Hemisphere'}, inplace=True)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INDI_Site_ID</th>\n",
       "      <th>INDI_Subject_ID</th>\n",
       "      <th>Session</th>\n",
       "      <th>Num_Strokes_LH_Cortical</th>\n",
       "      <th>Num_Strokes_LH_subcortical</th>\n",
       "      <th>Num_Strokes_RH_Cortical</th>\n",
       "      <th>Num_Strokes_RH_subcortical</th>\n",
       "      <th>Other_Location</th>\n",
       "      <th>Stroke_type</th>\n",
       "      <th>Primary_Stroke_Location</th>\n",
       "      <th>Lesion_Hemisphere</th>\n",
       "      <th>Vascular_territory</th>\n",
       "      <th>PVH</th>\n",
       "      <th>DWMH</th>\n",
       "      <th>Notes</th>\n",
       "      <th>SITE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31768</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Basal ganglia, corona radiata</td>\n",
       "      <td>Left</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31769</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Basal ganglia</td>\n",
       "      <td>Bilateral</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31770</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Frontal centrum semiovale, corona radiata</td>\n",
       "      <td>Left</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31771</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Corona radiata, basal ganglia</td>\n",
       "      <td>Right</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31772</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Corona radiata, basal ganglia</td>\n",
       "      <td>Right</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  INDI_Site_ID  INDI_Subject_ID Session  Num_Strokes_LH_Cortical  \\\n",
       "0        Site1            31768     t01                        0   \n",
       "1        Site1            31769     t01                        0   \n",
       "2        Site1            31770     t01                        0   \n",
       "3        Site1            31771     t01                        0   \n",
       "4        Site1            31772     t01                        0   \n",
       "\n",
       "   Num_Strokes_LH_subcortical  Num_Strokes_RH_Cortical  \\\n",
       "0                           2                        0   \n",
       "1                           1                        0   \n",
       "2                           1                        0   \n",
       "3                           0                        0   \n",
       "4                           0                        0   \n",
       "\n",
       "   Num_Strokes_RH_subcortical  Other_Location Stroke_type  \\\n",
       "0                           0               0     Embolic   \n",
       "1                           1               0     Embolic   \n",
       "2                           0               0     Embolic   \n",
       "3                           1               0     Embolic   \n",
       "4                           1               0     Embolic   \n",
       "\n",
       "                     Primary_Stroke_Location Lesion_Hemisphere  \\\n",
       "0              Basal ganglia, corona radiata              Left   \n",
       "1                              Basal ganglia         Bilateral   \n",
       "2  Frontal centrum semiovale, corona radiata              Left   \n",
       "3              Corona radiata, basal ganglia             Right   \n",
       "4              Corona radiata, basal ganglia             Right   \n",
       "\n",
       "  Vascular_territory  PVH  DWMH Notes  SITE_ID  \n",
       "0            Lacunar    2     2    NA        1  \n",
       "1            Lacunar    2     2    NA        1  \n",
       "2            Lacunar    2     3    NA        1  \n",
       "3            Lacunar    1     2    NA        1  \n",
       "4            Lacunar    1     1    NA        1  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#reformatting column values (e.g., r001 as string to 1 as integer)\n",
    "df['SITE_ID']=df['INDI_Site_ID'].apply(lambda x: (x[4:]))\n",
    "df['SITE_ID']=pd.to_numeric(df['SITE_ID'])\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(229, 16)\n"
     ]
    }
   ],
   "source": [
    "#check shape of dataframe\n",
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>INDI_Site_ID</th>\n",
       "      <th>INDI_Subject_ID</th>\n",
       "      <th>Session</th>\n",
       "      <th>Num_Strokes_LH_Cortical</th>\n",
       "      <th>Num_Strokes_LH_subcortical</th>\n",
       "      <th>Num_Strokes_RH_Cortical</th>\n",
       "      <th>Num_Strokes_RH_subcortical</th>\n",
       "      <th>Other_Location</th>\n",
       "      <th>Stroke_type</th>\n",
       "      <th>Primary_Stroke_Location</th>\n",
       "      <th>Lesion_Hemisphere</th>\n",
       "      <th>Vascular_territory</th>\n",
       "      <th>PVH</th>\n",
       "      <th>DWMH</th>\n",
       "      <th>Notes</th>\n",
       "      <th>SITE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31768</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Basal ganglia, corona radiata</td>\n",
       "      <td>Left</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31769</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Basal ganglia</td>\n",
       "      <td>Bilateral</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31770</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Frontal centrum semiovale, corona radiata</td>\n",
       "      <td>Left</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31771</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Corona radiata, basal ganglia</td>\n",
       "      <td>Right</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Site1</td>\n",
       "      <td>31772</td>\n",
       "      <td>t01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Embolic</td>\n",
       "      <td>Corona radiata, basal ganglia</td>\n",
       "      <td>Right</td>\n",
       "      <td>Lacunar</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  INDI_Site_ID  INDI_Subject_ID Session  Num_Strokes_LH_Cortical  \\\n",
       "0        Site1            31768     t01                        0   \n",
       "1        Site1            31769     t01                        0   \n",
       "2        Site1            31770     t01                        0   \n",
       "3        Site1            31771     t01                        0   \n",
       "4        Site1            31772     t01                        0   \n",
       "\n",
       "   Num_Strokes_LH_subcortical  Num_Strokes_RH_Cortical  \\\n",
       "0                           2                        0   \n",
       "1                           1                        0   \n",
       "2                           1                        0   \n",
       "3                           0                        0   \n",
       "4                           0                        0   \n",
       "\n",
       "   Num_Strokes_RH_subcortical  Other_Location Stroke_type  \\\n",
       "0                           0               0     Embolic   \n",
       "1                           1               0     Embolic   \n",
       "2                           0               0     Embolic   \n",
       "3                           1               0     Embolic   \n",
       "4                           1               0     Embolic   \n",
       "\n",
       "                     Primary_Stroke_Location Lesion_Hemisphere  \\\n",
       "0              Basal ganglia, corona radiata              Left   \n",
       "1                              Basal ganglia         Bilateral   \n",
       "2  Frontal centrum semiovale, corona radiata              Left   \n",
       "3              Corona radiata, basal ganglia             Right   \n",
       "4              Corona radiata, basal ganglia             Right   \n",
       "\n",
       "  Vascular_territory  PVH  DWMH Notes  SITE_ID  \n",
       "0            Lacunar    2     2    NA        1  \n",
       "1            Lacunar    2     2    NA        1  \n",
       "2            Lacunar    2     3    NA        1  \n",
       "3            Lacunar    1     2    NA        1  \n",
       "4            Lacunar    1     1    NA        1  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check out changes\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save dataframe to csv (can be read in Matlab or R)\n",
    "df.to_csv('ATLAS_data_reformatted.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-22-9c6365d9b1a5>, line 8)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-22-9c6365d9b1a5>\"\u001b[1;36m, line \u001b[1;32m8\u001b[0m\n\u001b[1;33m    df.dropna() to drop the rows or df.dropna(axis=1)\u001b[0m\n\u001b[1;37m                 ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# other data scrubbing quick tips\n",
    "\n",
    "# checking for null values\n",
    "pd.isnull()\n",
    "pd.isnull().sum()  #gives sum of all null cells\n",
    "\n",
    "# drop values\n",
    "df.dropna() to drop the rows or df.dropna(axis=1)\n",
    "\n",
    "# replace null values with NA\n",
    "df.fillna(x)\n",
    "\n",
    "# replace null values with mean value\n",
    "s.fillna(s.mean())\n",
    "\n",
    "# replacing data\n",
    "s.replace([1,3],['one','three'])\n",
    "\n",
    "# change index in data frame\n",
    "df.set_index('column_one')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# can also write short functions to do things, like merge files based on one common column and output to a csv\n",
    "\n",
    "def combine_csvs(file1,file2,matchid,outfile):    \n",
    "    df1 = pd.read_csv(file1)\n",
    "    df2 = pd.read_csv(file2)\n",
    "    output = pd.merge(df1, df2, how=\"inner\", on=matchid) # matchid should be column name common in both dataframes\n",
    "    final_output = output.drop_duplicates() # remove duplicate rows \n",
    "    final_output.to_csv(rootdir+outfile, index=False)\n",
    "    return final_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}