S18_MSLDAmerge-motfs-sumall.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "small-bangladesh",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "df_quant = pd.read_csv(\"/Users/delser/Desktop/PhD/Phytochemistry/FBMN/alltissues/FEATURE-BASED-MOLECULAR-NETWORKING-cf822b6c-15072021/alltissues-occ-for-motif-dist.csv\",index_col='row ID', sep=\",\")\n",
    "\n",
    "df_motif = pd.read_csv(\"/Users/delser/Desktop/PhD/Phytochemistry/FBMN/alltissues/FEATURE-BASED-MOLECULAR-NETWORKING-cf822b6c-15072021/MS2LDA-all.csv\",index_col='row ID', sep=\",\")\n",
    "\n",
    "df_frag=pd.read_csv(\"/Users/delser/Desktop/PhD/Phytochemistry/FBMN/alltissues/FEATURE-BASED-MOLECULAR-NETWORKING-cf822b6c-15072021/MS2LDA-fragments.csv\",index_col='Motif', sep=\",\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "mechanical-hebrew",
   "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>Feature</th>\n",
       "      <th>Min m/z</th>\n",
       "      <th>Max m/z</th>\n",
       "      <th>Probability</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Motif</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rhabdus_motif_95.m2m</th>\n",
       "      <td>fragment_100.0725</td>\n",
       "      <td>100.0700</td>\n",
       "      <td>100.0750</td>\n",
       "      <td>0.189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>urine_mass2motif_27.m2m</th>\n",
       "      <td>fragment_100.0775</td>\n",
       "      <td>100.0750</td>\n",
       "      <td>100.0800</td>\n",
       "      <td>0.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rhabdus_motif_250.m2m</th>\n",
       "      <td>fragment_100.1125</td>\n",
       "      <td>100.1100</td>\n",
       "      <td>100.1150</td>\n",
       "      <td>0.347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StrepSalini_motif_233.m2m</th>\n",
       "      <td>fragment_100.1500</td>\n",
       "      <td>100.1475</td>\n",
       "      <td>100.1525</td>\n",
       "      <td>0.117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rhamn_motif_163.m2m</th>\n",
       "      <td>fragment_101.0225</td>\n",
       "      <td>101.0200</td>\n",
       "      <td>101.0250</td>\n",
       "      <td>0.936</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Feature   Min m/z   Max m/z  Probability\n",
       "Motif                                                                        \n",
       "rhabdus_motif_95.m2m       fragment_100.0725  100.0700  100.0750        0.189\n",
       "urine_mass2motif_27.m2m    fragment_100.0775  100.0750  100.0800        0.800\n",
       "rhabdus_motif_250.m2m      fragment_100.1125  100.1100  100.1150        0.347\n",
       "StrepSalini_motif_233.m2m  fragment_100.1500  100.1475  100.1525        0.117\n",
       "rhamn_motif_163.m2m        fragment_101.0225  101.0200  101.0250        0.936"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_frag.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "vocational-scenario",
   "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>Motif</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row ID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>motif_433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>motif_591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>motif_502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gnps_motif_43.m2m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>motif_433</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Motif\n",
       "row ID                   \n",
       "1               motif_433\n",
       "1               motif_591\n",
       "1               motif_502\n",
       "1       gnps_motif_43.m2m\n",
       "2               motif_433"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_motif.head()\n",
    "df_motif_red=df_motif.drop([\"Precursor Mass\",\"Retention Time\",\"Document Annotation\",\"Document\",\"Probability\",\"Overlap Score\"], axis=1)\n",
    "\n",
    "df_motif_red.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "olympic-softball",
   "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>Nrep</th>\n",
       "      <th>Nnes</th>\n",
       "      <th>Nrxs</th>\n",
       "      <th>Nsto</th>\n",
       "      <th>Ntab</th>\n",
       "      <th>Nglu</th>\n",
       "      <th>Nben</th>\n",
       "      <th>Ncle</th>\n",
       "      <th>Ngla</th>\n",
       "      <th>Ngoo</th>\n",
       "      <th>...</th>\n",
       "      <th>Solvent_RA5</th>\n",
       "      <th>Solvent_RA26</th>\n",
       "      <th>Solvent_RA7</th>\n",
       "      <th>Document</th>\n",
       "      <th>Motif</th>\n",
       "      <th>Probability</th>\n",
       "      <th>Overlap Score</th>\n",
       "      <th>Precursor Mass</th>\n",
       "      <th>Retention Time</th>\n",
       "      <th>Document Annotation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row ID</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",
       "      <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></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>motif_433</td>\n",
       "      <td>0.579</td>\n",
       "      <td>0.791</td>\n",
       "      <td>375.3009</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>motif_591</td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.183</td>\n",
       "      <td>375.3009</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>motif_502</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.603</td>\n",
       "      <td>375.3009</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>gnps_motif_43.m2m</td>\n",
       "      <td>0.102</td>\n",
       "      <td>0.938</td>\n",
       "      <td>375.3009</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>motif_433</td>\n",
       "      <td>0.727</td>\n",
       "      <td>0.813</td>\n",
       "      <td>375.3009</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Nrep  Nnes  Nrxs  Nsto  Ntab  Nglu  Nben  Ncle  Ngla  Ngoo  ...  \\\n",
       "row ID                                                              ...   \n",
       "1          0     0     0     0     0     0     0     0     0     0  ...   \n",
       "1          0     0     0     0     0     0     0     0     0     0  ...   \n",
       "1          0     0     0     0     0     0     0     0     0     0  ...   \n",
       "1          0     0     0     0     0     0     0     0     0     0  ...   \n",
       "2          2     2     3     1     1     0     0     0     0     0  ...   \n",
       "\n",
       "        Solvent_RA5  Solvent_RA26  Solvent_RA7  Document              Motif  \\\n",
       "row ID                                                                        \n",
       "1                 0             0            0         1          motif_433   \n",
       "1                 0             0            0         1          motif_591   \n",
       "1                 0             0            0         1          motif_502   \n",
       "1                 0             0            0         1  gnps_motif_43.m2m   \n",
       "2                 0             0            0         2          motif_433   \n",
       "\n",
       "        Probability  Overlap Score  Precursor Mass  Retention Time  \\\n",
       "row ID                                                               \n",
       "1             0.579          0.791        375.3009            None   \n",
       "1             0.164          0.183        375.3009            None   \n",
       "1             0.111          0.603        375.3009            None   \n",
       "1             0.102          0.938        375.3009            None   \n",
       "2             0.727          0.813        375.3009            None   \n",
       "\n",
       "        Document Annotation  \n",
       "row ID                       \n",
       "1                      None  \n",
       "1                      None  \n",
       "1                      None  \n",
       "1                      None  \n",
       "2                      None  \n",
       "\n",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#result= pd.concat([df_quant, df_motif.reindex(df_quant.index)], axis=1)\n",
    "#result = pd.concat([df_quant, df_motif], ignore_index=True, sort=False)\n",
    "result = df_quant.join(df_motif, how=\"outer\")\n",
    "#result = left.join(right, how=\"outer\")\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "french-promotion",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "honest-gauge",
   "metadata": {},
   "outputs": [],
   "source": [
    "clean=result.drop([\"Precursor Mass\",\"Retention Time\",\"Document Annotation\",\"Document\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "caring-trout",
   "metadata": {},
   "outputs": [],
   "source": [
    "#clean=clean.reset_index()\n",
    "\n",
    "#full=result = df_quant.join(df_motif, how=\"outer\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "golden-aruba",
   "metadata": {},
   "outputs": [],
   "source": [
    "#clean=clean.set_index(\"Motif\")\n",
    "#clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "enhanced-democracy",
   "metadata": {},
   "outputs": [],
   "source": [
    "#full = clean.join(df_frag, how=\"outer\")\n",
    "#full.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "scientific-huntington",
   "metadata": {},
   "outputs": [],
   "source": [
    "#full.to_csv(\"alltissues27042021_all.csv\",sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "vulnerable-shower",
   "metadata": {},
   "outputs": [],
   "source": [
    "#occur = full.groupby(['Motif', 'row ID']).size()\n",
    "\n",
    "#occur.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "possible-symphony",
   "metadata": {},
   "outputs": [],
   "source": [
    "#occur.to_csv(\"alltissues27042021_counts.csv\",sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "equipped-qatar",
   "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>Nrep</th>\n",
       "      <th>Nnes</th>\n",
       "      <th>Nrxs</th>\n",
       "      <th>Nsto</th>\n",
       "      <th>Ntab</th>\n",
       "      <th>Nglu</th>\n",
       "      <th>Nben</th>\n",
       "      <th>Ncle</th>\n",
       "      <th>Ngla</th>\n",
       "      <th>Ngoo</th>\n",
       "      <th>...</th>\n",
       "      <th>Solvent_RA1</th>\n",
       "      <th>Solvent_RA2</th>\n",
       "      <th>Solvent_RA3</th>\n",
       "      <th>Solvent_RA4</th>\n",
       "      <th>Solvent_RA5</th>\n",
       "      <th>Solvent_RA26</th>\n",
       "      <th>Solvent_RA7</th>\n",
       "      <th>Motif</th>\n",
       "      <th>Probability</th>\n",
       "      <th>Overlap Score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row ID</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",
       "      <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></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>motif_433</td>\n",
       "      <td>0.579</td>\n",
       "      <td>0.791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>motif_591</td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>motif_502</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>gnps_motif_43.m2m</td>\n",
       "      <td>0.102</td>\n",
       "      <td>0.938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>motif_433</td>\n",
       "      <td>0.727</td>\n",
       "      <td>0.813</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Nrep  Nnes  Nrxs  Nsto  Ntab  Nglu  Nben  Ncle  Ngla  Ngoo  ...  \\\n",
       "row ID                                                              ...   \n",
       "1          0     0     0     0     0     0     0     0     0     0  ...   \n",
       "1          0     0     0     0     0     0     0     0     0     0  ...   \n",
       "1          0     0     0     0     0     0     0     0     0     0  ...   \n",
       "1          0     0     0     0     0     0     0     0     0     0  ...   \n",
       "2          2     2     3     1     1     0     0     0     0     0  ...   \n",
       "\n",
       "        Solvent_RA1  Solvent_RA2  Solvent_RA3  Solvent_RA4  Solvent_RA5  \\\n",
       "row ID                                                                    \n",
       "1                 0            0            0            0            0   \n",
       "1                 0            0            0            0            0   \n",
       "1                 0            0            0            0            0   \n",
       "1                 0            0            0            0            0   \n",
       "2                 0            0            0            0            0   \n",
       "\n",
       "        Solvent_RA26  Solvent_RA7              Motif  Probability  \\\n",
       "row ID                                                              \n",
       "1                  0            0          motif_433        0.579   \n",
       "1                  0            0          motif_591        0.164   \n",
       "1                  0            0          motif_502        0.111   \n",
       "1                  0            0  gnps_motif_43.m2m        0.102   \n",
       "2                  0            0          motif_433        0.727   \n",
       "\n",
       "        Overlap Score  \n",
       "row ID                 \n",
       "1               0.791  \n",
       "1               0.183  \n",
       "1               0.603  \n",
       "1               0.938  \n",
       "2               0.813  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "occur1 = clean.groupby(['Motif', 'row ID']).size()\n",
    "\n",
    "occur1.head()\n",
    "clean.head()\n",
    "#df.groupby(by=df.columns, axis=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cross-conditioning",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "quality-feeding",
   "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>Motif_StrepSalini_motif_10.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_105.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_106.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_108.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_110.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_113.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_119.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_128.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_144.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_168.m2m</th>\n",
       "      <th>...</th>\n",
       "      <th>Motif_urine_mass2motif_68.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_78.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_80.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_83.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_88.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_90.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_93.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_94.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_95.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_97.m2m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row ID</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",
       "      <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></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 658 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Motif_StrepSalini_motif_10.m2m  Motif_StrepSalini_motif_105.m2m  \\\n",
       "row ID                                                                    \n",
       "1                                    0                                0   \n",
       "1                                    0                                0   \n",
       "1                                    0                                0   \n",
       "1                                    0                                0   \n",
       "2                                    0                                0   \n",
       "\n",
       "        Motif_StrepSalini_motif_106.m2m  Motif_StrepSalini_motif_108.m2m  \\\n",
       "row ID                                                                     \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "2                                     0                                0   \n",
       "\n",
       "        Motif_StrepSalini_motif_110.m2m  Motif_StrepSalini_motif_113.m2m  \\\n",
       "row ID                                                                     \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "2                                     0                                0   \n",
       "\n",
       "        Motif_StrepSalini_motif_119.m2m  Motif_StrepSalini_motif_128.m2m  \\\n",
       "row ID                                                                     \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "1                                     0                                0   \n",
       "2                                     0                                0   \n",
       "\n",
       "        Motif_StrepSalini_motif_144.m2m  Motif_StrepSalini_motif_168.m2m  ...  \\\n",
       "row ID                                                                    ...   \n",
       "1                                     0                                0  ...   \n",
       "1                                     0                                0  ...   \n",
       "1                                     0                                0  ...   \n",
       "1                                     0                                0  ...   \n",
       "2                                     0                                0  ...   \n",
       "\n",
       "        Motif_urine_mass2motif_68.m2m  Motif_urine_mass2motif_78.m2m  \\\n",
       "row ID                                                                 \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "2                                   0                              0   \n",
       "\n",
       "        Motif_urine_mass2motif_80.m2m  Motif_urine_mass2motif_83.m2m  \\\n",
       "row ID                                                                 \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "2                                   0                              0   \n",
       "\n",
       "        Motif_urine_mass2motif_88.m2m  Motif_urine_mass2motif_90.m2m  \\\n",
       "row ID                                                                 \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "2                                   0                              0   \n",
       "\n",
       "        Motif_urine_mass2motif_93.m2m  Motif_urine_mass2motif_94.m2m  \\\n",
       "row ID                                                                 \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "1                                   0                              0   \n",
       "2                                   0                              0   \n",
       "\n",
       "        Motif_urine_mass2motif_95.m2m  Motif_urine_mass2motif_97.m2m  \n",
       "row ID                                                                \n",
       "1                                   0                              0  \n",
       "1                                   0                              0  \n",
       "1                                   0                              0  \n",
       "1                                   0                              0  \n",
       "2                                   0                              0  \n",
       "\n",
       "[5 rows x 658 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new=pd.get_dummies(df_motif_red, columns=['Motif'])\n",
    "new.head()\n",
    "#new.to_csv(\"counts-motif.csv\",sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "sorted-italy",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_bool=new.drop_duplicates(subset=[\"row m/z\"])\n",
    "#df_bool.head()\n",
    "#df_bool.to_csv(\"counts-motif-dupl.csv\",sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "single-worker",
   "metadata": {},
   "outputs": [],
   "source": [
    "#x=new.groupby(level=0)['row ID'].sum().reset_index()\n",
    "x=new.groupby(new.index).sum()\n",
    "x.head()\n",
    "\n",
    "#y=x.transpose()\n",
    "a=df_quant\n",
    "#a=a.transpose()\n",
    "\n",
    "#y.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "charming-basement",
   "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>Motif_StrepSalini_motif_10.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_105.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_106.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_108.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_110.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_113.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_119.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_128.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_144.m2m</th>\n",
       "      <th>Motif_StrepSalini_motif_168.m2m</th>\n",
       "      <th>...</th>\n",
       "      <th>Motif_urine_mass2motif_68.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_78.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_80.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_83.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_88.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_90.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_93.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_94.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_95.m2m</th>\n",
       "      <th>Motif_urine_mass2motif_97.m2m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row ID</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",
       "      <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></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 658 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Motif_StrepSalini_motif_10.m2m  Motif_StrepSalini_motif_105.m2m  \\\n",
       "row ID                                                                    \n",
       "1                                    0                                0   \n",
       "2                                    0                                0   \n",
       "3                                    0                                0   \n",
       "4                                    0                                0   \n",
       "5                                    0                                0   \n",
       "\n",
       "        Motif_StrepSalini_motif_106.m2m  Motif_StrepSalini_motif_108.m2m  \\\n",
       "row ID                                                                     \n",
       "1                                     0                                0   \n",
       "2                                     0                                0   \n",
       "3                                     0                                0   \n",
       "4                                     0                                0   \n",
       "5                                     0                                0   \n",
       "\n",
       "        Motif_StrepSalini_motif_110.m2m  Motif_StrepSalini_motif_113.m2m  \\\n",
       "row ID                                                                     \n",
       "1                                     0                                0   \n",
       "2                                     0                                0   \n",
       "3                                     0                                0   \n",
       "4                                     0                                0   \n",
       "5                                     0                                0   \n",
       "\n",
       "        Motif_StrepSalini_motif_119.m2m  Motif_StrepSalini_motif_128.m2m  \\\n",
       "row ID                                                                     \n",
       "1                                     0                                0   \n",
       "2                                     0                                0   \n",
       "3                                     0                                0   \n",
       "4                                     0                                0   \n",
       "5                                     0                                0   \n",
       "\n",
       "        Motif_StrepSalini_motif_144.m2m  Motif_StrepSalini_motif_168.m2m  ...  \\\n",
       "row ID                                                                    ...   \n",
       "1                                     0                                0  ...   \n",
       "2                                     0                                0  ...   \n",
       "3                                     0                                0  ...   \n",
       "4                                     0                                0  ...   \n",
       "5                                     0                                0  ...   \n",
       "\n",
       "        Motif_urine_mass2motif_68.m2m  Motif_urine_mass2motif_78.m2m  \\\n",
       "row ID                                                                 \n",
       "1                                   0                              0   \n",
       "2                                   0                              0   \n",
       "3                                   0                              0   \n",
       "4                                   0                              0   \n",
       "5                                   0                              0   \n",
       "\n",
       "        Motif_urine_mass2motif_80.m2m  Motif_urine_mass2motif_83.m2m  \\\n",
       "row ID                                                                 \n",
       "1                                   0                              0   \n",
       "2                                   0                              0   \n",
       "3                                   0                              0   \n",
       "4                                   0                              0   \n",
       "5                                   0                              0   \n",
       "\n",
       "        Motif_urine_mass2motif_88.m2m  Motif_urine_mass2motif_90.m2m  \\\n",
       "row ID                                                                 \n",
       "1                                   0                              0   \n",
       "2                                   0                              0   \n",
       "3                                   0                              0   \n",
       "4                                   0                              0   \n",
       "5                                   0                              0   \n",
       "\n",
       "        Motif_urine_mass2motif_93.m2m  Motif_urine_mass2motif_94.m2m  \\\n",
       "row ID                                                                 \n",
       "1                                   0                              0   \n",
       "2                                   0                              0   \n",
       "3                                   0                              0   \n",
       "4                                   0                              0   \n",
       "5                                   0                              0   \n",
       "\n",
       "        Motif_urine_mass2motif_95.m2m  Motif_urine_mass2motif_97.m2m  \n",
       "row ID                                                                \n",
       "1                                   0                              0  \n",
       "2                                   0                              0  \n",
       "3                                   0                              0  \n",
       "4                                   0                              0  \n",
       "5                                   0                              0  \n",
       "\n",
       "[5 rows x 658 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#a.head()\n",
    "x.head()\n",
    "#x.to_csv(\"counts-motif.csv\",sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "functional-kelly",
   "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>Nrep</th>\n",
       "      <th>Nnes</th>\n",
       "      <th>Nrxs</th>\n",
       "      <th>Nsto</th>\n",
       "      <th>Ntab</th>\n",
       "      <th>Nglu</th>\n",
       "      <th>Nben</th>\n",
       "      <th>Ncle</th>\n",
       "      <th>Ngla</th>\n",
       "      <th>Ngoo</th>\n",
       "      <th>...</th>\n",
       "      <th>Nsyl</th>\n",
       "      <th>10 Sol_</th>\n",
       "      <th>1-10 Sol_</th>\n",
       "      <th>Solvent_RA1</th>\n",
       "      <th>Solvent_RA2</th>\n",
       "      <th>Solvent_RA3</th>\n",
       "      <th>Solvent_RA4</th>\n",
       "      <th>Solvent_RA5</th>\n",
       "      <th>Solvent_RA26</th>\n",
       "      <th>Solvent_RA7</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row ID</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",
       "      <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></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1465</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11687</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23160</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13348</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30656</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Nrep  Nnes  Nrxs  Nsto  Ntab  Nglu  Nben  Ncle  Ngla  Ngoo  ...  Nsyl  \\\n",
       "row ID                                                              ...         \n",
       "1465       1     1     1     1     1     1     1     1     1     1  ...     1   \n",
       "11687      0     0     0     0     0     0     1     1     0     1  ...     1   \n",
       "23160      1     1     3     4     3     3     4     4     4     2  ...     2   \n",
       "13348      4     3     4     4     5     5     5     5     2     5  ...     4   \n",
       "30656      4     3     4     4     4     4     4     5     4     4  ...     4   \n",
       "\n",
       "        10 Sol_  1-10 Sol_  Solvent_RA1  Solvent_RA2  Solvent_RA3  \\\n",
       "row ID                                                              \n",
       "1465          1          1            0            0            0   \n",
       "11687         0          0            0            0            0   \n",
       "23160         0          0            0            0            0   \n",
       "13348         0          0            0            1            0   \n",
       "30656         0          0            0            0            0   \n",
       "\n",
       "        Solvent_RA4  Solvent_RA5  Solvent_RA26  Solvent_RA7  \n",
       "row ID                                                       \n",
       "1465              0            0             0            0  \n",
       "11687             0            0             0            0  \n",
       "23160             0            0             0            0  \n",
       "13348             0            0             0            0  \n",
       "30656             0            0             0            0  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#esult_bool = df_quant.join(x, how=\"outer\")\n",
    "#result_bool.head()\n",
    "a.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "accredited-grammar",
   "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>Nrep</th>\n",
       "      <th>Nnes</th>\n",
       "      <th>Nrxs</th>\n",
       "      <th>Nsto</th>\n",
       "      <th>Ntab</th>\n",
       "      <th>Nglu</th>\n",
       "      <th>Nben</th>\n",
       "      <th>Ncle</th>\n",
       "      <th>Ngla</th>\n",
       "      <th>Ngoo</th>\n",
       "      <th>...</th>\n",
       "      <th>Nsyl</th>\n",
       "      <th>10 Sol_</th>\n",
       "      <th>1-10 Sol_</th>\n",
       "      <th>Solvent_RA1</th>\n",
       "      <th>Solvent_RA2</th>\n",
       "      <th>Solvent_RA3</th>\n",
       "      <th>Solvent_RA4</th>\n",
       "      <th>Solvent_RA5</th>\n",
       "      <th>Solvent_RA26</th>\n",
       "      <th>Solvent_RA7</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row ID</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",
       "      <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></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1465</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11687</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23160</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13348</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30656</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Nrep  Nnes  Nrxs  Nsto  Ntab  Nglu  Nben  Ncle  Ngla  Ngoo  ...  Nsyl  \\\n",
       "row ID                                                              ...         \n",
       "1465       1     1     1     1     1     1     1     1     1     1  ...     1   \n",
       "11687      0     0     0     0     0     0     1     1     0     1  ...     1   \n",
       "23160      1     1     3     4     3     3     4     4     4     2  ...     2   \n",
       "13348      4     3     4     4     5     5     5     5     2     5  ...     4   \n",
       "30656      4     3     4     4     4     4     4     5     4     4  ...     4   \n",
       "\n",
       "        10 Sol_  1-10 Sol_  Solvent_RA1  Solvent_RA2  Solvent_RA3  \\\n",
       "row ID                                                              \n",
       "1465          1          1            0            0            0   \n",
       "11687         0          0            0            0            0   \n",
       "23160         0          0            0            0            0   \n",
       "13348         0          0            0            1            0   \n",
       "30656         0          0            0            0            0   \n",
       "\n",
       "        Solvent_RA4  Solvent_RA5  Solvent_RA26  Solvent_RA7  \n",
       "row ID                                                       \n",
       "1465              0            0             0            0  \n",
       "11687             0            0             0            0  \n",
       "23160             0            0             0            0  \n",
       "13348             0            0             0            0  \n",
       "30656             0            0             0            0  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quant0=a.where(a >0,0) \n",
    "quant0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "excellent-valuable",
   "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>Nrep</th>\n",
       "      <th>Nnes</th>\n",
       "      <th>Nrxs</th>\n",
       "      <th>Nsto</th>\n",
       "      <th>Ntab</th>\n",
       "      <th>Nglu</th>\n",
       "      <th>Nben</th>\n",
       "      <th>Ncle</th>\n",
       "      <th>Ngla</th>\n",
       "      <th>Ngoo</th>\n",
       "      <th>...</th>\n",
       "      <th>Nsyl</th>\n",
       "      <th>10 Sol_</th>\n",
       "      <th>1-10 Sol_</th>\n",
       "      <th>Solvent_RA1</th>\n",
       "      <th>Solvent_RA2</th>\n",
       "      <th>Solvent_RA3</th>\n",
       "      <th>Solvent_RA4</th>\n",
       "      <th>Solvent_RA5</th>\n",
       "      <th>Solvent_RA26</th>\n",
       "      <th>Solvent_RA7</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row ID</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",
       "      <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></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1465</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11687</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23160</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13348</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30656</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Nrep  Nnes  Nrxs  Nsto  Ntab  Nglu  Nben  Ncle  Ngla  Ngoo  ...  Nsyl  \\\n",
       "row ID                                                              ...         \n",
       "1465       1     1     1     1     1     1     1     1     1     1  ...     1   \n",
       "11687      0     0     0     0     0     0     1     1     0     1  ...     1   \n",
       "23160      1     1     1     1     1     1     1     1     1     1  ...     1   \n",
       "13348      1     1     1     1     1     1     1     1     1     1  ...     1   \n",
       "30656      1     1     1     1     1     1     1     1     1     1  ...     1   \n",
       "\n",
       "        10 Sol_  1-10 Sol_  Solvent_RA1  Solvent_RA2  Solvent_RA3  \\\n",
       "row ID                                                              \n",
       "1465          1          1            0            0            0   \n",
       "11687         0          0            0            0            0   \n",
       "23160         0          0            0            0            0   \n",
       "13348         0          0            0            1            0   \n",
       "30656         0          0            0            0            0   \n",
       "\n",
       "        Solvent_RA4  Solvent_RA5  Solvent_RA26  Solvent_RA7  \n",
       "row ID                                                       \n",
       "1465              0            0             0            0  \n",
       "11687             0            0             0            0  \n",
       "23160             0            0             0            0  \n",
       "13348             0            0             0            0  \n",
       "30656             0            0             0            0  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quant1=quant0.where(quant0==0,1)\n",
    "quant1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "silent-adapter",
   "metadata": {},
   "outputs": [],
   "source": [
    "quant1.to_csv(\"feature-bin.csv\",sep=',')\n",
    "#nrep=quant1[[\"07 Nrep\"]]\n",
    "#nrep.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "confused-bride",
   "metadata": {},
   "outputs": [],
   "source": [
    "#rslt_df = nrep[nrep[\"07 Nrep\"] > 0]\n",
    "#rslt_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "logical-discretion",
   "metadata": {},
   "outputs": [],
   "source": [
    "#test = rslt_df.join(x, how=\"inner\")\n",
    "#test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "daily-bulgaria",
   "metadata": {},
   "outputs": [],
   "source": [
    "#allsum=test.sum(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "determined-death",
   "metadata": {},
   "outputs": [],
   "source": [
    "#allsum.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "victorian-helen",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Nrep', 'Nnes', 'Nrxs', 'Nsto', 'Ntab', 'Nglu', 'Nben', 'Ncle', 'Ngla', 'Ngoo', 'Natt', 'Nnud', 'Nobt', 'Npal', 'Npan', 'Nplu', 'Nrus', 'Nset', 'Nwut', 'Nsyl', '10 Sol_', '1-10 Sol_', 'Solvent_RA1', 'Solvent_RA2', 'Solvent_RA3', 'Solvent_RA4', 'Solvent_RA5', 'Solvent_RA26', 'Solvent_RA7']\n"
     ]
    }
   ],
   "source": [
    "headers=quant1.columns.values.tolist()\n",
    "listindex=x.columns.values.tolist()\n",
    "#fullindex=headers+listindex\n",
    "print(headers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "executed-bulgarian",
   "metadata": {},
   "outputs": [],
   "source": [
    "kwargs = {}\n",
    "for i in headers:\n",
    "    fsamples=quant1[[i]] #retrieve one sample\n",
    "    fsamples = fsamples[fsamples[i] > 0] # remove zero occurences of features\n",
    "    fmotifs = fsamples.join(x, how=\"inner\") # merge with motifs, but only the ones present\n",
    "    allsums=fmotifs.sum(axis=0)\n",
    "    kwargs[i] = allsums\n",
    "\n",
    "#print(kwargs)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "representative-tumor",
   "metadata": {},
   "outputs": [],
   "source": [
    "#type(kwargs['07 Nrep'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "numeric-pepper",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\ndf2 = pd.DataFrame(columns=headers)\\nfor i in headers:\\n    df1=kwargs[i].to_frame()\\n    df2.join(df1, how=\"outer\")\\n    \\ndf2.head()\\n'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.DataFrame(kwargs, index=listindex )\n",
    "\n",
    "df4 =pd.DataFrame(kwargs, index=headers )\n",
    "\"\"\"\n",
    "df2 = pd.DataFrame(columns=headers)\n",
    "for i in headers:\n",
    "    df1=kwargs[i].to_frame()\n",
    "    df2.join(df1, how=\"outer\")\n",
    "    \n",
    "df2.head()\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "indoor-jungle",
   "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>Nrep</th>\n",
       "      <th>Nnes</th>\n",
       "      <th>Nrxs</th>\n",
       "      <th>Nsto</th>\n",
       "      <th>Ntab</th>\n",
       "      <th>Nglu</th>\n",
       "      <th>Nben</th>\n",
       "      <th>Ncle</th>\n",
       "      <th>Ngla</th>\n",
       "      <th>Ngoo</th>\n",
       "      <th>...</th>\n",
       "      <th>Nsyl</th>\n",
       "      <th>10 Sol_</th>\n",
       "      <th>1-10 Sol_</th>\n",
       "      <th>Solvent_RA1</th>\n",
       "      <th>Solvent_RA2</th>\n",
       "      <th>Solvent_RA3</th>\n",
       "      <th>Solvent_RA4</th>\n",
       "      <th>Solvent_RA5</th>\n",
       "      <th>Solvent_RA26</th>\n",
       "      <th>Solvent_RA7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Motif_StrepSalini_motif_10.m2m</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Motif_StrepSalini_motif_105.m2m</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Motif_StrepSalini_motif_106.m2m</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Motif_StrepSalini_motif_108.m2m</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Motif_StrepSalini_motif_110.m2m</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>19</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Nrep  Nnes  Nrxs  Nsto  Ntab  Nglu  Nben  \\\n",
       "Motif_StrepSalini_motif_10.m2m      1     2     1     1     1     2     1   \n",
       "Motif_StrepSalini_motif_105.m2m     1     1     1     1     1     0     1   \n",
       "Motif_StrepSalini_motif_106.m2m     3     3     3     2     4     2     2   \n",
       "Motif_StrepSalini_motif_108.m2m     1     1     1     1     2     2     2   \n",
       "Motif_StrepSalini_motif_110.m2m     1     1     0     2    13     2    30   \n",
       "\n",
       "                                 Ncle  Ngla  Ngoo  ...  Nsyl  10 Sol_  \\\n",
       "Motif_StrepSalini_motif_10.m2m      0     1     1  ...     0        0   \n",
       "Motif_StrepSalini_motif_105.m2m     1     0     0  ...     1        0   \n",
       "Motif_StrepSalini_motif_106.m2m     1     1     1  ...     4        0   \n",
       "Motif_StrepSalini_motif_108.m2m     2     3     2  ...     0        0   \n",
       "Motif_StrepSalini_motif_110.m2m     9     9    19  ...     1        0   \n",
       "\n",
       "                                 1-10 Sol_  Solvent_RA1  Solvent_RA2  \\\n",
       "Motif_StrepSalini_motif_10.m2m           0            0            0   \n",
       "Motif_StrepSalini_motif_105.m2m          0            0            0   \n",
       "Motif_StrepSalini_motif_106.m2m          0            0            0   \n",
       "Motif_StrepSalini_motif_108.m2m          0            0            0   \n",
       "Motif_StrepSalini_motif_110.m2m          0            0            0   \n",
       "\n",
       "                                 Solvent_RA3  Solvent_RA4  Solvent_RA5  \\\n",
       "Motif_StrepSalini_motif_10.m2m             0            0            0   \n",
       "Motif_StrepSalini_motif_105.m2m            0            0            0   \n",
       "Motif_StrepSalini_motif_106.m2m            0            0            0   \n",
       "Motif_StrepSalini_motif_108.m2m            0            0            0   \n",
       "Motif_StrepSalini_motif_110.m2m            0            0            0   \n",
       "\n",
       "                                 Solvent_RA26  Solvent_RA7  \n",
       "Motif_StrepSalini_motif_10.m2m              0            0  \n",
       "Motif_StrepSalini_motif_105.m2m             0            0  \n",
       "Motif_StrepSalini_motif_106.m2m             0            0  \n",
       "Motif_StrepSalini_motif_108.m2m             0            0  \n",
       "Motif_StrepSalini_motif_110.m2m             0            0  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#liste=[\"a\",\"b\",\"c\"]\n",
    "#df3 = pd.DataFrame( index=listindex )\n",
    "#df(3) = pd.DataFrame(columns = ['Name', 'Articles', 'Improved'], \n",
    "                   #index = ['a', 'b', 'c'])\n",
    "#df3.columns=liste\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "worse-aquarium",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_csv(\"alltissues15072021_counts-motif-sumall.csv\",sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "offensive-orleans",
   "metadata": {},
   "outputs": [],
   "source": [
    "df4.fillna(0,inplace=True)\n",
    "ds=df4.sum(axis=0)\n",
    "ds.to_csv(\"alltissues15072021_counts-samples.csv\",sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adaptive-cheat",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}