00-clean-data-Dec-2022.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# This file is used to analyze real experiment data collected in December of 2022\n",
    "\n",
    "\n",
    "The purpose of this file to produce data for the following python and Stata code\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXYAAAEJCAYAAACAKgxxAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAAAQUUlEQVR4nO3df4xlZX3H8fcH1h+4Q/21A4vYSUOoLlGXGkdZtdpCom2zMfxQNqhIsZa1prXVWCMQRLfYlBAtVknRJTV10UZX6GqT+mMrtJG0FRl0xaVaMBZQtO3usthtiBZ3v/3jnmGuw87cmdm9M8Oz71cyuc85z5lzv+fJzGdOnnvOmVQVkqR2HLXUBUiSDi+DXZIaY7BLUmMMdklqjMEuSY0x2CWpMQa79BiW5NeSnNW11yT5SJJbk6xL8pwkm5J8McnTlrhULSKDXfOS5BeSXD1t3cuS3JXk35K8N8knk1yXZKRvm6OTXJ5kW5Krk9ySpJK8rW+bE5P8xrR9vy/J7iTfTXJ63/p3JvlKkn9P8sLFOtbldLxJjgaeAvxKt9lIVf0e8IfAG4AHquo9wHZg1WEaFj0GGOyar9cAv5Nk5eSKqroF+Crwtap6b1W9HjgJuKLv+64D1gDnVNXbq+plwKbJziRrga3Aq/rW/SZwC/AM4NPA9d36FwHbq+rlwIeAS4dxoBzkWGH5HG9V7Qd+3FfXRNccAW6vqh8leTHwUuC/DnEs9BhisGu+VgG3A+dNW39g2vIEcDJAd4Z5AXBx/fytzlcA/wlQVXcA/zBtH3ur6ktV9X/AlcCJSZ4EfLOqvtlt8wDwH4d2SDOa6VhheR/vKcDHu/ZtwE3AuwZ8jxpisGvOkpwCfBP4KPC7s2z3BOBX6c446Z2V3lNV9/VvV1X7q+pTM+2nqm7tW3w88J2qeqiqftq9zyp6ofsnB6lhQzf18YJu+eokr07ytCRXJjkvyd8f6rEul+Ptq+Us4JPA45Okqn7W1eWzQ44gBrvm43R6Z5nbgJOSPHda/4uS/AW9aYr/Af61W38C3ZnqIXgV8KeTC0mOBX4fWMvU2ekjqmor8E/AL3arflhVNwJndP2fAt43y/sNOlZY4uNNEuAFwC8nWZnk9cDbgWvpTdlcmORNwMuADxxiPXoMWbHUBeixIckKemE31q26j96Z7Nv6NvtaVf1RkqOAv6b3od0p9KYPnn4I7/0U4FlV9chcelXtAzYl+TDwvSRPrqofT/vW64ALknyJXvACfAW4OsmTgYtneL+5HCssj+PtD+xPdl86wnnGrrn6LeDdVXVxVV1M7+zxDd00xM+pqgP0Pvxb000f3AQ8K8kJ833TLjTfygzTD1X1APBtYP9Buv8WeAm9+e6/69btBl4EjAK3J3niQb5vzsfa1bBcjlcCDHbN3a9X1XcmF6rqa8BD9K4cATh62vbPAX4I7Kmq7cAXgb9McszkBkmenWT9gPf9A+CjVfWTbrrhFUmemuQXun2MAHdX1f9O/8aq+gnwGeAVVfWjbvU5wI+r6jXAvfSuXJnvsS7L45UmORWjgZKcB5yT5GNVdWe37hR6Z42XJ/kBvTPjxyXZBDyZ3lzwmX1Xhbya3lUhX07ybeC/gXvoTZeQ5Fn0pj9WJnlOVd3ZzQ9/EPjz3nQyR9G7dO+c7n0/BOzj0VMk/T5Gd7VKZwT4qyRb6V3Jcsc8j3UHvWmW5Xq8EvEfbUhSW5yKkaTGGOyS1Jg5BXuS53XPpZAkLXMDPzxNchpwM70PjPb3rT8DeC4Q4KvT7pqTJC2ROX14muQeYE13+djkU+VuBSafqndTVZ0xrCIlSXO30Dn2MWB3dYCHk5x0GOuSJC3QQq9jX03vetpJ+4Djge9N3zDJRmAjwMqVK1+wZs3B7geRJM3k9ttv311Vo3PdfqHBvofejR6TRujdqv0oVbUZ2AwwPj5eExMTB9tMkjSDJPfOZ/t5BXv3HItVVXVXkmO7p8tB7z+33D2ffUmShmMuV8WM03tg0ivpPeXuUmADcAnwjm6zS4ZVoCRpfgYGe/fvtvr/NdiGbv0t9P6NlyRpGfHOU0lqjMEuSY0x2CWpMQa7JDXGYJekxhjsktQYg12SGmOwS1JjDHZJaozBLkmNMdglqTEGuyQ1xmCXpMYY7JLUGINdkhpjsEtSYwx2SWqMwS5JjTHYJakxBrskNcZgl6TGGOyS1BiDXZIaY7BLUmMMdklqjMEuSY0x2CWpMQa7JDXGYJekxhjsktQYg12SGmOwS1JjDHZJaozBLkmNMdglqTEGuyQ1ZsVsnUlWAO8Bvg6cAlxZVQe6vjcBDwInA9+qqs8Pt1RJ0lwMOmO/CLi/qrYBe4Fz+/rOr6obgWuBtwypPknSPA0K9nXAjq69A1jf17cryTuB1wIfnGkHSTYmmUgysWvXroVXKkmak0HBvhrY17X3Acf39b0VuAD4beCOmXZQVZuraryqxkdHRw+lVknSHAwK9j3ASNceAXb39V0FnAZcD3zk8JcmSVqIQcG+HTi1a68Ftic5rlt+ZlU9VFXXAquGVaAkaX4GBfsWYCzJBmAM2Alc0/XdkOTNSS4Erh5eiZKk+Zj1csfu0sbLusWt3euGru/aIdYlSVogb1CSpMYY7JLUGINdkhpjsEtSYwx2SWqMwS5JjTHYJakxBrskNcZgl6TGGOyS1BiDXZIaY7BLUmMMdklqjMEuSY0x2CWpMQa7JDXGYJekxhjsktQYg12SGmOwS1JjDHZJaozBLkmNMdglqTEGuyQ1xmCXpMYY7JLUGINdkhpjsEtSYwx2SWqMwS5JjTHYJakxBrskNcZgl6TGGOyS1JhDCvYkK5O8Mcnph6sgSdKhmTXYk6xIckWSs5NcmuSovr5VwDbg5qr6x2EXKkmam0Fn7BcB91fVNmAvcG5f3weAj1fVvcMqTpI0f4OCfR2wo2vvANYDJHkcvZA/IcmWJJuGVaAkaX5WDOhfDezr2vuA47v2KHBPVb0fIMmdSa6rqh9M30GSjcBGgLGxscNStCRpZoPO2PcAI117BNjdtR8E9vdtdxfwjIPtoKo2V9V4VY2Pjo4eQqmSpLkYFOzbgVO79lpge5LjquohYFeSY7u+Y4C7h1SjJGkeBgX7FmAsyQZgDNgJXNP1vQvYlOR1wPVVtXd4ZUqS5mrWOfaqOgBc1i1u7V43dH23AbcNrzRJ0kJ456kkNcZgl6TGGOyS1BiDXZIaY7BLUmMMdklqjMEuSY0x2CWpMQa7JDXGYJekxhjsktQYg12SGmOwS1JjDHZJaozBLkmNMdglqTEGuyQ1xmCXpMYY7JLUGINdkhpjsEtSYwx2SWqMwS5JjTHYJakxBrskNcZgl6TGGOyS1BiDXZIaY7BLUmMMdklqjMEuSY0x2CWpMQa7JDXGYJekxhjsktQYg12SGjNrsCdZkeSKJGcnuTTJo7ZPckOSXxpahZKkeRl0xn4RcH9VbQP2Auf2dyY5G3jCkGqTJC3AoGBfB+zo2juA9ZMdSZ4PfB/YM9sOkmxMMpFkYteuXQuvVJI0J4OCfTWwr2vvA44HSPJU4OSqmhj0BlW1uarGq2p8dHT0kIqVJA02KNj3ACNdewTY3bXXA+cn+SxwBrA5yYlDqVCSNC+Dgn07cGrXXgtsT3JcVX2iqs6sqrOAm4GNVXX/EOuUJM3RoGDfAowl2QCMATuBa4ZelSRpwVbM1llVB4DLusWt3euGadtcePjLkiQtlDcoSVJjDHZJaozBLkmNMdglqTEGuyQ1xmCXpMYY7JLUGINdkhpjsEtSYwx2SWqMwS5JjTHYJakxBrskNcZgl6TGGOyS1BiDXZIaY7BLUmMMdklqjMEuSY0x2CWpMQa7JDXGYJekxhjsktQYg12SGmOwS1JjDHZJaozBLkmNMdglqTEGuyQ1xmCXpMYY7JLUGINdkhpjsEtSYwx2SWqMwS5JjZk12JOsSHJFkrOTXJrkqL6+85L8c5LvJnnJ8EuVJM3FoDP2i4D7q2obsBc4FyDJMcD+qnopcDnw7qFWKUmas0HBvg7Y0bV3AOu79sPAjV37G8Cew12YJGlhBgX7amBf194HHA9QVT+rqgPd+pcDV820gyQbk0wkmdi1a9eh1itJGmBQsO8BRrr2CLC7vzPJScB9VXXHTDuoqs1VNV5V46Ojo4dUrCRpsEHBvh04tWuvBbYnOQ6ge11TVV9I8sTJ9ZKkpTUo2LcAY0k2AGPATuCaJE8CPgdclWQncBvwwFArlSTNyYrZOrt59Mu6xa3d64bu9cXDKkqStHDeoCRJjTHYJakxBrskNcZgl6TGGOyS1BiDXZIaY7BLUmMMdklqjMEuSY0x2CWpMQa7JDXGYJekxhjsktQYg12SGmOwS1JjDHZJaozBLkmNMdglqTEGuyQ1xmCXpMYY7JLUGINdkhpjsEtSYwx2SWqMwS5JjTHYJakxBrskNcZgl6TGGOyS1BiDXZIaY7BLUmMMdklqjMEuSY0x2CWpMQa7JDXGYJekxqyYrTPJCuA9wNeBU4Arq+pA13cG8FwgwFer6tYh1ypJmoNZgx24CLi/qrYlWQ2cC3w6ydHAVcALu+1uAs4YXpmSpLkaFOzrgGu79g7gLcCngTFgd1UVQJKHk5xUVd+bvoMkG4GN3eJPk+w8HIU3YBWwe6mLWCYciymOxRTHYsqz57PxoGBfDezr2vuA4w+yvr/vUcFeVZuBzQBJJqpqfD4FtsqxmOJYTHEspjgWU5JMzGf7QR+e7gFGuvYIU389+9dP75MkLaFBwb4dOLVrrwW2Jzmuqu4Cjk0HGKmqu4dZqCRpbgYF+xZgLMkGevPqO4Frur5LgHd0X5fM8f02L6TIRjkWUxyLKY7FFMdiyrzGIt3nn5KkRniDkiQ1xmCXpMYY7NIylGRlkjcmOX2pa9HiSvK87ibQBRt0HfuC+CiCKQPG4jzgrfTuAbigqv5lyQpdBLONRd82NwB/XFX3LH6Fi2fAz8Uq4G+Ai6rq3qWrcnEMGIs3AQ8CJwPfqqrPL1WdiyHJacDNwNOB/X3r55Wbwzpjf+RRBMBeeo8ioO9RBB8GPgT82ZDefzmZaSyOAfZX1UuBy4F3L12Ji+agYzEpydnAE5aisCUw21h8APj4kRDqndnG4vyqupHeHfBvWYriFlMX2Lv61y0kN4cV7OvoPYKA7nV9137kUQTd4wgeTnLSkGpYLmYai4eBG7v2N+jd9NW6mcaCJM8Hvs+RMQ4ww1gkeRy9YDshyZYkm5akusU1488FsCvJO4HXAh9c1KqWj3nn5lCmYjgMjyJoyEHHoqp+1rfNy+n9RW7dQcciyVOBk6vqM7373Y4IM/2OjAL3VNX7AZLcmeS6qvrBEtS4WGYaC+hNVX65W3/mIte1XMw7N4d1xu6jCKbMNBYAdH9576uqOxa7sCUw01isB85P8ll6TwndnOTExS9vUc00Fg/SN7cK3AU8Y/HKWhKz/Y5cBZwGXA98ZJHrWi7mnZvDCnYfRTDloGMB0L2uqaovJHni5PqGzfRz8YmqOrOqzqL3wdHGqrp/qYpcJDONxUP0ph+O7fqOAY7Y3xHgmVX1UFVdS+9pj0eMJEctNDeHFeyH+1EEj2UHHYskTwI+B1zVPcr4NuCBpStzUcz2c3GkmW0s3gVsSvI64Pqq2rtENS6W2cbihiRvTnIhcPUS1bdokozTm457Jb0/cgvKTR8pIEmN8QYlSWqMwS5JjTHYJakxBrskNcZgl6TGGOyS1BiDXZIa8/8nSgM5Wz7wCAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sys\n",
    "import copy\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "# To change the default font like latex font\n",
    "import matplotlib\n",
    "matplotlib.rcParams['mathtext.fontset'] = 'stix'\n",
    "matplotlib.rcParams['font.family'] = 'STIXGeneral'\n",
    "matplotlib.pyplot.title(r'ABC123 vs $\\mathrm{ABC123}^{123}$')\n",
    "\n",
    "import itertools\n",
    "import scipy.stats as stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def countCommas(s):\n",
    "    try:\n",
    "        return s.count(',')\n",
    "    except:\n",
    "        return np.nan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Content\n",
    "\n",
    "- [Data Import](#Data-Import)\n",
    "- [Define functions and important variables](#Define-functions-and-important-variables)\n",
    "- [Start Data Analysis](#Start-Data-Analysis)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Import\n",
    "\n",
    "Raw Data \n",
    "\n",
    "-[Return](#Content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Pilot data\n",
    "\n",
    "# data = pd.DataFrame()\n",
    "# s = { \n",
    "# #     'all_apps_wide_2022-11-04.csv': 'HETERO',\n",
    "#         'all_apps_wide_2022-11-07.csv': 'HETERO' ,\n",
    "#       'all_apps_wide_2022-11-07b.csv': 'HOMO' }\n",
    "# for ss in s.keys() :\n",
    "    \n",
    "#     temp = pd.read_csv('..\\\\RawData\\\\'+ss)\n",
    "#     temp['Treatment'] = s[ss]\n",
    "#     data = data.append(temp,  ignore_index = True)\n",
    "\n",
    "# data_pilot = data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.DataFrame()\n",
    "s = { \n",
    "        '2022_12_01_reals01.csv': ['HETERO', 20], \n",
    "    '2022_12_01_reals02.csv': ['HETERO', 80], \n",
    "    '2022_12_01_reals03.csv': ['HOMO', 20], \n",
    "    '2022_12_02_reals04.csv': ['HOMO', 80], \n",
    "}\n",
    "    \n",
    "for ss in s.keys() :\n",
    "    \n",
    "    temp = pd.read_csv('..\\\\RawData\\\\'+ss)\n",
    "    temp['Treatment'] = s[ss][0]\n",
    "    temp['FC'] = s[ss][1]\n",
    "    data = data.append(temp,  ignore_index = True)\n",
    "\n",
    "data_ABA = data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.DataFrame()\n",
    "s = { \n",
    "        '2022_12_06_reals05.csv': ['HETERO', 80], \n",
    "    '2022_12_06_reals06.csv': ['HETERO', 20],\n",
    "    '2022_12_06_reals07.csv': ['HOMO', 80],\n",
    "    '2022_12_06_reals08.csv': ['HOMO', 20],\n",
    "}\n",
    "    \n",
    "for ss in s.keys() :\n",
    "    \n",
    "    temp = pd.read_csv('..\\\\RawData\\\\'+ss)\n",
    "    temp['Treatment'] = s[ss][0]\n",
    "    temp['FC'] = s[ss][1]\n",
    "    data = data.append(temp,  ignore_index = True)\n",
    "\n",
    "data_BAB = data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_ABA.to_csv('..\\\\ProcessedData\\\\data_ABA.csv',index=False)\n",
    "data_BAB.to_csv('..\\\\ProcessedData\\\\data_BAB.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Define functions and important variables \n",
    "\n",
    "-[Return](#Content)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['participant.id_in_session', 'participant.code', 'participant.label',\n",
       "       'participant._is_bot', 'participant._index_in_pages',\n",
       "       'participant._max_page_index', 'participant._current_app_name',\n",
       "       'participant._current_page_name', 'participant.time_started_utc',\n",
       "       'participant.visited',\n",
       "       ...\n",
       "       'PGSO_12_FeedbackQuestions.1.player.strategy5Text',\n",
       "       'PGSO_12_FeedbackQuestions.1.group.id_in_subsession',\n",
       "       'PGSO_12_FeedbackQuestions.1.subsession.round_number',\n",
       "       'PGSO_13_Feedback.1.player.id_in_group',\n",
       "       'PGSO_13_Feedback.1.player.role', 'PGSO_13_Feedback.1.player.payoff',\n",
       "       'PGSO_13_Feedback.1.group.id_in_subsession',\n",
       "       'PGSO_13_Feedback.1.subsession.round_number', 'Treatment', 'FC'],\n",
       "      dtype='object', length=3174)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_BAB.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "variables = [ 'subsession.round_number','subsession.period', 'subsession.sg', \n",
    "#              'session.code','participant.code',\n",
    "             'player.id_in_group','player.endowment', 'player.join_club', \n",
    "             'player.local_community', 'player.id_in_local', \n",
    "             'player.contribution_local', 'player.contribution_global',\n",
    "             'player.total_contribution_local', 'player.individual_share_local', 'player.payoff',\n",
    "           'group.id_in_subsession', 'group.FC', 'group.total_contribution_global','group.individual_share_global',\n",
    "            ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cut out data, by AppName and Length\n",
    "\n",
    "def DataCutOutLong(data, AppName, endRound):\n",
    "    col_noOpp = AppName\n",
    "    col_name_a =   ['session.code', 'Treatment', 'FC','participant.code']\n",
    "    data_noOppor = pd.DataFrame()\n",
    "    for i in range(1,endRound+1):\n",
    "        col_names = [col_noOpp+'.'+str(i)+'.'+ x for x in variables]\n",
    "        col_rename = {}\n",
    "        for x in variables:\n",
    "            col_rename[col_noOpp+'.'+str(i)+'.'+ x ] =  x\n",
    "        temp = data[col_names + col_name_a]\n",
    "        temp = temp.rename(columns=col_rename) \n",
    "        data_noOppor = data_noOppor.append(temp, ignore_index=True)\n",
    "    data_noOppor['endowment'] = data_noOppor['player.endowment']/10\n",
    "    data_noOppor['endowment_j'] = data_noOppor['player.endowment'] * data_noOppor['player.join_club']\n",
    "    data_noOppor['endowment_nj'] = data_noOppor['player.endowment'] * (1-data_noOppor['player.join_club'])\n",
    "    data_noOppor['tot_contr'] = data_noOppor['player.contribution_local']+data_noOppor['player.contribution_global']\n",
    "    data_noOppor['privateAcc'] = data_noOppor['player.endowment']/10 - data_noOppor['player.contribution_local']-data_noOppor['player.contribution_global'] # note: endowment has counted subjects' joining behavior \n",
    "    data_noOppor['player.payoff'] = data_noOppor['player.payoff']/10\n",
    "    \n",
    "   \n",
    "    # Efficiency has to be measured at the larger group scale\n",
    "    if 'PurePG'  in AppName and 'PGSO_09BAB_PurePG_Part4B' not in AppName:\n",
    "        globalRealizedPay =data_noOppor.groupby(['session.code','subsession.round_number','group.id_in_subsession'], as_index=False)['player.payoff', 'player.endowment'].sum()\n",
    "        globalRealizedPay['Efficiency'] = globalRealizedPay['player.payoff'] / (80  * 0.6 * 8)\n",
    "        data_noOppor =pd.merge(data_noOppor, globalRealizedPay, on=['session.code','subsession.round_number','group.id_in_subsession'], how='inner',suffixes=('','_globalagg'))\n",
    "\n",
    "#         localMax = data_noOppor.groupby(['session.code','subsession.round_number','player.local_community'], as_index=False)['player.endowment'].sum()\n",
    "#         localMax['LocalBenefitMax'] = localMax['player.endowment'] * 4 * 0.6\n",
    "        \n",
    "#         localEarnedTotal = data_noOppor.groupby(['session.code','subsession.round_number','player.local_community'], as_index=False)['ActualTokenEarned'].sum()\n",
    "#         data_noOppor = pd.merge(data_noOppor, localEarnedTotal, on=['session.code','subsession.round_number','player.local_community'], how='inner',suffixes=('','_localEarnedTotal'))\n",
    "\n",
    "#         data_noOppor = pd.merge(data_noOppor, localMax, on=['session.code','subsession.round_number','player.local_community'], how='inner',suffixes=('','_localMax'))\n",
    "        \n",
    "#         data_noOppor['Efficiency'] = data_noOppor['player.payoff']/80/0.6\n",
    "#         data_noOppor['Efficiency2'] = (data_noOppor['ActualTokenEarned_localEarnedTotal'] - data_noOppor[\"player.endowment_localMax\"])/(data_noOppor[\"LocalBenefitMax\"] - data_noOppor[\"player.endowment_localMax\"])\n",
    "\n",
    "#         data_noOppor['Efficiency2'] = \n",
    "    \n",
    "    if 'ClubOpp' in AppName or 'PGSO_09BAB_PurePG_Part4B' in AppName:\n",
    "        globalRealizedPay =data_noOppor.groupby(['session.code','FC', \n",
    "                                                 'subsession.round_number','group.id_in_subsession'], as_index=False)['player.payoff', 'player.endowment'].sum()\n",
    "#         globalRealizedPay['Efficiency'] = globalRealizedPay['player.payoff'] / (globalRealizedPay['player.endowment'] * 8 * 0.6)*10\n",
    "        globalRealizedPay['Efficiency'] = globalRealizedPay['player.payoff'] / (  (160 - globalRealizedPay['FC']/10*8 )* 0.6 * 8)\n",
    "        data_noOppor =pd.merge(data_noOppor, globalRealizedPay, on=['session.code','subsession.round_number','group.id_in_subsession'], how='inner',suffixes=('','_globalagg'))\n",
    "        \n",
    "        globalMax =data_noOppor.groupby(['session.code',\n",
    "                                 'subsession.round_number',\n",
    "                                 'group.id_in_subsession'], as_index=False)['player.payoff','endowment_j',\n",
    "                                                                            'endowment_nj','player.join_club'].sum()\n",
    "        globalMax = globalMax.rename(columns={\"endowment_j\": \"ClubTotal\", \"endowment_nj\": \"LocalTotal\", 'player.join_club':\"ClubSize\"})\n",
    "        globalMax['SocialBenefitMax'] = globalMax['ClubTotal'] * globalMax['ClubSize'] * 0.6 + globalMax['LocalTotal'] * 4 * 0.6 \n",
    "        globalMax.loc[globalMax['ClubSize']<4,'SocialBenefitMax'] = globalMax['ClubTotal'] * 4 * 0.6 +  globalMax['LocalTotal'] * 4 * 0.6 \n",
    "        globalMax['Efficiency2'] = globalMax['player.payoff'] / globalMax['SocialBenefitMax'] *10\n",
    "        \n",
    "        data_noOppor =pd.merge(data_noOppor, globalMax, on=['session.code','subsession.round_number','group.id_in_subsession'], how='inner',suffixes=('','_globalMax'))\n",
    "        \n",
    "        \n",
    "#         localMax = data_noOppor.groupby(['session.code','subsession.round_number','player.local_community'], as_index=False)['player.endowment'].sum()\n",
    "#         localMax['LocalBenefitMax'] = localMax['player.endowment'] * 4 * 0.6\n",
    "\n",
    "#         data_noOppor = pd.merge(data_noOppor, localMax, on=['session.code','subsession.round_number','player.local_community'], how='inner',suffixes=('','_localMax'))\n",
    "\n",
    "\n",
    "#         globalMax =data_noOppor.groupby(['session.code','subsession.round_number','group.id_in_subsession'], as_index=False)['endowment_j','player.join_club'].sum()\n",
    "#         globalMax['GlobalBenefitMax'] = globalMax['endowment_j'] * globalMax['player.join_club'] * 0.6\n",
    "\n",
    "#         data_noOppor =pd.merge(data_noOppor, globalMax, on=['session.code','subsession.round_number','group.id_in_subsession'], how='inner',suffixes=('','_globalMax'))\n",
    "\n",
    "#         data_noOppor['ActualTokenEarned'] =data_noOppor['player.payoff'] * 10\n",
    "#         # data_noOppor['privateAcc'] * 10  + 0.6* data_noOppor['group.total_contribution_global'] + 0.6* data_noOppor['player.total_contribution_local'] Wrong, not considered the join behavior\n",
    "#         localEarnedTotal = data_noOppor.groupby(['session.code','subsession.round_number','player.local_community'], as_index=False)['ActualTokenEarned'].sum()\n",
    "#         data_noOppor = pd.merge(data_noOppor, localEarnedTotal, on=['session.code','subsession.round_number','player.local_community'], how='inner',suffixes=('','_localEarnedTotal'))\n",
    "\n",
    "#         data_noOppor[\"MaxBenefit\"] = data_noOppor[[\"LocalBenefitMax\", \"GlobalBenefitMax\"]].max(axis=1)\n",
    "\n",
    "#         data_noOppor['Efficiency'] = data_noOppor['player.payoff']/data_noOppor[\"MaxBenefit\"]\n",
    "\n",
    "#         data_noOppor['Efficiency2'] = (data_noOppor['ActualTokenEarned_localEarnedTotal'] - data_noOppor[\"player.endowment_localMax\"])/(data_noOppor[\"MaxBenefit\"] - data_noOppor[\"player.endowment_localMax\"])\n",
    "    \n",
    "    \n",
    "    return data_noOppor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [],
   "source": [
    "AppName = 'PGSO_05BAB_ClubOpp_Par2BBB'\n",
    "endRound = 10\n",
    "\n",
    "\n",
    "col_noOpp = AppName\n",
    "col_name_a =   ['session.code', 'Treatment', 'FC','participant.code']\n",
    "data_noOppor = pd.DataFrame()\n",
    "for i in range(1,endRound+1):\n",
    "    col_names = [col_noOpp+'.'+str(i)+'.'+ x for x in variables]\n",
    "    col_rename = {}\n",
    "    for x in variables:\n",
    "        col_rename[col_noOpp+'.'+str(i)+'.'+ x ] =  x\n",
    "    temp = data[col_names + col_name_a]\n",
    "    temp = temp.rename(columns=col_rename) \n",
    "    data_noOppor = data_noOppor.append(temp, ignore_index=True)\n",
    "data_noOppor['endowment'] = data_noOppor['player.endowment']/10\n",
    "data_noOppor['endowment_j'] = data_noOppor['player.endowment'] * data_noOppor['player.join_club']\n",
    "data_noOppor['endowment_nj'] = data_noOppor['player.endowment'] * (1-data_noOppor['player.join_club'])\n",
    "data_noOppor['tot_contr'] = data_noOppor['player.contribution_local']+data_noOppor['player.contribution_global']\n",
    "data_noOppor['privateAcc'] = data_noOppor['player.endowment']/10 - data_noOppor['player.contribution_local']-data_noOppor['player.contribution_global'] # note: endowment has counted subjects' joining behavior \n",
    "data_noOppor['player.payoff'] = data_noOppor['player.payoff']/10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\xinxi\\AppData\\Local\\Temp/ipykernel_15292/2498677884.py:1: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  globalMax =data_noOppor.groupby(['session.code',\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count    80.000000\n",
       "mean      0.606464\n",
       "std       0.097942\n",
       "min       0.382056\n",
       "25%       0.553183\n",
       "50%       0.591399\n",
       "75%       0.645880\n",
       "max       0.874074\n",
       "Name: Efficiency2, dtype: float64"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "globalMax =data_noOppor.groupby(['session.code',\n",
    "                                 'subsession.round_number',\n",
    "                                 'group.id_in_subsession'], as_index=False)['player.payoff', 'endowment_j','endowment_nj','player.join_club'].sum()\n",
    "globalMax = globalMax.rename(columns={\"endowment_j\": \"ClubTotal\", \"endowment_nj\": \"LocalTotal\", 'player.join_club':\"ClubSize\"})\n",
    "globalMax['SocialBenefitMax'] = globalMax['ClubTotal'] * globalMax['ClubSize'] * 0.6 +  globalMax['LocalTotal'] * 4 * 0.6 \n",
    "globalMax.loc[globalMax['ClubSize']<4,'SocialBenefitMax'] = globalMax['ClubTotal'] * 4 * 0.6 +  globalMax['LocalTotal'] * 4 * 0.6 \n",
    "globalMax['Efficiency2'] = globalMax['player.payoff'] / globalMax['SocialBenefitMax'] *10\n",
    "\n",
    "# data_noOppor =pd.merge(data_noOppor, globalMax, on=['session.code','subsession.round_number','group.id_in_subsession'], how='inner',suffixes=('','_globalMax'))\n",
    "\n",
    "globalMax['Efficiency2'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\xinxi\\AppData\\Local\\Temp/ipykernel_15292/971385742.py:7: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  globalMax =data_noOppor.groupby(['session.code','subsession.round_number','group.id_in_subsession'], as_index=False)['endowment_j','player.join_club'].sum()\n"
     ]
    }
   ],
   "source": [
    "localMax = data_noOppor.groupby(['session.code','subsession.round_number','player.local_community'], as_index=False)['player.endowment'].sum()\n",
    "localMax['LocalBenefitMax'] = localMax['player.endowment'] * 4 * 0.6\n",
    "\n",
    "data_noOppor = pd.merge(data_noOppor, localMax, on=['session.code','subsession.round_number','player.local_community'], how='inner',suffixes=('','_localMax'))\n",
    "\n",
    "\n",
    "globalMax =data_noOppor.groupby(['session.code','subsession.round_number','group.id_in_subsession'], as_index=False)['endowment_j','player.join_club'].sum()\n",
    "globalMax['GlobalBenefitMax'] = globalMax['endowment_j'] * globalMax['player.join_club'] * 0.6\n",
    "\n",
    "data_noOppor =pd.merge(data_noOppor, globalMax, on=['session.code','subsession.round_number','group.id_in_subsession'], how='inner',suffixes=('','_globalMax'))\n",
    "\n",
    "data_noOppor['ActualTokenEarned'] =data_noOppor['player.payoff'] * 10\n",
    "# data_noOppor['privateAcc'] * 10  + 0.6* data_noOppor['group.total_contribution_global'] + 0.6* data_noOppor['player.total_contribution_local'] Wrong, not considered the join behavior\n",
    "localEarnedTotal = data_noOppor.groupby(['session.code','subsession.round_number','player.local_community'], as_index=False)['ActualTokenEarned'].sum()\n",
    "data_noOppor = pd.merge(data_noOppor, localEarnedTotal, on=['session.code','subsession.round_number','player.local_community'], how='inner',suffixes=('','_localEarnedTotal'))\n",
    "\n",
    "data_noOppor[\"MaxBenefit\"] = data_noOppor[[\"LocalBenefitMax\", \"GlobalBenefitMax\"]].max(axis=1)\n",
    "\n",
    "data_noOppor['Efficiency'] = data_noOppor['player.payoff']/data_noOppor[\"MaxBenefit\"]\n",
    "\n",
    "data_noOppor['Efficiency2'] = (data_noOppor['ActualTokenEarned_localEarnedTotal'] - data_noOppor[\"player.endowment_localMax\"])/(data_noOppor[\"MaxBenefit\"] - data_noOppor[\"player.endowment_localMax\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    640.000000\n",
       "mean       0.274540\n",
       "std        0.177666\n",
       "min        0.025000\n",
       "25%        0.169112\n",
       "50%        0.217700\n",
       "75%        0.324140\n",
       "max        0.895833\n",
       "Name: Efficiency2, dtype: float64"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "data_noOppor['Efficiency2'].describe()\n",
    "# data_noOppor[data_noOppor['Efficiency2']<0]\n",
    "# [['player.payoff', 'ActualTokenEarned']]\n",
    "# data_noOppor[data_noOppor['player.payoff']*10!= data_noOppor['ActualTokenEarned']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['subsession.round_number', 'subsession.period', 'subsession.sg',\n",
       "       'player.id_in_group', 'player.endowment', 'player.join_club',\n",
       "       'player.local_community', 'player.id_in_local',\n",
       "       'player.contribution_local', 'player.contribution_global',\n",
       "       'player.total_contribution_local', 'player.individual_share_local',\n",
       "       'player.payoff', 'group.id_in_subsession', 'group.FC',\n",
       "       'group.total_contribution_global', 'group.individual_share_global',\n",
       "       'session.code', 'Treatment', 'FC', 'participant.code', 'endowment',\n",
       "       'endowment_j', 'tot_contr', 'privateAcc', 'player.endowment_localMax',\n",
       "       'LocalBenefitMax', 'endowment_j_globalMax',\n",
       "       'player.join_club_globalMax', 'GlobalBenefitMax', 'ActualTokenEarned',\n",
       "       'ActualTokenEarned_localEarnedTotal', 'MaxBenefit', 'Efficiency',\n",
       "       'Efficiency2'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_noOppor.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\xinxi\\AppData\\Local\\Temp/ipykernel_15292/2463659246.py:25: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  globalRealizedPay =data_noOppor.groupby(['session.code','FC', 'subsession.round_number','group.id_in_subsession'], as_index=False)['player.payoff', 'player.endowment'].sum()\n",
      "C:\\Users\\xinxi\\AppData\\Local\\Temp/ipykernel_15292/2463659246.py:30: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  globalMax =data_noOppor.groupby(['session.code',\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count    3200.000000\n",
       "mean        0.560127\n",
       "std         0.130322\n",
       "min         0.313929\n",
       "25%         0.462095\n",
       "50%         0.544271\n",
       "75%         0.646354\n",
       "max         0.895544\n",
       "Name: Efficiency2, dtype: float64"
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "B1 = DataCutOutLong(data_ABA, 'PGSO_08ABA_ClubOpp_Par3BBB',50)\n",
    "B1['Efficiency2'].describe()\n",
    "# B1[B1['Efficiency2']<0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\xinxi\\AppData\\Local\\Temp/ipykernel_15292/2463659246.py:64: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  globalRealizedPay =data_noOppor.groupby(['session.code','subsession.round_number','group.id_in_subsession'], as_index=False)['player.payoff', 'player.endowment'].sum()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count    640.000000\n",
       "mean       0.621426\n",
       "std        0.072345\n",
       "min        0.504167\n",
       "25%        0.558854\n",
       "50%        0.624479\n",
       "75%        0.666406\n",
       "max        0.835938\n",
       "Name: Efficiency, dtype: float64"
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A1 = DataCutOutLong(data_ABA, 'PGSO_05ABA_PurePG_Part2A',10)\n",
    "A1['Efficiency'].describe()\n",
    "# x = A1[A1['Efficiency']>1]\n",
    "# x[['subsession.round_number', 'player.endowment','player.payoff','tot_contr','player.total_contribution_local', 'Efficiency']]\n",
    "# A1[A1['Efficiency']>.4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Local Only vs With Opportunity\n",
    "\n",
    "\n",
    "Sequence : [7, 6, 18, 17, 13, 8, 12, 7]\n",
    "\n",
    "A1 = [7] (10 rounds)\n",
    "\n",
    "B1 = [6,18,17]\n",
    "\n",
    "A2 = [13]\n",
    "\n",
    "B2 = [8]\n",
    "\n",
    "-[Return](#Content)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "subsession.round_number\n",
      "subsession.period\n",
      "subsession.sg\n",
      "player.id_in_group\n",
      "player.endowment\n",
      "player.join_club\n",
      "player.local_community\n",
      "player.id_in_local\n",
      "player.contribution_local\n",
      "player.contribution_global\n",
      "player.total_contribution_local\n",
      "player.individual_share_local\n",
      "player.payoff\n",
      "group.id_in_subsession\n",
      "group.FC\n",
      "group.total_contribution_global\n",
      "group.individual_share_global\n",
      "session.code\n",
      "Treatment\n",
      "FC\n",
      "participant.code\n",
      "endowment\n",
      "endowment_j\n",
      "endowment_nj\n",
      "tot_contr\n",
      "privateAcc\n",
      "player.payoff_globalagg\n",
      "player.endowment_globalagg\n",
      "Efficiency\n",
      "Sequence\n"
     ]
    }
   ],
   "source": [
    "for x in data_noOppor_A2.columns:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\xinxi\\AppData\\Local\\Temp/ipykernel_15292/860489259.py:25: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  globalRealizedPay =data_noOppor.groupby(['session.code','subsession.round_number','group.id_in_subsession'], as_index=False)['player.payoff', 'player.endowment'].sum()\n",
      "C:\\Users\\xinxi\\AppData\\Local\\Temp/ipykernel_15292/860489259.py:43: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  globalRealizedPay =data_noOppor.groupby(['session.code','FC', 'subsession.round_number','group.id_in_subsession'], as_index=False)['player.payoff', 'player.endowment'].sum()\n",
      "C:\\Users\\xinxi\\AppData\\Local\\Temp/ipykernel_15292/860489259.py:48: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  globalMax =data_noOppor.groupby(['session.code',\n"
     ]
    }
   ],
   "source": [
    "A1 = DataCutOutLong(data_ABA, 'PGSO_05ABA_PurePG_Part2A',10)\n",
    "A1['Sequence'] = 'A1'\n",
    "A2 = DataCutOutLong(data_ABA, 'PGSO_09ABA_PurePG_Part4A',20)\n",
    "A2['Sequence'] = 'A2'\n",
    "B1 = DataCutOutLong(data_ABA, 'PGSO_08ABA_ClubOpp_Par3BBB',50)\n",
    "B1['Sequence'] = 'B1'\n",
    "\n",
    "B1_bab = DataCutOutLong(data_BAB, 'PGSO_05BAB_ClubOpp_Par2BBB',50)\n",
    "B1_bab['Sequence'] = 'B1_bab'\n",
    "A2_bab = DataCutOutLong(data_BAB, 'PGSO_08BAB_PurePG_Part3A',20)\n",
    "A2_bab['Sequence'] = 'A2_bab'\n",
    "B2_bab = DataCutOutLong(data_BAB, 'PGSO_09BAB_PurePG_Part4B',10)\n",
    "B2_bab['Sequence'] = 'B2_bab'\n",
    "\n",
    "\n",
    "# Data with opportunity \n",
    "data_Oppor_B1 = pd.concat([B1, B1_bab], ignore_index=True)\n",
    "data_Oppor_allB = pd.concat([B1, B1_bab,B2_bab], ignore_index=True)\n",
    "\n",
    "\n",
    "\n",
    "data_Oppor_B1.to_csv('..\\\\ProcessedData\\\\data_Oppor_B1.csv',index=False)\n",
    "data_Oppor_allB.to_csv('..\\\\ProcessedData\\\\data_Oppor_allB.csv',index=False)\n",
    "\n",
    "# Data local only \n",
    "data_noOppor_AllA = pd.concat([A1, A2, A2_bab], ignore_index=True)\n",
    "data_noOppor_A2 = pd.concat([A2, A2_bab], ignore_index=True)\n",
    "\n",
    "\n",
    "data_noOppor_AllA.to_csv('..\\\\ProcessedData\\\\data_noOppor_AllA.csv',index=False)\n",
    "data_noOppor_A2.to_csv('..\\\\ProcessedData\\\\data_noOppor_A2.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "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>subsession.round_number</th>\n",
       "      <th>subsession.period</th>\n",
       "      <th>subsession.sg</th>\n",
       "      <th>player.id_in_group</th>\n",
       "      <th>player.endowment</th>\n",
       "      <th>player.join_club</th>\n",
       "      <th>player.local_community</th>\n",
       "      <th>player.id_in_local</th>\n",
       "      <th>player.contribution_local</th>\n",
       "      <th>player.contribution_global</th>\n",
       "      <th>...</th>\n",
       "      <th>player.payoff_globalagg</th>\n",
       "      <th>player.endowment_globalagg</th>\n",
       "      <th>Efficiency</th>\n",
       "      <th>player.payoff_globalMax</th>\n",
       "      <th>ClubTotal</th>\n",
       "      <th>LocalTotal</th>\n",
       "      <th>ClubSize</th>\n",
       "      <th>SocialBenefitMax</th>\n",
       "      <th>Efficiency2</th>\n",
       "      <th>Sequence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>300</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>147.6</td>\n",
       "      <td>1280</td>\n",
       "      <td>0.320312</td>\n",
       "      <td>147.6</td>\n",
       "      <td>280</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>3072.0</td>\n",
       "      <td>0.480469</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>300</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>147.6</td>\n",
       "      <td>1280</td>\n",
       "      <td>0.320312</td>\n",
       "      <td>147.6</td>\n",
       "      <td>280</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>3072.0</td>\n",
       "      <td>0.480469</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>300</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>147.6</td>\n",
       "      <td>1280</td>\n",
       "      <td>0.320312</td>\n",
       "      <td>147.6</td>\n",
       "      <td>280</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>3072.0</td>\n",
       "      <td>0.480469</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>220</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>147.6</td>\n",
       "      <td>1280</td>\n",
       "      <td>0.320312</td>\n",
       "      <td>147.6</td>\n",
       "      <td>280</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>3072.0</td>\n",
       "      <td>0.480469</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>147.6</td>\n",
       "      <td>1280</td>\n",
       "      <td>0.320312</td>\n",
       "      <td>147.6</td>\n",
       "      <td>280</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>3072.0</td>\n",
       "      <td>0.480469</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>635</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>180</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>6912.0</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>636</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>180</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>6912.0</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>180</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>6912.0</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>180</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>6912.0</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>639</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>180</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>...</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>346.6</td>\n",
       "      <td>1440</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>6912.0</td>\n",
       "      <td>0.501447</td>\n",
       "      <td>B2_bab</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>640 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     subsession.round_number  subsession.period  subsession.sg  \\\n",
       "0                          1                  1              1   \n",
       "1                          1                  1              1   \n",
       "2                          1                  1              1   \n",
       "3                          1                  1              1   \n",
       "4                          1                  1              1   \n",
       "..                       ...                ...            ...   \n",
       "635                       10                 10              1   \n",
       "636                       10                 10              1   \n",
       "637                       10                 10              1   \n",
       "638                       10                 10              1   \n",
       "639                       10                 10              1   \n",
       "\n",
       "     player.id_in_group  player.endowment  player.join_club  \\\n",
       "0                     1               300                 0   \n",
       "1                     5               300                 0   \n",
       "2                     2               300                 0   \n",
       "3                     6               220                 1   \n",
       "4                     3                20                 1   \n",
       "..                  ...               ...               ...   \n",
       "635                   6               180                 1   \n",
       "636                   3               180                 1   \n",
       "637                   7               180                 1   \n",
       "638                   4               180                 1   \n",
       "639                   8               180                 1   \n",
       "\n",
       "     player.local_community  player.id_in_local  player.contribution_local  \\\n",
       "0                         1                   0                          0   \n",
       "1                         2                   0                          0   \n",
       "2                         1                   1                          5   \n",
       "3                         2                   1                          0   \n",
       "4                         1                   2                          0   \n",
       "..                      ...                 ...                        ...   \n",
       "635                       0                   1                          0   \n",
       "636                       3                   2                          0   \n",
       "637                       0                   2                          0   \n",
       "638                       3                   3                          2   \n",
       "639                       0                   3                          0   \n",
       "\n",
       "     player.contribution_global  ...  player.payoff_globalagg  \\\n",
       "0                             0  ...                    147.6   \n",
       "1                             0  ...                    147.6   \n",
       "2                             0  ...                    147.6   \n",
       "3                             6  ...                    147.6   \n",
       "4                             2  ...                    147.6   \n",
       "..                          ...  ...                      ...   \n",
       "635                           0  ...                    346.6   \n",
       "636                           0  ...                    346.6   \n",
       "637                           3  ...                    346.6   \n",
       "638                           9  ...                    346.6   \n",
       "639                          18  ...                    346.6   \n",
       "\n",
       "     player.endowment_globalagg  Efficiency  player.payoff_globalMax  \\\n",
       "0                          1280    0.320312                    147.6   \n",
       "1                          1280    0.320312                    147.6   \n",
       "2                          1280    0.320312                    147.6   \n",
       "3                          1280    0.320312                    147.6   \n",
       "4                          1280    0.320312                    147.6   \n",
       "..                          ...         ...                      ...   \n",
       "635                        1440    0.501447                    346.6   \n",
       "636                        1440    0.501447                    346.6   \n",
       "637                        1440    0.501447                    346.6   \n",
       "638                        1440    0.501447                    346.6   \n",
       "639                        1440    0.501447                    346.6   \n",
       "\n",
       "     ClubTotal  LocalTotal  ClubSize SocialBenefitMax Efficiency2  Sequence  \n",
       "0          280        1000         4           3072.0    0.480469    B2_bab  \n",
       "1          280        1000         4           3072.0    0.480469    B2_bab  \n",
       "2          280        1000         4           3072.0    0.480469    B2_bab  \n",
       "3          280        1000         4           3072.0    0.480469    B2_bab  \n",
       "4          280        1000         4           3072.0    0.480469    B2_bab  \n",
       "..         ...         ...       ...              ...         ...       ...  \n",
       "635       1440           0         8           6912.0    0.501447    B2_bab  \n",
       "636       1440           0         8           6912.0    0.501447    B2_bab  \n",
       "637       1440           0         8           6912.0    0.501447    B2_bab  \n",
       "638       1440           0         8           6912.0    0.501447    B2_bab  \n",
       "639       1440           0         8           6912.0    0.501447    B2_bab  \n",
       "\n",
       "[640 rows x 37 columns]"
      ]
     },
     "execution_count": 220,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "B2_bab"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Individual Characteristics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "LabelDict = { 'session': 'session.code', \n",
    "            'participant': 'participant.code',\n",
    "              'social_y_r' : 'PGSO_02_SocialPreferenceElicitation.1.player.responses1',\n",
    "              'social_x_r' : 'PGSO_02_SocialPreferenceElicitation.1.player.responses2',\n",
    "             'age' : 'PGSO_11_Demographics.1.player.ageSelect',\n",
    "             'gender' : 'PGSO_11_Demographics.1.player.genderSelect',\n",
    "             'major' : 'PGSO_11_Demographics.1.player.majorSelect',\n",
    "             'high_school' : 'PGSO_11_Demographics.1.player.hsSelect',\n",
    "              # post-survey:\n",
    "              'understand' : 'PGSO_12_FeedbackQuestions.1.player.understandingSelect', \n",
    "              #choices=['Clear', 'Somewhat Clear', 'Somewhat Confusing', 'Confusing']\n",
    "              'explain_better' : 'PGSO_12_FeedbackQuestions.1.player.explainedText',\n",
    "              #'What could have been explained better?'\n",
    "               #What was your strategy in part 2? How did you make contributions to your group account? (Please be specific)\n",
    "              'contr_local' : 'PGSO_12_FeedbackQuestions.1.player.strategy1Text',\n",
    "              #What was your strategy in part 3? How did you decide to pay the fee and contribute to group accounts? (Please be specific)\n",
    "              'stra_global' : 'PGSO_12_FeedbackQuestions.1.player.strategy2Text',\n",
    "              # 'Did you contribute to your Group Account differently when there is an opportunity to access to Group Account #2?'\n",
    "              'contr_diff_club' : 'PGSO_12_FeedbackQuestions.1.player.strategy3Text',\n",
    "              # 'Will your strategy be different if the fee is higher or lower?'\n",
    "              'stra_diff_fee_higher_lower' : 'PGSO_12_FeedbackQuestions.1.player.strategy4Text',\n",
    "              # 'Did your decision in a round depend on what happened in the previous rounds?'\n",
    "              'str_dep_previous_R' : 'PGSO_12_FeedbackQuestions.1.player.strategy5Text',\n",
    "             'TotalPayoff' : 'PGSO_10_PayoffScreen.1.player.TotalPayoff',\n",
    "            }\n",
    "# IQList = { 'IQ'+str(q) : 'DynamicPower_02d_IQ.'+str(q)+'.player.iqResponses' for q in range(1,12)}\n",
    "# LabelDict.update(IQList)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "All_data = data_ABA.append(data_BAB)\n",
    "individual_data = pd.DataFrame()\n",
    "for j, var in enumerate(LabelDict.keys()):\n",
    "    individual_data[var] = All_data[LabelDict[var]]\n",
    "# Process preference data\n",
    "for var in ['social_x_r', 'social_y_r']:\n",
    "    individual_data[var[:-2]] = individual_data[var].str.count('A')\n",
    "individual_data.to_csv('..\\\\ProcessedData\\\\individual_data.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 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}