new_vertices.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "vertices = pd.read_csv('vertices.csv')\n",
    "edges = pd.read_csv('edges.csv')\n",
    "ids = pd.read_csv('ids.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = edges.groupby('id_1', as_index=False).agg({'id_2':'count', 'value':'sum', 'n_transactions':'sum'})\n",
    "right = edges.groupby('id_1', as_index=False).agg({'id_2':'count', 'value':'sum', 'n_transactions':'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = edges.append(edges.rename(columns={'id_2':'id_1', 'id_1':'id_2'}),sort=False).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_vertices = temp.groupby('id_1', as_index=False).agg({'id_2':'count', 'value':'sum', 'n_transactions':'sum'}).rename(columns={'id_2':'count_connections', 'value':'sum_value', 'n_transactions':'sum_transactions'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_vertices = new_vertices.rename(columns={'id_1':'id'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "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>id</th>\n",
       "      <th>count_connections</th>\n",
       "      <th>sum_value</th>\n",
       "      <th>sum_transactions</th>\n",
       "      <th>main_okved</th>\n",
       "      <th>region_code</th>\n",
       "      <th>company_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>7.478158e+05</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>46.75</td>\n",
       "      <td>77</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>9.293199e+05</td>\n",
       "      <td>187.749487</td>\n",
       "      <td>41.20</td>\n",
       "      <td>78</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4.788663e+05</td>\n",
       "      <td>207.791701</td>\n",
       "      <td>25.11</td>\n",
       "      <td>50</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.361190e+06</td>\n",
       "      <td>604.608179</td>\n",
       "      <td>45.31</td>\n",
       "      <td>89</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1.578424e+06</td>\n",
       "      <td>535.494874</td>\n",
       "      <td>56.10</td>\n",
       "      <td>50</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>1514708</td>\n",
       "      <td>1534745</td>\n",
       "      <td>3</td>\n",
       "      <td>1.142074e+06</td>\n",
       "      <td>162.995900</td>\n",
       "      <td>63.99</td>\n",
       "      <td>77</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1514709</td>\n",
       "      <td>1534746</td>\n",
       "      <td>1</td>\n",
       "      <td>5.035241e+05</td>\n",
       "      <td>207.791701</td>\n",
       "      <td>47.19</td>\n",
       "      <td>66</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1514710</td>\n",
       "      <td>1534747</td>\n",
       "      <td>2</td>\n",
       "      <td>7.906844e+05</td>\n",
       "      <td>81.997950</td>\n",
       "      <td>41.20</td>\n",
       "      <td>77</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1514711</td>\n",
       "      <td>1534748</td>\n",
       "      <td>12</td>\n",
       "      <td>4.597791e+06</td>\n",
       "      <td>618.781450</td>\n",
       "      <td>74.20</td>\n",
       "      <td>33</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1514712</td>\n",
       "      <td>1534749</td>\n",
       "      <td>3</td>\n",
       "      <td>1.215793e+06</td>\n",
       "      <td>484.777078</td>\n",
       "      <td>68.20</td>\n",
       "      <td>66</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1514713 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  count_connections     sum_value  sum_transactions  \\\n",
       "0              1                  2  7.478158e+05          2.000000   \n",
       "1              2                  2  9.293199e+05        187.749487   \n",
       "2              3                  1  4.788663e+05        207.791701   \n",
       "3              4                  4  1.361190e+06        604.608179   \n",
       "4              5                  4  1.578424e+06        535.494874   \n",
       "...          ...                ...           ...               ...   \n",
       "1514708  1534745                  3  1.142074e+06        162.995900   \n",
       "1514709  1534746                  1  5.035241e+05        207.791701   \n",
       "1514710  1534747                  2  7.906844e+05         81.997950   \n",
       "1514711  1534748                 12  4.597791e+06        618.781450   \n",
       "1514712  1534749                  3  1.215793e+06        484.777078   \n",
       "\n",
       "         main_okved  region_code company_type  \n",
       "0             46.75           77      Limited  \n",
       "1             41.20           78      Limited  \n",
       "2             25.11           50      Limited  \n",
       "3             45.31           89      Limited  \n",
       "4             56.10           50      Limited  \n",
       "...             ...          ...          ...  \n",
       "1514708       63.99           77   Individual  \n",
       "1514709       47.19           66   Individual  \n",
       "1514710       41.20           77   Individual  \n",
       "1514711       74.20           33   Individual  \n",
       "1514712       68.20           66   Individual  \n",
       "\n",
       "[1514713 rows x 7 columns]"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('new_vertices.csv').merge(vertices, on='id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_vertices = new_vertices.merge(vertices, on='id', how='outer').fillna(0).sort_values('id').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "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>id</th>\n",
       "      <th>count_connections</th>\n",
       "      <th>sum_value</th>\n",
       "      <th>sum_transactions</th>\n",
       "      <th>main_okved</th>\n",
       "      <th>region_code</th>\n",
       "      <th>company_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.478158e+05</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>46.75</td>\n",
       "      <td>77</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.293199e+05</td>\n",
       "      <td>187.749487</td>\n",
       "      <td>41.20</td>\n",
       "      <td>78</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.788663e+05</td>\n",
       "      <td>207.791701</td>\n",
       "      <td>25.11</td>\n",
       "      <td>50</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.361190e+06</td>\n",
       "      <td>604.608179</td>\n",
       "      <td>45.31</td>\n",
       "      <td>89</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.578424e+06</td>\n",
       "      <td>535.494874</td>\n",
       "      <td>56.10</td>\n",
       "      <td>50</td>\n",
       "      <td>Limited</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>1534744</td>\n",
       "      <td>1534745</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.142074e+06</td>\n",
       "      <td>162.995900</td>\n",
       "      <td>63.99</td>\n",
       "      <td>77</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1534745</td>\n",
       "      <td>1534746</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.035241e+05</td>\n",
       "      <td>207.791701</td>\n",
       "      <td>47.19</td>\n",
       "      <td>66</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1534746</td>\n",
       "      <td>1534747</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.906844e+05</td>\n",
       "      <td>81.997950</td>\n",
       "      <td>41.20</td>\n",
       "      <td>77</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1534747</td>\n",
       "      <td>1534748</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.597791e+06</td>\n",
       "      <td>618.781450</td>\n",
       "      <td>74.20</td>\n",
       "      <td>33</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1534748</td>\n",
       "      <td>1534749</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.215793e+06</td>\n",
       "      <td>484.777078</td>\n",
       "      <td>68.20</td>\n",
       "      <td>66</td>\n",
       "      <td>Individual</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1534749 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  count_connections     sum_value  sum_transactions  \\\n",
       "0              1                2.0  7.478158e+05          2.000000   \n",
       "1              2                2.0  9.293199e+05        187.749487   \n",
       "2              3                1.0  4.788663e+05        207.791701   \n",
       "3              4                4.0  1.361190e+06        604.608179   \n",
       "4              5                4.0  1.578424e+06        535.494874   \n",
       "...          ...                ...           ...               ...   \n",
       "1534744  1534745                3.0  1.142074e+06        162.995900   \n",
       "1534745  1534746                1.0  5.035241e+05        207.791701   \n",
       "1534746  1534747                2.0  7.906844e+05         81.997950   \n",
       "1534747  1534748               12.0  4.597791e+06        618.781450   \n",
       "1534748  1534749                3.0  1.215793e+06        484.777078   \n",
       "\n",
       "         main_okved  region_code company_type  \n",
       "0             46.75           77      Limited  \n",
       "1             41.20           78      Limited  \n",
       "2             25.11           50      Limited  \n",
       "3             45.31           89      Limited  \n",
       "4             56.10           50      Limited  \n",
       "...             ...          ...          ...  \n",
       "1534744       63.99           77   Individual  \n",
       "1534745       47.19           66   Individual  \n",
       "1534746       41.20           77   Individual  \n",
       "1534747       74.20           33   Individual  \n",
       "1534748       68.20           66   Individual  \n",
       "\n",
       "[1534749 rows x 7 columns]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_vertices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn import preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "le = preprocessing.LabelEncoder()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "le.fit(new_vertices.company_type)\n",
    "le.classes_\n",
    "le.transform(new_vertices.company_type)\n",
    "new_vertices.company_type = le.transform(new_vertices.company_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "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>id</th>\n",
       "      <th>count_connections</th>\n",
       "      <th>sum_value</th>\n",
       "      <th>sum_transactions</th>\n",
       "      <th>main_okved</th>\n",
       "      <th>region_code</th>\n",
       "      <th>company_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.478158e+05</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>46.75</td>\n",
       "      <td>77</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.293199e+05</td>\n",
       "      <td>187.749487</td>\n",
       "      <td>41.20</td>\n",
       "      <td>78</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.788663e+05</td>\n",
       "      <td>207.791701</td>\n",
       "      <td>25.11</td>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.361190e+06</td>\n",
       "      <td>604.608179</td>\n",
       "      <td>45.31</td>\n",
       "      <td>89</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.578424e+06</td>\n",
       "      <td>535.494874</td>\n",
       "      <td>56.10</td>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>1534744</td>\n",
       "      <td>1534745</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.142074e+06</td>\n",
       "      <td>162.995900</td>\n",
       "      <td>63.99</td>\n",
       "      <td>77</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1534745</td>\n",
       "      <td>1534746</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.035241e+05</td>\n",
       "      <td>207.791701</td>\n",
       "      <td>47.19</td>\n",
       "      <td>66</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1534746</td>\n",
       "      <td>1534747</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.906844e+05</td>\n",
       "      <td>81.997950</td>\n",
       "      <td>41.20</td>\n",
       "      <td>77</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1534747</td>\n",
       "      <td>1534748</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.597791e+06</td>\n",
       "      <td>618.781450</td>\n",
       "      <td>74.20</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1534748</td>\n",
       "      <td>1534749</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.215793e+06</td>\n",
       "      <td>484.777078</td>\n",
       "      <td>68.20</td>\n",
       "      <td>66</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1534749 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  count_connections     sum_value  sum_transactions  \\\n",
       "0              1                2.0  7.478158e+05          2.000000   \n",
       "1              2                2.0  9.293199e+05        187.749487   \n",
       "2              3                1.0  4.788663e+05        207.791701   \n",
       "3              4                4.0  1.361190e+06        604.608179   \n",
       "4              5                4.0  1.578424e+06        535.494874   \n",
       "...          ...                ...           ...               ...   \n",
       "1534744  1534745                3.0  1.142074e+06        162.995900   \n",
       "1534745  1534746                1.0  5.035241e+05        207.791701   \n",
       "1534746  1534747                2.0  7.906844e+05         81.997950   \n",
       "1534747  1534748               12.0  4.597791e+06        618.781450   \n",
       "1534748  1534749                3.0  1.215793e+06        484.777078   \n",
       "\n",
       "         main_okved  region_code  company_type  \n",
       "0             46.75           77             3  \n",
       "1             41.20           78             3  \n",
       "2             25.11           50             3  \n",
       "3             45.31           89             3  \n",
       "4             56.10           50             3  \n",
       "...             ...          ...           ...  \n",
       "1534744       63.99           77             2  \n",
       "1534745       47.19           66             2  \n",
       "1534746       41.20           77             2  \n",
       "1534747       74.20           33             2  \n",
       "1534748       68.20           66             2  \n",
       "\n",
       "[1534749 rows x 7 columns]"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_vertices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "def first_okved(s):\n",
    "    return(int(str(s).split('.')[0]))\n",
    "def second_okved(s):\n",
    "    return(int(str(s).split('.')[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_vertices['first_okved']= new_vertices.main_okved.apply(first_okved)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_vertices.to_csv('new_vertices.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}