<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Data loading, storage, and file formats
Python has become a beloved language for text and file munging due to its simple syntax for interacting with files, intuitive data structures, and convenient features like tuple packing and unpacking.
pandas features a number of functions for reading tabular data as a DataFrame object. Table 6-1 has a summary of all of them, though read_csv and read_table are likely the ones you’ll use the most.
Step1:  Reading and Writing Data in Text Format
Type inference is one of the more important features of these functions; that means you don’t have to specify which columns are numeric, integer, boolean, or string. Handling dates and other custom types requires a bit more effort, though. Let’s start with a small comma-separated (CSV) text file
Step2:  A file will not always have a header row. Consider this file
Step3:  To read this in, you have a couple of options. You can allow pandas to assign default column names, or you can specify names yourself
Step4:  Suppose you wanted the message column to be the index of the returned DataFrame. You can either indicate you want the column at index 4 or named 'message' using the index_col argument
Step5:  In the event that you want to form a hierarchical index from multiple columns, just pass a list of column numbers or names
Step6:  In some cases, a table might not have a fixed delimiter, using whitespace or some other pattern to separate fields. In these cases, you can pass a regular expression as a delimiter for read_table. Consider a text file that looks like this
Step7:  Because there was one fewer column name than the number of data rows, read_table infers that the first column should be the DataFrame’s index in this special case.
The parser functions have many additional arguments to help you handle the wide variety of exception file formats that occur. For example, you can skip the first, third, and fourth rows of a file with skiprows
Step8:  Handling missing values is an important and frequently nuanced part of the file parsing process. Missing data is usually either not present (empty string) or marked by some sentinel value. By default, pandas uses a set of commonly occurring sentinels, such as NA, -1.#IND, and NULL
Step9:  The na_values option can take either a list or set of strings to consider missing values
Step10:  Different NA sentinels can be specified for each column in a dict
Step11:  Reading text files in pieces
When processing very large files or figuring out the right set of arguments to correctly process a large file, you may only want to read in a small piece of a file or iterate through smaller chunks of the file.
Step12:  If you want to only read out a small number of rows (avoiding reading the entire file), specify that with nrows
Step13:  To read out a file in pieces, specify a chunksize as a number of rows
Step14:  The TextParser object returned by read_csv allows you to iterate over the parts of the file according to the chunksize. For example, we can iterate over ex6.csv, aggregating the value counts in the 'key' column like so
Step15:  Writing data out to text format
Data can also be exported to delimited format. Let’s consider one of the CSV files read above
Step16:  Using DataFrame’s to_csv method, we can write the data out to a comma-separated file
Step17:  Other delimiters can be used, of course (writing to sys.stdout so it just prints the text result; make sure to import sys)
Step18:  Missing values appear as empty strings in the output. You might want to denote them by some other sentinel value
Step19:  With no other options specified, both the row and column labels are written. Both of these can be disabled
Step20:  You can also write only a subset of the columns, and in an order of your choosing
Step21:  Series also has a to_csv method
Step22:  With a bit of wrangling (no header, first column as index), you can read a CSV version of a Series with read_csv, but there is also a from_csv convenience method that makes it a bit simpler
Step23:  Manually working with delimited formats
Most forms of tabular data can be loaded from disk using functions like pan das.read_table. In some cases, however, some manual processing may be necessary. It’s not uncommon to receive a file with one or more malformed lines that trip up read_table. To illustrate the basic tools, consider a small CSV file
Step25:  JSON data
Step26:  XML and HTML, Web scraping
NB. The Yahoo! Finance API has changed and this example no longer works
Step27:  Parsing XML with lxml.objectify
Step28:  Binary data formats
Step29:  Using HDF5 format
Step30:  Interacting with HTML and Web APIs
Step32:  Interacting with databases
<ASSISTANT_TASK:>
Python Code:
from __future__ import division
from numpy.random import randn
import numpy as np
import os
import sys
import matplotlib.pyplot as plt
np.random.seed(12345)
plt.rc('figure', figsize=(10, 6))
from pandas import Series, DataFrame
import pandas as pd
np.set_printoptions(precision=4)
%pwd
Explanation: Data loading, storage, and file formats
Python has become a beloved language for text and file munging due to its simple syntax for interacting with files, intuitive data structures, and convenient features like tuple packing and unpacking.
pandas features a number of functions for reading tabular data as a DataFrame object. Table 6-1 has a summary of all of them, though read_csv and read_table are likely the ones you’ll use the most.
End of explanation
!cat ch06/ex1.csv
df = pd.read_csv('ch06/ex1.csv')
df
!cat ch06/test.csv
dfx = pd.read_csv('ch06/test.csv')
dfx
pd.read_table('ch06/ex1.csv', sep=',')
Explanation: Reading and Writing Data in Text Format
Type inference is one of the more important features of these functions; that means you don’t have to specify which columns are numeric, integer, boolean, or string. Handling dates and other custom types requires a bit more effort, though. Let’s start with a small comma-separated (CSV) text file:
End of explanation
!cat ch06/ex2.csv
Explanation: A file will not always have a header row. Consider this file:
End of explanation
pd.read_csv('ch06/ex2.csv', names=['a', 'b', 'c', 'd', 'message'])
pd.read_csv('ch06/ex2.csv', header=None)
Explanation: To read this in, you have a couple of options. You can allow pandas to assign default column names, or you can specify names yourself:
End of explanation
names = ['a', 'b', 'c', 'd', 'message']
pd.read_csv('ch06/ex2.csv', names=names, index_col='message')
Explanation: Suppose you wanted the message column to be the index of the returned DataFrame. You can either indicate you want the column at index 4 or named 'message' using the index_col argument:
End of explanation
!cat ch06/csv_mindex.csv
parsed = pd.read_csv('ch06/csv_mindex.csv', index_col=['key1', 'key2'])
parsed
list(open('ch06/ex3.txt'))
Explanation: In the event that you want to form a hierarchical index from multiple columns, just pass a list of column numbers or names:
End of explanation
result = pd.read_table('ch06/ex3.txt', sep='\s+')
result
Explanation: In some cases, a table might not have a fixed delimiter, using whitespace or some other pattern to separate fields. In these cases, you can pass a regular expression as a delimiter for read_table. Consider a text file that looks like this:
While you could do some munging by hand, in this case fields are separated by a variable amount of whitespace. This can be expressed by the regular expression \s+, so we have then:
End of explanation
!cat ch06/ex4.csv
pd.read_csv('ch06/ex4.csv', skiprows=[0, 2, 3])
Explanation: Because there was one fewer column name than the number of data rows, read_table infers that the first column should be the DataFrame’s index in this special case.
The parser functions have many additional arguments to help you handle the wide variety of exception file formats that occur. For example, you can skip the first, third, and fourth rows of a file with skiprows:
End of explanation
!cat ch06/ex5.csv
result = pd.read_csv('ch06/ex5.csv')
result
pd.isnull(result)
Explanation: Handling missing values is an important and frequently nuanced part of the file parsing process. Missing data is usually either not present (empty string) or marked by some sentinel value. By default, pandas uses a set of commonly occurring sentinels, such as NA, -1.#IND, and NULL:
End of explanation
result = pd.read_csv('ch06/ex5.csv', na_values=['NULL'])
result
Explanation: The na_values option can take either a list or set of strings to consider missing values:
End of explanation
sentinels = {'message': ['foo', 'NA'], 'something': ['two']}
pd.read_csv('ch06/ex5.csv', na_values=sentinels)
Explanation: Different NA sentinels can be specified for each column in a dict:
End of explanation
result = pd.read_csv('ch06/ex6.csv')
result
Explanation: Reading text files in pieces
When processing very large files or figuring out the right set of arguments to correctly process a large file, you may only want to read in a small piece of a file or iterate through smaller chunks of the file.
End of explanation
pd.read_csv('ch06/ex6.csv', nrows=5)
Explanation: If you want to only read out a small number of rows (avoiding reading the entire file), specify that with nrows:
End of explanation
chunker = pd.read_csv('ch06/ex6.csv', chunksize=1000)
chunker
Explanation: To read out a file in pieces, specify a chunksize as a number of rows:
End of explanation
chunker = pd.read_csv('ch06/ex6.csv', chunksize=1000)
tot = Series([])
for piece in chunker:
    tot = tot.add(piece['key'].value_counts(), fill_value=0)
tot = tot.sort_values(ascending=False)
tot[:10]
Explanation: The TextParser object returned by read_csv allows you to iterate over the parts of the file according to the chunksize. For example, we can iterate over ex6.csv, aggregating the value counts in the 'key' column like so:
End of explanation
data = pd.read_csv('ch06/ex5.csv')
data
Explanation: Writing data out to text format
Data can also be exported to delimited format. Let’s consider one of the CSV files read above:
End of explanation
data.to_csv('ch06/out.csv')
!cat ch06/out.csv
Explanation: Using DataFrame’s to_csv method, we can write the data out to a comma-separated file:
End of explanation
data.to_csv(sys.stdout, sep='|')
Explanation: Other delimiters can be used, of course (writing to sys.stdout so it just prints the text result; make sure to import sys):
End of explanation
data.to_csv(sys.stdout, na_rep='HELLO')
Explanation: Missing values appear as empty strings in the output. You might want to denote them by some other sentinel value:
End of explanation
data.to_csv(sys.stdout, index=False, header=False)
Explanation: With no other options specified, both the row and column labels are written. Both of these can be disabled:
End of explanation
data.to_csv(sys.stdout, index=False, columns=['a', 'b', 'c'])
Explanation: You can also write only a subset of the columns, and in an order of your choosing:
End of explanation
dates = pd.date_range('1/1/2000', periods=7)
ts = Series(np.arange(7), index=dates)
ts.to_csv('ch06/tseries.csv')
!cat ch06/tseries.csv
Explanation: Series also has a to_csv method:
End of explanation
Series.from_csv('ch06/tseries.csv', parse_dates=True)
Explanation: With a bit of wrangling (no header, first column as index), you can read a CSV version of a Series with read_csv, but there is also a from_csv convenience method that makes it a bit simpler:
End of explanation
!cat ch06/ex7.csv
import csv
f = open('ch06/ex7.csv')
reader = csv.reader(f)
for line in reader:
    print(line)
lines = list(csv.reader(open('ch06/ex7.csv')))
header, values = lines[0], lines[1:]
data_dict = {h: v for h, v in zip(header, zip(*values))}
data_dict
class my_dialect(csv.Dialect):
    lineterminator = '\n'
    delimiter = ';'
    quotechar = '"'
    quoting = csv.QUOTE_MINIMAL
with open('mydata.csv', 'w') as f:
    writer = csv.writer(f, dialect=my_dialect)
    writer.writerow(('one', 'two', 'three'))
    writer.writerow(('1', '2', '3'))
    writer.writerow(('4', '5', '6'))
    writer.writerow(('7', '8', '9'))
%cat mydata.csv
Explanation: Manually working with delimited formats
Most forms of tabular data can be loaded from disk using functions like pan das.read_table. In some cases, however, some manual processing may be necessary. It’s not uncommon to receive a file with one or more malformed lines that trip up read_table. To illustrate the basic tools, consider a small CSV file:
End of explanation
obj = 
{"name": "Wes",
 "places_lived": ["United States", "Spain", "Germany"],
 "pet": null,
 "siblings": [{"name": "Scott", "age": 25, "pet": "Zuko"},
              {"name": "Katie", "age": 33, "pet": "Cisco"}]
}
import json
result = json.loads(obj)
result
asjson = json.dumps(result)
siblings = DataFrame(result['siblings'], columns=['name', 'age'])
siblings
Explanation: JSON data
End of explanation
from lxml.html import parse
from urllib2 import urlopen
parsed = parse(urlopen('http://finance.yahoo.com/q/op?s=AAPL+Options'))
doc = parsed.getroot()
links = doc.findall('.//a')
links[15:20]
lnk = links[28]
lnk
lnk.get('href')
lnk.text_content()
urls = [lnk.get('href') for lnk in doc.findall('.//a')]
urls[-10:]
tables = doc.findall('.//table')
calls = tables[9]
puts = tables[13]
rows = calls.findall('.//tr')
def _unpack(row, kind='td'):
    elts = row.findall('.//%s' % kind)
    return [val.text_content() for val in elts]
_unpack(rows[0], kind='th')
_unpack(rows[1], kind='td')
from pandas.io.parsers import TextParser
def parse_options_data(table):
    rows = table.findall('.//tr')
    header = _unpack(rows[0], kind='th')
    data = [_unpack(r) for r in rows[1:]]
    return TextParser(data, names=header).get_chunk()
call_data = parse_options_data(calls)
put_data = parse_options_data(puts)
call_data[:10]
Explanation: XML and HTML, Web scraping
NB. The Yahoo! Finance API has changed and this example no longer works
End of explanation
%cd ch06/mta_perf/Performance_XML_Data
!head -21 Performance_MNR.xml
from lxml import objectify
path = 'Performance_MNR.xml'
parsed = objectify.parse(open(path))
root = parsed.getroot()
data = []
skip_fields = ['PARENT_SEQ', 'INDICATOR_SEQ',
               'DESIRED_CHANGE', 'DECIMAL_PLACES']
for elt in root.INDICATOR:
    el_data = {}
    for child in elt.getchildren():
        if child.tag in skip_fields:
            continue
        el_data[child.tag] = child.pyval
    data.append(el_data)
perf = DataFrame(data)
perf
root
root.get('href')
root.text
Explanation: Parsing XML with lxml.objectify
End of explanation
cd ../..
frame = pd.read_csv('ch06/ex1.csv')
frame
frame.to_pickle('ch06/frame_pickle')
pd.read_pickle('ch06/frame_pickle')
Explanation: Binary data formats
End of explanation
store = pd.HDFStore('mydata.h5')
store['obj1'] = frame
store['obj1_col'] = frame['a']
store
store['obj1']
store.close()
os.remove('mydata.h5')
Explanation: Using HDF5 format
End of explanation
import requests
url = 'https://api.github.com/repos/pydata/pandas/milestones/28/labels'
resp = requests.get(url)
resp
data[:5]
issue_labels = DataFrame(data)
issue_labels
Explanation: Interacting with HTML and Web APIs
End of explanation
import sqlite3
query = 
CREATE TABLE test
(a VARCHAR(20), b VARCHAR(20),
 c REAL,        d INTEGER
);
con = sqlite3.connect(':memory:')
con.execute(query)
con.commit()
data = [('Atlanta', 'Georgia', 1.25, 6),
        ('Tallahassee', 'Florida', 2.6, 3),
        ('Sacramento', 'California', 1.7, 5)]
stmt = "INSERT INTO test VALUES(?, ?, ?, ?)"
con.executemany(stmt, data)
con.commit()
cursor = con.execute('select * from test')
rows = cursor.fetchall()
rows
cursor.description
DataFrame(rows, columns=zip(*cursor.description)[0])
import pandas.io.sql as sql
sql.read_sql('select * from test', con)
Explanation: Interacting with databases
End of explanation
<END_TASK>