assets/notebook/county_state_resoure.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46a90da8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('/id_county_pop.csv',dtype={'id': str})\n",
    "df1 = pd.read_csv('/content/drive/MyDrive/Colab Notebooks/coordi_data/CA.csv')\n",
    "df['id'] = df['id'].str.zfill(5)\n",
    "df1['County'] = df1['County'] + ' County'\n",
    "\n",
    "df1['State'] = df1['Site State'].replace({'CA': 'California'})\n",
    "df1 = df1[df1['State'].str.contains('California')]\n",
    "\n",
    "df1 = df1.groupby('County').agg(State=('State', 'first'), Site_Count=('Site Name', 'count')).reset_index()\n",
    "m = pd.merge(df1,df, on=['County','State'], how='left')\n",
    "m['state'] = m['id'].str[:2]\n",
    "m['county'] = m['id'].str[2:]\n",
    "\n",
    "# Drop the original 'ID' column\n",
    "m = m.drop(columns=['id','County','State','Population'])\n",
    "m.to_csv('/content/drive/MyDrive/Colab Notebooks/bubble/CA_bubble.csv', index=False)"
   ]
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}