<jupyter_start><jupyter_text>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.<jupyter_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<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>!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=',')<jupyter_output><empty_output><jupyter_text>A file will not always have a header row. Consider this file:<jupyter_code>!cat ch06/ex2.csv<jupyter_output>1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo<jupyter_text>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:<jupyter_code>pd.read_csv('ch06/ex2.csv', names=['a', 'b', 'c', 'd', 'message'])
pd.read_csv('ch06/ex2.csv', header=None)<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>names = ['a', 'b', 'c', 'd', 'message']
pd.read_csv('ch06/ex2.csv', names=names, index_col='message')<jupyter_output><empty_output><jupyter_text>In the event that you want to form a hierarchical index from multiple columns, just pass a list of column numbers or names:<jupyter_code>!cat ch06/csv_mindex.csv
parsed = pd.read_csv('ch06/csv_mindex.csv', index_col=['key1', 'key2'])
parsed
list(open('ch06/ex3.txt'))<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>result = pd.read_table('ch06/ex3.txt', sep='\s+')
result<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>!cat ch06/ex4.csv
pd.read_csv('ch06/ex4.csv', skiprows=[0, 2, 3])<jupyter_output># hey!
a,b,c,d,message
# just wanted to make things more difficult for you
# who reads CSV files with computers, anyway?
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo<jupyter_text>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:<jupyter_code>!cat ch06/ex5.csv
result = pd.read_csv('ch06/ex5.csv')
result
pd.isnull(result)<jupyter_output>something,a,b,c,d,message
one,1,2,3,4,NA
two,5,6,,8,world
three,9,10,11,12,foo<jupyter_text>The na_values option can take either a list or set of strings to consider missing values:<jupyter_code>result = pd.read_csv('ch06/ex5.csv', na_values=['NULL'])
result<jupyter_output><empty_output><jupyter_text>Different NA sentinels can be specified for each column in a dict:<jupyter_code>sentinels = {'message': ['foo', 'NA'], 'something': ['two']}
pd.read_csv('ch06/ex5.csv', na_values=sentinels)<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>result = pd.read_csv('ch06/ex6.csv')
result<jupyter_output><empty_output><jupyter_text>If you want to only read out a small number of rows (avoiding reading the entire file), specify that with nrows:<jupyter_code>pd.read_csv('ch06/ex6.csv', nrows=5)<jupyter_output><empty_output><jupyter_text>To read out a file in pieces, specify a chunksize as a number of rows:<jupyter_code>chunker = pd.read_csv('ch06/ex6.csv', chunksize=1000)
chunker<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>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]<jupyter_output><empty_output><jupyter_text>Writing data out to text format Data can also be exported to delimited format. Let’s consider one of the CSV files read above:<jupyter_code>data = pd.read_csv('ch06/ex5.csv')
data<jupyter_output><empty_output><jupyter_text>Using DataFrame’s to_csv method, we can write the data out to a comma-separated file:<jupyter_code>data.to_csv('ch06/out.csv')
!cat ch06/out.csv<jupyter_output>,something,a,b,c,d,message
0,one,1,2,3.0,4,
1,two,5,6,,8,world
2,three,9,10,11.0,12,foo<jupyter_text>Other delimiters can be used, of course (writing to sys.stdout so it just prints the text result; make sure to import sys):<jupyter_code>data.to_csv(sys.stdout, sep='|')<jupyter_output>|something|a|b|c|d|message
0|one|1|2|3.0|4|
1|two|5|6||8|world
2|three|9|10|11.0|12|foo<jupyter_text>Missing values appear as empty strings in the output. You might want to denote them by some other sentinel value:<jupyter_code>data.to_csv(sys.stdout, na_rep='HELLO')<jupyter_output>,something,a,b,c,d,message
0,one,1,2,3.0,4,HELLO
1,two,5,6,HELLO,8,world
2,three,9,10,11.0,12,foo<jupyter_text>With no other options specified, both the row and column labels are written. Both of these can be disabled:<jupyter_code>data.to_csv(sys.stdout, index=False, header=False)<jupyter_output>one,1,2,3.0,4,
two,5,6,,8,world
three,9,10,11.0,12,foo<jupyter_text>You can also write only a subset of the columns, and in an order of your choosing:<jupyter_code>data.to_csv(sys.stdout, index=False, columns=['a', 'b', 'c'])<jupyter_output>a,b,c
1,2,3.0
5,6,
9,10,11.0<jupyter_text>Series also has a to_csv method:<jupyter_code>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<jupyter_output>2000-01-01,0
2000-01-02,1
2000-01-03,2
2000-01-04,3
2000-01-05,4
2000-01-06,5
2000-01-07,6<jupyter_text>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:<jupyter_code>Series.from_csv('ch06/tseries.csv', parse_dates=True)<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>!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<jupyter_output><empty_output><jupyter_text>JSON data<jupyter_code>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<jupyter_output><empty_output><jupyter_text>XML and HTML, Web scraping**NB. The Yahoo! Finance API has changed and this example no longer works**<jupyter_code>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]<jupyter_output><empty_output><jupyter_text>Parsing XML with lxml.objectify<jupyter_code>%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<jupyter_output><empty_output><jupyter_text>Binary data formats<jupyter_code>cd ../..
frame = pd.read_csv('ch06/ex1.csv')
frame
frame.to_pickle('ch06/frame_pickle')
pd.read_pickle('ch06/frame_pickle')<jupyter_output><empty_output><jupyter_text>Using HDF5 format<jupyter_code>store = pd.HDFStore('mydata.h5')
store['obj1'] = frame
store['obj1_col'] = frame['a']
store
store['obj1']
store.close()
os.remove('mydata.h5')<jupyter_output><empty_output><jupyter_text>Interacting with HTML and Web APIs<jupyter_code>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<jupyter_output><empty_output><jupyter_text>Interacting with databases<jupyter_code>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)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12874610405178613, 'fraction_numerical': 0.031007751937984496, 'mean_word_length': 5.967706013363029, 'pattern_counts': {'":': 10, '<': 116, '<?xml version=': 0, '>': 116, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 5}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '31', 'dir': 'jupyter-structured-clean-dedup', 'id': '421486', 'license': 'MIT', 'n_tokens_mistral': 4651, 'n_tokens_neox': 4426, 'n_words': 1238, 'path': 'lecture03.more.wrangling/ch06.ipynb', 'repo_name': 'xjdong/datascience', 'size': '12513'}