baseline_by_project.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Baseline by project (public score=0.1068)\n",
    "\n",
    "What has been done:\n",
    "- calculate funding amount from weight\n",
    "- sum up funding amount by project\n",
    "- use above funding amount to predict\n",
    "- null values are filled with 0.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20958\n",
      "4261\n"
     ]
    },
    {
     "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>project_a</th>\n",
       "      <th>project_b</th>\n",
       "      <th>weight_a</th>\n",
       "      <th>weight_b</th>\n",
       "      <th>total_amount_usd</th>\n",
       "      <th>funder</th>\n",
       "      <th>quarter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>715</td>\n",
       "      <td>https://github.com/mochajs/mocha</td>\n",
       "      <td>https://github.com/chzyer/readline</td>\n",
       "      <td>0.961581</td>\n",
       "      <td>0.038419</td>\n",
       "      <td>2681</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2016-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>716</td>\n",
       "      <td>https://github.com/mochajs/mocha</td>\n",
       "      <td>https://github.com/gulpjs/gulp</td>\n",
       "      <td>0.992302</td>\n",
       "      <td>0.007698</td>\n",
       "      <td>2598</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2016-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>717</td>\n",
       "      <td>https://github.com/chzyer/readline</td>\n",
       "      <td>https://github.com/gulpjs/gulp</td>\n",
       "      <td>0.837398</td>\n",
       "      <td>0.162602</td>\n",
       "      <td>123</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2016-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>718</td>\n",
       "      <td>https://github.com/chzyer/readline</td>\n",
       "      <td>https://github.com/gulpjs/gulp</td>\n",
       "      <td>0.231595</td>\n",
       "      <td>0.768405</td>\n",
       "      <td>652</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2016-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>719</td>\n",
       "      <td>https://github.com/chzyer/readline</td>\n",
       "      <td>https://github.com/mochajs/mocha</td>\n",
       "      <td>0.078851</td>\n",
       "      <td>0.921149</td>\n",
       "      <td>1915</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2016-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                           project_a  \\\n",
       "0  715    https://github.com/mochajs/mocha   \n",
       "1  716    https://github.com/mochajs/mocha   \n",
       "2  717  https://github.com/chzyer/readline   \n",
       "3  718  https://github.com/chzyer/readline   \n",
       "4  719  https://github.com/chzyer/readline   \n",
       "\n",
       "                            project_b  weight_a  weight_b  total_amount_usd  \\\n",
       "0  https://github.com/chzyer/readline  0.961581  0.038419              2681   \n",
       "1      https://github.com/gulpjs/gulp  0.992302  0.007698              2598   \n",
       "2      https://github.com/gulpjs/gulp  0.837398  0.162602               123   \n",
       "3      https://github.com/gulpjs/gulp  0.231595  0.768405               652   \n",
       "4    https://github.com/mochajs/mocha  0.078851  0.921149              1915   \n",
       "\n",
       "           funder  quarter  \n",
       "0  opencollective  2016-04  \n",
       "1  opencollective  2016-04  \n",
       "2  opencollective  2016-04  \n",
       "3  opencollective  2016-07  \n",
       "4  opencollective  2016-07  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('dataset/dataset.csv')\n",
    "test_df = pd.read_csv('dataset/test.csv')\n",
    "\n",
    "# dataset size\n",
    "print(len(df))\n",
    "print(len(test_df))\n",
    "\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "104\n",
      "82\n",
      "117\n"
     ]
    }
   ],
   "source": [
    "# check unique projects (project_a and project_b intersect)\n",
    "\n",
    "df_nunique = set(df['project_a'].unique().tolist() + df['project_b'].unique().tolist())\n",
    "test_df_nunique = set(test_df['project_a'].unique().tolist() + test_df['project_b'].unique().tolist())\n",
    "\n",
    "total_unique = df_nunique.union(test_df_nunique)\n",
    "\n",
    "print(len(df_nunique))\n",
    "print(len(test_df_nunique))\n",
    "print(len(total_unique))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>project_a</th>\n",
       "      <th>project_b</th>\n",
       "      <th>total_amount_usd</th>\n",
       "      <th>funder</th>\n",
       "      <th>quarter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20884</td>\n",
       "      <td>https://github.com/rollup/rollup</td>\n",
       "      <td>https://github.com/webpack/webpack</td>\n",
       "      <td>29097</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2024-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20885</td>\n",
       "      <td>https://github.com/rollup/rollup</td>\n",
       "      <td>https://github.com/babel/babel</td>\n",
       "      <td>26093</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2024-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20886</td>\n",
       "      <td>https://github.com/rollup/rollup</td>\n",
       "      <td>https://github.com/sindresorhus/type-fest</td>\n",
       "      <td>4460</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2024-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20887</td>\n",
       "      <td>https://github.com/rollup/rollup</td>\n",
       "      <td>https://github.com/inikulin/parse5</td>\n",
       "      <td>2454</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2024-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20888</td>\n",
       "      <td>https://github.com/rollup/rollup</td>\n",
       "      <td>https://github.com/zloirock/core-js</td>\n",
       "      <td>13229</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>2024-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4247</th>\n",
       "      <td>25206</td>\n",
       "      <td>https://github.com/ethereum/go-ethereum</td>\n",
       "      <td>https://github.com/chainsafe/lodestar</td>\n",
       "      <td>572742</td>\n",
       "      <td>optimism</td>\n",
       "      <td>2024-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4249</th>\n",
       "      <td>25208</td>\n",
       "      <td>https://github.com/ethereum/go-ethereum</td>\n",
       "      <td>https://github.com/bluealloy/revm</td>\n",
       "      <td>622843</td>\n",
       "      <td>optimism</td>\n",
       "      <td>2024-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4251</th>\n",
       "      <td>25210</td>\n",
       "      <td>https://github.com/sigp/lighthouse</td>\n",
       "      <td>https://github.com/chainsafe/lodestar</td>\n",
       "      <td>411176</td>\n",
       "      <td>optimism</td>\n",
       "      <td>2024-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4253</th>\n",
       "      <td>25212</td>\n",
       "      <td>https://github.com/sigp/lighthouse</td>\n",
       "      <td>https://github.com/bluealloy/revm</td>\n",
       "      <td>461277</td>\n",
       "      <td>optimism</td>\n",
       "      <td>2024-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4256</th>\n",
       "      <td>25215</td>\n",
       "      <td>https://github.com/chainsafe/lodestar</td>\n",
       "      <td>https://github.com/bluealloy/revm</td>\n",
       "      <td>398133</td>\n",
       "      <td>optimism</td>\n",
       "      <td>2024-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3865 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                                project_a  \\\n",
       "0     20884         https://github.com/rollup/rollup   \n",
       "1     20885         https://github.com/rollup/rollup   \n",
       "2     20886         https://github.com/rollup/rollup   \n",
       "3     20887         https://github.com/rollup/rollup   \n",
       "4     20888         https://github.com/rollup/rollup   \n",
       "...     ...                                      ...   \n",
       "4247  25206  https://github.com/ethereum/go-ethereum   \n",
       "4249  25208  https://github.com/ethereum/go-ethereum   \n",
       "4251  25210       https://github.com/sigp/lighthouse   \n",
       "4253  25212       https://github.com/sigp/lighthouse   \n",
       "4256  25215    https://github.com/chainsafe/lodestar   \n",
       "\n",
       "                                      project_b  total_amount_usd  \\\n",
       "0            https://github.com/webpack/webpack             29097   \n",
       "1                https://github.com/babel/babel             26093   \n",
       "2     https://github.com/sindresorhus/type-fest              4460   \n",
       "3            https://github.com/inikulin/parse5              2454   \n",
       "4           https://github.com/zloirock/core-js             13229   \n",
       "...                                         ...               ...   \n",
       "4247      https://github.com/chainsafe/lodestar            572742   \n",
       "4249          https://github.com/bluealloy/revm            622843   \n",
       "4251      https://github.com/chainsafe/lodestar            411176   \n",
       "4253          https://github.com/bluealloy/revm            461277   \n",
       "4256          https://github.com/bluealloy/revm            398133   \n",
       "\n",
       "              funder  quarter  \n",
       "0     opencollective  2024-01  \n",
       "1     opencollective  2024-01  \n",
       "2     opencollective  2024-01  \n",
       "3     opencollective  2024-01  \n",
       "4     opencollective  2024-01  \n",
       "...              ...      ...  \n",
       "4247        optimism  2024-10  \n",
       "4249        optimism  2024-10  \n",
       "4251        optimism  2024-10  \n",
       "4253        optimism  2024-10  \n",
       "4256        optimism  2024-10  \n",
       "\n",
       "[3865 rows x 6 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check if there are any row in test_df that both project_a and project_b are in df\n",
    "test_df[test_df['project_a'].isin(df_nunique) & test_df['project_b'].isin(df_nunique)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">weight_a</th>\n",
       "      <th colspan=\"8\" halign=\"left\">weight_b</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>project_a</th>\n",
       "      <th>project_b</th>\n",
       "      <th>funder</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 rowspan=\"5\" valign=\"top\">https://github.com/ajv-validator/ajv</th>\n",
       "      <th>https://github.com/axios/axios</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>4.0</td>\n",
       "      <td>0.734310</td>\n",
       "      <td>0.140193</td>\n",
       "      <td>0.548673</td>\n",
       "      <td>0.690003</td>\n",
       "      <td>0.750239</td>\n",
       "      <td>0.794546</td>\n",
       "      <td>0.888091</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.265690</td>\n",
       "      <td>0.140193</td>\n",
       "      <td>0.111909</td>\n",
       "      <td>0.205454</td>\n",
       "      <td>0.249761</td>\n",
       "      <td>0.309997</td>\n",
       "      <td>0.451327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>https://github.com/babel/babel</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>9.0</td>\n",
       "      <td>0.096502</td>\n",
       "      <td>0.123520</td>\n",
       "      <td>0.001080</td>\n",
       "      <td>0.016056</td>\n",
       "      <td>0.053034</td>\n",
       "      <td>0.105648</td>\n",
       "      <td>0.394981</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.903498</td>\n",
       "      <td>0.123520</td>\n",
       "      <td>0.605019</td>\n",
       "      <td>0.894352</td>\n",
       "      <td>0.946966</td>\n",
       "      <td>0.983944</td>\n",
       "      <td>0.998920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>https://github.com/boa-dev/boa</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>6.0</td>\n",
       "      <td>0.695423</td>\n",
       "      <td>0.363096</td>\n",
       "      <td>0.166263</td>\n",
       "      <td>0.445942</td>\n",
       "      <td>0.892226</td>\n",
       "      <td>0.918087</td>\n",
       "      <td>0.997935</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.304577</td>\n",
       "      <td>0.363096</td>\n",
       "      <td>0.002065</td>\n",
       "      <td>0.081913</td>\n",
       "      <td>0.107774</td>\n",
       "      <td>0.554058</td>\n",
       "      <td>0.833737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>https://github.com/brooooooklyn/snappy</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.848439</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.848439</td>\n",
       "      <td>0.848439</td>\n",
       "      <td>0.848439</td>\n",
       "      <td>0.848439</td>\n",
       "      <td>0.848439</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.151561</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.151561</td>\n",
       "      <td>0.151561</td>\n",
       "      <td>0.151561</td>\n",
       "      <td>0.151561</td>\n",
       "      <td>0.151561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>https://github.com/browserslist/browserslist</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>6.0</td>\n",
       "      <td>0.817813</td>\n",
       "      <td>0.207995</td>\n",
       "      <td>0.401961</td>\n",
       "      <td>0.851482</td>\n",
       "      <td>0.881390</td>\n",
       "      <td>0.927911</td>\n",
       "      <td>0.957207</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.182187</td>\n",
       "      <td>0.207995</td>\n",
       "      <td>0.042793</td>\n",
       "      <td>0.072089</td>\n",
       "      <td>0.118610</td>\n",
       "      <td>0.148518</td>\n",
       "      <td>0.598039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">https://github.com/zloirock/core-js</th>\n",
       "      <th>https://github.com/webdriverio/webdriverio</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>4.0</td>\n",
       "      <td>0.986140</td>\n",
       "      <td>0.008558</td>\n",
       "      <td>0.975473</td>\n",
       "      <td>0.982648</td>\n",
       "      <td>0.986426</td>\n",
       "      <td>0.989918</td>\n",
       "      <td>0.996235</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.013860</td>\n",
       "      <td>0.008558</td>\n",
       "      <td>0.003765</td>\n",
       "      <td>0.010082</td>\n",
       "      <td>0.013574</td>\n",
       "      <td>0.017352</td>\n",
       "      <td>0.024527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>https://github.com/webpack/webpack</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>9.0</td>\n",
       "      <td>0.144986</td>\n",
       "      <td>0.091189</td>\n",
       "      <td>0.033526</td>\n",
       "      <td>0.091070</td>\n",
       "      <td>0.139393</td>\n",
       "      <td>0.172637</td>\n",
       "      <td>0.329543</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.855014</td>\n",
       "      <td>0.091189</td>\n",
       "      <td>0.670457</td>\n",
       "      <td>0.827363</td>\n",
       "      <td>0.860607</td>\n",
       "      <td>0.908930</td>\n",
       "      <td>0.966474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>https://github.com/webreflection/flatted</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>5.0</td>\n",
       "      <td>0.873306</td>\n",
       "      <td>0.110943</td>\n",
       "      <td>0.736988</td>\n",
       "      <td>0.768152</td>\n",
       "      <td>0.944613</td>\n",
       "      <td>0.954450</td>\n",
       "      <td>0.962325</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.126694</td>\n",
       "      <td>0.110943</td>\n",
       "      <td>0.037675</td>\n",
       "      <td>0.045550</td>\n",
       "      <td>0.055387</td>\n",
       "      <td>0.231848</td>\n",
       "      <td>0.263012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>https://github.com/wooorm/markdown-table</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.396519</td>\n",
       "      <td>0.149617</td>\n",
       "      <td>0.238787</td>\n",
       "      <td>0.326567</td>\n",
       "      <td>0.414347</td>\n",
       "      <td>0.475385</td>\n",
       "      <td>0.536424</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.603481</td>\n",
       "      <td>0.149617</td>\n",
       "      <td>0.463576</td>\n",
       "      <td>0.524615</td>\n",
       "      <td>0.585653</td>\n",
       "      <td>0.673433</td>\n",
       "      <td>0.761213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>https://github.com/yarnpkg/yarn</th>\n",
       "      <th>opencollective</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.876369</td>\n",
       "      <td>0.124274</td>\n",
       "      <td>0.739552</td>\n",
       "      <td>0.823424</td>\n",
       "      <td>0.907297</td>\n",
       "      <td>0.944777</td>\n",
       "      <td>0.982257</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.123631</td>\n",
       "      <td>0.124274</td>\n",
       "      <td>0.017743</td>\n",
       "      <td>0.055223</td>\n",
       "      <td>0.092703</td>\n",
       "      <td>0.176576</td>\n",
       "      <td>0.260448</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5224 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                 weight_a  \\\n",
       "                                                                                                    count   \n",
       "project_a                            project_b                                    funder                    \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective      4.0   \n",
       "                                     https://github.com/babel/babel               opencollective      9.0   \n",
       "                                     https://github.com/boa-dev/boa               opencollective      6.0   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective      1.0   \n",
       "                                     https://github.com/browserslist/browserslist opencollective      6.0   \n",
       "...                                                                                                   ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective      4.0   \n",
       "                                     https://github.com/webpack/webpack           opencollective      9.0   \n",
       "                                     https://github.com/webreflection/flatted     opencollective      5.0   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective      3.0   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective      3.0   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                      mean   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.734310   \n",
       "                                     https://github.com/babel/babel               opencollective  0.096502   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.695423   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.848439   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.817813   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.986140   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.144986   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.873306   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.396519   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.876369   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       std   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.140193   \n",
       "                                     https://github.com/babel/babel               opencollective  0.123520   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.363096   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective       NaN   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.207995   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.008558   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.091189   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.110943   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.149617   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.124274   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       min   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.548673   \n",
       "                                     https://github.com/babel/babel               opencollective  0.001080   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.166263   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.848439   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.401961   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.975473   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.033526   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.736988   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.238787   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.739552   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       25%   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.690003   \n",
       "                                     https://github.com/babel/babel               opencollective  0.016056   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.445942   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.848439   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.851482   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.982648   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.091070   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.768152   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.326567   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.823424   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       50%   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.750239   \n",
       "                                     https://github.com/babel/babel               opencollective  0.053034   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.892226   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.848439   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.881390   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.986426   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.139393   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.944613   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.414347   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.907297   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       75%   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.794546   \n",
       "                                     https://github.com/babel/babel               opencollective  0.105648   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.918087   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.848439   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.927911   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.989918   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.172637   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.954450   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.475385   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.944777   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       max   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.888091   \n",
       "                                     https://github.com/babel/babel               opencollective  0.394981   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.997935   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.848439   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.957207   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.996235   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.329543   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.962325   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.536424   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.982257   \n",
       "\n",
       "                                                                                                 weight_b  \\\n",
       "                                                                                                    count   \n",
       "project_a                            project_b                                    funder                    \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective      4.0   \n",
       "                                     https://github.com/babel/babel               opencollective      9.0   \n",
       "                                     https://github.com/boa-dev/boa               opencollective      6.0   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective      1.0   \n",
       "                                     https://github.com/browserslist/browserslist opencollective      6.0   \n",
       "...                                                                                                   ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective      4.0   \n",
       "                                     https://github.com/webpack/webpack           opencollective      9.0   \n",
       "                                     https://github.com/webreflection/flatted     opencollective      5.0   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective      3.0   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective      3.0   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                      mean   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.265690   \n",
       "                                     https://github.com/babel/babel               opencollective  0.903498   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.304577   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.151561   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.182187   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.013860   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.855014   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.126694   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.603481   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.123631   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       std   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.140193   \n",
       "                                     https://github.com/babel/babel               opencollective  0.123520   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.363096   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective       NaN   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.207995   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.008558   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.091189   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.110943   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.149617   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.124274   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       min   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.111909   \n",
       "                                     https://github.com/babel/babel               opencollective  0.605019   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.002065   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.151561   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.042793   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.003765   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.670457   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.037675   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.463576   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.017743   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       25%   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.205454   \n",
       "                                     https://github.com/babel/babel               opencollective  0.894352   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.081913   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.151561   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.072089   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.010082   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.827363   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.045550   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.524615   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.055223   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       50%   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.249761   \n",
       "                                     https://github.com/babel/babel               opencollective  0.946966   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.107774   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.151561   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.118610   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.013574   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.860607   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.055387   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.585653   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.092703   \n",
       "\n",
       "                                                                                                            \\\n",
       "                                                                                                       75%   \n",
       "project_a                            project_b                                    funder                     \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.309997   \n",
       "                                     https://github.com/babel/babel               opencollective  0.983944   \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.554058   \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.151561   \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.148518   \n",
       "...                                                                                                    ...   \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.017352   \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.908930   \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.231848   \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.673433   \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.176576   \n",
       "\n",
       "                                                                                                            \n",
       "                                                                                                       max  \n",
       "project_a                            project_b                                    funder                    \n",
       "https://github.com/ajv-validator/ajv https://github.com/axios/axios               opencollective  0.451327  \n",
       "                                     https://github.com/babel/babel               opencollective  0.998920  \n",
       "                                     https://github.com/boa-dev/boa               opencollective  0.833737  \n",
       "                                     https://github.com/brooooooklyn/snappy       opencollective  0.151561  \n",
       "                                     https://github.com/browserslist/browserslist opencollective  0.598039  \n",
       "...                                                                                                    ...  \n",
       "https://github.com/zloirock/core-js  https://github.com/webdriverio/webdriverio   opencollective  0.024527  \n",
       "                                     https://github.com/webpack/webpack           opencollective  0.966474  \n",
       "                                     https://github.com/webreflection/flatted     opencollective  0.263012  \n",
       "                                     https://github.com/wooorm/markdown-table     opencollective  0.761213  \n",
       "                                     https://github.com/yarnpkg/yarn              opencollective  0.260448  \n",
       "\n",
       "[5224 rows x 16 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# group by project_a and project_b and describe weight_a and weight_b\n",
    "\n",
    "df.groupby(['project_a', 'project_b', 'funder'])[['weight_a', 'weight_b']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate funding amount\n",
    "\n",
    "df['amount_a'] = df['weight_a'] * df['total_amount_usd']\n",
    "df['amount_b'] = df['weight_b'] * df['total_amount_usd']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>quarter</th>\n",
       "      <th>funder</th>\n",
       "      <th>project</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-04</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/chzyer/readline</td>\n",
       "      <td>103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-04</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/gulpjs/gulp</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-04</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/mochajs/mocha</td>\n",
       "      <td>2578.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-07</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/chzyer/readline</td>\n",
       "      <td>151.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-07</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/gulpjs/gulp</td>\n",
       "      <td>501.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149</th>\n",
       "      <td>2023-10</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/webdriverio/webdriverio</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1150</th>\n",
       "      <td>2023-10</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/webpack/webpack</td>\n",
       "      <td>25140.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1151</th>\n",
       "      <td>2023-10</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/webreflection/flatted</td>\n",
       "      <td>711.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1152</th>\n",
       "      <td>2023-10</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/yarnpkg/yarn</td>\n",
       "      <td>1047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1153</th>\n",
       "      <td>2023-10</td>\n",
       "      <td>opencollective</td>\n",
       "      <td>https://github.com/zloirock/core-js</td>\n",
       "      <td>12126.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1154 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      quarter          funder                                     project  \\\n",
       "0     2016-04  opencollective          https://github.com/chzyer/readline   \n",
       "1     2016-04  opencollective              https://github.com/gulpjs/gulp   \n",
       "2     2016-04  opencollective            https://github.com/mochajs/mocha   \n",
       "3     2016-07  opencollective          https://github.com/chzyer/readline   \n",
       "4     2016-07  opencollective              https://github.com/gulpjs/gulp   \n",
       "...       ...             ...                                         ...   \n",
       "1149  2023-10  opencollective  https://github.com/webdriverio/webdriverio   \n",
       "1150  2023-10  opencollective          https://github.com/webpack/webpack   \n",
       "1151  2023-10  opencollective    https://github.com/webreflection/flatted   \n",
       "1152  2023-10  opencollective             https://github.com/yarnpkg/yarn   \n",
       "1153  2023-10  opencollective         https://github.com/zloirock/core-js   \n",
       "\n",
       "       amount  \n",
       "0       103.0  \n",
       "1        20.0  \n",
       "2      2578.0  \n",
       "3       151.0  \n",
       "4       501.0  \n",
       "...       ...  \n",
       "1149    135.0  \n",
       "1150  25140.0  \n",
       "1151    711.0  \n",
       "1152   1047.0  \n",
       "1153  12126.0  \n",
       "\n",
       "[1154 rows x 4 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract unique project_a and amount_a, project_b and amount_b per quarter and funder, and then unfold\n",
    "\n",
    "df_a = df.groupby(['quarter', 'funder', 'project_a'])['amount_a'].mean().reset_index()\n",
    "df_b = df.groupby(['quarter', 'funder', 'project_b'])['amount_b'].mean().reset_index()\n",
    "\n",
    "# rename project_a and project_b to project\n",
    "df_a.rename(columns={'project_a': 'project'}, inplace=True)\n",
    "df_b.rename(columns={'project_b': 'project'}, inplace=True)\n",
    "\n",
    "# rename amount_a and amount_b to amount\n",
    "df_a.rename(columns={'amount_a': 'amount'}, inplace=True)\n",
    "df_b.rename(columns={'amount_b': 'amount'}, inplace=True)\n",
    "\n",
    "# concat df_a and df_b\n",
    "df_ab = pd.concat([df_a, df_b]).groupby(['quarter', 'funder', 'project']).mean().reset_index()\n",
    "\n",
    "df_ab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4261 entries, 0 to 4260\n",
      "Data columns (total 8 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   id                4261 non-null   int64  \n",
      " 1   project_a         4261 non-null   object \n",
      " 2   project_b         4261 non-null   object \n",
      " 3   total_amount_usd  4261 non-null   int64  \n",
      " 4   funder            4261 non-null   object \n",
      " 5   quarter           4261 non-null   object \n",
      " 6   amount_a          4011 non-null   float64\n",
      " 7   amount_b          4081 non-null   float64\n",
      "dtypes: float64(2), int64(2), object(4)\n",
      "memory usage: 266.4+ KB\n"
     ]
    }
   ],
   "source": [
    "# approach by doing one score per project\n",
    "fd = df_ab.groupby(['project']).amount.sum()\n",
    "\n",
    "# copy test_df to test_fd\n",
    "test_fd = test_df.copy()\n",
    "\n",
    "# fill amount into test_df by looking up project_a and project_b\n",
    "test_fd['amount_a'] = test_fd['project_a'].map(fd)\n",
    "test_fd['amount_b'] = test_fd['project_b'].map(fd)\n",
    "\n",
    "test_fd.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/gt/sg3v8rd13l52jx91mfbgzbfc0000gn/T/ipykernel_23487/3532855579.py:6: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  test_fd['pred'].fillna(0.5, inplace=True)\n"
     ]
    }
   ],
   "source": [
    "# calculate pred (weight_a by amount_a / (amount_a + amount_b))\n",
    "\n",
    "test_fd['pred'] = test_fd['amount_a'] / (test_fd['amount_a'] + test_fd['amount_b'])\n",
    "\n",
    "# fill in null values with 0.5\n",
    "test_fd['pred'].fillna(0.5, inplace=True)\n",
    "\n",
    "# save id, pred to csv\n",
    "test_fd[['id', 'pred']].to_csv('submissions/baseline_by_project.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.float64(0.0684150000570122)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check against train set\n",
    "\n",
    "df['pred_amount_a'] = df['project_a'].map(fd)\n",
    "df['pred_amount_b'] = df['project_b'].map(fd)\n",
    "\n",
    "df['pred'] = df['pred_amount_a'] / (df['pred_amount_a'] + df['pred_amount_b'])\n",
    "\n",
    "# calculate MSE between pred and weight_a\n",
    "\n",
    "df['mse'] = (df['pred'] - df['weight_a']) ** 2\n",
    "\n",
    "df['mse'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "xgboost",
   "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.13.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}