examples/3_reshaping.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6caf535f-568a-46b0-8b5c-ad0f0637e74d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e51497bf-4869-4ca7-bd49-6d5cac0d5a9a",
   "metadata": {},
   "source": [
    "## A few widely-used functions\n",
    "1. `crosstab`, `pivot_table`\n",
    "2. `groupby`, `stack`, `unstack`, `reset_index`, `set_index`\n",
    "3. `melt`, `join`, `concat`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a0aebf47-031d-4555-875b-c81b2918522b",
   "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>samples</th>\n",
       "      <th>mutations</th>\n",
       "      <th>yes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sample1</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sample2</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sample3</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sample4</td>\n",
       "      <td>mutation2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sample5</td>\n",
       "      <td>mutation2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>sample6</td>\n",
       "      <td>mutation3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>sample3</td>\n",
       "      <td>mutation3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>sample1</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   samples  mutations  yes\n",
       "0  sample1  mutation1    1\n",
       "1  sample2  mutation1    1\n",
       "2  sample3  mutation1    1\n",
       "3  sample4  mutation2    4\n",
       "4  sample5  mutation2    1\n",
       "5  sample6  mutation3    1\n",
       "6  sample3  mutation3    1\n",
       "7  sample1  mutation1    5"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "samples = np.array(['sample1','sample2','sample3','sample4','sample5','sample6','sample3','sample1'])\n",
    "mutations = np.array(['mutation1','mutation1','mutation1','mutation2','mutation2','mutation3','mutation3','mutation1'])\n",
    "yes = np.array([1,1,1,4,1,1,1,5])\n",
    "df = pd.DataFrame({'samples':samples,'mutations':mutations,'yes':yes})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "56bc0367-c87d-4b88-9956-d0c981c22662",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mutations  mutation1  mutation2  mutation3\n",
      "samples                                   \n",
      "sample1          6.0        NaN        NaN\n",
      "sample2          1.0        NaN        NaN\n",
      "sample3          1.0        NaN        1.0\n",
      "sample4          NaN        4.0        NaN\n",
      "sample5          NaN        1.0        NaN\n",
      "sample6          NaN        NaN        1.0\n",
      "mutations  mutation1  mutation2  mutation3\n",
      "samples                                   \n",
      "sample1            6          0          0\n",
      "sample2            1          0          0\n",
      "sample3            1          0          1\n",
      "sample4            0          4          0\n",
      "sample5            0          1          0\n",
      "sample6            0          0          1\n"
     ]
    }
   ],
   "source": [
    "# crosstab, pivot_table (a more general form of pivot function)\n",
    "print(pd.crosstab(index=df['samples'],columns=df['mutations'],values=df['yes'],aggfunc=np.sum))\n",
    "print(pd.pivot_table(data=df,index='samples',columns='mutations',values='yes',aggfunc=np.sum,fill_value=0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "55988c44-5446-4f0e-9b13-c5f54738afe5",
   "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>mutations</th>\n",
       "      <th>mutation1</th>\n",
       "      <th>mutation2</th>\n",
       "      <th>mutation3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>samples</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>sample1</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sample2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sample3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sample4</th>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sample5</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sample6</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "mutations  mutation1  mutation2  mutation3\n",
       "samples                                   \n",
       "sample1            6          0          0\n",
       "sample2            1          0          0\n",
       "sample3            1          0          1\n",
       "sample4            0          4          0\n",
       "sample5            0          1          0\n",
       "sample6            0          0          1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# groupby, unstack, stack\n",
    "df.groupby(['samples','mutations'])['yes'].sum().unstack(fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "83c24c17-dbc1-49a2-8009-8874be54b7d2",
   "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>samples</th>\n",
       "      <th>var</th>\n",
       "      <th>values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sample1</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sample2</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sample3</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sample4</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sample5</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>sample6</td>\n",
       "      <td>mutation1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>sample1</td>\n",
       "      <td>mutation2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>sample2</td>\n",
       "      <td>mutation2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>sample3</td>\n",
       "      <td>mutation2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>sample4</td>\n",
       "      <td>mutation2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>sample5</td>\n",
       "      <td>mutation2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>sample6</td>\n",
       "      <td>mutation2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>sample1</td>\n",
       "      <td>mutation3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>sample2</td>\n",
       "      <td>mutation3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>sample3</td>\n",
       "      <td>mutation3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>sample4</td>\n",
       "      <td>mutation3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>sample5</td>\n",
       "      <td>mutation3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>sample6</td>\n",
       "      <td>mutation3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    samples        var  values\n",
       "0   sample1  mutation1       6\n",
       "1   sample2  mutation1       1\n",
       "2   sample3  mutation1       1\n",
       "3   sample4  mutation1       0\n",
       "4   sample5  mutation1       0\n",
       "5   sample6  mutation1       0\n",
       "6   sample1  mutation2       0\n",
       "7   sample2  mutation2       0\n",
       "8   sample3  mutation2       0\n",
       "9   sample4  mutation2       4\n",
       "10  sample5  mutation2       1\n",
       "11  sample6  mutation2       0\n",
       "12  sample1  mutation3       0\n",
       "13  sample2  mutation3       0\n",
       "14  sample3  mutation3       1\n",
       "15  sample4  mutation3       0\n",
       "16  sample5  mutation3       0\n",
       "17  sample6  mutation3       1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# melt to go back to long-format\n",
    "wide = df.groupby(['samples','mutations'])['yes'].sum().unstack(fill_value=0)\n",
    "wide = wide.reset_index()\n",
    "long = wide.melt(id_vars='samples',value_vars=['mutation1','mutation2','mutation3'],var_name='var',value_name='values')\n",
    "long"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "59b47119-c89e-4870-8857-482bc9933c7d",
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   col1  col2  col3\n",
       "0     1     5     9\n",
       "1     2     6    10\n",
       "0     4     0     1\n",
       "2     3     7    11"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# join and concat can be used interchangeably\n",
    "df = pd.DataFrame({'col1':[1,2,3],'col2':[5,6,7],'col3':[9,10,11]})\n",
    "# I want to insert a row between 1 and 2\n",
    "row = pd.DataFrame({'col1':[4],'col2':[0],'col3':1})\n",
    "pd.concat([df.iloc[0:2],row,df.iloc[2:]],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ed01795-242a-48e6-8470-93b17aa86596",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}