creating-reading-and-writing.ipynb

{"cells": [{"cell_type": "markdown", "metadata": {}, "source": ["**[Pandas Micro-Course Home Page](https://www.kaggle.com/learn/pandas)**\n", "\n", "---\n"]}, {"cell_type": "markdown", "metadata": {}, "source": ["# Creating, reading, and writing reference\n", "\n", "This is the reference component to the \"Creating, reading, and writing\" section of the tutorial. \n", "\n", "The very first step in any data analytics project will probably reading the data out of a file somewhere, so it makes sense that that's the first thing we'd need to cover. In this section, we'll look at exercises on creating `pandas` `Series` and `DataFrame` objects, both by hand and by reading data from disc.\n", "\n", "The [IO Tools](http://pandas.pydata.org/pandas-docs/stable/io.html) section of the official `pandas` docs provides a comprehensive overview on this subject."]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["import pandas as pd"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Creating data\n", "\n", "There are two core objects in `pandas`: the **DataFrame** and the **Series**.\n", "\n", "A DataFrame is a table. It contains an array of individual *entries*, each of which has a certain *value*. Each entry corresponds with a row (or *record*) and a *column*.\n", "\n", "For example, consider the following simple `DataFrame`:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["pd.DataFrame({'Yes': [50, 21], 'No': [131, 2]})"]}, {"cell_type": "markdown", "metadata": {}, "source": ["In this example, the \"0, No\" entry has the value of 131. The \"0, Yes\" entry has a value of 50, and so on.\n", "\n", "`DataFrame` entries are not limited to integers. For instance, here's a `DataFrame` whose values are `str` strings:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["pd.DataFrame({'Bob': ['I liked it.', 'It was awful.'], 'Sue': ['Pretty good.', 'Bland.']})"]}, {"cell_type": "markdown", "metadata": {}, "source": ["We are using the `pd.DataFrame` constructor to generate these `DataFrame` objects. The syntax for declaring a new one is a dictionary whose keys are the column names (`Bob` and `Sue` in this example), and whose values are a list of entries. This is the standard way of constructing a new `DataFrame`, and the one you are likliest to encounter."]}, {"cell_type": "markdown", "metadata": {}, "source": ["The dictionary-list constructor assigns values to the *column labels*, but just uses an ascending count from 0 (0, 1, 2, 3, ...) for the *row labels*. Sometimes this is OK, but oftentimes we will want to assign these labels ourselves.\n", "\n", "The list of row labels used in a `DataFrame` is known as an **Index**. We can assign values to it by using an `index` parameter in our constructor:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["pd.DataFrame({'Bob': ['I liked it.', 'It was awful.'], \n", "              'Sue': ['Pretty good.', 'Bland.']},\n", "             index=['Product A', 'Product B'])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["A Series, by contrast, is a sequence of data values. If a `DataFrame` is a table, a `Series` is a list. And in fact you can create one with nothing more than a list:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["pd.Series([1, 2, 3, 4, 5])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["A `Series` is, in essence, a single column of a `DataFrame`. So you can assign column values to the `Series` the same way as before, using an `index` parameter. However, a `Series` do not have a column name, it only has one overall `name`:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["pd.Series([30, 35, 40], index=['2015 Sales', '2016 Sales', '2017 Sales'], name='Product A')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["`Series` and the `DataFrame` are intimately related. It's helpful to think of a `DataFrame` as actually being just a bunch of `Series` \"glue together\". We'll see more of this in the next section of this tutorial."]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Reading common file formats\n", "\n", "Being able to create a `DataFrame` and `Series` by hand is handy. But, most of the time, we won't actually be creating our own data by hand, we'll be working with data that already exists.\n", "\n", "Data can be stored in any of a number of different forms and formats. By far the most basic of these is the humble CSV file. When you open a CSV file you get something that looks like this:\n", "\n", "```csv\n", "Product A,Product B,Product C,\n", "30,21,9,\n", "35,34,1,\n", "41,11,11\n", "```\n", "\n", "So a CSV file is a table of values separated by commas. Hence the name: \"comma-seperated values\", or CSV.\n", "\n", "Let's now set aside our toy datasets and see what a real dataset looks like when we read it into a `DataFrame`. We'll use the `read_csv` function to read the data into a `DataFrame`. This goes thusly:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["wine_reviews = pd.read_csv(\"../input/wine-reviews/winemag-data-130k-v2.csv\")"]}, {"cell_type": "markdown", "metadata": {}, "source": ["We can use the `shape` attribute to check how large the resulting `DataFrame` is:"]}, {"cell_type": "code", "execution_count": null, "metadata": {"scrolled": false}, "outputs": [], "source": ["wine_reviews.shape"]}, {"cell_type": "markdown", "metadata": {}, "source": ["So our new `DataFrame` has 130,000 records split across 14 different columns. That's almost 2 million entries!\n", "\n", "We can examine the contents of the resultant `DataFrame` using the `head` command, which grabs the first five rows:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["wine_reviews.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The `pandas` `read_csv` function is well-endowed, with over 30 optional parameters you can specify. For example, you can see in this dataset that the `csv` file has an in-built index, which `pandas` did not pick up on automatically. To make `pandas` use that column for the index (instead of creating a new one from scratch), we may specify and use an `index_col`."]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["wine_reviews = pd.read_csv(\"../input/wine-reviews/winemag-data-130k-v2.csv\", index_col=0)\n", "wine_reviews.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Let's look at a few more datatypes you're likely to encounter.\n", "\n", "First up, the venerable Excel spreadsheet. An Excel file (`XLS` or `XLST`) organizes itself as a sequence of named sheets. Each sheet is basically a table. So to load the data into `pandas` we need one additional parameter: the name of the sheet of interest.\n", "\n", "So this:\n", "\n", "![](https://s3.amazonaws.com/nonwebstorage/excel.png)\n", "\n", "Becomes this:\n", "\n", "<!-- First up, the venerable SQL database. You can read a single table in a SQL database directly into a `pandas` `DataFrame` using the `read_sql` method. The only thing you need is -->"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["wic = pd.read_excel(\"../input/publicassistance/xls_files_all/WICAgencies2013ytd.xls\", \n", "                    sheet_name='Total Women')\n", "wic.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["As you can see in this example, Excel files are often not formatted as well as CSV files are. Spreadsheets allow (and encourage) creating notes and fields which are human-readable, but not machine-readable.\n", "\n", "So before we can use this particular dataset, we will need to clean it up a bit. We will see how to do so in the next section.\n", "\n", "For now, let's move on to another common data format: SQL files.\n", "\n", "SQL databases are where most of the data on the web ultimately gets stored. They can be used to store data on things as simple as recipes to things as complicated as \"almost everything on the Kaggle website\".\n", "\n", "Connecting to a SQL database requires a lot more thought than reading from an Excel file. For one, you need to create a **connector**, something that will handle siphoning data from the database.\n", "\n", "`pandas` won't do this for you automatically because there are many, many different types of SQL databases out there, each with its own connector. So for a SQLite database (the only kind supported on Kaggle), you would need to first do the following (using the `sqlite3` library that comes with Python):"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["import sqlite3\n", "conn = sqlite3.connect(\"../input/188-million-us-wildfires/FPA_FOD_20170508.sqlite\")"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The other thing you need to do is write a SQL statement. Internally, SQL databases all operate very differently. Externally, however, they all provide the same API, the \"Structured Query Language\" (or...SQL...for short).\n", "\n", "We (very briefly) need to use SQL to load data into \n", "\n", "For the purposes of analysis however we can usually just think of a SQL database as a set of tables with names, and SQL as a minor inconvenience in getting that data out of said tables.\n", "\n", "So, without further ado, here is all the SQL you have to know to get the data out of `SQLite` and into `pandas`:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["fires = pd.read_sql_query(\"SELECT * FROM fires\", conn)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Every SQL statement begins with `SELECT`. The asterisk (`*`) is a wildcard character, meaning \"everything\", and `FROM fires` tells the database we want only the data from the `fires` table specifically.\n", "\n", "And, out the other end, data:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["fires.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Writing common file formats"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Writing data to a file is usually easier than reading it out of one, because `pandas` handles the nuisance of conversions for you. \n", "\n", "We'll start with CSV files again. The opposite of `read_csv`, which reads our data, is `to_csv`, which writes it. With CSV files it's dead simple:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["wine_reviews.head().to_csv(\"wine_reviews.csv\")"]}, {"cell_type": "markdown", "metadata": {}, "source": ["To write an Excel file back you need `to_excel` and the `sheet_name` again:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["wic.to_excel('wic.xlsx', sheet_name='Total Women')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["And finally, to output to a SQL database, supply the name of the table in the database we want to throw the data into, and a connector:"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["conn = sqlite3.connect(\"fires.sqlite\")\n", "fires.head(10).to_sql(\"fires\", conn)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Painless!"]}, {"cell_type": "markdown", "metadata": {}, "source": ["---\n", "**[Pandas Micro-Course Home Page](https://www.kaggle.com/learn/pandas)**\n", "\n"]}], "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.6.5"}}, "nbformat": 4, "nbformat_minor": 2}