02_data_handling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8a2683d8-1d43-4044-9632-e22d7be5fdd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "\n",
    "%store -r query_vars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4ce9eb8a-005d-43a9-82e1-64b38e0eee0d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'wikis_to_query': ('wikishared',),\n",
       " 'snapshot': '2023-09',\n",
       " 'metrics_month': '2023-09',\n",
       " 'metrics_month_start': '2023-09-01 00:00:00',\n",
       " 'metrics_month_first_day': '2023-09-01',\n",
       " 'metrics_month_end': '2023-10-01 00:00:00',\n",
       " 'year': '2023',\n",
       " 'month': '09',\n",
       " 'month_int': 9,\n",
       " 'month_int_end': 10,\n",
       " 'utc_start': '20230901000000',\n",
       " 'utc_end': '20231001000000',\n",
       " 'day_before_utc_end': '20230801000000',\n",
       " 'metrics_month_last_day': '2023-09-30',\n",
       " 'api_metrics_month_first_day': '20230901',\n",
       " 'api_metrics_month_next_month_first_day': '20231001',\n",
       " 'api_metrics_month_day_after': '20231001',\n",
       " 'metrics_prev_month': '2023-08',\n",
       " 'retention_cohort': '2023-07'}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_vars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25bf65c4-edd0-4312-875a-ed24c929a5b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_data  = pd.read_csv('output/wrangling/base_view.csv')  \n",
    "\n",
    "parse_dates_editors = ['cep_registered_at', 'cep_unregistered_at','last_activity_date','ac_date']#'gu_registration'\n",
    "\n",
    "editor_data = pd.read_csv('output/wrangling/editors.csv',parse_dates=parse_dates_editors)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "dc9b41c0-beba-48fa-baf9-1d094c3df698",
   "metadata": {},
   "source": [
    "event_data  = pd.read_csv('output/wrangling/base_view.csv')  \n",
    "editor_data = pd.read_csv('output/wrangling/editors.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aae7ae03-7163-499a-aae8-d53bf764d4c6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05a70f6f-cdcf-40cc-b21f-10c13c6e1012",
   "metadata": {},
   "outputs": [],
   "source": [
    "# datetime\n",
    "event_data['event_created_at'] = pd.to_datetime(event_data['event_created_at'], format = \"%Y%m%d%H%M%S\")#.dt.strftime(\"%Y%m%d%H\")\n",
    "event_data['event_deleted_at'] = pd.to_datetime(event_data['event_deleted_at'], format = \"%Y%m%d%H%M%S\")#.dt.strftime(\"%Y%m%d%H\")\n",
    "event_data['event_last_edit'] = pd.to_datetime(event_data['event_last_edit'], format = \"%Y%m%d%H%M%S\")#.dt.strftime(\"%Y%m%d%H\")\n",
    "event_data['event_start_utc'] = pd.to_datetime(event_data['event_start_utc'], format = \"%Y%m%d%H%M%S\")#.dt.strftime(\"%Y%m%d%H\")\n",
    "event_data['event_end_utc'] = pd.to_datetime(event_data['event_end_utc'], format = \"%Y%m%d%H%M%S\")#.dt.strftime(\"%Y%m%d%H\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f7ea40f2-0d69-486a-b93e-646fe2fd76a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6906a734-b4e5-4ca6-8ca1-ed7aa7f0660e",
   "metadata": {},
   "source": [
    "## handle event type/status\n",
    "### address event dates and whether events are active or registers or tests, etc."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dabdfc47-ff64-4bdd-a994-cfcd13032304",
   "metadata": {},
   "source": [
    "| `active_event` column category | description |\n",
    "|:----------|:--------|\n",
    "| `create` | events that will happen in the future that were created in this period |\n",
    "| `test_register` | event registers, on test events, in this period  |\n",
    "| `test_edit` | organizer edited the event in this period and the event is a test event |\n",
    "| `deleted` | event deletes in this period |\n",
    "| `future_event_editing` | organizer edited the event in this period, the event takes place in the future |\n",
    "| `inprogress_single_month` | active events happening in this period only |\n",
    "| `inprogress_multi_month` | started this period, ongoing |\n",
    "| `inprogress_multi_month` | started previous period, ending this period |\n",
    "| `inprogress_multi_month` | started previous period, ongoing |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a6dda303-a377-4c3f-a86b-4e8cb66f4776",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#for categorizing as 'inprogress_single_month'\n",
    "event_active_mask = ((event_data['event_start_utc'] >= query_vars['utc_start']) & \n",
    "                        (event_data['event_end_utc'] < query_vars['utc_end'])\n",
    "                       )\n",
    "\n",
    "# for categorizing as 'create'\n",
    "event_register_mask = ((event_data['event_created_at'] >= query_vars['utc_start']) & \n",
    "                       (event_data['event_created_at'] < query_vars['utc_end']) \n",
    "                       #& (event_data['event_start_utc']>= query_vars['utc_end'])\n",
    "                      )\n",
    "\n",
    "\n",
    "event_last_edit_mask = ((event_data['event_last_edit'] >= query_vars['utc_start']) & \n",
    "                       (event_data['event_last_edit'] < query_vars['utc_end']))\n",
    "\n",
    "event_deleted_mask = ((event_data['event_deleted_at'] >= query_vars['utc_start']) &\n",
    "                      (event_data['event_deleted_at'] < query_vars['utc_end']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "39d7e1d0-994a-4f46-b970-842c45194de0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#register_test: event registers in the last month that are test events\n",
    "event_data.loc[(event_register_mask) &\n",
    "               (event_data['event_deleted_at'].isna()) &\n",
    "               (event_data['event_status'] == 5), 'active_event'] = 'test_register'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a5550199-fff8-4acc-85ef-c500d1067971",
   "metadata": {
    "tags": []
   },
   "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>event_id</th>\n",
       "      <th>event_status</th>\n",
       "      <th>active_event</th>\n",
       "      <th>organizer_username</th>\n",
       "      <th>event_deleted_at</th>\n",
       "      <th>event_start_utc</th>\n",
       "      <th>event_end_utc</th>\n",
       "      <th>event_created_at</th>\n",
       "      <th>event_last_edit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [event_id, event_status, active_event, organizer_username, event_deleted_at, event_start_utc, event_end_utc, event_created_at, event_last_edit]\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "event_data[['event_id', 'event_status','active_event','organizer_username','event_deleted_at','event_start_utc', 'event_end_utc','event_created_at','event_last_edit']][event_data['active_event']=='register_test']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ae674c63-74a8-4475-b6fa-41447db40110",
   "metadata": {},
   "outputs": [],
   "source": [
    "#edit_test: event edits in the last month on test events\n",
    "event_data.loc[(event_last_edit_mask) &\n",
    "               (event_data['event_deleted_at'].isna()) &\n",
    "               (event_data['event_status'] == 5), 'active_event'] = 'test_edit'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e7328a23-8edf-4cdc-8fe9-90c3c7837a24",
   "metadata": {},
   "outputs": [],
   "source": [
    "#'inprogress_single_month': active events happening in this period only \n",
    "event_data.loc[(event_active_mask) &\n",
    "               (event_data['event_status'] != 5) &\n",
    "               (event_data['event_deleted_at'].isna()), 'active_event'] = 'inprogress_single_month'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2ff4e2f1-9dc6-42d2-bd67-f09f73bd8b59",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create: events that will happen in the future that were created in this period\n",
    "event_data.loc[(event_register_mask) & \n",
    "               (event_data['event_status'] != 5) &\n",
    "               (event_data['event_deleted_at'].isna()), 'active_event'] = 'create'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "997ed222-3443-4431-bc1c-a58555c73f05",
   "metadata": {},
   "outputs": [],
   "source": [
    "#inprogress_multi_month: started this period, ongoing\n",
    "event_data.loc[(event_data['active_event'].isna()) & \n",
    "               (event_data['event_start_utc'].dt.month==query_vars['month_int']) & \n",
    "               (event_data['event_status'] != 5) &\n",
    "               (event_data['event_end_utc'] > query_vars['utc_end']), 'active_event'] = 'inprogress_multi_month'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "71e73868-d23c-4f51-b2a1-6a7ef3925952",
   "metadata": {},
   "outputs": [],
   "source": [
    "#inprogress_multi_month: started previous period, ongoing\n",
    "event_data.loc[(event_data['active_event'].isna()) & \n",
    "               (event_data['event_start_utc']<= query_vars['utc_start']) & \n",
    "               (event_data['event_status'] != 5) &\n",
    "               (event_data['event_end_utc'] > query_vars['utc_end']), 'active_event'] = 'inprogress_multi_month'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f47da24f-a150-4daf-8340-442b43bd5963",
   "metadata": {},
   "outputs": [],
   "source": [
    "#inprogress_multi_month: started previous period, ending this period\n",
    "event_data.loc[(event_data['active_event'].isna()) & \n",
    "               (event_data['event_status'] != 5) &\n",
    "               (event_data['event_end_utc'].dt.month==query_vars['month_int']), 'active_event'] = 'inprogress_multi_month'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "44cc30ad-675c-4548-b34a-6a01ab5e8352",
   "metadata": {},
   "outputs": [],
   "source": [
    "#future_event_editing: organizer edited the event in this period, the event takes place in the future\n",
    "event_data.loc[(event_data['active_event'].isna()) & \n",
    "               (event_data['event_status'] != 5) &\n",
    "               (event_data['event_last_edit'].dt.month==query_vars['month_int']), 'active_event'] = 'future_event_editing'"
   ]
  },
  {
   "cell_type": "raw",
   "id": "7bab3988-2656-4baa-85fc-923a19ce95bf",
   "metadata": {
    "tags": []
   },
   "source": [
    "event_data[['event_id', 'event_page_title','event_status','event_deleted_at','organizer_username','event_start_utc', 'event_end_utc','event_created_at','event_last_edit']][event_data['active_event']=='inprogress_multi_month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "945cd323-6457-4911-97c1-45b125001fe9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#event deletes in this period\n",
    "event_data.loc[event_deleted_mask, 'active_event'] = 'deleted'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9973fde8-3067-4a58-a37a-cc1179d331b7",
   "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>event_id</th>\n",
       "      <th>event_status</th>\n",
       "      <th>event_deleted_at</th>\n",
       "      <th>organizer_username</th>\n",
       "      <th>event_start_utc</th>\n",
       "      <th>event_end_utc</th>\n",
       "      <th>event_created_at</th>\n",
       "      <th>event_last_edit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [event_id, event_status, event_deleted_at, organizer_username, event_start_utc, event_end_utc, event_created_at, event_last_edit]\n",
       "Index: []"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "event_data[['event_id', 'event_status','event_deleted_at','organizer_username','event_start_utc', 'event_end_utc','event_created_at','event_last_edit']][event_data['active_event']=='deleted']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3765e63a-0f27-4dd8-91cb-92a43752b33e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "z = (event_data[['event_id', 'event_status','event_deleted_at','organizer_username','event_start_utc', \n",
    "            'event_end_utc','event_created_at','event_last_edit']][event_data['active_event'].isna()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "9abefd88-dd54-416f-8b54-d86dd22522f2",
   "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>event_id</th>\n",
       "      <th>event_status</th>\n",
       "      <th>event_deleted_at</th>\n",
       "      <th>organizer_username</th>\n",
       "      <th>event_start_utc</th>\n",
       "      <th>event_end_utc</th>\n",
       "      <th>event_created_at</th>\n",
       "      <th>event_last_edit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [event_id, event_status, event_deleted_at, organizer_username, event_start_utc, event_end_utc, event_created_at, event_last_edit]\n",
       "Index: []"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "z[z['event_status']==5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "85e304ee-e588-4eff-8932-6b525a0a5d15",
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorder columns\n",
    "event_data = event_data[['event_id','event_status','active_event','event_page_title',\n",
    "                                   'country','wmf_region', \n",
    "                                   'pageviews',\n",
    "                                   'organizers','organizer_username',\n",
    "                                   'new_users_count',\n",
    "                                   'participants',\n",
    "                                   'event_created_at','event_start_utc', 'event_end_utc', 'event_last_edit','event_deleted_at',\n",
    "                                   'event_chat_url_clean','cett_tool_id','cett_tool_event_id','event_meeting_url',\n",
    "                                   'participants_register_count','participant_unregister_count',\n",
    "                                   'event_type','event_meeting_type'\n",
    "                                  ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cd737b7-1d95-4c88-bfca-4208c5397d94",
   "metadata": {},
   "outputs": [],
   "source": [
    "## remove events created internally for testing by organizer username"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a3abf87d-1070-4701-9544-b3724e2e3ad2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO - For meta, all test events should be subpages of Event:Sandbox. If that's not the case, then those that don't comply should be moved.\n",
    "#see conversation on slack: https://wikimedia.slack.com/archives/C020V0GN9P1/p1690976380675029?thread_ts=1690924097.149909&cid=C020V0GN9P1\n",
    "#remove tester data\n",
    "#list, discussion: https://wikimedia.slack.com/archives/C020V0GN9P1/p1690924134855739?thread_ts=1690924097.149909&cid=C020V0GN9P1\n",
    "do_not_include_list = ['VWalters-WMF', \n",
    "                       'QTE-CampaignEvents0', 'QTE-CampaignEvents1', \n",
    "                       'QTE-CampaignEvents2', 'QTE-CampaignEvents3', 'QTE-CampaignEvents4', \n",
    "                       'QTE-CampaignEvents5', 'QTE-CampaignEvents6', 'QTE-CampaignEvents7','LDelench (WMF)']\n",
    "\n",
    "\n",
    "df_exclude = pd.DataFrame({'testers': do_not_include_list})\n",
    "\n",
    "\n",
    "for a in event_data.organizer_username.index:\n",
    "    for search in df_exclude.testers:\n",
    "        if search in event_data.loc[a].organizer_username:\n",
    "            event_data.drop(a, inplace=True)\n",
    "            break  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a63a97e2-a53e-40e1-85f4-f72219a4b171",
   "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>event_id</th>\n",
       "      <th>event_status</th>\n",
       "      <th>active_event</th>\n",
       "      <th>event_page_title</th>\n",
       "      <th>country</th>\n",
       "      <th>wmf_region</th>\n",
       "      <th>pageviews</th>\n",
       "      <th>organizers</th>\n",
       "      <th>organizer_username</th>\n",
       "      <th>new_users_count</th>\n",
       "      <th>...</th>\n",
       "      <th>event_last_edit</th>\n",
       "      <th>event_deleted_at</th>\n",
       "      <th>event_chat_url_clean</th>\n",
       "      <th>cett_tool_id</th>\n",
       "      <th>cett_tool_event_id</th>\n",
       "      <th>event_meeting_url</th>\n",
       "      <th>participants_register_count</th>\n",
       "      <th>participant_unregister_count</th>\n",
       "      <th>event_type</th>\n",
       "      <th>event_meeting_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [event_id, event_status, active_event, event_page_title, country, wmf_region, pageviews, organizers, organizer_username, new_users_count, participants, event_created_at, event_start_utc, event_end_utc, event_last_edit, event_deleted_at, event_chat_url_clean, cett_tool_id, cett_tool_event_id, event_meeting_url, participants_register_count, participant_unregister_count, event_type, event_meeting_type]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 24 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "event_data[~event_data['event_deleted_at'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "9db8fb10-e53c-4f23-bc6c-882724270a05",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_data.to_csv('output/handling/metrics.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "928b3460-bbdc-43fd-b6a8-264e404de0c9",
   "metadata": {},
   "source": [
    "## handle editor data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "81446d85-c8c6-4906-a618-f93f5d8c7f8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "editor_data_df = editor_data.merge(event_data[['event_id', 'event_type']], on = 'event_id', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5d96731-a5c7-4317-9c53-2f935b16b2d0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3437d709-82aa-45c1-85eb-e0156b2d02b8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df5cdf7d-924e-422e-8d3c-d1a605453637",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "id": "881a955d-9c37-4983-893c-a17ac13be3ff",
   "metadata": {
    "tags": []
   },
   "source": [
    "#if this participant joined a test event, flag that\n",
    "\n",
    "editor_data_df.loc[(editor_data_df['event_type']  == 5) &\n",
    "                (editor_data_df['editor_type'] == 'event_participant'), 'test_registration'] = 'test_registration'"
   ]
  },
  {
   "cell_type": "raw",
   "id": "aefcc8a7-5eb3-4a0b-a0d9-0cc0c04a22bd",
   "metadata": {},
   "source": [
    "participant_registered_in_month_mask = (editor_data['cep_registered_at'] >= query_vars['utc_start']) & (editor_data['cep_registered_at'] <= query_vars['utc_end'])\n",
    "participant_registered_previously_mask = (editor_data['cep_registered_at'] < query_vars['utc_start'])\n",
    "\n",
    "editor_data.loc[participant_registered_in_month_mask & (editor_data['editor_type'] == 'event_participant'), 'registration_month'] = 'registered_in_month'\n",
    "editor_data.loc[participant_registered_previously_mask & (editor_data['editor_type'] == 'event_participant'), 'registration_month'] = 'registered_previously'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "89593667-a150-4eb0-aac1-825e66a70fb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "editor_data_df.to_csv('output/handling/editors.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e32dfcf4-395c-4c64-bcfc-7240ffd151a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#remove tester data"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}