02. Import_Export.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Import and Export Data\n",
    "\n",
    "[![Python Data Science](https://apmonitor.com/che263/uploads/Begin_Python/DataScience02.png)](https://www.youtube.com/watch?v=H05A_rftppU&list=PLLBUgWXdTBDg1Qgmwt4jKtVn9BWh5-zgy \"Python Data Science\")\n",
    "\n",
    "Python has functions for reading, creating, and deleting files. The high-level steps for many data science applications is to import data, analyze data, and export results.\n",
    "\n",
    "### File `open` for read or write\n",
    "\n",
    "A basic function for working with files is `open(filename,mode)`. The `filename` is a string that identifies the file to open and `mode` is how the file should be opened as `'r'` for read, `'a'` for append, `'w'` for write, and `'x'` for create (returns error if file exists). You can also specify if the file should be handled as a text `'t'` or binary `'b'` file. The defaults is `'rt'` to read a file in text mode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File stored in: C:\\Users\\johnh\\Desktop\\data_science\n",
      "This is a test file\n"
     ]
    }
   ],
   "source": [
    "# write a test file with a message\n",
    "f = open('02-file.txt','w')\n",
    "f.write('This is a test file')\n",
    "f.close()\n",
    "\n",
    "import os\n",
    "print('File stored in: ' + os.getcwd())\n",
    "\n",
    "# read and print the file contents\n",
    "f = open('02-file.txt')\n",
    "print(f.read())\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![idea](https://apmonitor.com/che263/uploads/Begin_Python/idea.png)\n",
    "\n",
    "### Write Data Files\n",
    "\n",
    "A common data file form is the Comma Separated Value (CSV) where entries are delimited (separated) by a comma. There is some data `m` that we would like to write to a CSV file with headers in `clist`. This example shows how to write the CSV file with several modules.\n",
    "\n",
    "```\n",
    "x,y,z\n",
    "1,2,3\n",
    "4,5,6\n",
    "7,8,9\n",
    "```\n",
    "\n",
    "After running each cell, open the file in your current run directory with either Excel or a text editor.\n",
    "\n",
    "#### `open` and `csv` module\n",
    "\n",
    "The `with` command automatically closes the file when the commands inside the block are completed. The `newline=''` is only required for Windows. The `writerow` function writes one row of the CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "clist = ['x','y','z']\n",
    "m = [[1,2,3],\\\n",
    "     [4,5,6],\\\n",
    "     [7,8,9]]\n",
    "\n",
    "import csv\n",
    "with open('02-data1.csv',mode='w',newline='') as f:\n",
    "    cw = csv.writer(f)\n",
    "    cw.writerow(clist)\n",
    "    for i in range(len(m)):\n",
    "        cw.writerow(m[i])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `numpy` writes CSV\n",
    "\n",
    "The numerical Python `numpy` package is used throughout this course. The `np.savetxt` function requires the file name, data `m`, the type of delimiter `,`, and header. If `comments=''` is omitted then the header has a `#` sign in front."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "np.savetxt('02-data2.csv',m,delimiter=',',comments='',header='x,y,z')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `pandas` writes CSV\n",
    "\n",
    "The module `pandas` requires that the data be in `DataFrame` form for writing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.DataFrame(m,columns=clist)\n",
    "df.to_csv('02-data3.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `pandas` writes XLSX and JSON\n",
    "\n",
    "`pandas` can also write other files such as json or Excel files. You may need to install openpyxl to write the Excel file. You can do this in a cell with `!pip install openpyxl` and include `--user` if you do not have administrative privilege. You may need to restart the IPython notebook kernel after pip installs the `openpyxl` package."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_json('02-data3.json',orient='table',index=False)\n",
    "df.to_excel('02-data3.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![expert](https://apmonitor.com/che263/uploads/Begin_Python/expert.png)\n",
    "\n",
    "### Write Activity\n",
    "\n",
    "Use `numpy` to create `51` equally spaced values for `x` between `0` and `100`. Calculate `y=x**2` and `z=x**3` that are derived from `x`. Store `x`, `y`, and `z` in a CSV file with headings in file `02-test.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![idea](https://apmonitor.com/che263/uploads/Begin_Python/idea.png)\n",
    "\n",
    "### Read Data Files\n",
    "\n",
    "Just like writing the CSV files, there are modules for reading data files.\n",
    "\n",
    "#### Use `numpy` to read CSV\n",
    "\n",
    "The `np.loadtxt` function reads the CSV data file with option `skiprows=1` to skip the header row. Numpy does not label the rows or columns and only stores the CSV values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = np.loadtxt('02-data1.csv',delimiter=',',skiprows=1)\n",
    "print(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Use `pandas` to read CSV\n",
    "\n",
    "The `pd.read_csv` function reads the CSV data file the the header row to label the colunns. The `data.head()` and `data.tail()` functions prints up to the first or last 5 values, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('02-data1.csv')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![expert](https://apmonitor.com/che263/uploads/Begin_Python/expert.png)\n",
    "\n",
    "### Read Activity\n",
    "\n",
    "Use `pandas` to read the `02-test.csv` file created above. Display the first 5 rows of the file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![idea](https://apmonitor.com/che263/uploads/Begin_Python/idea.png)\n",
    "\n",
    "### Delete Data Files\n",
    "\n",
    "It is also possible to delete files using the `os` (operating system) module.\n",
    "\n",
    "```python\n",
    "import os\n",
    "os.remove('02-data1.csv')\n",
    "```\n",
    "\n",
    "The `glob` module builds a list of files that start with `02-data` and end with `.csv`. It uses the wildcard character `*` to select any files that match the first and last parts.\n",
    "\n",
    "```python\n",
    "['02-data1.csv', '02-data2.csv', '02-data3.csv']\n",
    "```\n",
    "\n",
    "If the user's first letter of the answer is `y` then it deletes these files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "filelist = glob.glob('02-data*.csv')\n",
    "\n",
    "if filelist==[]:\n",
    "    print('No files to delete')\n",
    "    ans='no'\n",
    "else:\n",
    "    ans = input('Delete files '+str(filelist)+'? ')\n",
    "\n",
    "if ans[0].lower()=='y':\n",
    "    for f in filelist:\n",
    "        os.remove(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![expert](https://apmonitor.com/che263/uploads/Begin_Python/expert.png)\n",
    "\n",
    "### Delete Activity\n",
    "\n",
    "Delete the file `02-test.csv` with Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![expert](https://apmonitor.com/che263/uploads/Begin_Python/expert.png)\n",
    "\n",
    "### TCLab Activity\n",
    "\n",
    "Write data file `02-tclab.csv` with 5 columns that include time in seconds (`t`), heater levels (`Q1` and `Q2`), and temperatures (`lab.T1` and `lab.T2`). Include a data row every second for 20 seconds. The starting script only prints those values to the screen but they also need to be saved to a file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tclab\n",
    "import time\n",
    "n = 20\n",
    "Q1 = 30; Q2 = 70\n",
    "with tclab.TCLab() as lab:\n",
    "    lab.Q1(Q1); lab.Q2(Q2)\n",
    "    print('t Q1 Q2 T1    T2')\n",
    "    for t in range(n):\n",
    "        print(t,Q1,Q2,lab.T1,lab.T2)\n",
    "        time.sleep(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the `02-tclab.csv` file and print the first 5 rows. If you do not have a TCLab device, read the data file from the `url` with `data=pd.read_csv(url)`\n",
    "\n",
    "```python\n",
    "# read this file if you do not have a TCLab\n",
    "url = 'http://apmonitor.com/pdc/uploads/Main/tclab_data2.txt'\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}