Fill_in_checklist_ENA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4f428326",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "727d13a4",
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Checklist</th>\n",
       "      <th>ERC000018</th>\n",
       "      <th>GSC MIxS human vaginal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>tax_id</th>\n",
       "      <th>scientific_name</th>\n",
       "      <th>sample_alias</th>\n",
       "      <th>sample_title</th>\n",
       "      <th>sample_description</th>\n",
       "      <th>project name</th>\n",
       "      <th>collection date</th>\n",
       "      <th>geographic location (country and/or sea)</th>\n",
       "      <th>geographic location (latitude)</th>\n",
       "      <th>geographic location (longitude)</th>\n",
       "      <td>broad-scale environmental context</td>\n",
       "      <td>local environmental context</td>\n",
       "      <td>environmental medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#units</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>DD</th>\n",
       "      <th>DD</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                                                                                                                                  Checklist  \\\n",
       "tax_id scientific_name sample_alias sample_title sample_description project name collection date geographic location (country and/or sea) geographic location (latitude) geographic location (longitude)  broad-scale environmental context   \n",
       "#units                                                                                                                                    DD                             DD                                                                   \n",
       "\n",
       "                                                                                                                                                                                                                            ERC000018  \\\n",
       "tax_id scientific_name sample_alias sample_title sample_description project name collection date geographic location (country and/or sea) geographic location (latitude) geographic location (longitude)  local environmental context   \n",
       "#units                                                                                                                                    DD                             DD                                                             \n",
       "\n",
       "                                                                                                                                                                                                         GSC MIxS human vaginal  \n",
       "tax_id scientific_name sample_alias sample_title sample_description project name collection date geographic location (country and/or sea) geographic location (latitude) geographic location (longitude)   environmental medium  \n",
       "#units                                                                                                                                    DD                             DD                                                      "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "checklist_control = pd.read_csv(\"/ceph/projects/031_VaMiGyn/gustav.ahlstrom/ENA_upload/VaMiGyn_upload_April24_Checklist_GSC-MIxS_human_vaginal_1712565803423.tsv\",sep=\"\\t\")\n",
    "checklist_control.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "514f258d",
   "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>Unnamed: 0</th>\n",
       "      <th>Flowcell_L1_bc</th>\n",
       "      <th>SampleID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SampleID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>VMG_0178</th>\n",
       "      <td>1</td>\n",
       "      <td>E100018190_L1_35</td>\n",
       "      <td>VMG_0178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_0179</th>\n",
       "      <td>2</td>\n",
       "      <td>E100016244_L1_100</td>\n",
       "      <td>VMG_0179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_0180</th>\n",
       "      <td>3</td>\n",
       "      <td>E100009797_L1_30</td>\n",
       "      <td>VMG_0180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_0181</th>\n",
       "      <td>4</td>\n",
       "      <td>E100018414_L1_4</td>\n",
       "      <td>VMG_0181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_0182</th>\n",
       "      <td>5</td>\n",
       "      <td>E100013703_L1_121</td>\n",
       "      <td>VMG_0182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_1446</th>\n",
       "      <td>1269</td>\n",
       "      <td>E100016338_L1_28</td>\n",
       "      <td>VMG_1446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_1447</th>\n",
       "      <td>1270</td>\n",
       "      <td>E100026888_L1_92</td>\n",
       "      <td>VMG_1447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_1448</th>\n",
       "      <td>1271</td>\n",
       "      <td>E100017979_L1_104</td>\n",
       "      <td>VMG_1448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_1449</th>\n",
       "      <td>1272</td>\n",
       "      <td>E100016338_L1_73</td>\n",
       "      <td>VMG_1449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VMG_1450</th>\n",
       "      <td>1273</td>\n",
       "      <td>E100016200_L1_127</td>\n",
       "      <td>VMG_1450</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1273 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Unnamed: 0     Flowcell_L1_bc  SampleID\n",
       "SampleID                                         \n",
       "VMG_0178           1   E100018190_L1_35  VMG_0178\n",
       "VMG_0179           2  E100016244_L1_100  VMG_0179\n",
       "VMG_0180           3   E100009797_L1_30  VMG_0180\n",
       "VMG_0181           4    E100018414_L1_4  VMG_0181\n",
       "VMG_0182           5  E100013703_L1_121  VMG_0182\n",
       "...              ...                ...       ...\n",
       "VMG_1446        1269   E100016338_L1_28  VMG_1446\n",
       "VMG_1447        1270   E100026888_L1_92  VMG_1447\n",
       "VMG_1448        1271  E100017979_L1_104  VMG_1448\n",
       "VMG_1449        1272   E100016338_L1_73  VMG_1449\n",
       "VMG_1450        1273  E100016200_L1_127  VMG_1450\n",
       "\n",
       "[1273 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_samples_meta_data = pd.read_csv(\"/ceph/projects/031_VaMiGyn/gustav.ahlstrom/ENA_upload/240407_VMG_list_forENA.csv\",header=0,sep=\";\")\n",
    "\n",
    "\n",
    "all_samples_meta_data.index = all_samples_meta_data[\"SampleID\"]\n",
    "#all_samples_meta_data\n",
    "all_samples_meta_data\n",
    "#crl_meta_data = all_samples_meta_data.loc[ctrl_samples[\"SampleID\"]]\n",
    "#dysp_meta_data = all_samples_meta_data.loc[dysp_samples[\"SampleID\"]]\n",
    "#dysp_meta_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "030db61f",
   "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>Unnamed: 0_x</th>\n",
       "      <th>Flowcell_L1_bc</th>\n",
       "      <th>SampleID</th>\n",
       "      <th>Unnamed: 0_y</th>\n",
       "      <th>Date_short_sampling</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>E100018190_L1_35</td>\n",
       "      <td>VMG_0178</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>E100016244_L1_100</td>\n",
       "      <td>VMG_0179</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>E100009797_L1_30</td>\n",
       "      <td>VMG_0180</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>E100018414_L1_4</td>\n",
       "      <td>VMG_0181</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>E100013703_L1_121</td>\n",
       "      <td>VMG_0182</td>\n",
       "      <td>5</td>\n",
       "      <td>2019-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0_x     Flowcell_L1_bc  SampleID  Unnamed: 0_y Date_short_sampling\n",
       "0             1   E100018190_L1_35  VMG_0178             1             2019-01\n",
       "1             2  E100016244_L1_100  VMG_0179             2             2019-09\n",
       "2             3   E100009797_L1_30  VMG_0180             3             2018-09\n",
       "3             4    E100018414_L1_4  VMG_0181             4             2019-12\n",
       "4             5  E100013703_L1_121  VMG_0182             5             2019-03"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_meta_data = pd.read_csv(\"/ceph/projects/031_VaMiGyn/gustav.ahlstrom/ENA_upload/240407_VMG_list_forENA_dates.csv\",header=0,sep=\";\")\n",
    "#date_meta_data.head()\n",
    "merged_df = pd.merge(all_samples_meta_data, date_meta_data, on=\"Flowcell_L1_bc\", how=\"inner\")\n",
    "\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "acf276b3",
   "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>tax_id</th>\n",
       "      <th>scientific_name</th>\n",
       "      <th>sample_alias</th>\n",
       "      <th>sample_title</th>\n",
       "      <th>sample_description</th>\n",
       "      <th>project name</th>\n",
       "      <th>collection date</th>\n",
       "      <th>geographic location (country and/or sea)</th>\n",
       "      <th>geographic location (latitude)</th>\n",
       "      <th>geographic location (longitude)</th>\n",
       "      <th>broad-scale environmental context</th>\n",
       "      <th>local environmental context</th>\n",
       "      <th>environmental medium</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_0178</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-01</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_0179</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-09</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_0180</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2018-09</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_0181</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-12</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_0182</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-03</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1268</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1446</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2018-11</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1269</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1447</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-02</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1270</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1448</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-10</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1271</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1449</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1272</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1450</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-10</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1273 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       tax_id           scientific_name sample_alias    sample_title  \\\n",
       "0     1632839  human vaginal metagenome     VMG_0178  Vaginal sample   \n",
       "1     1632839  human vaginal metagenome     VMG_0179  Vaginal sample   \n",
       "2     1632839  human vaginal metagenome     VMG_0180  Vaginal sample   \n",
       "3     1632839  human vaginal metagenome     VMG_0181  Vaginal sample   \n",
       "4     1632839  human vaginal metagenome     VMG_0182  Vaginal sample   \n",
       "...       ...                       ...          ...             ...   \n",
       "1268  1632839  human vaginal metagenome     VMG_1446  Vaginal sample   \n",
       "1269  1632839  human vaginal metagenome     VMG_1447  Vaginal sample   \n",
       "1270  1632839  human vaginal metagenome     VMG_1448  Vaginal sample   \n",
       "1271  1632839  human vaginal metagenome     VMG_1449  Vaginal sample   \n",
       "1272  1632839  human vaginal metagenome     VMG_1450  Vaginal sample   \n",
       "\n",
       "                     sample_description project name collection date  \\\n",
       "0     Vaginal swabs from Swedish cohort      VaMiGyn         2019-01   \n",
       "1     Vaginal swabs from Swedish cohort      VaMiGyn         2019-09   \n",
       "2     Vaginal swabs from Swedish cohort      VaMiGyn         2018-09   \n",
       "3     Vaginal swabs from Swedish cohort      VaMiGyn         2019-12   \n",
       "4     Vaginal swabs from Swedish cohort      VaMiGyn         2019-03   \n",
       "...                                 ...          ...             ...   \n",
       "1268  Vaginal swabs from Swedish cohort      VaMiGyn         2018-11   \n",
       "1269  Vaginal swabs from Swedish cohort      VaMiGyn         2019-02   \n",
       "1270  Vaginal swabs from Swedish cohort      VaMiGyn         2019-10   \n",
       "1271  Vaginal swabs from Swedish cohort      VaMiGyn         2019-08   \n",
       "1272  Vaginal swabs from Swedish cohort      VaMiGyn         2019-10   \n",
       "\n",
       "     geographic location (country and/or sea)  geographic location (latitude)  \\\n",
       "0                                      Sweden                        59.84972   \n",
       "1                                      Sweden                        59.84972   \n",
       "2                                      Sweden                        59.84972   \n",
       "3                                      Sweden                        59.84972   \n",
       "4                                      Sweden                        59.84972   \n",
       "...                                       ...                             ...   \n",
       "1268                                   Sweden                        59.84972   \n",
       "1269                                   Sweden                        59.84972   \n",
       "1270                                   Sweden                        59.84972   \n",
       "1271                                   Sweden                        59.84972   \n",
       "1272                                   Sweden                        59.84972   \n",
       "\n",
       "      geographic location (longitude) broad-scale environmental context  \\\n",
       "0                            17.63889                            vagina   \n",
       "1                            17.63889                            vagina   \n",
       "2                            17.63889                            vagina   \n",
       "3                            17.63889                            vagina   \n",
       "4                            17.63889                            vagina   \n",
       "...                               ...                               ...   \n",
       "1268                         17.63889                            vagina   \n",
       "1269                         17.63889                            vagina   \n",
       "1270                         17.63889                            vagina   \n",
       "1271                         17.63889                            vagina   \n",
       "1272                         17.63889                            vagina   \n",
       "\n",
       "     local environmental context environmental medium  \n",
       "0                     mid fornix         vaginal swab  \n",
       "1                     mid fornix         vaginal swab  \n",
       "2                     mid fornix         vaginal swab  \n",
       "3                     mid fornix         vaginal swab  \n",
       "4                     mid fornix         vaginal swab  \n",
       "...                          ...                  ...  \n",
       "1268                  mid fornix         vaginal swab  \n",
       "1269                  mid fornix         vaginal swab  \n",
       "1270                  mid fornix         vaginal swab  \n",
       "1271                  mid fornix         vaginal swab  \n",
       "1272                  mid fornix         vaginal swab  \n",
       "\n",
       "[1273 rows x 13 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filled_checklist = pd.DataFrame(index=range(merged_df.shape[0]),columns=range(13))\n",
    "filled_checklist.columns = [\"tax_id\",\"scientific_name\",\"sample_alias\",\"sample_title\",\"sample_description\",\"project name\",\"collection date\",\"geographic location (country and/or sea)\",\"geographic location (latitude)\",\"geographic location (longitude)\",\"broad-scale environmental context\",\"local environmental context\",\"environmental medium\"]\n",
    "filled_checklist.index = merged_df.index\n",
    "\n",
    "filled_checklist[\"tax_id\"] = 1632839\n",
    "filled_checklist[\"scientific_name\"] = \"human vaginal metagenome\"\n",
    "filled_checklist[\"sample_alias\"] = merged_df[\"SampleID\"]\n",
    "filled_checklist[\"sample_title\"] = \"Vaginal sample\"\n",
    "filled_checklist[\"sample_description\"] = \"Vaginal swabs from Swedish cohort\"\n",
    "filled_checklist[\"collection date\"] = merged_df[\"Date_short_sampling\"]\n",
    "filled_checklist[\"project name\"] = \"VaMiGyn\"\n",
    "filled_checklist[\"geographic location (country and/or sea)\"] = \"Sweden\"\n",
    "filled_checklist[\"geographic location (latitude)\"] = 59.84972\n",
    "filled_checklist[\"geographic location (longitude)\"] = 17.63889\n",
    "filled_checklist[\"broad-scale environmental context\"] = \"vagina\"\n",
    "filled_checklist[\"local environmental context\"] = \"mid fornix\"\n",
    "filled_checklist[\"environmental medium\"] = \"vaginal swab\"\n",
    "filled_checklist.index = range(len(filled_checklist))\n",
    "filled_checklist\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0821265e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "row_id_and_geolatlong = pd.DataFrame({\"tax_id\":[\"#units\"],\n",
    "                         \"scientific_name\":[\"\"],\n",
    "                         \"sample_alias\":[\"\"],\n",
    "                         \"sample_title\":[\"\"],\n",
    "                         \"sample_description\":[\"\"],\n",
    "                         \"project name\":[\"\"],\n",
    "                         \"collection date\":[\"\"],\n",
    "                         \"geographic location (country and/or sea)\":[\"\"],\n",
    "                         \"geographic location (latitude)\":[\"DD\"],\n",
    "                         \"geographic location (longitude)\":[\"DD\"],\n",
    "                         \"broad-scale environmental context\":[\"\"],\n",
    "                         \"local environmental context\":[\"\"],\n",
    "                         \"environmental medium\":[\"\"]})\n",
    "\n",
    "row_column_names = pd.DataFrame({\"tax_id\":[\"tax_id\"],\n",
    "                         \"scientific_name\":[\"scientific_name\"],\n",
    "                         \"sample_alias\":[\"sample_alias\"],\n",
    "                         \"sample_title\":[\"sample_title\"],\n",
    "                         \"sample_description\":[\"sample_description\"],\n",
    "                         \"project name\":[\"project name\"],\n",
    "                         \"collection date\":[\"collection date\"],\n",
    "                         \"geographic location (country and/or sea)\":[\"geographic location (country and/or sea)\"],\n",
    "                         \"geographic location (latitude)\":[\"geographic location (latitude)\"],\n",
    "                         \"geographic location (longitude)\":[\"geographic location (longitude)\"],\n",
    "                         \"broad-scale environmental context\":[\"broad-scale environmental context\"],\n",
    "                         \"local environmental context\":[\"local environmental context\"],\n",
    "                         \"environmental medium\":[\"environmental medium\"]})\n",
    "\n",
    "filled_checklist_concat = pd.concat([pd.DataFrame(row_id_and_geolatlong, index=[0]), filled_checklist])\n",
    "filled_checklist_concat = pd.concat([pd.DataFrame(row_column_names, index=[0]), filled_checklist_concat])\n",
    "\n",
    "filled_checklist_concat.columns = [\"Checklist\",\"ERC000018\",\"GSC MIxS human vaginal\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\"]\n",
    "filled_checklist_concat\n",
    "filled_checklist_concat.to_csv(\"/ceph/projects/031_VaMiGyn/gustav.ahlstrom/ENA_upload/filled_metadata_ENA_VaMiGyn_240409.csv\",index=False,header=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c100a5ca",
   "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>tax_id</th>\n",
       "      <th>scientific_name</th>\n",
       "      <th>sample_alias</th>\n",
       "      <th>sample_title</th>\n",
       "      <th>sample_description</th>\n",
       "      <th>project name</th>\n",
       "      <th>collection date</th>\n",
       "      <th>geographic location (country and/or sea)</th>\n",
       "      <th>geographic location (latitude)</th>\n",
       "      <th>geographic location (longitude)</th>\n",
       "      <th>broad-scale environmental context</th>\n",
       "      <th>local environmental context</th>\n",
       "      <th>environmental medium</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>#units</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>DD</td>\n",
       "      <td>DD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tax_id scientific_name sample_alias sample_title sample_description  \\\n",
       "0  #units                                                                \n",
       "\n",
       "  project name collection date geographic location (country and/or sea)  \\\n",
       "0                                                                         \n",
       "\n",
       "  geographic location (latitude) geographic location (longitude)  \\\n",
       "0                             DD                              DD   \n",
       "\n",
       "  broad-scale environmental context local environmental context  \\\n",
       "0                                                                 \n",
       "\n",
       "  environmental medium  \n",
       "0                       "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row_id_and_geolatlong"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4abfe9a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['tax_id', 'scientific_name', 'sample_alias', 'sample_title',\n",
       "       'sample_description', 'project name', 'collection date',\n",
       "       'geographic location (country and/or sea)',\n",
       "       'geographic location (latitude)', 'geographic location (longitude)',\n",
       "       'broad-scale environmental context', 'local environmental context',\n",
       "       'environmental medium'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filled_checklist.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d1b5ae84",
   "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>Checklist</th>\n",
       "      <th>ERC000018</th>\n",
       "      <th>GSC MIxS human vaginal</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tax_id</td>\n",
       "      <td>scientific_name</td>\n",
       "      <td>sample_alias</td>\n",
       "      <td>sample_title</td>\n",
       "      <td>sample_description</td>\n",
       "      <td>project name</td>\n",
       "      <td>collection date</td>\n",
       "      <td>geographic location (country and/or sea)</td>\n",
       "      <td>geographic location (latitude)</td>\n",
       "      <td>geographic location (longitude)</td>\n",
       "      <td>broad-scale environmental context</td>\n",
       "      <td>local environmental context</td>\n",
       "      <td>environmental medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>#units</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>DD</td>\n",
       "      <td>DD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_0178</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-01</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_0179</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-09</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_0180</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2018-09</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1268</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1446</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2018-11</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1269</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1447</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-02</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1270</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1448</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-10</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1271</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1449</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1272</th>\n",
       "      <td>1632839</td>\n",
       "      <td>human vaginal metagenome</td>\n",
       "      <td>VMG_1450</td>\n",
       "      <td>Vaginal sample</td>\n",
       "      <td>Vaginal swabs from Swedish cohort</td>\n",
       "      <td>VaMiGyn</td>\n",
       "      <td>2019-10</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>59.84972</td>\n",
       "      <td>17.63889</td>\n",
       "      <td>vagina</td>\n",
       "      <td>mid fornix</td>\n",
       "      <td>vaginal swab</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1275 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Checklist                 ERC000018 GSC MIxS human vaginal  \\\n",
       "0       tax_id           scientific_name           sample_alias   \n",
       "0       #units                                                    \n",
       "0      1632839  human vaginal metagenome               VMG_0178   \n",
       "1      1632839  human vaginal metagenome               VMG_0179   \n",
       "2      1632839  human vaginal metagenome               VMG_0180   \n",
       "...        ...                       ...                    ...   \n",
       "1268   1632839  human vaginal metagenome               VMG_1446   \n",
       "1269   1632839  human vaginal metagenome               VMG_1447   \n",
       "1270   1632839  human vaginal metagenome               VMG_1448   \n",
       "1271   1632839  human vaginal metagenome               VMG_1449   \n",
       "1272   1632839  human vaginal metagenome               VMG_1450   \n",
       "\n",
       "                                                                       \\\n",
       "0       sample_title                 sample_description  project name   \n",
       "0                                                                       \n",
       "0     Vaginal sample  Vaginal swabs from Swedish cohort       VaMiGyn   \n",
       "1     Vaginal sample  Vaginal swabs from Swedish cohort       VaMiGyn   \n",
       "2     Vaginal sample  Vaginal swabs from Swedish cohort       VaMiGyn   \n",
       "...              ...                                ...           ...   \n",
       "1268  Vaginal sample  Vaginal swabs from Swedish cohort       VaMiGyn   \n",
       "1269  Vaginal sample  Vaginal swabs from Swedish cohort       VaMiGyn   \n",
       "1270  Vaginal sample  Vaginal swabs from Swedish cohort       VaMiGyn   \n",
       "1271  Vaginal sample  Vaginal swabs from Swedish cohort       VaMiGyn   \n",
       "1272  Vaginal sample  Vaginal swabs from Swedish cohort       VaMiGyn   \n",
       "\n",
       "                                                                 \\\n",
       "0     collection date  geographic location (country and/or sea)   \n",
       "0                                                                 \n",
       "0             2019-01                                    Sweden   \n",
       "1             2019-09                                    Sweden   \n",
       "2             2018-09                                    Sweden   \n",
       "...               ...                                       ...   \n",
       "1268          2018-11                                    Sweden   \n",
       "1269          2019-02                                    Sweden   \n",
       "1270          2019-10                                    Sweden   \n",
       "1271          2019-08                                    Sweden   \n",
       "1272          2019-10                                    Sweden   \n",
       "\n",
       "                                                                       \\\n",
       "0     geographic location (latitude)  geographic location (longitude)   \n",
       "0                                 DD                               DD   \n",
       "0                           59.84972                         17.63889   \n",
       "1                           59.84972                         17.63889   \n",
       "2                           59.84972                         17.63889   \n",
       "...                              ...                              ...   \n",
       "1268                        59.84972                         17.63889   \n",
       "1269                        59.84972                         17.63889   \n",
       "1270                        59.84972                         17.63889   \n",
       "1271                        59.84972                         17.63889   \n",
       "1272                        59.84972                         17.63889   \n",
       "\n",
       "                                                                      \\\n",
       "0     broad-scale environmental context  local environmental context   \n",
       "0                                                                      \n",
       "0                                vagina                   mid fornix   \n",
       "1                                vagina                   mid fornix   \n",
       "2                                vagina                   mid fornix   \n",
       "...                                 ...                          ...   \n",
       "1268                             vagina                   mid fornix   \n",
       "1269                             vagina                   mid fornix   \n",
       "1270                             vagina                   mid fornix   \n",
       "1271                             vagina                   mid fornix   \n",
       "1272                             vagina                   mid fornix   \n",
       "\n",
       "                            \n",
       "0     environmental medium  \n",
       "0                           \n",
       "0             vaginal swab  \n",
       "1             vaginal swab  \n",
       "2             vaginal swab  \n",
       "...                    ...  \n",
       "1268          vaginal swab  \n",
       "1269          vaginal swab  \n",
       "1270          vaginal swab  \n",
       "1271          vaginal swab  \n",
       "1272          vaginal swab  \n",
       "\n",
       "[1275 rows x 13 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filled_checklist_concat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1c6f828",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}