{ "cells": [ { "cell_type": "code", "execution_count": 86, "id": "beace815-b5ae-44a4-a81c-a7f82cb66296", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\u001b[2K\u001b[2mResolved \u001b[1m118 packages\u001b[0m \u001b[2min 386ms\u001b[0m\u001b[0m \u001b[0m\n", "\u001b[2K\u001b[2mPrepared \u001b[1m2 packages\u001b[0m \u001b[2min 124ms\u001b[0m\u001b[0m \n", "\u001b[2K\u001b[2mInstalled \u001b[1m2 packages\u001b[0m \u001b[2min 5ms\u001b[0m\u001b[0m \u001b[0m\n", " \u001b[32m+\u001b[39m \u001b[1met-xmlfile\u001b[0m\u001b[2m==2.0.0\u001b[0m\n", " \u001b[32m+\u001b[39m \u001b[1mopenpyxl\u001b[0m\u001b[2m==3.1.5\u001b[0m\n" ] } ], "source": [ "!uv add pandas requests openai dotenv openpyxl" ] }, { "cell_type": "code", "execution_count": 3, "id": "941d511f-ad72-4306-bbab-52127583e513", "metadata": {}, "outputs": [], "source": [ "import os\n", "import dotenv\n", "import openai\n", "import sqlite3\n", "import pandas as pd\n", "\n", "dotenv.load_dotenv() # Copy .env.example to .env and fill in the blanks\n", "\n", "oai_token = os.getenv(\"OPENAI_API_KEY\")\n", "\n", "oai = openai.OpenAI(api_key=oai_token)\n", "onet = sqlite3.connect(\"onet.database\") # Run ./create_onet_database.sh to create it\n", "# This dataset comes from https://epoch.ai/gradient-updates/consequences-of-automating-remote-work\n", "# It contains labels for whethere a O*NET task can be done remotely or not (labeled by GPT-4o)\n", "# You can download it here: https://drive.google.com/file/d/1GrHhuYIgaCCgo99dZ_40BWraz-fzo76r/view?usp=sharing\n", "df_remote_status = pd.read_csv(\"epoch_task_data.csv\")\n", "\n", "# BLS OEWS: https://www.bls.gov/oes/special-requests/oesm23nat.zip\n", "df_oesm = pd.read_excel(\"oesm23national.xlsx\")\n", "\n", "# Run uv run enrich_task_ratings.py to get this file (trs = Task RatingS)\n", "df_enriched_trs = pd.read_json(\"task_ratings_enriched.json\")" ] }, { "cell_type": "code", "execution_count": 4, "id": "a5351f8b-c2ad-4d3e-af4a-992f539a6064", "metadata": {}, "outputs": [], "source": [ "FREQUENCY_MAP = {\n", " 'frequency_category_1': \"Yearly or less\",\n", " 'frequency_category_2': \"More than yearly\",\n", " 'frequency_category_3': \"More than monthly\",\n", " 'frequency_category_4': \"More than weekly\",\n", " 'frequency_category_5': \"Daily\",\n", " 'frequency_category_6': \"Several times daily\",\n", " 'frequency_category_7': \"Hourly or more\"\n", "}" ] }, { "cell_type": "code", "execution_count": 5, "id": "8b2ab22a-afab-41f9-81a3-48eab261b568", "metadata": {}, "outputs": [], "source": [ "background_prompt = '''\n", "Estimate the typical duration to complete *one instance* of the following job task from the moment a person starts to work on it to the last moment the person will need to keep it in mind\n", "\n", "Take into account that there might be delays between the steps to complete the task, which would lengthen the estimate.\n", "\n", "Output a range with the format [duration A] - [duration B] where [duration A] and [duration B] correspond to one of the durations below:\n", "- less than 30 minutes\n", "- 30 minutes\n", "- 1 hour\n", "- 4 hours\n", "- 8 hours\n", "- 16 hours\n", "- 3 days\n", "- 1 week\n", "- 3 weeks\n", "- 6 weeks\n", "- 3 months\n", "- 6 months\n", "- 1 year\n", "- 3 years\n", "- more than 3 year\n", "\n", "**Do not output anything besides the range**\n", "'''" ] }, { "cell_type": "code", "execution_count": 16, "id": "d2e4a855-f327-4b3d-ad0b-ed997e720639", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
onetsoc_codetask_idtaskoccupation_titleoccupation_descriptionfrequency_category_1frequency_category_2frequency_category_3frequency_category_4frequency_category_5frequency_category_6frequency_category_7importance_averagerelevance_averageOCC_CODETOT_EMPH_MEANA_MEAN
011-1011.008823Direct or coordinate an organization's financi...Chief ExecutivesDetermine and formulate policies and provide o...5.9215.9829.6821.1819.714.912.634.5274.4411-1011211230.0124.47258900
111-1011.008824Confer with board members, organization offici...Chief ExecutivesDetermine and formulate policies and provide o...1.4214.4427.3125.5226.882.521.904.3281.7111-1011211230.0124.47258900
211-1011.008827Prepare budgets for approval, including those ...Chief ExecutivesDetermine and formulate policies and provide o...15.5038.2132.735.155.250.192.984.3093.4111-1011211230.0124.47258900
311-1011.008826Direct, plan, or implement policies, objective...Chief ExecutivesDetermine and formulate policies and provide o...3.0317.3320.3018.1033.162.016.074.2497.7911-1011211230.0124.47258900
411-1011.008834Prepare or present reports concerning activiti...Chief ExecutivesDetermine and formulate policies and provide o...1.9814.0642.6021.2413.186.240.704.1792.9211-1011211230.0124.47258900
.........................................................
1763453-7121.0012807Unload cars containing liquids by connecting h...Tank Car, Truck, and Ship LoadersLoad and unload chemicals and bulk solids, suc...6.0529.216.8813.9527.657.938.344.0864.0453-712111400.029.160530
1763553-7121.0012804Clean interiors of tank cars or tank trucks, u...Tank Car, Truck, and Ship LoadersLoad and unload chemicals and bulk solids, suc...1.476.3321.7025.6932.3512.470.004.0244.3353-712111400.029.160530
1763653-7121.0012803Lower gauge rods into tanks or read meters to ...Tank Car, Truck, and Ship LoadersLoad and unload chemicals and bulk solids, suc...4.521.764.6517.8137.4223.3110.553.8865.0053-712111400.029.160530
1763753-7121.0012805Operate conveyors and equipment to transfer gr...Tank Car, Truck, and Ship LoadersLoad and unload chemicals and bulk solids, suc...6.9712.002.525.9035.4822.0815.053.8747.9053-712111400.029.160530
1763853-7121.0012810Perform general warehouse activities, such as ...Tank Car, Truck, and Ship LoadersLoad and unload chemicals and bulk solids, suc...5.9110.856.4614.4634.1416.3911.783.5347.8453-712111400.029.160530
\n", "

17639 rows × 18 columns

\n", "
" ], "text/plain": [ " onetsoc_code task_id \\\n", "0 11-1011.00 8823 \n", "1 11-1011.00 8824 \n", "2 11-1011.00 8827 \n", "3 11-1011.00 8826 \n", "4 11-1011.00 8834 \n", "... ... ... \n", "17634 53-7121.00 12807 \n", "17635 53-7121.00 12804 \n", "17636 53-7121.00 12803 \n", "17637 53-7121.00 12805 \n", "17638 53-7121.00 12810 \n", "\n", " task \\\n", "0 Direct or coordinate an organization's financi... \n", "1 Confer with board members, organization offici... \n", "2 Prepare budgets for approval, including those ... \n", "3 Direct, plan, or implement policies, objective... \n", "4 Prepare or present reports concerning activiti... \n", "... ... \n", "17634 Unload cars containing liquids by connecting h... \n", "17635 Clean interiors of tank cars or tank trucks, u... \n", "17636 Lower gauge rods into tanks or read meters to ... \n", "17637 Operate conveyors and equipment to transfer gr... \n", "17638 Perform general warehouse activities, such as ... \n", "\n", " occupation_title \\\n", "0 Chief Executives \n", "1 Chief Executives \n", "2 Chief Executives \n", "3 Chief Executives \n", "4 Chief Executives \n", "... ... \n", "17634 Tank Car, Truck, and Ship Loaders \n", "17635 Tank Car, Truck, and Ship Loaders \n", "17636 Tank Car, Truck, and Ship Loaders \n", "17637 Tank Car, Truck, and Ship Loaders \n", "17638 Tank Car, Truck, and Ship Loaders \n", "\n", " occupation_description \\\n", "0 Determine and formulate policies and provide o... \n", "1 Determine and formulate policies and provide o... \n", "2 Determine and formulate policies and provide o... \n", "3 Determine and formulate policies and provide o... \n", "4 Determine and formulate policies and provide o... \n", "... ... \n", "17634 Load and unload chemicals and bulk solids, suc... \n", "17635 Load and unload chemicals and bulk solids, suc... \n", "17636 Load and unload chemicals and bulk solids, suc... \n", "17637 Load and unload chemicals and bulk solids, suc... \n", "17638 Load and unload chemicals and bulk solids, suc... \n", "\n", " frequency_category_1 frequency_category_2 frequency_category_3 \\\n", "0 5.92 15.98 29.68 \n", "1 1.42 14.44 27.31 \n", "2 15.50 38.21 32.73 \n", "3 3.03 17.33 20.30 \n", "4 1.98 14.06 42.60 \n", "... ... ... ... \n", "17634 6.05 29.21 6.88 \n", "17635 1.47 6.33 21.70 \n", "17636 4.52 1.76 4.65 \n", "17637 6.97 12.00 2.52 \n", "17638 5.91 10.85 6.46 \n", "\n", " frequency_category_4 frequency_category_5 frequency_category_6 \\\n", "0 21.18 19.71 4.91 \n", "1 25.52 26.88 2.52 \n", "2 5.15 5.25 0.19 \n", "3 18.10 33.16 2.01 \n", "4 21.24 13.18 6.24 \n", "... ... ... ... \n", "17634 13.95 27.65 7.93 \n", "17635 25.69 32.35 12.47 \n", "17636 17.81 37.42 23.31 \n", "17637 5.90 35.48 22.08 \n", "17638 14.46 34.14 16.39 \n", "\n", " frequency_category_7 importance_average relevance_average OCC_CODE \\\n", "0 2.63 4.52 74.44 11-1011 \n", "1 1.90 4.32 81.71 11-1011 \n", "2 2.98 4.30 93.41 11-1011 \n", "3 6.07 4.24 97.79 11-1011 \n", "4 0.70 4.17 92.92 11-1011 \n", "... ... ... ... ... \n", "17634 8.34 4.08 64.04 53-7121 \n", "17635 0.00 4.02 44.33 53-7121 \n", "17636 10.55 3.88 65.00 53-7121 \n", "17637 15.05 3.87 47.90 53-7121 \n", "17638 11.78 3.53 47.84 53-7121 \n", "\n", " TOT_EMP H_MEAN A_MEAN \n", "0 211230.0 124.47 258900 \n", "1 211230.0 124.47 258900 \n", "2 211230.0 124.47 258900 \n", "3 211230.0 124.47 258900 \n", "4 211230.0 124.47 258900 \n", "... ... ... ... \n", "17634 11400.0 29.1 60530 \n", "17635 11400.0 29.1 60530 \n", "17636 11400.0 29.1 60530 \n", "17637 11400.0 29.1 60530 \n", "17638 11400.0 29.1 60530 \n", "\n", "[17639 rows x 18 columns]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_oesm_detailed = df_oesm[df_oesm['O_GROUP'] == 'detailed'][['OCC_CODE', 'TOT_EMP', 'H_MEAN', 'A_MEAN']].copy()\n", "df_enriched_trs['occ_code_join'] = df_enriched_trs['onetsoc_code'].str[:7]\n", "df = pd.merge(\n", " df_enriched_trs,\n", " df_oesm_detailed,\n", " left_on='occ_code_join',\n", " right_on='OCC_CODE',\n", " how='left'\n", ")\n", "df = df.drop(columns=['occ_code_join'])\n", "df" ] }, { "cell_type": "code", "execution_count": 11, "id": "9be7acb5-2374-4f61-bba3-13b0077c0bd2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Task: Identify, evaluate and recommend hardware or software technologies to achieve desired database performance.\n", "Occupation Description: Design strategies for enterprise databases, data warehouse systems, and multidimensional networks. Set standards for database operations, programming, query processes, and security. Model, design, and construct large relational databases or data warehouses. Create and optimize data models for warehouse infrastructure and workflow. Integrate new systems with existing warehouse structure and refine system performance and functionality.\n", "Occupation Title: Database Architects\n" ] }, { "data": { "text/plain": [ "119976" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_merged = pd \\\n", " .merge(left=df_enriched_trs, right=df_remote_status[['O*NET-SOC Code', 'Remote']], how='left', left_on='onetsoc_code', right_on='O*NET-SOC Code') \\\n", " .drop(columns=['O*NET-SOC Code']) \\\n", " .rename(columns={'Remote': 'remote'}) \\\n", " .rename(columns=FREQUENCY_MAP) \\\n", " .query('remote == \"remote\" and importance_average >= 3 and relevance_average > 50')\n", "\n", "row = df_merged.iloc[30000]\n", "print('Task: ', row['task'])\n", "print('Occupation Description: ', row['occupation_description'])\n", "print('Occupation Title: ', row['occupation_title'])\n", "\n", "len(df_merged)" ] }, { "cell_type": "code", "execution_count": null, "id": "fd9ac1c3-6d17-4764-8a2e-c84d4019bd9e", "metadata": { "jp-MarkdownHeadingCollapsed": true }, "outputs": [], "source": [ "# Cross-reference woth BLS OEWS\n", "# It doesn't really make sens to have it per-task, we only need it per-occupation...\n", "df_oesm_detailed = df_oesm[df_oesm['O_GROUP'] == 'detailed'][['OCC_CODE', 'TOT_EMP', 'H_MEAN', 'A_MEAN']].copy()\n", "df_merged['occ_code_join'] = df_merged['onetsoc_code'].str[:7]\n", "df_merged = pd.merge(\n", " df_merged,\n", " df_oesm_detailed,\n", " left_on='occ_code_join',\n", " right_on='OCC_CODE',\n", " how='left'\n", ")\n", "df_merged = df_merged.drop(columns=['occ_code_join'])\n", "df_merged" ] }, { "cell_type": "code", "execution_count": 76, "id": "08f45d91-039d-4ec0-94a2-f305a3312e6a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Why did the scarecrow win an award?\n", "\n", "Because he was outstanding in his field!\n" ] } ], "source": [ "response = oai.chat.completions.create(messages=[{\"role\": \"user\", \"content\": \"Tell me a joke\"}], model=\"gpt-4.1-2025-04-14\", max_tokens=100, temperature=0.7, n=1, stop=None)\n", "joke = response.choices[0].message.content.strip()\n", "print(joke)" ] } ], "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.13.2" } }, "nbformat": 4, "nbformat_minor": 5 }