数据加载、存储与文件格式
1 读写文本格式的数据
pandas提供了一些用于将表格型数据读取为DataFrame对象的函数。
可以使用read_table,并指定分隔符
In [11]: pd.read_table('examples/ex1.csv', sep=',')
Out[11]:
a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
读入该文件的办法有两个。你可以让pandas为其分配默认的列名,也可以自己定义列名:
In [13]: pd.read_csv('examples/ex2.csv', header=None)
Out[13]:
0 1 2 3 4
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
In [14]: pd.read_csv('examples/ex2.csv', names=['a', 'b', 'c', 'd',
'message'])
Out[14]:
a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
如果希望将多个列做成一个层次化索引,只需传入由列编号或列名组成的列表即可:
In [17]: !cat examples/csv_mindex.csv
key1,key2,value1,value2
one,a,1,2
one,b,3,4
one,c,5,6
one,d,7,8
two,a,9,10
two,b,11,12
two,c,13,14
two,d,15,16
In [18]: parsed = pd.read_csv('examples/csv_mindex.csv',
....: index_col=['key1', 'key2'])
In [19]: parsed
Out[19]:
value1 value2
key1 key2
one a 1 2
b 3 4
c 5 6
d 7 8
two a 9 10
b 11 12
c 13 14
d 15 16
逐块读取文本文件
在看大文件之前,我们先设置pandas显示地更紧些:
In [33]: pd.options.display.max_rows = 10
如果只想读取几行(避免读取整个文件),通过nrows进行指定即可:
pd.read_csv('examples/ex6.csv', nrows=5)
JSON数据
许多Python库都可以读写JSON数据。我将使用json,因为它是构建于Python标准库中的。通过json.loads即可将JSON字符串转换成Python形式:
obj = """
{"name": "Wes",
"places_lived": ["United States", "Spain", "Germany"],
"pet": null,
"siblings": [{"name": "Scott", "age": 30, "pets": ["Zeus", "Zuko"]},
{"name": "Katie", "age": 38,
"pets": ["Sixes", "Stache", "Cisco"]}]
}
"""
In [62]: import json
In [63]: result = json.loads(obj)
In [64]: result
Out[64]:
{'name': 'Wes',
'pet': None,
'places_lived': ['United States', 'Spain', 'Germany'],
'siblings': [{'age': 30, 'name': 'Scott', 'pets': ['Zeus', 'Zuko']},
{'age': 38, 'name': 'Katie', 'pets': ['Sixes', 'Stache', 'Cisco']}]}
json.dumps则将Python对象转换成JSON格式:
In [65]: asjson = json.dumps(result)
XML和HTML:Web信息收集
pandas有一个内置的功能,read_html,它可以使用lxml和Beautiful Soup自动将HTML文件中的表格解析为DataFrame对象。
首先,你需要安装read_html用到的库:
conda install lxml
pip install beautifulsoup4 html5lib
In [73]: tables = pd.read_html('examples/fdic_failed_bank_list.html')
In [74]: len(tables)
Out[74]: 1
In [75]: failures = tables[0]
In [76]: failures.head()
Out[76]:
Bank Name City ST CERT \
0 Allied Bank Mulberry AR 91
1 The Woodbury Banking Company Woodbury GA 11297
2 First CornerStone Bank King of Prussia PA 35312
3 Trust Company Bank Memphis TN 9956
4 North Milwaukee State Bank Milwaukee WI 20364
Acquiring Institution Closing Date Updated Date
0 Today's Bank September 23, 2016 November 17, 2016
1 United Bank August 19, 2016 November 17, 2016
2 First-Citizens Bank & Trust Company May 6, 2016 September 6, 2016
3 The Bank of Fayette County April 29, 2016 September 6, 2016
4 First-Citizens Bank & Trust Company March 11, 2016 June 16, 2016
读取Microsoft Excel文件
要使用ExcelFile,通过传递xls或xlsx路径创建一个实例:
In [104]: xlsx = pd.ExcelFile('examples/ex1.xlsx')
如果要读取一个文件中的多个表单,创建ExcelFile会更快,但你也可以将文件名传递到pandas.read_excel:
In [106]: frame = pd.read_excel('examples/ex1.xlsx', 'Sheet1')
In [107]: frame
Out[107]:
a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo