C7W1 Activity.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4ed0db65",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import Pandas as pd\n",
    "\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5d57c5b9",
   "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>LatD</th>\n",
       "      <th>\"LatM\"</th>\n",
       "      <th>\"LatS\"</th>\n",
       "      <th>\"NS\"</th>\n",
       "      <th>\"LonD\"</th>\n",
       "      <th>\"LonM\"</th>\n",
       "      <th>\"LonS\"</th>\n",
       "      <th>\"EW\"</th>\n",
       "      <th>\"City\"</th>\n",
       "      <th>\"State\"</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41</td>\n",
       "      <td>5</td>\n",
       "      <td>59</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>80</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Youngstown\"</td>\n",
       "      <td>OH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>52</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>97</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Yankton\"</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46</td>\n",
       "      <td>35</td>\n",
       "      <td>59</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>120</td>\n",
       "      <td>30</td>\n",
       "      <td>36</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Yakima\"</td>\n",
       "      <td>WA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>42</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>71</td>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Worcester\"</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>43</td>\n",
       "      <td>37</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>89</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Wisconsin Dells\"</td>\n",
       "      <td>WI</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>39</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>119</td>\n",
       "      <td>48</td>\n",
       "      <td>35</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Reno\"</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>50</td>\n",
       "      <td>25</td>\n",
       "      <td>11</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>104</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Regina\"</td>\n",
       "      <td>SA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>122</td>\n",
       "      <td>14</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Red Bluff\"</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>40</td>\n",
       "      <td>19</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>75</td>\n",
       "      <td>55</td>\n",
       "      <td>48</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Reading\"</td>\n",
       "      <td>PA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>41</td>\n",
       "      <td>9</td>\n",
       "      <td>35</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>81</td>\n",
       "      <td>14</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Ravenna\"</td>\n",
       "      <td>OH</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     LatD   \"LatM\"   \"LatS\"  \"NS\"   \"LonD\"   \"LonM\"   \"LonS\"  \"EW\"  \\\n",
       "0      41        5       59   \"N\"       80       39        0   \"W\"   \n",
       "1      42       52       48   \"N\"       97       23       23   \"W\"   \n",
       "2      46       35       59   \"N\"      120       30       36   \"W\"   \n",
       "3      42       16       12   \"N\"       71       48        0   \"W\"   \n",
       "4      43       37       48   \"N\"       89       46       11   \"W\"   \n",
       "..    ...      ...      ...   ...      ...      ...      ...   ...   \n",
       "123    39       31       12   \"N\"      119       48       35   \"W\"   \n",
       "124    50       25       11   \"N\"      104       39        0   \"W\"   \n",
       "125    40       10       48   \"N\"      122       14       23   \"W\"   \n",
       "126    40       19       48   \"N\"       75       55       48   \"W\"   \n",
       "127    41        9       35   \"N\"       81       14       23   \"W\"   \n",
       "\n",
       "                 \"City\"  \"State\"  \n",
       "0          \"Youngstown\"       OH  \n",
       "1             \"Yankton\"       SD  \n",
       "2              \"Yakima\"       WA  \n",
       "3           \"Worcester\"       MA  \n",
       "4     \"Wisconsin Dells\"       WI  \n",
       "..                  ...      ...  \n",
       "123              \"Reno\"       NV  \n",
       "124            \"Regina\"       SA  \n",
       "125         \"Red Bluff\"       CA  \n",
       "126           \"Reading\"       PA  \n",
       "127           \"Ravenna\"      OH   \n",
       "\n",
       "[128 rows x 10 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#import csv to dataframe\n",
    "df = pd.read_csv('cities.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "40fff895",
   "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>LatD</th>\n",
       "      <th>\"LatM\"</th>\n",
       "      <th>\"LatS\"</th>\n",
       "      <th>\"NS\"</th>\n",
       "      <th>\"LonD\"</th>\n",
       "      <th>\"LonM\"</th>\n",
       "      <th>\"LonS\"</th>\n",
       "      <th>\"EW\"</th>\n",
       "      <th>\"City\"</th>\n",
       "      <th>\"State\"</th>\n",
       "      <th>City_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41</td>\n",
       "      <td>5</td>\n",
       "      <td>59</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>80</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Youngstown\"</td>\n",
       "      <td>OH</td>\n",
       "      <td>YOT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>52</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>97</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Yankton\"</td>\n",
       "      <td>SD</td>\n",
       "      <td>YT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46</td>\n",
       "      <td>35</td>\n",
       "      <td>59</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>120</td>\n",
       "      <td>30</td>\n",
       "      <td>36</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Yakima\"</td>\n",
       "      <td>WA</td>\n",
       "      <td>YK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>42</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>71</td>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Worcester\"</td>\n",
       "      <td>MA</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>43</td>\n",
       "      <td>37</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>89</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Wisconsin Dells\"</td>\n",
       "      <td>WI</td>\n",
       "      <td>WD</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>39</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>119</td>\n",
       "      <td>48</td>\n",
       "      <td>35</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Reno\"</td>\n",
       "      <td>NV</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>50</td>\n",
       "      <td>25</td>\n",
       "      <td>11</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>104</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Regina\"</td>\n",
       "      <td>SA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>122</td>\n",
       "      <td>14</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Red Bluff\"</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>40</td>\n",
       "      <td>19</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>75</td>\n",
       "      <td>55</td>\n",
       "      <td>48</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Reading\"</td>\n",
       "      <td>PA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>41</td>\n",
       "      <td>9</td>\n",
       "      <td>35</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>81</td>\n",
       "      <td>14</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Ravenna\"</td>\n",
       "      <td>OH</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     LatD   \"LatM\"   \"LatS\"  \"NS\"   \"LonD\"   \"LonM\"   \"LonS\"  \"EW\"  \\\n",
       "0      41        5       59   \"N\"       80       39        0   \"W\"   \n",
       "1      42       52       48   \"N\"       97       23       23   \"W\"   \n",
       "2      46       35       59   \"N\"      120       30       36   \"W\"   \n",
       "3      42       16       12   \"N\"       71       48        0   \"W\"   \n",
       "4      43       37       48   \"N\"       89       46       11   \"W\"   \n",
       "..    ...      ...      ...   ...      ...      ...      ...   ...   \n",
       "123    39       31       12   \"N\"      119       48       35   \"W\"   \n",
       "124    50       25       11   \"N\"      104       39        0   \"W\"   \n",
       "125    40       10       48   \"N\"      122       14       23   \"W\"   \n",
       "126    40       19       48   \"N\"       75       55       48   \"W\"   \n",
       "127    41        9       35   \"N\"       81       14       23   \"W\"   \n",
       "\n",
       "                 \"City\"  \"State\" City_Code  \n",
       "0          \"Youngstown\"       OH       YOT  \n",
       "1             \"Yankton\"       SD        YT  \n",
       "2              \"Yakima\"       WA        YK  \n",
       "3           \"Worcester\"       MA        WT  \n",
       "4     \"Wisconsin Dells\"       WI        WD  \n",
       "..                  ...      ...       ...  \n",
       "123              \"Reno\"       NV       NaN  \n",
       "124            \"Regina\"       SA       NaN  \n",
       "125         \"Red Bluff\"       CA       NaN  \n",
       "126           \"Reading\"       PA       NaN  \n",
       "127           \"Ravenna\"      OH        NaN  \n",
       "\n",
       "[128 rows x 11 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add Column to Dataframe\n",
    "citycode = ['YOT', 'YT', 'YK', 'WT', 'WD']\n",
    "\n",
    "df['City_Code'] = pd.Series(citycode)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "26a01365",
   "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>LatD</th>\n",
       "      <th>\"LatM\"</th>\n",
       "      <th>\"LatS\"</th>\n",
       "      <th>\"LonD\"</th>\n",
       "      <th>\"LonM\"</th>\n",
       "      <th>\"LonS\"</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>128.000000</td>\n",
       "      <td>128.000000</td>\n",
       "      <td>128.000000</td>\n",
       "      <td>128.000000</td>\n",
       "      <td>128.000000</td>\n",
       "      <td>128.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>38.820312</td>\n",
       "      <td>30.765625</td>\n",
       "      <td>27.492188</td>\n",
       "      <td>93.250000</td>\n",
       "      <td>27.742188</td>\n",
       "      <td>26.960938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.200596</td>\n",
       "      <td>16.426158</td>\n",
       "      <td>18.977814</td>\n",
       "      <td>15.466499</td>\n",
       "      <td>16.927937</td>\n",
       "      <td>18.727807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>26.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>35.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>39.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>89.500000</td>\n",
       "      <td>26.500000</td>\n",
       "      <td>23.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>42.250000</td>\n",
       "      <td>45.000000</td>\n",
       "      <td>47.000000</td>\n",
       "      <td>103.250000</td>\n",
       "      <td>40.250000</td>\n",
       "      <td>47.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>50.000000</td>\n",
       "      <td>59.000000</td>\n",
       "      <td>59.000000</td>\n",
       "      <td>123.000000</td>\n",
       "      <td>58.000000</td>\n",
       "      <td>59.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             LatD      \"LatM\"      \"LatS\"      \"LonD\"      \"LonM\"      \"LonS\"\n",
       "count  128.000000  128.000000  128.000000  128.000000  128.000000  128.000000\n",
       "mean    38.820312   30.765625   27.492188   93.250000   27.742188   26.960938\n",
       "std      5.200596   16.426158   18.977814   15.466499   16.927937   18.727807\n",
       "min     26.000000    1.000000    0.000000   71.000000    0.000000    0.000000\n",
       "25%     35.000000   16.000000   11.000000   80.000000   14.000000   11.000000\n",
       "50%     39.000000   31.000000   24.000000   89.500000   26.500000   23.500000\n",
       "75%     42.250000   45.000000   47.000000  103.250000   40.250000   47.000000\n",
       "max     50.000000   59.000000   59.000000  123.000000   58.000000   59.000000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Perform describe and info on dataframe\n",
    "\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b5b8d6d3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 128 entries, 0 to 127\n",
      "Data columns (total 11 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   LatD       128 non-null    int64 \n",
      " 1    \"LatM\"    128 non-null    int64 \n",
      " 2    \"LatS\"    128 non-null    int64 \n",
      " 3    \"NS\"      128 non-null    object\n",
      " 4    \"LonD\"    128 non-null    int64 \n",
      " 5    \"LonM\"    128 non-null    int64 \n",
      " 6    \"LonS\"    128 non-null    int64 \n",
      " 7    \"EW\"      128 non-null    object\n",
      " 8    \"City\"    128 non-null    object\n",
      " 9    \"State\"   128 non-null    object\n",
      " 10  City_Code  5 non-null      object\n",
      "dtypes: int64(6), object(5)\n",
      "memory usage: 11.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# Perform describe and info on dataframe\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a0a300c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export and save this data as cities1.csv\n",
    "df.to_csv('cities1.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "08ba6379",
   "metadata": {},
   "outputs": [],
   "source": [
    "# read the file after export \n",
    "df1 = pd.read_csv('cities1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "adcde510",
   "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>Unnamed: 0</th>\n",
       "      <th>LatD</th>\n",
       "      <th>\"LatM\"</th>\n",
       "      <th>\"LatS\"</th>\n",
       "      <th>\"NS\"</th>\n",
       "      <th>\"LonD\"</th>\n",
       "      <th>\"LonM\"</th>\n",
       "      <th>\"LonS\"</th>\n",
       "      <th>\"EW\"</th>\n",
       "      <th>\"City\"</th>\n",
       "      <th>\"State\"</th>\n",
       "      <th>City_Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "      <td>5</td>\n",
       "      <td>59</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>80</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Youngstown\"</td>\n",
       "      <td>OH</td>\n",
       "      <td>YOT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "      <td>52</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>97</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Yankton\"</td>\n",
       "      <td>SD</td>\n",
       "      <td>YT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>46</td>\n",
       "      <td>35</td>\n",
       "      <td>59</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>120</td>\n",
       "      <td>30</td>\n",
       "      <td>36</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Yakima\"</td>\n",
       "      <td>WA</td>\n",
       "      <td>YK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>42</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>71</td>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Worcester\"</td>\n",
       "      <td>MA</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>43</td>\n",
       "      <td>37</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>89</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Wisconsin Dells\"</td>\n",
       "      <td>WI</td>\n",
       "      <td>WD</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>123</td>\n",
       "      <td>39</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>119</td>\n",
       "      <td>48</td>\n",
       "      <td>35</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Reno\"</td>\n",
       "      <td>NV</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>124</td>\n",
       "      <td>50</td>\n",
       "      <td>25</td>\n",
       "      <td>11</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>104</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Regina\"</td>\n",
       "      <td>SA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>125</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>122</td>\n",
       "      <td>14</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Red Bluff\"</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>126</td>\n",
       "      <td>40</td>\n",
       "      <td>19</td>\n",
       "      <td>48</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>75</td>\n",
       "      <td>55</td>\n",
       "      <td>48</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Reading\"</td>\n",
       "      <td>PA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>127</td>\n",
       "      <td>41</td>\n",
       "      <td>9</td>\n",
       "      <td>35</td>\n",
       "      <td>\"N\"</td>\n",
       "      <td>81</td>\n",
       "      <td>14</td>\n",
       "      <td>23</td>\n",
       "      <td>\"W\"</td>\n",
       "      <td>\"Ravenna\"</td>\n",
       "      <td>OH</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0  LatD   \"LatM\"   \"LatS\"  \"NS\"   \"LonD\"   \"LonM\"   \"LonS\"  \\\n",
       "0             0    41        5       59   \"N\"       80       39        0   \n",
       "1             1    42       52       48   \"N\"       97       23       23   \n",
       "2             2    46       35       59   \"N\"      120       30       36   \n",
       "3             3    42       16       12   \"N\"       71       48        0   \n",
       "4             4    43       37       48   \"N\"       89       46       11   \n",
       "..          ...   ...      ...      ...   ...      ...      ...      ...   \n",
       "123         123    39       31       12   \"N\"      119       48       35   \n",
       "124         124    50       25       11   \"N\"      104       39        0   \n",
       "125         125    40       10       48   \"N\"      122       14       23   \n",
       "126         126    40       19       48   \"N\"       75       55       48   \n",
       "127         127    41        9       35   \"N\"       81       14       23   \n",
       "\n",
       "     \"EW\"              \"City\"  \"State\" City_Code  \n",
       "0     \"W\"        \"Youngstown\"       OH       YOT  \n",
       "1     \"W\"           \"Yankton\"       SD        YT  \n",
       "2     \"W\"            \"Yakima\"       WA        YK  \n",
       "3     \"W\"         \"Worcester\"       MA        WT  \n",
       "4     \"W\"   \"Wisconsin Dells\"       WI        WD  \n",
       "..    ...                 ...      ...       ...  \n",
       "123   \"W\"              \"Reno\"       NV       NaN  \n",
       "124   \"W\"            \"Regina\"       SA       NaN  \n",
       "125   \"W\"         \"Red Bluff\"       CA       NaN  \n",
       "126   \"W\"           \"Reading\"       PA       NaN  \n",
       "127   \"W\"           \"Ravenna\"      OH        NaN  \n",
       "\n",
       "[128 rows x 12 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c79edb0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#export file to local machine folder\n",
    "df.to_csv(r'C:\\Users\\User\\Documents\\npower\\Course 7 - data analysis\\week 1\\C7W1 Activity\\cities2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0c4b6f80",
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Date</th>\n",
       "      <th>Model</th>\n",
       "      <th>Dealer ID</th>\n",
       "      <th>Quantity Sold</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Beaufort</td>\n",
       "      <td>1222</td>\n",
       "      <td>10</td>\n",
       "      <td>15000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Salish</td>\n",
       "      <td>1222</td>\n",
       "      <td>13</td>\n",
       "      <td>39650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Labrador</td>\n",
       "      <td>1222</td>\n",
       "      <td>16</td>\n",
       "      <td>12800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Champlain</td>\n",
       "      <td>1222</td>\n",
       "      <td>3</td>\n",
       "      <td>6900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Hudson</td>\n",
       "      <td>1222</td>\n",
       "      <td>22</td>\n",
       "      <td>11000.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1195</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Beaufort</td>\n",
       "      <td>1288</td>\n",
       "      <td>12</td>\n",
       "      <td>18000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1196</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Salish</td>\n",
       "      <td>1288</td>\n",
       "      <td>14</td>\n",
       "      <td>42700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1197</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Labrador</td>\n",
       "      <td>1288</td>\n",
       "      <td>18</td>\n",
       "      <td>14400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1198</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Champlain</td>\n",
       "      <td>1288</td>\n",
       "      <td>4</td>\n",
       "      <td>9200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1199</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Hudson</td>\n",
       "      <td>1288</td>\n",
       "      <td>25</td>\n",
       "      <td>12500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1200 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year     Month                Date      Model  Dealer ID  Quantity Sold  \\\n",
       "0     2018   January 2018-01-01 23:00:00   Beaufort       1222             10   \n",
       "1     2018   January 2018-01-01 23:00:00     Salish       1222             13   \n",
       "2     2018   January 2018-01-01 23:00:00   Labrador       1222             16   \n",
       "3     2018   January 2018-01-01 23:00:00  Champlain       1222              3   \n",
       "4     2018   January 2018-01-01 23:00:00     Hudson       1222             22   \n",
       "...    ...       ...                 ...        ...        ...            ...   \n",
       "1195  2019  December 2019-12-01 23:00:00   Beaufort       1288             12   \n",
       "1196  2019  December 2019-12-01 23:00:00     Salish       1288             14   \n",
       "1197  2019  December 2019-12-01 23:00:00   Labrador       1288             18   \n",
       "1198  2019  December 2019-12-01 23:00:00  Champlain       1288              4   \n",
       "1199  2019  December 2019-12-01 23:00:00     Hudson       1288             25   \n",
       "\n",
       "       Profit  \n",
       "0     15000.0  \n",
       "1     39650.0  \n",
       "2     12800.0  \n",
       "3      6900.0  \n",
       "4     11000.0  \n",
       "...       ...  \n",
       "1195  18000.0  \n",
       "1196  42700.0  \n",
       "1197  14400.0  \n",
       "1198   9200.0  \n",
       "1199  12500.0  \n",
       "\n",
       "[1200 rows x 7 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import xlsx in a dataframe\n",
    "\n",
    "# Perform describe and info on dataframe\n",
    "\n",
    "\n",
    "df_xlsx = pd.read_excel('CarSalesByModelStart.xlsx')\n",
    "df_xlsx\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e8f76895",
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Date</th>\n",
       "      <th>Model</th>\n",
       "      <th>Dealer ID</th>\n",
       "      <th>Quantity Sold</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Beaufort</td>\n",
       "      <td>1222</td>\n",
       "      <td>10</td>\n",
       "      <td>15000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Salish</td>\n",
       "      <td>1222</td>\n",
       "      <td>13</td>\n",
       "      <td>39650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Labrador</td>\n",
       "      <td>1222</td>\n",
       "      <td>16</td>\n",
       "      <td>12800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Champlain</td>\n",
       "      <td>1222</td>\n",
       "      <td>3</td>\n",
       "      <td>6900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>2018-01-01 23:00:00</td>\n",
       "      <td>Hudson</td>\n",
       "      <td>1222</td>\n",
       "      <td>22</td>\n",
       "      <td>11000.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1196</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Salish</td>\n",
       "      <td>1288</td>\n",
       "      <td>14</td>\n",
       "      <td>42700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1197</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Labrador</td>\n",
       "      <td>1288</td>\n",
       "      <td>18</td>\n",
       "      <td>14400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1198</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Champlain</td>\n",
       "      <td>1288</td>\n",
       "      <td>4</td>\n",
       "      <td>9200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1199</th>\n",
       "      <td>2019</td>\n",
       "      <td>December</td>\n",
       "      <td>2019-12-01 23:00:00</td>\n",
       "      <td>Hudson</td>\n",
       "      <td>1288</td>\n",
       "      <td>25</td>\n",
       "      <td>12500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018</td>\n",
       "      <td>January</td>\n",
       "      <td>23:00:00</td>\n",
       "      <td>Salish</td>\n",
       "      <td>1222</td>\n",
       "      <td>10</td>\n",
       "      <td>6900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1201 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year     Month                 Date      Model  Dealer ID  \\\n",
       "0     2018   January  2018-01-01 23:00:00   Beaufort       1222   \n",
       "1     2018   January  2018-01-01 23:00:00     Salish       1222   \n",
       "2     2018   January  2018-01-01 23:00:00   Labrador       1222   \n",
       "3     2018   January  2018-01-01 23:00:00  Champlain       1222   \n",
       "4     2018   January  2018-01-01 23:00:00     Hudson       1222   \n",
       "...    ...       ...                  ...        ...        ...   \n",
       "1196  2019  December  2019-12-01 23:00:00     Salish       1288   \n",
       "1197  2019  December  2019-12-01 23:00:00   Labrador       1288   \n",
       "1198  2019  December  2019-12-01 23:00:00  Champlain       1288   \n",
       "1199  2019  December  2019-12-01 23:00:00     Hudson       1288   \n",
       "0     2018   January             23:00:00     Salish       1222   \n",
       "\n",
       "      Quantity Sold   Profit  \n",
       "0                10  15000.0  \n",
       "1                13  39650.0  \n",
       "2                16  12800.0  \n",
       "3                 3   6900.0  \n",
       "4                22  11000.0  \n",
       "...             ...      ...  \n",
       "1196             14  42700.0  \n",
       "1197             18  14400.0  \n",
       "1198              4   9200.0  \n",
       "1199             25  12500.0  \n",
       "0                10   6900.0  \n",
       "\n",
       "[1201 rows x 7 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add a column or row  to the dataframe\n",
    "\n",
    "# adding row to dataframe \n",
    "row = {'Year': [2018] ,'Month': ['January'], 'Date': ['23:00:00'], 'Model': ['Salish'],'Dealer ID': [1222], 'Quantity Sold':[10], 'Profit':6900.0}\n",
    "\n",
    "rowdf = pd.DataFrame(row)\n",
    "rowdf\n",
    "df_xlsx1 = pd.concat([df_xlsx,rowdf])    #concat method create new dataframe does not change the orginal dataframe \n",
    "df_xlsx1\n",
    "# df_xlsx1.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a68735fb",
   "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>Year</th>\n",
       "      <th>Dealer ID</th>\n",
       "      <th>Quantity Sold</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1200.000000</td>\n",
       "      <td>1200.000000</td>\n",
       "      <td>1200.00000</td>\n",
       "      <td>1200.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2018.500000</td>\n",
       "      <td>1281.800000</td>\n",
       "      <td>17.99750</td>\n",
       "      <td>24974.055208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.500208</td>\n",
       "      <td>72.453672</td>\n",
       "      <td>9.97432</td>\n",
       "      <td>17370.750612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>1212.000000</td>\n",
       "      <td>2.00000</td>\n",
       "      <td>4600.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>1217.000000</td>\n",
       "      <td>11.00000</td>\n",
       "      <td>13500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2018.500000</td>\n",
       "      <td>1256.000000</td>\n",
       "      <td>18.00000</td>\n",
       "      <td>18500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2019.000000</td>\n",
       "      <td>1336.000000</td>\n",
       "      <td>24.00000</td>\n",
       "      <td>28922.812500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2019.000000</td>\n",
       "      <td>1402.000000</td>\n",
       "      <td>48.00000</td>\n",
       "      <td>83875.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Year    Dealer ID  Quantity Sold        Profit\n",
       "count  1200.000000  1200.000000     1200.00000   1200.000000\n",
       "mean   2018.500000  1281.800000       17.99750  24974.055208\n",
       "std       0.500208    72.453672        9.97432  17370.750612\n",
       "min    2018.000000  1212.000000        2.00000   4600.000000\n",
       "25%    2018.000000  1217.000000       11.00000  13500.000000\n",
       "50%    2018.500000  1256.000000       18.00000  18500.000000\n",
       "75%    2019.000000  1336.000000       24.00000  28922.812500\n",
       "max    2019.000000  1402.000000       48.00000  83875.000000"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Perform describe and info on  original dataframe\n",
    "df_xlsx.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e35d517a",
   "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>Year</th>\n",
       "      <th>Dealer ID</th>\n",
       "      <th>Quantity Sold</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1201.000000</td>\n",
       "      <td>1201.000000</td>\n",
       "      <td>1201.000000</td>\n",
       "      <td>1201.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2018.499584</td>\n",
       "      <td>1281.750208</td>\n",
       "      <td>17.990841</td>\n",
       "      <td>24959.006037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.500208</td>\n",
       "      <td>72.444030</td>\n",
       "      <td>9.972834</td>\n",
       "      <td>17371.342029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>1212.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>4600.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>1217.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>13500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>1224.000000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>18500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2019.000000</td>\n",
       "      <td>1336.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>28903.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2019.000000</td>\n",
       "      <td>1402.000000</td>\n",
       "      <td>48.000000</td>\n",
       "      <td>83875.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Year    Dealer ID  Quantity Sold        Profit\n",
       "count  1201.000000  1201.000000    1201.000000   1201.000000\n",
       "mean   2018.499584  1281.750208      17.990841  24959.006037\n",
       "std       0.500208    72.444030       9.972834  17371.342029\n",
       "min    2018.000000  1212.000000       2.000000   4600.000000\n",
       "25%    2018.000000  1217.000000      11.000000  13500.000000\n",
       "50%    2018.000000  1224.000000      18.000000  18500.000000\n",
       "75%    2019.000000  1336.000000      24.000000  28903.750000\n",
       "max    2019.000000  1402.000000      48.000000  83875.000000"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Perform describe and info on dataframe after adding row\n",
    "df_xlsx1.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9b183fd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "cd5941f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method DataFrame.info of       Year     Month                Date      Model  Dealer ID  Quantity Sold  \\\n",
       "0     2018   January 2018-01-01 23:00:00   Beaufort       1222             10   \n",
       "1     2018   January 2018-01-01 23:00:00     Salish       1222             13   \n",
       "2     2018   January 2018-01-01 23:00:00   Labrador       1222             16   \n",
       "3     2018   January 2018-01-01 23:00:00  Champlain       1222              3   \n",
       "4     2018   January 2018-01-01 23:00:00     Hudson       1222             22   \n",
       "...    ...       ...                 ...        ...        ...            ...   \n",
       "1195  2019  December 2019-12-01 23:00:00   Beaufort       1288             12   \n",
       "1196  2019  December 2019-12-01 23:00:00     Salish       1288             14   \n",
       "1197  2019  December 2019-12-01 23:00:00   Labrador       1288             18   \n",
       "1198  2019  December 2019-12-01 23:00:00  Champlain       1288              4   \n",
       "1199  2019  December 2019-12-01 23:00:00     Hudson       1288             25   \n",
       "\n",
       "       Profit  \n",
       "0     15000.0  \n",
       "1     39650.0  \n",
       "2     12800.0  \n",
       "3      6900.0  \n",
       "4     11000.0  \n",
       "...       ...  \n",
       "1195  18000.0  \n",
       "1196  42700.0  \n",
       "1197  14400.0  \n",
       "1198   9200.0  \n",
       "1199  12500.0  \n",
       "\n",
       "[1200 rows x 7 columns]>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Perform describe and info on dataframe\n",
    "df_xlsx.info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3326a7cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export and save this data as CarSalesByModelStart1.xlsx\n",
    "\n",
    "\n",
    "df_xlsx1.to_excel('CarSalesByModelStart1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "061564b3",
   "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>Duration</th>\n",
       "      <th>Pulse</th>\n",
       "      <th>Maxpulse</th>\n",
       "      <th>Calories</th>\n",
       "      <th>Blood Pressure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60</td>\n",
       "      <td>110</td>\n",
       "      <td>130</td>\n",
       "      <td>409.1</td>\n",
       "      <td>120/80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60</td>\n",
       "      <td>117</td>\n",
       "      <td>145</td>\n",
       "      <td>479.0</td>\n",
       "      <td>118/84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60</td>\n",
       "      <td>103</td>\n",
       "      <td>135</td>\n",
       "      <td>340.0</td>\n",
       "      <td>120/78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45</td>\n",
       "      <td>109</td>\n",
       "      <td>175</td>\n",
       "      <td>282.4</td>\n",
       "      <td>115/75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45</td>\n",
       "      <td>117</td>\n",
       "      <td>148</td>\n",
       "      <td>406.0</td>\n",
       "      <td>124/80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>60</td>\n",
       "      <td>105</td>\n",
       "      <td>140</td>\n",
       "      <td>290.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>60</td>\n",
       "      <td>110</td>\n",
       "      <td>145</td>\n",
       "      <td>300.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>60</td>\n",
       "      <td>115</td>\n",
       "      <td>145</td>\n",
       "      <td>310.2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>75</td>\n",
       "      <td>120</td>\n",
       "      <td>150</td>\n",
       "      <td>320.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>75</td>\n",
       "      <td>125</td>\n",
       "      <td>150</td>\n",
       "      <td>330.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>169 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Duration  Pulse  Maxpulse  Calories Blood Pressure\n",
       "0          60    110       130     409.1         120/80\n",
       "1          60    117       145     479.0         118/84\n",
       "2          60    103       135     340.0         120/78\n",
       "3          45    109       175     282.4         115/75\n",
       "4          45    117       148     406.0         124/80\n",
       "..        ...    ...       ...       ...            ...\n",
       "164        60    105       140     290.8            NaN\n",
       "165        60    110       145     300.4            NaN\n",
       "166        60    115       145     310.2            NaN\n",
       "167        75    120       150     320.4            NaN\n",
       "168        75    125       150     330.4            NaN\n",
       "\n",
       "[169 rows x 5 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Download Jay.json in C7W1 Activity folder\n",
    "# Import the Json in dataframe\n",
    "\n",
    "df_json = pd.read_json('Jay1.json')\n",
    "df_json\n",
    "\n",
    "\n",
    "# Add a column to the dataframe\n",
    "bp_data = ['120/80', '118/84','120/78', '115/75', '124/80']\n",
    "\n",
    "df_json['Blood Pressure']= pd.Series(bp_data)\n",
    "\n",
    "df_json\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3143c106",
   "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>Duration</th>\n",
       "      <th>Pulse</th>\n",
       "      <th>Maxpulse</th>\n",
       "      <th>Calories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>169.000000</td>\n",
       "      <td>169.000000</td>\n",
       "      <td>169.000000</td>\n",
       "      <td>164.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>63.846154</td>\n",
       "      <td>107.461538</td>\n",
       "      <td>134.047337</td>\n",
       "      <td>375.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>42.299949</td>\n",
       "      <td>14.510259</td>\n",
       "      <td>16.450434</td>\n",
       "      <td>266.377134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>15.000000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>50.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>45.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>124.000000</td>\n",
       "      <td>250.925000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>60.000000</td>\n",
       "      <td>105.000000</td>\n",
       "      <td>131.000000</td>\n",
       "      <td>318.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>60.000000</td>\n",
       "      <td>111.000000</td>\n",
       "      <td>141.000000</td>\n",
       "      <td>387.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>300.000000</td>\n",
       "      <td>159.000000</td>\n",
       "      <td>184.000000</td>\n",
       "      <td>1860.400000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Duration       Pulse    Maxpulse     Calories\n",
       "count  169.000000  169.000000  169.000000   164.000000\n",
       "mean    63.846154  107.461538  134.047337   375.800000\n",
       "std     42.299949   14.510259   16.450434   266.377134\n",
       "min     15.000000   80.000000  100.000000    50.300000\n",
       "25%     45.000000  100.000000  124.000000   250.925000\n",
       "50%     60.000000  105.000000  131.000000   318.600000\n",
       "75%     60.000000  111.000000  141.000000   387.600000\n",
       "max    300.000000  159.000000  184.000000  1860.400000"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Perform describe and info on dataframe\n",
    "\n",
    "df_json.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "525569a4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 169 entries, 0 to 168\n",
      "Data columns (total 5 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   Duration        169 non-null    int64  \n",
      " 1   Pulse           169 non-null    int64  \n",
      " 2   Maxpulse        169 non-null    int64  \n",
      " 3   Calories        164 non-null    float64\n",
      " 4   Blood Pressure  5 non-null      object \n",
      "dtypes: float64(1), int64(3), object(1)\n",
      "memory usage: 7.9+ KB\n"
     ]
    }
   ],
   "source": [
    "# Perform describe and info on dataframe\n",
    "\n",
    "df_json.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "68e4f896",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export and save this data as Jay1.json\n",
    "df_json.to_json('Jay2.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2c34a8c",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}