IO工具（文本，CSV，HDF5，…）
pandas的I/O API是一组read函数，比如pandas.read_csv()函数。这类函数可以返回pandas对象。相应的write函数是像DataFrame.to_csv()一样的对象方法。下面是一个方法列表，包含了这里面的所有readers函数和writer函数。
| Format Type | Data Description | Reader | Writer |
|---|---|---|---|
| text | CSV | read_csv | to_csv |
| text | JSON | read_json | to_json |
| text | HTML | read_html | to_html |
| text | Local clipboard | read_clipboard | to_clipboard |
| binary | MS Excel | read_excel | to_excel |
| binary | OpenDocument | read_excel | |
| binary | HDF5 Format | read_hdf | to_hdf |
| binary | Feather Format | read_feather | to_feather |
| binary | Parquet Format | read_parquet | to_parquet |
| binary | Msgpack | read_msgpack | to_msgpack |
| binary | Stata | read_stata | to_stata |
| binary | SAS | read_sas | |
| binary | Python Pickle Format | read_pickle | to_pickle |
| SQL | SQL | read_sql | to_sql |
| SQL | Google Big Query | read_gbq | to_gbq |
Here is an informal performance comparison for some of these IO methods.
注意
比如在使用 StringIO 类时, 请先确定python的版本信息。也就是说，是使用python2的from StringIO import StringIO还是python3的from io import StringIO。
CSV & 文本文件
读文本文件 (a.k.a. flat files)的主要方法 is read_csv(). 关于一些更高级的用法请参阅cookbook。
方法解析(Parsing options)
read_csv() 可接受以下常用参数:
基础
filepath_or_buffer : various
- 文件路径 (a
str,pathlib.Path, orpy._path.local.LocalPath), URL (including http, ftp, and S3 locations), 或者具有read()方法的任何对象 (such as an open file orStringIO).
sep : str, 默认 read_csv()分隔符为',', read_table()方法，分隔符为 \t
- 分隔符的使用. 如果分隔符为
None，虽然C不能解析，但python解析引擎可解析，这意味着python将被使用，通过内置的sniffer tool自动检测分隔符,csv.Sniffer. 除此之外,字符长度超过１并且不同于's+'的将被视为正则表达式，并且将强制使用python解析引擎。需要注意的是，正则表达式易于忽略引用数据 例如:'\\r\\t'.
delimiter : str, default None
- sep的替代参数.
delim_whitespace : boolean, default False
- 指定是否将空格 (e.g.
' 'or'\t')当作delimiter。 等价于设置sep='\s+'. 如果这个选项被设置为True,就不要给delimiter传参了.
version 0.18.1: 支持Python解析器.
列、索引、名称
header : int or list of ints, default 'infer'
当选择默认值或
header=0时，将首行设为列名。如果列名被传入明确值就令header=None。注意，当header=0时，即使列名被传参也会被覆盖。标题可以是指定列上的MultiIndex的行位置的整数列表，例如
[0,1,3]。在列名指定时，若某列未被指定，读取时将跳过该列 (例如 在下面的例子中第二列将被跳过).注意，如果skip_blank_lines=True，此参数将忽略空行和注释行, 因此 header=0 表示第一行数据而非文件的第一行.
names : array-like, default None
- 列名列表的使用. 如果文件不包含列名，那么应该设置
header=None。 列名列表中不允许有重复值.
index_col : int, str, sequence of int / str, or False, default None
DataFrame的行索引列表, 既可以是字符串名称也可以是列索引. 如果传入一个字符串序列或者整数序列,那么一定要使用多级索引（MultiIndex）.注意: 当
index_col=False，pandas不再使用首列作为索引。例如， 当你的文件是一个每行末尾都带有一个分割符的格式错误的文件时.
usecols : list-like or callable, default None
返回列名列表的子集. 如果该参数为列表形式, 那么所有元素应全为位置（即文档列中的整数索引）或者 全为相应列的列名字符串（这些列名字符串为names参数给出的或者文档的
header行内容）.例如，一个有效的列表型参数 usecols 将会是是[0, 1, 2]或者['foo', 'bar', 'baz'].元素顺序可忽略，因此
usecols=[0, 1]等价于[1, 0]。如果想实例化一个自定义列顺序的DataFrame，请使用pd.read_csv(data, usecols=['foo', 'bar'])[['foo', 'bar']]，这样列的顺序为['foo', 'bar']。如果设置pd.read_csv(data, usecols=['foo', 'bar'])[['bar', 'foo']]那么列的顺序为['bar', 'foo']。如果使用callable的方式, 可调用函数将根据列名计算， 返回可调用函数计算结果为True的名称:
In [1]: from io import StringIO, BytesIO
In [2]: data = ('col1,col2,col3\n'
...: 'a,b,1\n'
...: 'a,b,2\n'
...: 'c,d,3')
...:
In [3]: pd.read_csv(StringIO(data))
Out[3]:
col1 col2 col3
0 a b 1
1 a b 2
2 c d 3
In [4]: pd.read_csv(StringIO(data), usecols=lambda x: x.upper() in ['COL1', 'COL3'])
Out[4]:
col1 col3
0 a 1
1 a 2
2 c 3
使用此参数可以大大加快解析时间并降低内存使用率。
squeeze : boolean, default False
- 如果解析的数据仅包含一个列，那么结果将以
Series的形式返回.
prefix : str, default None
- 当没有header时，可通过该参数为数字列名添加前缀, e.g. ‘X’ for X0, X1, …
mangle_dupe_cols : boolean, default True
- 当列名有重复时，解析列名将变为 ‘X’, ‘X.1’…’X.N’而不是 ‘X’…’X’。 如果该参数为
False，那么当列名中有重复时，前列将会被后列覆盖。
常规解析配置
dtype : Type name or dict of column -> type, default None
指定某列或整体数据的数据类型. E.g.
{'a': np.float64, 'b': np.int32}(不支持engine='python').将str或object与合适的设置一起使用以保留和不解释dtype。New in version 0.20.0: 支持python解析器.
engine : {'c', 'python'}
- 解析引擎的使用。 尽管C引擎速度更快，但是目前python引擎功能更加完美。
converters : dict, default None
- Dict of functions for converting values in certain columns. Keys can either be integers or column labels.
true_values : list, default None
- Values to consider as
True.
false_values : list, default None
- Values to consider as
False.
skipinitialspace : boolean, default False
- Skip spaces after delimiter.
skiprows : list-like or integer, default None
Line numbers to skip (0-indexed) or number of lines to skip (int) at the start of the file.
If callable, the callable function will be evaluated against the row indices, returning True if the row should be skipped and False otherwise:
In [5]: data = ('col1,col2,col3\n'
...: 'a,b,1\n'
...: 'a,b,2\n'
...: 'c,d,3')
...:
In [6]: pd.read_csv(StringIO(data))
Out[6]:
col1 col2 col3
0 a b 1
1 a b 2
2 c d 3
In [7]: pd.read_csv(StringIO(data), skiprows=lambda x: x % 2 != 0)
Out[7]:
col1 col2 col3
0 a b 2
skipfooter : int, default 0
- Number of lines at bottom of file to skip (unsupported with engine=’c’).
nrows : int, default None
- Number of rows of file to read. Useful for reading pieces of large files.
low_memory : boolean, default True
- Internally process the file in chunks, resulting in lower memory use while parsing, but possibly mixed type inference. To ensure no mixed types either set
False, or specify the type with thedtypeparameter. Note that the entire file is read into a singleDataFrameregardless, use thechunksizeoriteratorparameter to return the data in chunks. (Only valid with C parser)
memory_map : boolean, default False
- If a filepath is provided for
filepath_or_buffer, map the file object directly onto memory and access the data directly from there. Using this option can improve performance because there is no longer any I/O overhead.
NA and missing data handling
na_values : scalar, str, list-like, or dict, default None
- Additional strings to recognize as NA/NaN. If dict passed, specific per-column NA values. See na values const below for a list of the values interpreted as NaN by default.
keep_default_na : boolean, default True
Whether or not to include the default NaN values when parsing the data. Depending on whether na_values is passed in, the behavior is as follows:
- If keep_default_na is
True, and na_values are specified, na_values is appended to the default NaN values used for parsing. - If keep_default_na is
True, and na_values are not specified, only the default NaN values are used for parsing. - If keep_default_na is
False, and na_values are specified, only the NaN values specified na_values are used for parsing. - If keep_default_na is
False, and na_values are not specified, no strings will be parsed as NaN.
Note that if na_filter is passed in as
False, the keep_default_na and na_values parameters will be ignored.- If keep_default_na is
na_filter : boolean, default True
- Detect missing value markers (empty strings and the value of na_values). In data without any NAs, passing
na_filter=Falsecan improve the performance of reading a large file.
verbose : boolean, default False
- Indicate number of NA values placed in non-numeric columns.
skip_blank_lines : boolean, default True
- If
True, skip over blank lines rather than interpreting as NaN values.
Datetime handling
parse_dates : boolean or list of ints or names or list of lists or dict, default False.
- If
True-> try parsing the index. - If
[1, 2, 3]-> try parsing columns 1, 2, 3 each as a separate date column. - If
[[1, 3]]-> combine columns 1 and 3 and parse as a single date column. - If
{'foo': [1, 3]}-> parse columns 1, 3 as date and call result ‘foo’. A fast-path exists for iso8601-formatted dates.
infer_datetime_format : boolean, default False
- If
Trueand parse_dates is enabled for a column, attempt to infer the datetime format to speed up the processing.
keep_date_col : boolean, default False
- If
Trueand parse_dates specifies combining multiple columns then keep the original columns.
date_parser : function, default None
- Function to use for converting a sequence of string columns to an array of datetime instances. The default uses
dateutil.parser.parserto do the conversion. pandas will try to call date_parser in three different ways, advancing to the next if an exception occurs: 1) Pass one or more arrays (as defined by parse_dates) as arguments; 2) concatenate (row-wise) the string values from the columns defined by parse_dates into a single array and pass that; and 3) call date_parser once for each row using one or more strings (corresponding to the columns defined by parse_dates) as arguments.
dayfirst : boolean, default False
- DD/MM format dates, international and European format.
cache_dates : boolean, default True
- If True, use a cache of unique, converted dates to apply the datetime conversion. May produce significant speed-up when parsing duplicate date strings, especially ones with timezone offsets.
New in version 0.25.0.
Iteration
iterator : boolean, default False
- Return TextFileReader object for iteration or getting chunks with
get_chunk().
chunksize : int, default None
- Return TextFileReader object for iteration. See iterating and chunking below.
Quoting, compression, and file format
compression : {'infer', 'gzip', 'bz2', 'zip', 'xz', None}, default 'infer'
- For on-the-fly decompression of on-disk data. If ‘infer’, then use gzip, bz2, zip, or xz if filepath_or_buffer is a string ending in ‘.gz’, ‘.bz2’, ‘.zip’, or ‘.xz’, respectively, and no decompression otherwise. If using ‘zip’, the ZIP file must contain only one data file to be read in. Set to
Nonefor no decompression.
New in version 0.18.1: support for ‘zip’ and ‘xz’ compression.
Changed in version 0.24.0: ‘infer’ option added and set to default.
thousands : str, default None
- Thousands separator.
decimal : str, default '.'
- Character to recognize as decimal point. E.g. use ',' for European data.
float_precision : string, default None
- Specifies which converter the C engine should use for floating-point values. The options are
Nonefor the ordinary converter,highfor the high-precision converter, andround_tripfor the round-trip converter.
lineterminator : str (length 1), default None
- Character to break file into lines. Only valid with C parser.
quotechar : str (length 1)
- The character used to denote the start and end of a quoted item. Quoted items can include the delimiter and it will be ignored.
quoting : int or csv.QUOTE_* instance, default 0
- Control field quoting behavior per
csv.QUOTE_*constants. Use one ofQUOTE_MINIMAL(0),QUOTE_ALL(1),QUOTE_NONNUMERIC(2) orQUOTE_NONE(3).
doublequote : boolean, default True
- When
quotecharis specified andquotingis notQUOTE_NONE, indicate whether or not to interpret two consecutivequotecharelements inside a field as a singlequotecharelement.
escapechar : str (length 1), default None
- One-character string used to escape delimiter when quoting is
QUOTE_NONE.
comment : str, default None
- Indicates remainder of line should not be parsed. If found at the beginning of a line, the line will be ignored altogether. This parameter must be a single character. Like empty lines (as long as
skip_blank_lines=True), fully commented lines are ignored by the parameter header but not by skiprows. For example, ifcomment='#', parsing ‘#empty a,b,c 1,2,3’ with header=0 will result in ‘a,b,c’ being treated as the header.
encoding : str, default None
- Encoding to use for UTF when reading/writing (e.g.
'utf-8'). List of Python standard encodings.
dialect : str or csv.Dialect instance, default None
- If provided, this parameter will override values (default or not) for the following parameters: delimiter, doublequote, escapechar, skipinitialspace, quotechar, and quoting. If it is necessary to override values, a ParserWarning will be issued. See csv.Dialect documentation for more details.
Error handling
error_bad_lines : boolean, default True
- Lines with too many fields (e.g. a csv line with too many commas) will by default cause an exception to be raised, and no
DataFramewill be returned. IfFalse, then these “bad lines” will dropped from theDataFramethat is returned. See bad lines below.
warn_bad_lines : boolean, default True
- If error_bad_lines is
False, and warn_bad_lines isTrue, a warning for each “bad line” will be output.
Specifying column data types
You can indicate the data type for the whole DataFrame or individual columns:
In [8]: data = ('a,b,c,d\n'
...: '1,2,3,4\n'
...: '5,6,7,8\n'
...: '9,10,11')
...:
In [9]: print(data)
a,b,c,d
1,2,3,4
5,6,7,8
9,10,11
In [10]: df = pd.read_csv(StringIO(data), dtype=object)
In [11]: df
Out[11]:
a b c d
0 1 2 3 4
1 5 6 7 8
2 9 10 11 NaN
In [12]: df['a'][0]
Out[12]: '1'
In [13]: df = pd.read_csv(StringIO(data),
....: dtype={'b': object, 'c': np.float64, 'd': 'Int64'})
....:
In [14]: df.dtypes
Out[14]:
a int64
b object
c float64
d Int64
dtype: object
Fortunately, pandas offers more than one way to ensure that your column(s) contain only one dtype. If you’re unfamiliar with these concepts, you can see here to learn more about dtypes, and here to learn more about object conversion in pandas.
For instance, you can use the converters argument of read_csv():
In [15]: data = ("col_1\n"
....: "1\n"
....: "2\n"
....: "'A'\n"
....: "4.22")
....:
In [16]: df = pd.read_csv(StringIO(data), converters={'col_1': str})
In [17]: df
Out[17]:
col_1
0 1
1 2
2 'A'
3 4.22
In [18]: df['col_1'].apply(type).value_counts()
Out[18]:
<class 'str'> 4
Name: col_1, dtype: int64
Or you can use the to_numeric() function to coerce the dtypes after reading in the data,
In [19]: df2 = pd.read_csv(StringIO(data))
In [20]: df2['col_1'] = pd.to_numeric(df2['col_1'], errors='coerce')
In [21]: df2
Out[21]:
col_1
0 1.00
1 2.00
2 NaN
3 4.22
In [22]: df2['col_1'].apply(type).value_counts()
Out[22]:
<class 'float'> 4
Name: col_1, dtype: int64
which will convert all valid parsing to floats, leaving the invalid parsing as NaN.
Ultimately, how you deal with reading in columns containing mixed dtypes depends on your specific needs. In the case above, if you wanted to NaN out the data anomalies, then to_numeric() is probably your best option. However, if you wanted for all the data to be coerced, no matter the type, then using the converters argument of read_csv() would certainly be worth trying.
New in version 0.20.0: support for the Python parser.
The dtype option is supported by the ‘python’ engine.
Note
In some cases, reading in abnormal data with columns containing mixed dtypes will result in an inconsistent dataset. If you rely on pandas to infer the dtypes of your columns, the parsing engine will go and infer the dtypes for different chunks of the data, rather than the whole dataset at once. Consequently, you can end up with column(s) with mixed dtypes. For example,
In [23]: col_1 = list(range(500000)) + ['a', 'b'] + list(range(500000))
In [24]: df = pd.DataFrame({'col_1': col_1})
In [25]: df.to_csv('foo.csv')
In [26]: mixed_df = pd.read_csv('foo.csv')
In [27]: mixed_df['col_1'].apply(type).value_counts()
Out[27]:
<class 'int'> 737858
<class 'str'> 262144
Name: col_1, dtype: int64
In [28]: mixed_df['col_1'].dtype
Out[28]: dtype('O')
will result with mixed_df containing an int dtype for certain chunks of the column, and str for others due to the mixed dtypes from the data that was read in. It is important to note that the overall column will be marked with a dtype of object, which is used for columns with mixed dtypes.
Specifying categorical dtype
New in version 0.19.0.
Categorical columns can be parsed directly by specifying dtype='category' or dtype=CategoricalDtype(categories, ordered).
In [29]: data = ('col1,col2,col3\n'
....: 'a,b,1\n'
....: 'a,b,2\n'
....: 'c,d,3')
....:
In [30]: pd.read_csv(StringIO(data))
Out[30]:
col1 col2 col3
0 a b 1
1 a b 2
2 c d 3
In [31]: pd.read_csv(StringIO(data)).dtypes
Out[31]:
col1 object
col2 object
col3 int64
dtype: object
In [32]: pd.read_csv(StringIO(data), dtype='category').dtypes
Out[32]:
col1 category
col2 category
col3 category
dtype: object
Individual columns can be parsed as a Categorical using a dict specification:
In [33]: pd.read_csv(StringIO(data), dtype={'col1': 'category'}).dtypes
Out[33]:
col1 category
col2 object
col3 int64
dtype: object
New in version 0.21.0.
Specifying dtype='category' will result in an unordered Categorical whose categories are the unique values observed in the data. For more control on the categories and order, create a CategoricalDtype ahead of time, and pass that for that column’s dtype.
In [34]: from pandas.api.types import CategoricalDtype
In [35]: dtype = CategoricalDtype(['d', 'c', 'b', 'a'], ordered=True)
In [36]: pd.read_csv(StringIO(data), dtype={'col1': dtype}).dtypes
Out[36]:
col1 category
col2 object
col3 int64
dtype: object
When using dtype=CategoricalDtype, “unexpected” values outside of dtype.categories are treated as missing values.
In [37]: dtype = CategoricalDtype(['a', 'b', 'd']) # No 'c'
In [38]: pd.read_csv(StringIO(data), dtype={'col1': dtype}).col1
Out[38]:
0 a
1 a
2 NaN
Name: col1, dtype: category
Categories (3, object): [a, b, d]
This matches the behavior of Categorical.set_categories().
Note
With dtype='category', the resulting categories will always be parsed as strings (object dtype). If the categories are numeric they can be converted using the to_numeric() function, or as appropriate, another converter such as to_datetime().
When dtype is a CategoricalDtype with homogeneous categories ( all numeric, all datetimes, etc.), the conversion is done automatically.
In [39]: df = pd.read_csv(StringIO(data), dtype='category')
In [40]: df.dtypes
Out[40]:
col1 category
col2 category
col3 category
dtype: object
In [41]: df['col3']
Out[41]:
0 1
1 2
2 3
Name: col3, dtype: category
Categories (3, object): [1, 2, 3]
In [42]: df['col3'].cat.categories = pd.to_numeric(df['col3'].cat.categories)
In [43]: df['col3']
Out[43]:
0 1
1 2
2 3
Name: col3, dtype: category
Categories (3, int64): [1, 2, 3]
Naming and using columns
Handling column names
A file may or may not have a header row. pandas assumes the first row should be used as the column names:
In [44]: data = ('a,b,c\n'
....: '1,2,3\n'
....: '4,5,6\n'
....: '7,8,9')
....:
In [45]: print(data)
a,b,c
1,2,3
4,5,6
7,8,9
In [46]: pd.read_csv(StringIO(data))
Out[46]:
a b c
0 1 2 3
1 4 5 6
2 7 8 9
By specifying the names argument in conjunction with header you can indicate other names to use and whether or not to throw away the header row (if any):
In [47]: print(data)
a,b,c
1,2,3
4,5,6
7,8,9
In [48]: pd.read_csv(StringIO(data), names=['foo', 'bar', 'baz'], header=0)
Out[48]:
foo bar baz
0 1 2 3
1 4 5 6
2 7 8 9
In [49]: pd.read_csv(StringIO(data), names=['foo', 'bar', 'baz'], header=None)
Out[49]:
foo bar baz
0 a b c
1 1 2 3
2 4 5 6
3 7 8 9
If the header is in a row other than the first, pass the row number to header. This will skip the preceding rows:
In [50]: data = ('skip this skip it\n'
....: 'a,b,c\n'
....: '1,2,3\n'
....: '4,5,6\n'
....: '7,8,9')
....:
In [51]: pd.read_csv(StringIO(data), header=1)
Out[51]:
a b c
0 1 2 3
1 4 5 6
2 7 8 9
Note
Default behavior is to infer the column names: if no names are passed the behavior is identical to header=0 and column names are inferred from the first non-blank line of the file, if column names are passed explicitly then the behavior is identical to header=None.
Duplicate names parsing
If the file or header contains duplicate names, pandas will by default distinguish between them so as to prevent overwriting data:
In [52]: data = ('a,b,a\n'
....: '0,1,2\n'
....: '3,4,5')
....:
In [53]: pd.read_csv(StringIO(data))
Out[53]:
a b a.1
0 0 1 2
1 3 4 5
There is no more duplicate data because mangle_dupe_cols=True by default, which modifies a series of duplicate columns ‘X’, …, ‘X’ to become ‘X’, ‘X.1’, …, ‘X.N’. If mangle_dupe_cols=False, duplicate data can arise:
In [2]: data = 'a,b,a\n0,1,2\n3,4,5'
In [3]: pd.read_csv(StringIO(data), mangle_dupe_cols=False)
Out[3]:
a b a
0 2 1 2
1 5 4 5
To prevent users from encountering this problem with duplicate data, a ValueError exception is raised if mangle_dupe_cols != True:
In [2]: data = 'a,b,a\n0,1,2\n3,4,5'
In [3]: pd.read_csv(StringIO(data), mangle_dupe_cols=False)
...
ValueError: Setting mangle_dupe_cols=False is not supported yet
Filtering columns (usecols)
The usecols argument allows you to select any subset of the columns in a file, either using the column names, position numbers or a callable:
New in version 0.20.0: support for callable usecols arguments
In [54]: data = 'a,b,c,d\n1,2,3,foo\n4,5,6,bar\n7,8,9,baz'
In [55]: pd.read_csv(StringIO(data))
Out[55]:
a b c d
0 1 2 3 foo
1 4 5 6 bar
2 7 8 9 baz
In [56]: pd.read_csv(StringIO(data), usecols=['b', 'd'])
Out[56]:
b d
0 2 foo
1 5 bar
2 8 baz
In [57]: pd.read_csv(StringIO(data), usecols=[0, 2, 3])
Out[57]:
a c d
0 1 3 foo
1 4 6 bar
2 7 9 baz
In [58]: pd.read_csv(StringIO(data), usecols=lambda x: x.upper() in ['A', 'C'])
Out[58]:
a c
0 1 3
1 4 6
2 7 9
The usecols argument can also be used to specify which columns not to use in the final result:
In [59]: pd.read_csv(StringIO(data), usecols=lambda x: x not in ['a', 'c'])
Out[59]:
b d
0 2 foo
1 5 bar
2 8 baz
In this case, the callable is specifying that we exclude the “a” and “c” columns from the output.
Comments and empty lines
Ignoring line comments and empty lines
If the comment parameter is specified, then completely commented lines will be ignored. By default, completely blank lines will be ignored as well.
In [60]: data = ('\n'
....: 'a,b,c\n'
....: ' \n'
....: '# commented line\n'
....: '1,2,3\n'
....: '\n'
....: '4,5,6')
....:
In [61]: print(data)
a,b,c
# commented line
1,2,3
4,5,6
In [62]: pd.read_csv(StringIO(data), comment='#')
Out[62]:
a b c
0 1 2 3
1 4 5 6
If skip_blank_lines=False, then read_csv will not ignore blank lines:
In [63]: data = ('a,b,c\n'
....: '\n'
....: '1,2,3\n'
....: '\n'
....: '\n'
....: '4,5,6')
....:
In [64]: pd.read_csv(StringIO(data), skip_blank_lines=False)
Out[64]:
a b c
0 NaN NaN NaN
1 1.0 2.0 3.0
2 NaN NaN NaN
3 NaN NaN NaN
4 4.0 5.0 6.0
Warning
The presence of ignored lines might create ambiguities involving line numbers; the parameter header uses row numbers (ignoring commented/empty lines), while skiprows uses line numbers (including commented/empty lines):
In [65]: data = ('#comment\n'
....: 'a,b,c\n'
....: 'A,B,C\n'
....: '1,2,3')
....:
In [66]: pd.read_csv(StringIO(data), comment='#', header=1)
Out[66]:
A B C
0 1 2 3
In [67]: data = ('A,B,C\n'
....: '#comment\n'
....: 'a,b,c\n'
....: '1,2,3')
....:
In [68]: pd.read_csv(StringIO(data), comment='#', skiprows=2)
Out[68]:
a b c
0 1 2 3
If both header and skiprows are specified, header will be relative to the end of skiprows. For example:
In [69]: data = ('# empty\n'
....: '# second empty line\n'
....: '# third emptyline\n'
....: 'X,Y,Z\n'
....: '1,2,3\n'
....: 'A,B,C\n'
....: '1,2.,4.\n'
....: '5.,NaN,10.0\n')
....:
In [70]: print(data)
# empty
# second empty line
# third emptyline
X,Y,Z
1,2,3
A,B,C
1,2.,4.
5.,NaN,10.0
In [71]: pd.read_csv(StringIO(data), comment='#', skiprows=4, header=1)
Out[71]:
A B C
0 1.0 2.0 4.0
1 5.0 NaN 10.0
Comments
Sometimes comments or meta data may be included in a file:
In [72]: print(open('tmp.csv').read())
ID,level,category
Patient1,123000,x # really unpleasant
Patient2,23000,y # wouldn't take his medicine
Patient3,1234018,z # awesome
By default, the parser includes the comments in the output:
In [73]: df = pd.read_csv('tmp.csv')
In [74]: df
Out[74]:
ID level category
0 Patient1 123000 x # really unpleasant
1 Patient2 23000 y # wouldn't take his medicine
2 Patient3 1234018 z # awesome
We can suppress the comments using the comment keyword:
In [75]: df = pd.read_csv('tmp.csv', comment='#')
In [76]: df
Out[76]:
ID level category
0 Patient1 123000 x
1 Patient2 23000 y
2 Patient3 1234018 z
Dealing with Unicode data
The encoding argument should be used for encoded unicode data, which will result in byte strings being decoded to unicode in the result:
In [77]: data = (b'word,length\n'
....: b'Tr\xc3\xa4umen,7\n'
....: b'Gr\xc3\xbc\xc3\x9fe,5')
....:
In [78]: data = data.decode('utf8').encode('latin-1')
In [79]: df = pd.read_csv(BytesIO(data), encoding='latin-1')
In [80]: df
Out[80]:
word length
0 Träumen 7
1 Grüße 5
In [81]: df['word'][1]
Out[81]: 'Grüße'
Some formats which encode all characters as multiple bytes, like UTF-16, won’t parse correctly at all without specifying the encoding. Full list of Python standard encodings.
Index columns and trailing delimiters
If a file has one more column of data than the number of column names, the first column will be used as the DataFrame’s row names:
In [82]: data = ('a,b,c\n'
....: '4,apple,bat,5.7\n'
....: '8,orange,cow,10')
....:
In [83]: pd.read_csv(StringIO(data))
Out[83]:
a b c
4 apple bat 5.7
8 orange cow 10.0
In [84]: data = ('index,a,b,c\n'
....: '4,apple,bat,5.7\n'
....: '8,orange,cow,10')
....:
In [85]: pd.read_csv(StringIO(data), index_col=0)
Out[85]:
a b c
index
4 apple bat 5.7
8 orange cow 10.0
Ordinarily, you can achieve this behavior using the index_col option.
There are some exception cases when a file has been prepared with delimiters at the end of each data line, confusing the parser. To explicitly disable the index column inference and discard the last column, pass index_col=False:
In [86]: data = ('a,b,c\n'
....: '4,apple,bat,\n'
....: '8,orange,cow,')
....:
In [87]: print(data)
a,b,c
4,apple,bat,
8,orange,cow,
In [88]: pd.read_csv(StringIO(data))
Out[88]:
a b c
4 apple bat NaN
8 orange cow NaN
In [89]: pd.read_csv(StringIO(data), index_col=False)
Out[89]:
a b c
0 4 apple bat
1 8 orange cow
If a subset of data is being parsed using the usecols option, the index_col specification is based on that subset, not the original data.
In [90]: data = ('a,b,c\n'
....: '4,apple,bat,\n'
....: '8,orange,cow,')
....:
In [91]: print(data)
a,b,c
4,apple,bat,
8,orange,cow,
In [92]: pd.read_csv(StringIO(data), usecols=['b', 'c'])
Out[92]:
b c
4 bat NaN
8 cow NaN
In [93]: pd.read_csv(StringIO(data), usecols=['b', 'c'], index_col=0)
Out[93]:
b c
4 bat NaN
8 cow NaN
Date Handling
Specifying date columns
To better facilitate working with datetime data, read_csv() uses the keyword arguments parse_dates and date_parser to allow users to specify a variety of columns and date/time formats to turn the input text data into datetime objects.
The simplest case is to just pass in parse_dates=True:
# Use a column as an index, and parse it as dates.
In [94]: df = pd.read_csv('foo.csv', index_col=0, parse_dates=True)
In [95]: df
Out[95]:
A B C
date
2009-01-01 a 1 2
2009-01-02 b 3 4
2009-01-03 c 4 5
# These are Python datetime objects
In [96]: df.index
Out[96]: DatetimeIndex(['2009-01-01', '2009-01-02', '2009-01-03'], dtype='datetime64[ns]', name='date', freq=None)
It is often the case that we may want to store date and time data separately, or store various date fields separately. the parse_dates keyword can be used to specify a combination of columns to parse the dates and/or times from.
You can specify a list of column lists to parse_dates, the resulting date columns will be prepended to the output (so as to not affect the existing column order) and the new column names will be the concatenation of the component column names:
In [97]: print(open('tmp.csv').read())
KORD,19990127, 19:00:00, 18:56:00, 0.8100
KORD,19990127, 20:00:00, 19:56:00, 0.0100
KORD,19990127, 21:00:00, 20:56:00, -0.5900
KORD,19990127, 21:00:00, 21:18:00, -0.9900
KORD,19990127, 22:00:00, 21:56:00, -0.5900
KORD,19990127, 23:00:00, 22:56:00, -0.5900
In [98]: df = pd.read_csv('tmp.csv', header=None, parse_dates=[[1, 2], [1, 3]])
In [99]: df
Out[99]:
1_2 1_3 0 4
0 1999-01-27 19:00:00 1999-01-27 18:56:00 KORD 0.81
1 1999-01-27 20:00:00 1999-01-27 19:56:00 KORD 0.01
2 1999-01-27 21:00:00 1999-01-27 20:56:00 KORD -0.59
3 1999-01-27 21:00:00 1999-01-27 21:18:00 KORD -0.99
4 1999-01-27 22:00:00 1999-01-27 21:56:00 KORD -0.59
5 1999-01-27 23:00:00 1999-01-27 22:56:00 KORD -0.59
By default the parser removes the component date columns, but you can choose to retain them via the keep_date_col keyword:
In [100]: df = pd.read_csv('tmp.csv', header=None, parse_dates=[[1, 2], [1, 3]],
.....: keep_date_col=True)
.....:
In [101]: df
Out[101]:
1_2 1_3 0 1 2 3 4
0 1999-01-27 19:00:00 1999-01-27 18:56:00 KORD 19990127 19:00:00 18:56:00 0.81
1 1999-01-27 20:00:00 1999-01-27 19:56:00 KORD 19990127 20:00:00 19:56:00 0.01
2 1999-01-27 21:00:00 1999-01-27 20:56:00 KORD 19990127 21:00:00 20:56:00 -0.59
3 1999-01-27 21:00:00 1999-01-27 21:18:00 KORD 19990127 21:00:00 21:18:00 -0.99
4 1999-01-27 22:00:00 1999-01-27 21:56:00 KORD 19990127 22:00:00 21:56:00 -0.59
5 1999-01-27 23:00:00 1999-01-27 22:56:00 KORD 19990127 23:00:00 22:56:00 -0.59
Note that if you wish to combine multiple columns into a single date column, a nested list must be used. In other words, parse_dates=[1, 2] indicates that the second and third columns should each be parsed as separate date columns while parse_dates=[[1, 2]] means the two columns should be parsed into a single column.
You can also use a dict to specify custom name columns:
In [102]: date_spec = {'nominal': [1, 2], 'actual': [1, 3]}
In [103]: df = pd.read_csv('tmp.csv', header=None, parse_dates=date_spec)
In [104]: df
Out[104]:
nominal actual 0 4
0 1999-01-27 19:00:00 1999-01-27 18:56:00 KORD 0.81
1 1999-01-27 20:00:00 1999-01-27 19:56:00 KORD 0.01
2 1999-01-27 21:00:00 1999-01-27 20:56:00 KORD -0.59
3 1999-01-27 21:00:00 1999-01-27 21:18:00 KORD -0.99
4 1999-01-27 22:00:00 1999-01-27 21:56:00 KORD -0.59
5 1999-01-27 23:00:00 1999-01-27 22:56:00 KORD -0.59
It is important to remember that if multiple text columns are to be parsed into a single date column, then a new column is prepended to the data. The index_col specification is based off of this new set of columns rather than the original data columns:
In [105]: date_spec = {'nominal': [1, 2], 'actual': [1, 3]}
In [106]: df = pd.read_csv('tmp.csv', header=None, parse_dates=date_spec,
.....: index_col=0) # index is the nominal column
.....:
In [107]: df
Out[107]:
actual 0 4
nominal
1999-01-27 19:00:00 1999-01-27 18:56:00 KORD 0.81
1999-01-27 20:00:00 1999-01-27 19:56:00 KORD 0.01
1999-01-27 21:00:00 1999-01-27 20:56:00 KORD -0.59
1999-01-27 21:00:00 1999-01-27 21:18:00 KORD -0.99
1999-01-27 22:00:00 1999-01-27 21:56:00 KORD -0.59
1999-01-27 23:00:00 1999-01-27 22:56:00 KORD -0.59
Note
If a column or index contains an unparsable date, the entire column or index will be returned unaltered as an object data type. For non-standard datetime parsing, use to_datetime() after pd.read_csv.
Note
read_csv has a fast_path for parsing datetime strings in iso8601 format, e.g “2000-01-01T00:01:02+00:00” and similar variations. If you can arrange for your data to store datetimes in this format, load times will be significantly faster, ~20x has been observed.
Note
When passing a dict as the parse_dates argument, the order of the columns prepended is not guaranteed, because dict objects do not impose an ordering on their keys. On Python 2.7+ you may use collections.OrderedDict instead of a regular dict if this matters to you. Because of this, when using a dict for ‘parse_dates’ in conjunction with the index_col argument, it’s best to specify index_col as a column label rather then as an index on the resulting frame.
Date parsing functions
Finally, the parser allows you to specify a custom date_parser function to take full advantage of the flexibility of the date parsing API:
In [108]: df = pd.read_csv('tmp.csv', header=None, parse_dates=date_spec,
.....: date_parser=pd.io.date_converters.parse_date_time)
.....:
In [109]: df
Out[109]:
nominal actual 0 4
0 1999-01-27 19:00:00 1999-01-27 18:56:00 KORD 0.81
1 1999-01-27 20:00:00 1999-01-27 19:56:00 KORD 0.01
2 1999-01-27 21:00:00 1999-01-27 20:56:00 KORD -0.59
3 1999-01-27 21:00:00 1999-01-27 21:18:00 KORD -0.99
4 1999-01-27 22:00:00 1999-01-27 21:56:00 KORD -0.59
5 1999-01-27 23:00:00 1999-01-27 22:56:00 KORD -0.59
Pandas will try to call the date_parser function in three different ways. If an exception is raised, the next one is tried:
date_parseris first called with one or more arrays as arguments, as defined using parse_dates (e.g.,date_parser(['2013', '2013'], ['1', '2'])).- If #1 fails,
date_parseris called with all the columns concatenated row-wise into a single array (e.g.,date_parser(['2013 1', '2013 2'])). - If #2 fails,
date_parseris called once for every row with one or more string arguments from the columns indicated with parse_dates (e.g.,date_parser('2013', '1')for the first row,date_parser('2013', '2')for the second, etc.).
Note that performance-wise, you should try these methods of parsing dates in order:
- Try to infer the format using
infer_datetime_format=True(see section below). - If you know the format, use
pd.to_datetime():date_parser=lambda x: pd.to_datetime(x, format=...). - If you have a really non-standard format, use a custom
date_parserfunction. For optimal performance, this should be vectorized, i.e., it should accept arrays as arguments.
You can explore the date parsing functionality in date_converters.py and add your own. We would love to turn this module into a community supported set of date/time parsers. To get you started, date_converters.py contains functions to parse dual date and time columns, year/month/day columns, and year/month/day/hour/minute/second columns. It also contains a generic_parser function so you can curry it with a function that deals with a single date rather than the entire array.
Parsing a CSV with mixed timezones
Pandas cannot natively represent a column or index with mixed timezones. If your CSV file contains columns with a mixture of timezones, the default result will be an object-dtype column with strings, even with parse_dates.
In [110]: content = """\
.....: a
.....: 2000-01-01T00:00:00+05:00
.....: 2000-01-01T00:00:00+06:00"""
.....:
In [111]: df = pd.read_csv(StringIO(content), parse_dates=['a'])
In [112]: df['a']
Out[112]:
0 2000-01-01 00:00:00+05:00
1 2000-01-01 00:00:00+06:00
Name: a, dtype: object
To parse the mixed-timezone values as a datetime column, pass a partially-applied to_datetime() with utc=True as the date_parser.
In [113]: df = pd.read_csv(StringIO(content), parse_dates=['a'],
.....: date_parser=lambda col: pd.to_datetime(col, utc=True))
.....:
In [114]: df['a']
Out[114]:
0 1999-12-31 19:00:00+00:00
1 1999-12-31 18:00:00+00:00
Name: a, dtype: datetime64[ns, UTC]
Inferring datetime format
If you have parse_dates enabled for some or all of your columns, and your datetime strings are all formatted the same way, you may get a large speed up by setting infer_datetime_format=True. If set, pandas will attempt to guess the format of your datetime strings, and then use a faster means of parsing the strings. 5-10x parsing speeds have been observed. pandas will fallback to the usual parsing if either the format cannot be guessed or the format that was guessed cannot properly parse the entire column of strings. So in general, infer_datetime_format should not have any negative consequences if enabled.
Here are some examples of datetime strings that can be guessed (All representing December 30th, 2011 at 00:00:00):
- “20111230”
- “2011/12/30”
- “20111230 00:00:00”
- “12/30/2011 00:00:00”
- “30/Dec/2011 00:00:00”
- “30/December/2011 00:00:00”
Note that infer_datetime_format is sensitive to dayfirst. With dayfirst=True, it will guess “01/12/2011” to be December 1st. With dayfirst=False (default) it will guess “01/12/2011” to be January 12th.
# Try to infer the format for the index column
In [115]: df = pd.read_csv('foo.csv', index_col=0, parse_dates=True,
.....: infer_datetime_format=True)
.....:
In [116]: df
Out[116]:
A B C
date
2009-01-01 a 1 2
2009-01-02 b 3 4
2009-01-03 c 4 5
International date formats
While US date formats tend to be MM/DD/YYYY, many international formats use DD/MM/YYYY instead. For convenience, a dayfirst keyword is provided:
In [117]: print(open('tmp.csv').read())
date,value,cat
1/6/2000,5,a
2/6/2000,10,b
3/6/2000,15,c
In [118]: pd.read_csv('tmp.csv', parse_dates=[0])
Out[118]:
date value cat
0 2000-01-06 5 a
1 2000-02-06 10 b
2 2000-03-06 15 c
In [119]: pd.read_csv('tmp.csv', dayfirst=True, parse_dates=[0])
Out[119]:
date value cat
0 2000-06-01 5 a
1 2000-06-02 10 b
2 2000-06-03 15 c
Specifying method for floating-point conversion
The parameter float_precision can be specified in order to use a specific floating-point converter during parsing with the C engine. The options are the ordinary converter, the high-precision converter, and the round-trip converter (which is guaranteed to round-trip values after writing to a file). For example:
In [120]: val = '0.3066101993807095471566981359501369297504425048828125'
In [121]: data = 'a,b,c\n1,2,{0}'.format(val)
In [122]: abs(pd.read_csv(StringIO(data), engine='c',
.....: float_precision=None)['c'][0] - float(val))
.....:
Out[122]: 1.1102230246251565e-16
In [123]: abs(pd.read_csv(StringIO(data), engine='c',
.....: float_precision='high')['c'][0] - float(val))
.....:
Out[123]: 5.551115123125783e-17
In [124]: abs(pd.read_csv(StringIO(data), engine='c',
.....: float_precision='round_trip')['c'][0] - float(val))
.....:
Out[124]: 0.0
Thousand separators
For large numbers that have been written with a thousands separator, you can set the thousands keyword to a string of length 1 so that integers will be parsed correctly:
By default, numbers with a thousands separator will be parsed as strings:
In [125]: print(open('tmp.csv').read())
ID|level|category
Patient1|123,000|x
Patient2|23,000|y
Patient3|1,234,018|z
In [126]: df = pd.read_csv('tmp.csv', sep='|')
In [127]: df
Out[127]:
ID level category
0 Patient1 123,000 x
1 Patient2 23,000 y
2 Patient3 1,234,018 z
In [128]: df.level.dtype
Out[128]: dtype('O')
The thousands keyword allows integers to be parsed correctly:
In [129]: print(open('tmp.csv').read())
ID|level|category
Patient1|123,000|x
Patient2|23,000|y
Patient3|1,234,018|z
In [130]: df = pd.read_csv('tmp.csv', sep='|', thousands=',')
In [131]: df
Out[131]:
ID level category
0 Patient1 123000 x
1 Patient2 23000 y
2 Patient3 1234018 z
In [132]: df.level.dtype
Out[132]: dtype('int64')
NA values
To control which values are parsed as missing values (which are signified by NaN), specify a string in na_values. If you specify a list of strings, then all values in it are considered to be missing values. If you specify a number (a float, like 5.0 or an integer like 5), the corresponding equivalent values will also imply a missing value (in this case effectively [5.0, 5] are recognized as NaN).
To completely override the default values that are recognized as missing, specify keep_default_na=False.
The default NaN recognized values are ['-1.#IND', '1.#QNAN', '1.#IND', '-1.#QNAN', '#N/A N/A', '#N/A', 'N/A', 'n/a', 'NA', '#NA', 'NULL', 'null', 'NaN', '-NaN', 'nan', '-nan', ''].
Let us consider some examples:
pd.read_csv('path_to_file.csv', na_values=[5])
In the example above 5 and 5.0 will be recognized as NaN, in addition to the defaults. A string will first be interpreted as a numerical 5, then as a NaN.
pd.read_csv('path_to_file.csv', keep_default_na=False, na_values=[""])
Above, only an empty field will be recognized as NaN.
pd.read_csv('path_to_file.csv', keep_default_na=False, na_values=["NA", "0"])
Above, both NA and 0 as strings are NaN.
pd.read_csv('path_to_file.csv', na_values=["Nope"])
The default values, in addition to the string "Nope" are recognized as NaN.
Infinity
inf like values will be parsed as np.inf (positive infinity), and -inf as -np.inf (negative infinity). These will ignore the case of the value, meaning Inf, will also be parsed as np.inf.
Returning Series
Using the squeeze keyword, the parser will return output with a single column as a Series:
In [133]: print(open('tmp.csv').read())
level
Patient1,123000
Patient2,23000
Patient3,1234018
In [134]: output = pd.read_csv('tmp.csv', squeeze=True)
In [135]: output
Out[135]:
Patient1 123000
Patient2 23000
Patient3 1234018
Name: level, dtype: int64
In [136]: type(output)
Out[136]: pandas.core.series.Series
Boolean values
The common values True, False, TRUE, and FALSE are all recognized as boolean. Occasionally you might want to recognize other values as being boolean. To do this, use the true_values and false_values options as follows:
In [137]: data = ('a,b,c\n'
.....: '1,Yes,2\n'
.....: '3,No,4')
.....:
In [138]: print(data)
a,b,c
1,Yes,2
3,No,4
In [139]: pd.read_csv(StringIO(data))
Out[139]:
a b c
0 1 Yes 2
1 3 No 4
In [140]: pd.read_csv(StringIO(data), true_values=['Yes'], false_values=['No'])
Out[140]:
a b c
0 1 True 2
1 3 False 4
Handling “bad” lines
Some files may have malformed lines with too few fields or too many. Lines with too few fields will have NA values filled in the trailing fields. Lines with too many fields will raise an error by default:
In [141]: data = ('a,b,c\n'
.....: '1,2,3\n'
.....: '4,5,6,7\n'
.....: '8,9,10')
.....:
In [142]: pd.read_csv(StringIO(data))
---------------------------------------------------------------------------
ParserError Traceback (most recent call last)
<ipython-input-142-6388c394e6b8> in <module>
----> 1 pd.read_csv(StringIO(data))
/pandas/pandas/io/parsers.py in parser_f(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision)
683 )
684
--> 685 return _read(filepath_or_buffer, kwds)
686
687 parser_f.__name__ = name
/pandas/pandas/io/parsers.py in _read(filepath_or_buffer, kwds)
461
462 try:
--> 463 data = parser.read(nrows)
464 finally:
465 parser.close()
/pandas/pandas/io/parsers.py in read(self, nrows)
1152 def read(self, nrows=None):
1153 nrows = _validate_integer("nrows", nrows)
-> 1154 ret = self._engine.read(nrows)
1155
1156 # May alter columns / col_dict
/pandas/pandas/io/parsers.py in read(self, nrows)
2046 def read(self, nrows=None):
2047 try:
-> 2048 data = self._reader.read(nrows)
2049 except StopIteration:
2050 if self._first_chunk:
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader.read()
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._read_low_memory()
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._read_rows()
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._tokenize_rows()
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.raise_parser_error()
ParserError: Error tokenizing data. C error: Expected 3 fields in line 3, saw 4
You can elect to skip bad lines:
In [29]: pd.read_csv(StringIO(data), error_bad_lines=False)
Skipping line 3: expected 3 fields, saw 4
Out[29]:
a b c
0 1 2 3
1 8 9 10
You can also use the usecols parameter to eliminate extraneous column data that appear in some lines but not others:
In [30]: pd.read_csv(StringIO(data), usecols=[0, 1, 2])
Out[30]:
a b c
0 1 2 3
1 4 5 6
2 8 9 10
Dialect
The dialect keyword gives greater flexibility in specifying the file format. By default it uses the Excel dialect but you can specify either the dialect name or a csv.Dialect instance.
Suppose you had data with unenclosed quotes:
In [143]: print(data)
label1,label2,label3
index1,"a,c,e
index2,b,d,f
By default, read_csv uses the Excel dialect and treats the double quote as the quote character, which causes it to fail when it finds a newline before it finds the closing double quote.
We can get around this using dialect:
In [144]: import csv
In [145]: dia = csv.excel()
In [146]: dia.quoting = csv.QUOTE_NONE
In [147]: pd.read_csv(StringIO(data), dialect=dia)
Out[147]:
label1 label2 label3
index1 "a c e
index2 b d f
All of the dialect options can be specified separately by keyword arguments:
In [148]: data = 'a,b,c~1,2,3~4,5,6'
In [149]: pd.read_csv(StringIO(data), lineterminator='~')
Out[149]:
a b c
0 1 2 3
1 4 5 6
Another common dialect option is skipinitialspace, to skip any whitespace after a delimiter:
In [150]: data = 'a, b, c\n1, 2, 3\n4, 5, 6'
In [151]: print(data)
a, b, c
1, 2, 3
4, 5, 6
In [152]: pd.read_csv(StringIO(data), skipinitialspace=True)
Out[152]:
a b c
0 1 2 3
1 4 5 6
The parsers make every attempt to “do the right thing” and not be fragile. Type inference is a pretty big deal. If a column can be coerced to integer dtype without altering the contents, the parser will do so. Any non-numeric columns will come through as object dtype as with the rest of pandas objects.
Quoting and Escape Characters
Quotes (and other escape characters) in embedded fields can be handled in any number of ways. One way is to use backslashes; to properly parse this data, you should pass the escapechar option:
In [153]: data = 'a,b\n"hello, \\"Bob\\", nice to see you",5'
In [154]: print(data)
a,b
"hello, \"Bob\", nice to see you",5
In [155]: pd.read_csv(StringIO(data), escapechar='\\')
Out[155]:
a b
0 hello, "Bob", nice to see you 5
Files with fixed width columns
While read_csv() reads delimited data, the read_fwf() function works with data files that have known and fixed column widths. The function parameters to read_fwf are largely the same as read_csv with two extra parameters, and a different usage of the delimiter parameter:
colspecs: A list of pairs (tuples) giving the extents of the fixed-width fields of each line as half-open intervals (i.e., [from, to[ ). String value ‘infer’ can be used to instruct the parser to try detecting the column specifications from the first 100 rows of the data. Default behavior, if not specified, is to infer.widths: A list of field widths which can be used instead of ‘colspecs’ if the intervals are contiguous.delimiter: Characters to consider as filler characters in the fixed-width file. Can be used to specify the filler character of the fields if it is not spaces (e.g., ‘~’).
Consider a typical fixed-width data file:
In [156]: print(open('bar.csv').read())
id8141 360.242940 149.910199 11950.7
id1594 444.953632 166.985655 11788.4
id1849 364.136849 183.628767 11806.2
id1230 413.836124 184.375703 11916.8
id1948 502.953953 173.237159 12468.3
In order to parse this file into a DataFrame, we simply need to supply the column specifications to the read_fwf function along with the file name:
# Column specifications are a list of half-intervals
In [157]: colspecs = [(0, 6), (8, 20), (21, 33), (34, 43)]
In [158]: df = pd.read_fwf('bar.csv', colspecs=colspecs, header=None, index_col=0)
In [159]: df
Out[159]:
1 2 3
0
id8141 360.242940 149.910199 11950.7
id1594 444.953632 166.985655 11788.4
id1849 364.136849 183.628767 11806.2
id1230 413.836124 184.375703 11916.8
id1948 502.953953 173.237159 12468.3
Note how the parser automatically picks column names X.<column number> when header=None argument is specified. Alternatively, you can supply just the column widths for contiguous columns:
# Widths are a list of integers
In [160]: widths = [6, 14, 13, 10]
In [161]: df = pd.read_fwf('bar.csv', widths=widths, header=None)
In [162]: df
Out[162]:
0 1 2 3
0 id8141 360.242940 149.910199 11950.7
1 id1594 444.953632 166.985655 11788.4
2 id1849 364.136849 183.628767 11806.2
3 id1230 413.836124 184.375703 11916.8
4 id1948 502.953953 173.237159 12468.3
The parser will take care of extra white spaces around the columns so it’s ok to have extra separation between the columns in the file.
By default, read_fwf will try to infer the file’s colspecs by using the first 100 rows of the file. It can do it only in cases when the columns are aligned and correctly separated by the provided delimiter (default delimiter is whitespace).
In [163]: df = pd.read_fwf('bar.csv', header=None, index_col=0)
In [164]: df
Out[164]:
1 2 3
0
id8141 360.242940 149.910199 11950.7
id1594 444.953632 166.985655 11788.4
id1849 364.136849 183.628767 11806.2
id1230 413.836124 184.375703 11916.8
id1948 502.953953 173.237159 12468.3
New in version 0.20.0.
read_fwf supports the dtype parameter for specifying the types of parsed columns to be different from the inferred type.
In [165]: pd.read_fwf('bar.csv', header=None, index_col=0).dtypes
Out[165]:
1 float64
2 float64
3 float64
dtype: object
In [166]: pd.read_fwf('bar.csv', header=None, dtype={2: 'object'}).dtypes
Out[166]:
0 object
1 float64
2 object
3 float64
dtype: object
Indexes
Files with an “implicit” index column
Consider a file with one less entry in the header than the number of data column:
In [167]: print(open('foo.csv').read())
A,B,C
20090101,a,1,2
20090102,b,3,4
20090103,c,4,5
In this special case, read_csv assumes that the first column is to be used as the index of the DataFrame:
In [168]: pd.read_csv('foo.csv')
Out[168]:
A B C
20090101 a 1 2
20090102 b 3 4
20090103 c 4 5
Note that the dates weren’t automatically parsed. In that case you would need to do as before:
In [169]: df = pd.read_csv('foo.csv', parse_dates=True)
In [170]: df.index
Out[170]: DatetimeIndex(['2009-01-01', '2009-01-02', '2009-01-03'], dtype='datetime64[ns]', freq=None)
Reading an index with a MultiIndex
Suppose you have data indexed by two columns:
In [171]: print(open('data/mindex_ex.csv').read())
year,indiv,zit,xit
1977,"A",1.2,.6
1977,"B",1.5,.5
1977,"C",1.7,.8
1978,"A",.2,.06
1978,"B",.7,.2
1978,"C",.8,.3
1978,"D",.9,.5
1978,"E",1.4,.9
1979,"C",.2,.15
1979,"D",.14,.05
1979,"E",.5,.15
1979,"F",1.2,.5
1979,"G",3.4,1.9
1979,"H",5.4,2.7
1979,"I",6.4,1.2
The index_col argument to read_csv can take a list of column numbers to turn multiple columns into a MultiIndex for the index of the returned object:
In [172]: df = pd.read_csv("data/mindex_ex.csv", index_col=[0, 1])
In [173]: df
Out[173]:
zit xit
year indiv
1977 A 1.20 0.60
B 1.50 0.50
C 1.70 0.80
1978 A 0.20 0.06
B 0.70 0.20
C 0.80 0.30
D 0.90 0.50
E 1.40 0.90
1979 C 0.20 0.15
D 0.14 0.05
E 0.50 0.15
F 1.20 0.50
G 3.40 1.90
H 5.40 2.70
I 6.40 1.20
In [174]: df.loc[1978]
Out[174]:
zit xit
indiv
A 0.2 0.06
B 0.7 0.20
C 0.8 0.30
D 0.9 0.50
E 1.4 0.90
Reading columns with a MultiIndex
By specifying list of row locations for the header argument, you can read in a MultiIndex for the columns. Specifying non-consecutive rows will skip the intervening rows.
In [175]: from pandas.util.testing import makeCustomDataframe as mkdf
In [176]: df = mkdf(5, 3, r_idx_nlevels=2, c_idx_nlevels=4)
In [177]: df.to_csv('mi.csv')
In [178]: print(open('mi.csv').read())
C0,,C_l0_g0,C_l0_g1,C_l0_g2
C1,,C_l1_g0,C_l1_g1,C_l1_g2
C2,,C_l2_g0,C_l2_g1,C_l2_g2
C3,,C_l3_g0,C_l3_g1,C_l3_g2
R0,R1,,,
R_l0_g0,R_l1_g0,R0C0,R0C1,R0C2
R_l0_g1,R_l1_g1,R1C0,R1C1,R1C2
R_l0_g2,R_l1_g2,R2C0,R2C1,R2C2
R_l0_g3,R_l1_g3,R3C0,R3C1,R3C2
R_l0_g4,R_l1_g4,R4C0,R4C1,R4C2
In [179]: pd.read_csv('mi.csv', header=[0, 1, 2, 3], index_col=[0, 1])
Out[179]:
C0 C_l0_g0 C_l0_g1 C_l0_g2
C1 C_l1_g0 C_l1_g1 C_l1_g2
C2 C_l2_g0 C_l2_g1 C_l2_g2
C3 C_l3_g0 C_l3_g1 C_l3_g2
R0 R1
R_l0_g0 R_l1_g0 R0C0 R0C1 R0C2
R_l0_g1 R_l1_g1 R1C0 R1C1 R1C2
R_l0_g2 R_l1_g2 R2C0 R2C1 R2C2
R_l0_g3 R_l1_g3 R3C0 R3C1 R3C2
R_l0_g4 R_l1_g4 R4C0 R4C1 R4C2
read_csv is also able to interpret a more common format of multi-columns indices.
In [180]: print(open('mi2.csv').read())
,a,a,a,b,c,c
,q,r,s,t,u,v
one,1,2,3,4,5,6
two,7,8,9,10,11,12
In [181]: pd.read_csv('mi2.csv', header=[0, 1], index_col=0)
Out[181]:
a b c
q r s t u v
one 1 2 3 4 5 6
two 7 8 9 10 11 12
Note: If an index_col is not specified (e.g. you don’t have an index, or wrote it with df.to_csv(..., index=False), then any names on the columns index will be lost.
Automatically “sniffing” the delimiter
read_csv is capable of inferring delimited (not necessarily comma-separated) files, as pandas uses the csv.Sniffer class of the csv module. For this, you have to specify sep=None.
In [182]: print(open('tmp2.sv').read())
:0:1:2:3
0:0.4691122999071863:-0.2828633443286633:-1.5090585031735124:-1.1356323710171934
1:1.2121120250208506:-0.17321464905330858:0.11920871129693428:-1.0442359662799567
2:-0.8618489633477999:-2.1045692188948086:-0.4949292740687813:1.071803807037338
3:0.7215551622443669:-0.7067711336300845:-1.0395749851146963:0.27185988554282986
4:-0.42497232978883753:0.567020349793672:0.27623201927771873:-1.0874006912859915
5:-0.6736897080883706:0.1136484096888855:-1.4784265524372235:0.5249876671147047
6:0.4047052186802365:0.5770459859204836:-1.7150020161146375:-1.0392684835147725
7:-0.3706468582364464:-1.1578922506419993:-1.344311812731667:0.8448851414248841
8:1.0757697837155533:-0.10904997528022223:1.6435630703622064:-1.4693879595399115
9:0.35702056413309086:-0.6746001037299882:-1.776903716971867:-0.9689138124473498
In [183]: pd.read_csv('tmp2.sv', sep=None, engine='python')
Out[183]:
Unnamed: 0 0 1 2 3
0 0 0.469112 -0.282863 -1.509059 -1.135632
1 1 1.212112 -0.173215 0.119209 -1.044236
2 2 -0.861849 -2.104569 -0.494929 1.071804
3 3 0.721555 -0.706771 -1.039575 0.271860
4 4 -0.424972 0.567020 0.276232 -1.087401
5 5 -0.673690 0.113648 -1.478427 0.524988
6 6 0.404705 0.577046 -1.715002 -1.039268
7 7 -0.370647 -1.157892 -1.344312 0.844885
8 8 1.075770 -0.109050 1.643563 -1.469388
9 9 0.357021 -0.674600 -1.776904 -0.968914
Reading multiple files to create a single DataFrame
It’s best to use concat() to combine multiple files. See the cookbook for an example.
Iterating through files chunk by chunk
Suppose you wish to iterate through a (potentially very large) file lazily rather than reading the entire file into memory, such as the following:
In [184]: print(open('tmp.sv').read())
|0|1|2|3
0|0.4691122999071863|-0.2828633443286633|-1.5090585031735124|-1.1356323710171934
1|1.2121120250208506|-0.17321464905330858|0.11920871129693428|-1.0442359662799567
2|-0.8618489633477999|-2.1045692188948086|-0.4949292740687813|1.071803807037338
3|0.7215551622443669|-0.7067711336300845|-1.0395749851146963|0.27185988554282986
4|-0.42497232978883753|0.567020349793672|0.27623201927771873|-1.0874006912859915
5|-0.6736897080883706|0.1136484096888855|-1.4784265524372235|0.5249876671147047
6|0.4047052186802365|0.5770459859204836|-1.7150020161146375|-1.0392684835147725
7|-0.3706468582364464|-1.1578922506419993|-1.344311812731667|0.8448851414248841
8|1.0757697837155533|-0.10904997528022223|1.6435630703622064|-1.4693879595399115
9|0.35702056413309086|-0.6746001037299882|-1.776903716971867|-0.9689138124473498
In [185]: table = pd.read_csv('tmp.sv', sep='|')
In [186]: table
Out[186]:
Unnamed: 0 0 1 2 3
0 0 0.469112 -0.282863 -1.509059 -1.135632
1 1 1.212112 -0.173215 0.119209 -1.044236
2 2 -0.861849 -2.104569 -0.494929 1.071804
3 3 0.721555 -0.706771 -1.039575 0.271860
4 4 -0.424972 0.567020 0.276232 -1.087401
5 5 -0.673690 0.113648 -1.478427 0.524988
6 6 0.404705 0.577046 -1.715002 -1.039268
7 7 -0.370647 -1.157892 -1.344312 0.844885
8 8 1.075770 -0.109050 1.643563 -1.469388
9 9 0.357021 -0.674600 -1.776904 -0.968914
By specifying a chunksize to read_csv, the return value will be an iterable object of type TextFileReader:
In [187]: reader = pd.read_csv('tmp.sv', sep='|', chunksize=4)
In [188]: reader
Out[188]: <pandas.io.parsers.TextFileReader at 0x7f65f17cf7f0>
In [189]: for chunk in reader:
.....: print(chunk)
.....:
Unnamed: 0 0 1 2 3
0 0 0.469112 -0.282863 -1.509059 -1.135632
1 1 1.212112 -0.173215 0.119209 -1.044236
2 2 -0.861849 -2.104569 -0.494929 1.071804
3 3 0.721555 -0.706771 -1.039575 0.271860
Unnamed: 0 0 1 2 3
4 4 -0.424972 0.567020 0.276232 -1.087401
5 5 -0.673690 0.113648 -1.478427 0.524988
6 6 0.404705 0.577046 -1.715002 -1.039268
7 7 -0.370647 -1.157892 -1.344312 0.844885
Unnamed: 0 0 1 2 3
8 8 1.075770 -0.10905 1.643563 -1.469388
9 9 0.357021 -0.67460 -1.776904 -0.968914
Specifying iterator=True will also return the TextFileReader object:
In [190]: reader = pd.read_csv('tmp.sv', sep='|', iterator=True)
In [191]: reader.get_chunk(5)
Out[191]:
Unnamed: 0 0 1 2 3
0 0 0.469112 -0.282863 -1.509059 -1.135632
1 1 1.212112 -0.173215 0.119209 -1.044236
2 2 -0.861849 -2.104569 -0.494929 1.071804
3 3 0.721555 -0.706771 -1.039575 0.271860
4 4 -0.424972 0.567020 0.276232 -1.087401
Specifying the parser engine
Under the hood pandas uses a fast and efficient parser implemented in C as well as a Python implementation which is currently more feature-complete. Where possible pandas uses the C parser (specified as engine='c'), but may fall back to Python if C-unsupported options are specified. Currently, C-unsupported options include:
sepother than a single character (e.g. regex separators)skipfootersep=Nonewithdelim_whitespace=False
Specifying any of the above options will produce a ParserWarning unless the python engine is selected explicitly using engine='python'.
Reading remote files
You can pass in a URL to a CSV file:
df = pd.read_csv('https://download.bls.gov/pub/time.series/cu/cu.item',
sep='\t')
S3 URLs are handled as well but require installing the S3Fs library:
df = pd.read_csv('s3://pandas-test/tips.csv')
If your S3 bucket requires credentials you will need to set them as environment variables or in the ~/.aws/credentials config file, refer to the S3Fs documentation on credentials.
Writing out data
Writing to CSV format
The Series and DataFrame objects have an instance method to_csv which allows storing the contents of the object as a comma-separated-values file. The function takes a number of arguments. Only the first is required.
path_or_buf: A string path to the file to write or a file object. If a file object it must be opened with newline=’‘sep: Field delimiter for the output file (default “,”)na_rep: A string representation of a missing value (default ‘’)float_format: Format string for floating point numberscolumns: Columns to write (default None)header: Whether to write out the column names (default True)index: whether to write row (index) names (default True)index_label: Column label(s) for index column(s) if desired. If None (default), and header and index are True, then the index names are used. (A sequence should be given if theDataFrameuses MultiIndex).mode: Python write mode, default ‘w’encoding: a string representing the encoding to use if the contents are non-ASCII, for Python versions prior to 3line_terminator: Character sequence denoting line end (default os.linesep)quoting: Set quoting rules as in csv module (default csv.QUOTE_MINIMAL). Note that if you have set a float_format then floats are converted to strings and csv.QUOTE_NONNUMERIC will treat them as non-numericquotechar: Character used to quote fields (default ‘”’)doublequote: Control quoting ofquotecharin fields (default True)escapechar: Character used to escapesepandquotecharwhen appropriate (default None)chunksize: Number of rows to write at a timedate_format: Format string for datetime objects
Writing a formatted string
The DataFrame object has an instance method to_string which allows control over the string representation of the object. All arguments are optional:
bufdefault None, for example a StringIO objectcolumnsdefault None, which columns to writecol_spacedefault None, minimum width of each column.na_repdefaultNaN, representation of NA valueformattersdefault None, a dictionary (by column) of functions each of which takes a single argument and returns a formatted stringfloat_formatdefault None, a function which takes a single (float) argument and returns a formatted string; to be applied to floats in theDataFrame.sparsifydefault True, set to False for aDataFramewith a hierarchical index to print every MultiIndex key at each row.index_namesdefault True, will print the names of the indicesindexdefault True, will print the index (ie, row labels)headerdefault True, will print the column labelsjustifydefaultleft, will print column headers left- or right-justified
The Series object also has a to_string method, but with only the buf, na_rep, float_format arguments. There is also a length argument which, if set to True, will additionally output the length of the Series.
JSON
读取和写入 JSON 格式的文本和字符串。
Writing JSON
一个Series 或 DataFrame 能转化成一个有效的JSON字符串。使用to_json 同可选的参数：
path_or_buf： 写入输出的路径名或缓存可以是None， 在这种情况下会返回一个JSON字符串。orient:Series:- 默认是
index； - 允许的值可以是{
split,records,index}。
DataFrame:- 默认是
columns; - 允许的值可以是{
split,records,index,columns,values,table}。
JSON字符串的格式：
split dict like {index -> [index], columns -> [columns], data -> [values]} records list like [{column -> value}, … , {column -> value}] index dict like {index -> {column -> value}} columns dict like {column -> {index -> value}} values just the values array - 默认是
date_format: 字符串，日期类型的转换，'eposh'是时间戳，'iso'是 ISO8601。double_precision: 当要编码的是浮点数值时使用的小数位数，默认是 10。force_ascii: 强制编码字符串为 ASCII , 默认是True。date_unit: 时间单位被编码来管理时间戳 和 ISO8601精度。's', 'ms', 'us' 或'ns'中的一个分别为 秒，毫秒，微秒，纳秒。默认是 'ms'。default_handler: 如果一个对象没有转换成一个恰当的JSON格式，处理程序就会被调用。采用单个参数，即要转换的对象，并返回一个序列化的对象。lines： 如果面向records，就将每行写入记录为json。
注意：NaN'S , NaT'S 和None 将会被转换为null, 并且datetime 将会基于date_format 和 date_unit 两个参数转换。
In [192]: dfj = pd.DataFrame(np.random.randn(5, 2), columns=list('AB'))
In [193]: json = dfj.to_json()
In [194]: json
Out[194]: '{"A":{"0":-1.2945235903,"1":0.2766617129,"2":-0.0139597524,"3":-0.0061535699,"4":0.8957173022},"B":{"0":0.4137381054,
"1":-0.472034511,"2":-0.3625429925,"3":-0.923060654,"4":0.8052440254}}'
面向选项（Orient options）
要生成JSON文件/字符串，这儿有很多可选的格式。如下面的 DataFrame 和 Series :
In [195]: dfjo = pd.DataFrame(dict(A=range(1, 4), B=range(4, 7), C=range(7, 10)),
..... : columns=list('ABC'), index=list('xyz'))
..... :
In [196]: dfjo
Out[196]:
A B C
x 1 4 7
y 2 5 8
z 3 6 9
In [197]: sjo = pd.Series(dict(x=15, y=16, z=17), name='D')
In [198]: sjo
Out[198]:
x 15
y 16
z 17
Name: D, dtype: int64
面向列 序列化数据（默认是 DataFrame)来作为嵌套的JSON对象，且列标签充当主索引：
In [199]: dfjo.to_json(orient="columns")
Out[199]: '{"A":{"x":1,"y":2,"z":3},"B":{"x":4,"y":5,"z":6},"C":{"x":7,"y":8,"z":9}}'
# Not available for Series （不适用于 Series）
面向索引 （默认是 Series) 与面向列类似，但是索引标签是主键：
In [200]: dfjo.to_json(orient="index")
Out[200]: '{"x":{"A":1,"B":4,"C":7},"y":{"A":2,"B":5,"C":8},"z":{"A":3,"B":6,"C":9}}'
In [201]: sjo.to_json(orient="index")
Out[201]: '{"x":15,"y":16,"z":17}'
面向记录 序列化数据为一列JSON数组 -> 值的记录，索引标签不包括在内。这个在传递 DataFrame 数据到绘图库的时候很有用，例如JavaScript库 d3.js :
In [202]: dfjo.to_json(orient="records")
Out[202]: '[{"A":1,"B":4,"C":7},{"A":2,"B":5,"C":8},{"A":3,"B":6,"C":9}]'
In [203]: sjo.to_json(orient="records")
Out[203]: '[15,16,17]'
面向值 是一个概要的选项，它只序列化为嵌套的JSON数组值，列和索引标签不包括在内：
In [204]: dfjo.to_json(orient="values")
Out[204]: '[[1,4,7],[2,5,8],[3,6,9]]'
# Not available for Series
面向切分 序列化成一个JSON对象，它包括单项的值、索引和列。Series 的命名也包括：
In [205]: dfjo.to_json(orient="split")
Out[205]: '{"columns":["A","B","C"],"index":["x","y","z"],"data":[[1,4,7],[2,5,8],[3,6,9]]}'
In [206]: sjo.to_json(orient="split")
Out[206]: '{"name":"D","index":["x","y","z"],"data":[15,16,17]}'
面向表格 序列化为JSON的 表格模式（Table Schema），允许保存为元数据，包括但不限于dtypes和索引名称。
注意
任何面向选项编码为一个JSON对象在转为序列化期间将不会保留索引和列标签的顺序。如果你想要保留标签的顺序，就使用split选项，因为它使用有序的容器。
日期处理（Date handling）
用ISO日期格式来写入：
In [207]: dfd = pd.DataFrame(np.random.randn(5, 2), columns=list('AB'))
In [208]: dfd['date'] = pd.Timestamp('20130101')
In [209]: dfd = dfd.sort_index(1, ascending=False)
In [210]: json = dfd.to_json(date_format='iso')
In [211]: json
Out[211]: '{"date":{"0":"2013-01-01T00:00:00.000Z","1":"2013-01-01T00:00:00.000Z","2":"2013-01-01T00:00:00.000Z","3":"2013-01-01T00:00:00.000Z","4":"2013-01-01T00:00:00.000Z"},"B":{"0":2.5656459463,"1":1.3403088498,"2":-0.2261692849,"3":0.8138502857,"4":-0.8273169356},"A":{"0":-1.2064117817,"1":1.4312559863,"2":-1.1702987971,"3":0.4108345112,"4":0.1320031703}}'
以ISO日期格式的微秒单位写入：
In [212]: json = dfd.to_json(date_format='iso', date_unit='us')
In [213]: json
Out[213]: '{"date":{"0":"2013-01-01T00:00:00.000000Z","1":"2013-01-01T00:00:00.000000Z","2":"2013-01-01T00:00:00.000000Z","3":"2013-01-01T00:00:00.000000Z","4":"2013-01-01T00:00:00.000000Z"},"B":{"0":2.5656459463,"1":1.3403088498,"2":-0.2261692849,"3":0.8138502857,"4":-0.8273169356},"A":{"0":-1.2064117817,"1":1.4312559863,"2":-1.1702987971,"3":0.4108345112,"4":0.1320031703}}
时间戳的时间，以秒为单位：
In [214]: json = dfd.to_json(date_format='epoch', date_unit='s')
In [215]: json
Out[215]: '{"date":{"0":1356998400,"1":1356998400,"2":1356998400,"3":1356998400,"4":1356998400},"B":{"0":2.5656459463,"1":1.3403088498,"2":-0.2261692849,"3":0.8138502857,"4":-0.8273169356},"A":{"0":-1.2064117817,"1":1.4312559863,"2":-1.1702987971,"3":0.4108345112,"4":0.1320031703}}'
写入文件，以日期索引和日期列格式：
In [216]: dfj2 = dfj.copy()
In [217]: dfj2['date'] = pd.Timestamp('20130101')
In [218]: dfj2['ints'] = list(range(5))
In [219]: dfj2['bools'] = True
In [220]: dfj2.index = pd.date_range('20130101', periods=5)
In [221]: dfj2.to_json('test.json')
In [222]: with open('test.json') as fh:
.....: print(fh.read())
.....:
{"A":{"1356998400000":-1.2945235903,"1357084800000":0.2766617129,"1357171200000":-0.0139597524,"1357257600000":-0.0061535699,"1357344000000":0.8957173022},"B":{"1356998400000":0.4137381054,"1357084800000":-0.472034511,"1357171200000":-0.3625429925,"1357257600000":-0.923060654,"1357344000000":0.8052440254},"date":{"1356998400000":1356998400000,"1357084800000":1356998400000,"1357171200000":1356998400000,"1357257600000":1356998400000,"1357344000000":1356998400000},"ints":{"1356998400000":0,"1357084800000":1,"1357171200000":2,"1357257600000":3,"1357344000000":4},"bools":{"1356998400000":true,"1357084800000":true,"1357171200000":true,"1357257600000":true,"1357344000000":true}}
回退行为（Fallback behavior）
如果JSON序列不能直接处理容器的内容，他将会以下面的方式发生回退：
如果dtype是不被支持的（例如：
np.complex) ，则将为每个值调用default_handler（如果提供），否则引发异常。如果对象不受支持，它将尝试以下操作：
- 检查一下是否对象被定义为
toDict的方法并调用它。toDict的方法将返回一个dict，它将会是序列化的JSON格式。 - 如果提供了
default_handler，则调用它。 - 通过遍历其内容将对象转换为
dict。 但是，这通常会出现OverflowError而失败或抛出意外的结果。
- 检查一下是否对象被定义为
通常，对于不被支持的对象或dtypes，处理的最佳方法是提供default_handler。 例如:
>>> DataFrame([1.0, 2.0, complex(1.0, 2.0)]).to_json() # raises
RuntimeError: Unhandled numpy dtype 15
可以通过指定一个简单default_handler来处理：
In [223]: pd.DataFrame([1.0, 2.0, complex(1.0, 2.0)]).to_json(default_handler=str)
Out[223]: '{"0":{"0":"(1+0j)","1":"(2+0j)","2":"(1+2j)"}}'
JSON的读取（Reading JSON)
把JSON字符串读取到pandas对象里会采用很多参数。如果typ没有提供或者为None，解析器将尝试解析DataFrame。 要强制地进行Series解析，请传递参数如typ = series。
filepath_or_buffer: 一个有效的JSON字符串或文件句柄/StringIO(在内存中读写字符串)。字符串可以是一个URL。有效的URL格式包括http， ftp， S3和文件。对于文件型的URL, 最好有个主机地址。例如一个本地文件可以是 file://localhost/path/to/table.json 这样的格式。typ： 要恢复的对象类型（series或者frame），默认“frame”。orient:Series:
- 默认是
index。 - 允许值为{
split,records,index}。
DataFrame:
- 默认是
columns。 - 允许值是{
split,records,index,columns,values,table}。
- 默认是
JSON字符串的格式：
| split | dict like {index -> [index], columns -> [columns], data -> [values]} |
|---|---|
| records | list like [{column -> value}, … , {column -> value}] |
| index | dict like {index -> {column -> value}} |
| columns | dict like {column -> {index -> value}} |
| values | just the values array |
| table | adhering to the JSON Table Schema |
dtype： 如果为True，推断dtypes，如果列为dtype的字典，则使用那些；如果为False，则根本不推断dtypes，默认为True，仅适用于数据。convert_axes： 布尔值，尝试将轴转换为正确的dtypes，默认为True。convert_dates：一列列表要解析为日期; 如果为True，则尝试解析类似日期的列，默认为True。keep_default_dates：布尔值，默认为True。 如果解析日期，则解析默认的类似日期的列。numpy：直接解码为NumPy数组。 默认为False; 虽然标签可能是非数字的，但仅支持数字数据。 另请注意，如果numpy = True，则每个术语的JSON顺序 必须 相同。precise_float：布尔值，默认为False。 当解码字符串为双值时，设置为能使用更高精度（strtod）函数。 默认（False）快速使用但不精确的内置功能。date_unit：字符串，用于检测转换日期的时间戳单位。 默认无。 默认情况下，将检测时间戳精度，如果不需要，则传递's'，'ms'，'us'或'ns'中的一个，以强制时间戳精度分别为秒，毫秒，微秒或纳秒。lines：读取文件每行作为一个JSON对象。encoding：用于解码py3字节的编码。chunksize：当与lines = True结合使用时，返回一个Json读取器（JSONReader），每次迭代读取chunksize行。
如果JSON不能解析，解析器将抛出ValueError / TypeError / AssertionError 中的一个错误。
如果在编码为JSON时使用非默认的orient方法，请确保在此处传递相同的选项以便解码产生合理的结果，请参阅 Orient Options以获取概述。
数据转换（Data conversion）
convert_axes = True，dtype = True和convert_dates = True的默认值将尝试解析轴，并将所有数据解析为适当的类型，包括日期。 如果需要覆盖特定的dtypes，请将字典传递给dtype。 如果您需要在轴中保留类似字符串的数字（例如“1”，“2”），则只应将convert_axes设置为False。
注意
如果convert_dates = True并且数据和/或列标签显示为“类似日期（'date-like'）“，则可以将大的整数值转换为日期。 确切的标准取决于指定的date_unit。 'date-like'表示列标签符合以下标准之一：
- 结尾以
'_at' - 结尾以
'_time' - 开头以
'timestamp' - 它是
'modified' - 它是
'date'
警告
在读取JSON数据时，自动强制转换为dtypes有一些不同寻常的地方：
索引可以按序列化的不同顺序重建，也就是说，返回的顺序不能保证与序列化之前的顺序相同
如果可以安全地，那么一列浮动（
float)数据将被转换为一列整数(integer)，例如 一列1布尔列将在重建时转换为整数（
integer)
因此，有时你会有那样的时刻可能想通过dtype关键字参数指定特定的dtypes。
读取JSON字符串：
In [224]: pd.read_json(json)
Out[224]:
date B A
0 2013-01-01 2.565646 -1.206412
1 2013-01-01 1.340309 1.431256
2 2013-01-01 -0.226169 -1.170299
3 2013-01-01 0.813850 0.410835
4 2013-01-01 -0.827317 0.132003
读取文件：
In [225]: pd.read_json('test.json')
Out[225]:
A B date ints bools
2013-01-01 -1.294524 0.413738 2013-01-01 0 True
2013-01-02 0.276662 -0.472035 2013-01-01 1 True
2013-01-03 -0.013960 -0.362543 2013-01-01 2 True
2013-01-04 -0.006154 -0.923061 2013-01-01 3 True
2013-01-05 0.895717 0.805244 2013-01-01 4 True
不要转换任何数据（但仍然转换轴和日期）：
In [226]: pd.read_json('test.json', dtype=object).dtypes
Out[226]:
A object
B object
date object
ints object
bools object
dtype: object
指定转换的dtypes：
In [227]: pd.read_json('test.json', dtype={'A': 'float32', 'bools': 'int8'}).dtypes
Out[227]:
A float32
B float64
date datetime64[ns]
ints int64
bools int8
dtype: object
保留字符串索引：
In [228]: si = pd.DataFrame(np.zeros((4, 4)), columns=list(range(4)),
.....: index=[str(i) for i in range(4)])
.....:
In [229]: si
Out[229]:
0 1 2 3
0 0.0 0.0 0.0 0.0
1 0.0 0.0 0.0 0.0
2 0.0 0.0 0.0 0.0
3 0.0 0.0 0.0 0.0
In [230]: si.index
Out[230]: Index(['0', '1', '2', '3'], dtype='object')
In [231]: si.columns
Out[231]: Int64Index([0, 1, 2, 3], dtype='int64')
In [232]: json = si.to_json()
In [233]: sij = pd.read_json(json, convert_axes=False)
In [234]: sij
Out[234]:
0 1 2 3
0 0 0 0 0
1 0 0 0 0
2 0 0 0 0
3 0 0 0 0
In [235]: sij.index
Out[235]: Index(['0', '1', '2', '3'], dtype='object')
In [236]: sij.columns
Out[236]: Index(['0', '1', '2', '3'], dtype='object')
以纳秒为单位的日期需要以纳秒为单位读回：
In [237]: json = dfj2.to_json(date_unit='ns')
# Try to parse timestamps as milliseconds -> Won't Work
In [238]: dfju = pd.read_json(json, date_unit='ms')
In [239]: dfju
Out[239]:
A B date ints bools
1356998400000000000 -1.294524 0.413738 1356998400000000000 0 True
1357084800000000000 0.276662 -0.472035 1356998400000000000 1 True
1357171200000000000 -0.013960 -0.362543 1356998400000000000 2 True
1357257600000000000 -0.006154 -0.923061 1356998400000000000 3 True
1357344000000000000 0.895717 0.805244 1356998400000000000 4 True
# Let pandas detect the correct precision
In [240]: dfju = pd.read_json(json)
In [241]: dfju
Out[241]:
A B date ints bools
2013-01-01 -1.294524 0.413738 2013-01-01 0 True
2013-01-02 0.276662 -0.472035 2013-01-01 1 True
2013-01-03 -0.013960 -0.362543 2013-01-01 2 True
2013-01-04 -0.006154 -0.923061 2013-01-01 3 True
2013-01-05 0.895717 0.805244 2013-01-01 4 True
# Or specify that all timestamps are in nanoseconds
In [242]: dfju = pd.read_json(json, date_unit='ns')
In [243]: dfju
Out[243]:
A B date ints bools
2013-01-01 -1.294524 0.413738 2013-01-01 0 True
2013-01-02 0.276662 -0.472035 2013-01-01 1 True
2013-01-03 -0.013960 -0.362543 2013-01-01 2 True
2013-01-04 -0.006154 -0.923061 2013-01-01 3 True
2013-01-05 0.895717 0.805244 2013-01-01 4 True
Numpy 参数
注意
这仅支持数值数据。 索引和列标签可以是非数字的，例如 字符串，日期等。
如果将numpy = True传递给read_json，则会在反序列化期间尝试找到适当的dtype，然后直接解码到NumPy数组，从而绕过对中间Python对象的需求。
如果要反序列化大量数值数据，这可以提供加速：
In [244]: randfloats = np.random.uniform(-100, 1000, 10000)
In [245]: randfloats.shape = (1000, 10)
In [246]: dffloats = pd.DataFrame(randfloats, columns=list('ABCDEFGHIJ'))
In [247]: jsonfloats = dffloats.to_json()
In [248]: %timeit pd.read_json(jsonfloats)
12.4 ms +- 116 us per loop (mean +- std. dev. of 7 runs, 100 loops each)
In [249]: %timeit pd.read_json(jsonfloats, numpy=True)
9.56 ms +- 82.8 us per loop (mean +- std. dev. of 7 runs, 100 loops each)
对于较小的数据集，加速不太明显：
In [250]: jsonfloats = dffloats.head(100).to_json()
In [251]: %timeit pd.read_json(jsonfloats)
8.05 ms +- 120 us per loop (mean +- std. dev. of 7 runs, 100 loops each)
In [252]: %timeit pd.read_json(jsonfloats, numpy=True)
7 ms +- 162 us per loop (mean +- std. dev. of 7 runs, 100 loops each)
警告
直接NumPy解码会产生许多假设并可能导致失败，或如果这些假设不满足，则产生意外地输出：
数据是数值。
数据是统一的。 从解码的第一个值中找到dtype。可能会引发
ValueError错误，或者如果这个条件不满足可能产生不正确的输出。标签是有序的。 标签仅从第一个容器读取，假设每个后续行/列已按相同顺序编码。 如果使用
to_json编码数据，则应该满足这一要求，但如果JSON来自其他来源，则可能不是这种情况。
标准化（Normalization）
pandas提供了一个实用程序函数来获取一个字典或字典列表，并将这个半结构化数据规范化为一个平面表。
In [253]: from pandas.io.json import json_normalize
In [254]: data = [{'id': 1, 'name': {'first': 'Coleen', 'last': 'Volk'}},
.....: {'name': {'given': 'Mose', 'family': 'Regner'}},
.....: {'id': 2, 'name': 'Faye Raker'}]
.....:
In [255]: json_normalize(data)
Out[255]:
id name.first name.last name.given name.family name
0 1.0 Coleen Volk NaN NaN NaN
1 NaN NaN NaN Mose Regner NaN
2 2.0 NaN NaN NaN NaN Faye Raker
In [256]: data = [{'state': 'Florida',
.....: 'shortname': 'FL',
.....: 'info': {'governor': 'Rick Scott'},
.....: 'counties': [{'name': 'Dade', 'population': 12345},
.....: {'name': 'Broward', 'population': 40000},
.....: {'name': 'Palm Beach', 'population': 60000}]},
.....: {'state': 'Ohio',
.....: 'shortname': 'OH',
.....: 'info': {'governor': 'John Kasich'},
.....: 'counties': [{'name': 'Summit', 'population': 1234},
.....: {'name': 'Cuyahoga', 'population': 1337}]}]
.....:
In [257]: json_normalize(data, 'counties', ['state', 'shortname', ['info', 'governor']])
Out[257]:
name population state shortname info.governor
0 Dade 12345 Florida FL Rick Scott
1 Broward 40000 Florida FL Rick Scott
2 Palm Beach 60000 Florida FL Rick Scott
3 Summit 1234 Ohio OH John Kasich
4 Cuyahoga 1337 Ohio OH John Kasich
max_level 参数提供了对结束规范化的级别的更多控制。 当max_level = 1时，以下代码段会标准化，直到提供了字典的第一个嵌套级别为止。
In [258]: data = [{'CreatedBy': {'Name': 'User001'},
.....: 'Lookup': {'TextField': 'Some text',
.....: 'UserField': {'Id': 'ID001',
.....: 'Name': 'Name001'}},
.....: 'Image': {'a': 'b'}
.....: }]
.....:
In [259]: json_normalize(data, max_level=1)
Out[259]:
CreatedBy.Name Lookup.TextField Lookup.UserField Image.a
0 User001 Some text {'Id': 'ID001', 'Name': 'Name001'} b
json的行分割（Line delimited json）
New in version 0.19.0.
pandas能够读取和写入行分隔的json文件通常是在用Hadoop或Spark进行数据处理的管道中。
New in version 0.21.0.
对于行分隔的json文件，pandas也可以返回一个迭代器，它能一次读取chunksize行。 这对于大型文件或从数据流中读取非常有用。
In [260]: jsonl = '''
.....: {"a": 1, "b": 2}
.....: {"a": 3, "b": 4}
.....: '''
.....:
In [261]: df = pd.read_json(jsonl, lines=True)
In [262]: df
Out[262]:
a b
0 1 2
1 3 4
In [263]: df.to_json(orient='records', lines=True)
Out[263]: '{"a":1,"b":2}\n{"a":3,"b":4}'
# reader is an iterator that returns `chunksize` lines each iteration
In [264]: reader = pd.read_json(StringIO(jsonl), lines=True, chunksize=1)
In [265]: reader
Out[265]: <pandas.io.json._json.JsonReader at 0x7f65f15bac18>
In [266]: for chunk in reader:
.....: print(chunk)
.....:
Empty DataFrame
Columns: []
Index: []
a b
0 1 2
a b
1 3 4
表模式（Table schema）
New in version 0.20.0.
表模式（Table schema）是用于将表格数据集描述为JSON对象的一种规范。 JSON包含有关字段名称，类型和其他属性的信息。 你可以使用面向table来构建一个JSON字符串包含两个字段，schema和data。
In [267]: df = pd.DataFrame({'A': [1, 2, 3],
.....: 'B': ['a', 'b', 'c'],
.....: 'C': pd.date_range('2016-01-01', freq='d', periods=3)},
.....: index=pd.Index(range(3), name='idx'))
.....:
In [268]: df
Out[268]:
A B C
idx
0 1 a 2016-01-01
1 2 b 2016-01-02
2 3 c 2016-01-03
In [269]: df.to_json(orient='table', date_format="iso")
Out[269]: '{"schema": {"fields":[{"name":"idx","type":"integer"},{"name":"A","type":"integer"},{"name":"B","type":"string"},{"name":"C","type":"datetime"}],"primaryKey":["idx"],"pandas_version":"0.20.0"}, "data": [{"idx":0,"A":1,"B":"a","C":"2016-01-01T00:00:00.000Z"},{"idx":1,"A":2,"B":"b","C":"2016-01-02T00:00:00.000Z"},{"idx":2,"A":3,"B":"c","C":"2016-01-03T00:00:00.000Z"}]}'
schema字段包含fields主键，它本身包含一个列名称到列对的列表，包括Index或MultiIndex（请参阅下面的类型列表）。 如果（多）索引是唯一的，则schema字段也包含一个primaryKey字段。
第二个字段data包含用面向records来序列化数据。 索引是包括的，并且任何日期时间都是ISO 8601格式，正如表模式规范所要求的那样。
表模式规范中描述了所有支持的全部类型列表。 此表显示了pandas类型的映射：
| Pandas type | Table Schema type |
|---|---|
| int64 | integer |
| float64 | number |
| bool | boolean |
| datetime64[ns] | datetime |
| timedelta64[ns] | duration |
| categorical | any |
| object | str |
关于生成的表模式的一些注意事项：
schema对象包含pandas_version的字段。 它包含模式的pandas方言版本，并将随每个修订增加。- 序列化时，所有日期都转换为UTC。 甚至是时区的初始值，也被视为UTC，偏移量为0。
In [270]: from pandas.io.json import build_table_schema
In [271]: s = pd.Series(pd.date_range('2016', periods=4))
In [272]: build_table_schema(s)
Out[272]:
{'fields': [{'name': 'index', 'type': 'integer'},
{'name': 'values', 'type': 'datetime'}],
'primaryKey': ['index'],
'pandas_version': '0.20.0'}
- 具有时区的日期时间（在序列化之前），包括具有时区名称的附加字段
tz（例如：'US / Central'）。
In [273]: s_tz = pd.Series(pd.date_range('2016', periods=12,
.....: tz='US/Central'))
.....:
In [274]: build_table_schema(s_tz)
Out[274]:
{'fields': [{'name': 'index', 'type': 'integer'},
{'name': 'values', 'type': 'datetime', 'tz': 'US/Central'}],
'primaryKey': ['index'],
'pandas_version': '0.20.0'}
- 时间段在序列化之前是转换为时间戳的，因此具有转换为UTC的相同方式。 此外，时间段将包含具有时间段频率的附加字段
freq，例如：'A-DEC'。
In [275]: s_per = pd.Series(1, index=pd.period_range('2016', freq='A-DEC',
.....: periods=4))
.....:
In [276]: build_table_schema(s_per)
Out[276]:
{'fields': [{'name': 'index', 'type': 'datetime', 'freq': 'A-DEC'},
{'name': 'values', 'type': 'integer'}],
'primaryKey': ['index'],
'pandas_version': '0.20.0'}
- 分类使用
any类型和enum约束来列出可能值的集合。 此外，还包括一个ordered字段：
In [277]: s_cat = pd.Series(pd.Categorical(['a', 'b', 'a']))
In [278]: build_table_schema(s_cat)
Out[278]:
{'fields': [{'name': 'index', 'type': 'integer'},
{'name': 'values',
'type': 'any',
'constraints': {'enum': ['a', 'b']},
'ordered': False}],
'primaryKey': ['index'],
'pandas_version': '0.20.0'}
- 如果索引是唯一的，则包含
primaryKey字段,它包含了标签数组：
In [279]: s_dupe = pd.Series([1, 2], index=[1, 1])
In [280]: build_table_schema(s_dupe)
Out[280]:
{'fields': [{'name': 'index', 'type': 'integer'},
{'name': 'values', 'type': 'integer'}],
'pandas_version': '0.20.0'}
primaryKey的形式与多索引相同，但在这种情况下，primaryKey是一个数组：
In [281]: s_multi = pd.Series(1, index=pd.MultiIndex.from_product([('a', 'b'),
.....: (0, 1)]))
.....:
In [282]: build_table_schema(s_multi)
Out[282]:
{'fields': [{'name': 'level_0', 'type': 'string'},
{'name': 'level_1', 'type': 'integer'},
{'name': 'values', 'type': 'integer'}],
'primaryKey': FrozenList(['level_0', 'level_1']),
'pandas_version': '0.20.0'}
默认命名大致遵循以下规则：
- 对于series，使用
object.name。 如果没有，那么名称就是values - 对于
DataFrames，使用列名称的字符串化版本 - 对于
Index（不是MultiIndex），使用index.name，如果为None，则使用回退index。 - 对于
MultiIndex，使用mi.names。 如果任何级别没有名称，则使用level_。
- 对于series，使用
New in version 0.23.0.
read_json也接受orient ='table'作为参数。 这允许以可循环移动的方式保存诸如dtypes和索引名称之类的元数据。
In [283]: df = pd.DataFrame({'foo': [1, 2, 3, 4],
.....: 'bar': ['a', 'b', 'c', 'd'],
.....: 'baz': pd.date_range('2018-01-01', freq='d', periods=4),
.....: 'qux': pd.Categorical(['a', 'b', 'c', 'c'])
.....: }, index=pd.Index(range(4), name='idx'))
.....:
In [284]: df
Out[284]:
foo bar baz qux
idx
0 1 a 2018-01-01 a
1 2 b 2018-01-02 b
2 3 c 2018-01-03 c
3 4 d 2018-01-04 c
In [285]: df.dtypes
Out[285]:
foo int64
bar object
baz datetime64[ns]
qux category
dtype: object
In [286]: df.to_json('test.json', orient='table')
In [287]: new_df = pd.read_json('test.json', orient='table')
In [288]: new_df
Out[288]:
foo bar baz qux
idx
0 1 a 2018-01-01 a
1 2 b 2018-01-02 b
2 3 c 2018-01-03 c
3 4 d 2018-01-04 c
In [289]: new_df.dtypes
Out[289]:
foo int64
bar object
baz datetime64[ns]
qux category
dtype: object
请注意，作为 Index 名称的文字字符串'index'是不能循环移动的，也不能在 MultiIndex 中用以'level_'开头的任何名称。 这些默认情况下在 DataFrame.to_json（) 中用于指示缺失值和后续读取无法区分的目的。
In [290]: df.index.name = 'index'
In [291]: df.to_json('test.json', orient='table')
In [292]: new_df = pd.read_json('test.json', orient='table')
In [293]: print(new_df.index.name)
None
HTML
读取HTML的内容
警告：
我们强烈建议你阅读 HTML Table Parsing gotchas里面相关的围绕BeautifulSoup4/html5lib/lxml解析器部分的问题。
顶级的read_html()函数能接受HTML字符串/文件/URL格式，并且能解析HTML 表格为pandasDataFrames的列表，让我们看看下面的几个例子。
注意：
read_html返回的是一个DataFrame对象的list，即便在HTML页面里只包含单个表格。
读取一个没有选项的URL：
In [294]: url = 'https://www.fdic.gov/bank/individual/failed/banklist.html'
In [295]: dfs = pd.read_html(url)
In [296]: dfs
Out[296]:
[ Bank Name City ST CERT Acquiring Institution Closing Date Updated Date
0 The Enloe State Bank Cooper TX 10716 Legend Bank, N. A. May 31, 2019 June 18, 2019
1 Washington Federal Bank for Savings Chicago IL 30570 Royal Savings Bank December 15, 2017 February 1, 2019
2 The Farmers and Merchants State Bank of Argonia Argonia KS 17719 Conway Bank October 13, 2017 February 21, 2018
3 Fayette County Bank Saint Elmo IL 1802 United Fidelity Bank, fsb May 26, 2017 January 29, 2019
4 Guaranty Bank, (d/b/a BestBank in Georgia & Mi... Milwaukee WI 30003 First-Citizens Bank & Trust Company May 5, 2017 March 22, 2018
.. ... ... .. ... ... ... ...
551 Superior Bank, FSB Hinsdale IL 32646 Superior Federal, FSB July 27, 2001 August 19, 2014
552 Malta National Bank Malta OH 6629 North Valley Bank May 3, 2001 November 18, 2002
553 First Alliance Bank & Trust Co. Manchester NH 34264 Southern New Hampshire Bank & Trust February 2, 2001 February 18, 2003
554 National State Bank of Metropolis Metropolis IL 3815 Banterra Bank of Marion December 14, 2000 March 17, 2005
555 Bank of Honolulu Honolulu HI 21029 Bank of the Orient October 13, 2000 March 17, 2005
[556 rows x 7 columns]]
注意：
上面的URL数据修改了每个周一以至于上面的数据结果跟下面的数据结果可能有轻微的不同。
从上面的URL读取文件内容并且传递它给read_html作为一个字符串：
In [297]: with open(file_path, 'r') as f:
.....: dfs = pd.read_html(f.read())
.....:
In [298]: dfs
Out[298]:
[ Bank Name City ST CERT Acquiring Institution Closing Date Updated Date
0 Banks of Wisconsin d/b/a Bank of Kenosha Kenosha WI 35386 North Shore Bank, FSB May 31, 2013 May 31, 2013
1 Central Arizona Bank Scottsdale AZ 34527 Western State Bank May 14, 2013 May 20, 2013
2 Sunrise Bank Valdosta GA 58185 Synovus Bank May 10, 2013 May 21, 2013
3 Pisgah Community Bank Asheville NC 58701 Capital Bank, N.A. May 10, 2013 May 14, 2013
4 Douglas County Bank Douglasville GA 21649 Hamilton State Bank April 26, 2013 May 16, 2013
.. ... ... .. ... ... ... ...
500 Superior Bank, FSB Hinsdale IL 32646 Superior Federal, FSB July 27, 2001 June 5, 2012
501 Malta National Bank Malta OH 6629 North Valley Bank May 3, 2001 November 18, 2002
502 First Alliance Bank & Trust Co. Manchester NH 34264 Southern New Hampshire Bank & Trust February 2, 2001 February 18, 2003
503 National State Bank of Metropolis Metropolis IL 3815 Banterra Bank of Marion December 14, 2000 March 17, 2005
504 Bank of Honolulu Honolulu HI 21029 Bank of the Orient October 13, 2000 March 17, 2005
[505 rows x 7 columns]]
甚至如果你想，你还可以传递一个StringIO的实例：
In [299]: with open(file_path, 'r') as f:
.....: sio = StringIO(f.read())
.....:
In [300]: dfs = pd.read_html(sio)
In [301]: dfs
Out[301]:
[ Bank Name City ST CERT Acquiring Institution Closing Date Updated Date
0 Banks of Wisconsin d/b/a Bank of Kenosha Kenosha WI 35386 North Shore Bank, FSB May 31, 2013 May 31, 2013
1 Central Arizona Bank Scottsdale AZ 34527 Western State Bank May 14, 2013 May 20, 2013
2 Sunrise Bank Valdosta GA 58185 Synovus Bank May 10, 2013 May 21, 2013
3 Pisgah Community Bank Asheville NC 58701 Capital Bank, N.A. May 10, 2013 May 14, 2013
4 Douglas County Bank Douglasville GA 21649 Hamilton State Bank April 26, 2013 May 16, 2013
.. ... ... .. ... ... ... ...
500 Superior Bank, FSB Hinsdale IL 32646 Superior Federal, FSB July 27, 2001 June 5, 2012
501 Malta National Bank Malta OH 6629 North Valley Bank May 3, 2001 November 18, 2002
502 First Alliance Bank & Trust Co. Manchester NH 34264 Southern New Hampshire Bank & Trust February 2, 2001 February 18, 2003
503 National State Bank of Metropolis Metropolis IL 3815 Banterra Bank of Marion December 14, 2000 March 17, 2005
504 Bank of Honolulu Honolulu HI 21029 Bank of the Orient October 13, 2000 March 17, 2005
[505 rows x 7 columns]]
注意：
以下的例子在IPython的程序中不会运行，因为有太多的网络接入函数减缓了文档的创建。如果你的程序报错或者例子不运行，请立即向 pandas GitHub issues page 上报。
读取一个URL并匹配表格里面所包含的具体文本内容：
match = 'Metcalf Bank'
df_list = pd.read_html(url, match=match)
指定一个标题行（通过默认的<th>或者<td>定位并伴随一个<thead>被用来作为列的索引，如果是多行含有<thead>，则多索引就会被创建）；如果已经指定，标题行则从数据减去已解析的标题元素中获取（<th>元素）。
dfs = pd.read_html(url, header=0)
指定一个索引列：
dfs = pd.read_html(url, index_col=0)
指定跳过行的数量：
dfs = pd.read_html(url, skiprows=0)
指定使用列表来跳过行的数量（xrange(只在Python 2 中)也有效）：
dfs = pd.read_html(url, skiprows=range(2))
指定一个HTML属性：
dfs1 = pd.read_html(url, attrs={'id': 'table'})
dfs2 = pd.read_html(url, attrs={'class': 'sortable'})
print(np.array_equal(dfs1[0], dfs2[0])) # Should be True
指定值将会被转换为NaN(非数值)：
dfs = pd.read_html(url, na_values=['No Acquirer'])
New in version 0.19.
指定是否保持默认的NaN值的设置：
dfs = pd.read_html(url, keep_default_na=False)
New in version 0.19.
指定列的转换器。这对于有前置零的数字文本数据很有用。默认情况下，数值列会转换成数值类型且前置零会丢失。为了避免这种情况，我们能转换这些列为字符串。
url_mcc = 'https://en.wikipedia.org/wiki/Mobile_country_code'
dfs = pd.read_html(url_mcc, match='Telekom Albania', header=0,
converters={'MNC': str})
New in version 0.19.
把上面的一些结合使用：
dfs = pd.read_html(url, match='Metcalf Bank', index_col=0)
读取pandasto_html输出（同时一些精确的浮点会失去）：
df = pd.DataFrame(np.random.randn(2, 2))
s = df.to_html(float_format='{0:.40g}'.format)
dfin = pd.read_html(s, index_col=0)
如果lxml后端是你提供的唯一解析器，那么它将在解析失败时报错。如果你能提供的解析器只有一个就选字符串，但是传递一个字符串列表会是很好的训练，例如，这个函数期望是一个字符串序列。你可以这样使用：
dfs = pd.read_html(url, 'Metcalf Bank', index_col=0, flavor=['lxml'])
或者你可以传递flavor='lxml'而不要列表：
dfs = pd.read_html(url, 'Metcalf Bank', index_col=0, flavor='lxml')
然而，如果你已经安装了bs4 和 html5lib并且传递None或['lxml', 'bs4']，那么极大可能会解析成功。注意一旦解析成功了，函数将会返回。
dfs = pd.read_html(url, 'Metcalf Bank', index_col=0, flavor=['lxml', 'bs4'])
写入HTML文件
DataFrame对象具有实例的方法to_html，它能渲染DataFrame的内容为HTML表格。这个函数的参数同上面的to_string方法的一样。
注意：
为了简洁起见，这儿显示的不是所有的DataFrame.to_html可选项。所有的选项设置见to_html()。
In [302]: df = pd.DataFrame(np.random.randn(2, 2))
In [303]: df
Out[303]:
0 1
0 -0.184744 0.496971
1 -0.856240 1.857977
In [304]: print(df.to_html()) # raw html
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>0</th>
<th>1</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>-0.184744</td>
<td>0.496971</td>
</tr>
<tr>
<th>1</th>
<td>-0.856240</td>
<td>1.857977</td>
</tr>
</tbody>
</table>
HTML:
| - | 0 | 1 |
|---|---|---|
| 0 | -0.184744 | 0.496971 |
| 1 | -0.856240 | 1.857977 |
In [302]: df = pd.DataFrame(np.random.randn(2, 2))
In [303]: df
Out[303]:
0 1
0 -0.184744 0.496971
1 -0.856240 1.857977
In [304]: print(df.to_html()) # raw html
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>0</th>
<th>1</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>-0.184744</td>
<td>0.496971</td>
</tr>
<tr>
<th>1</th>
<td>-0.856240</td>
<td>1.857977</td>
</tr>
</tbody>
</table>
columns 参数将限制列的显示：
In [305]: print(df.to_html(columns=[0]))
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>0</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>-0.184744</td>
</tr>
<tr>
<th>1</th>
<td>-0.856240</td>
</tr>
</tbody>
</table>
HTML：
| - | 0 |
|---|---|
| 0 | -0.184744 |
| 1 | -0.856240 |
float_format采用可调用的 Python来控制浮点值的精确度：
In [306]: print(df.to_html(float_format='{0:.10f}'.format))
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>0</th>
<th>1</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>-0.1847438576</td>
<td>0.4969711327</td>
</tr>
<tr>
<th>1</th>
<td>-0.8562396763</td>
<td>1.8579766508</td>
</tr>
</tbody>
</table>
HTML：
| - | 0 | 1 |
|---|---|---|
| 0 | -0.1847438576 | 0.4969711327 |
| 1 | -0.8562396763 | 1.8579766508 |
默认情况下，bold_rows可以加粗行标签，但是你可以关掉它：
In [307]: print(df.to_html(bold_rows=False))
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>0</th>
<th>1</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>-0.184744</td>
<td>0.496971</td>
</tr>
<tr>
<td>1</td>
<td>-0.856240</td>
<td>1.857977</td>
</tr>
</tbody>
</table>
| - | 0 | 1 |
|---|---|---|
| 0 | -0.184744 | 0.496971 |
| 1 | -0.856240 | 1.857977 |
classes 参数提供了能生成HTML表的CSS类的功能。注意这些类是已添加到现有的'dataframe' 类中的。
In [308]: print(df.to_html(classes=['awesome_table_class', 'even_more_awesome_class']))
<table border="1" class="dataframe awesome_table_class even_more_awesome_class">
<thead>
<tr style="text-align: right;">
<th></th>
<th>0</th>
<th>1</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>-0.184744</td>
<td>0.496971</td>
</tr>
<tr>
<th>1</th>
<td>-0.856240</td>
<td>1.857977</td>
</tr>
</tbody>
</table>
render_links参数提供了向包含URL的单元格添加超链接的功能。
New in version 0.24.
In [309]: url_df = pd.DataFrame({
.....: 'name': ['Python', 'Pandas'],
.....: 'url': ['https://www.python.org/', 'http://pandas.pydata.org']})
.....:
In [310]: print(url_df.to_html(render_links=True))
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>name</th>
<th>url</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>Python</td>
<td><a href="https://www.python.org/" target="_blank">https://www.python.org/</a></td>
</tr>
<tr>
<th>1</th>
<td>Pandas</td>
<td><a href="http://pandas.pydata.org" target="_blank">http://pandas.pydata.org</a></td>
</tr>
</tbody>
</table>
HTML:
| - | name | url |
|---|---|---|
| 0 | Python | https://www.python.org/ |
| 1 | Pandas | http://pandas.pydata.org |
最后，escape参数允许你控制是否对生成的 HTML字符“<”, “>”和 “&”进行转义（默认是True）。因此，获取不转义的HTML字符就设置为escape=False。
In [311]: df = pd.DataFrame({'a': list('&<>'), 'b': np.random.randn(3)})
转义的：
In [312]: print(df.to_html())
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>a</th>
<th>b</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>&</td>
<td>-0.474063</td>
</tr>
<tr>
<th>1</th>
<td><</td>
<td>-0.230305</td>
</tr>
<tr>
<th>2</th>
<td>></td>
<td>-0.400654</td>
</tr>
</tbody>
</table>
| - | a | b |
|---|---|---|
| 0 | & | -0.474063 |
| 1 | < | -0.230305 |
| 2 | > | -0.400654 |
不转义的：
In [313]: print(df.to_html(escape=False))
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>a</th>
<th>b</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>&</td>
<td>-0.474063</td>
</tr>
<tr>
<th>1</th>
<td><</td>
<td>-0.230305</td>
</tr>
<tr>
<th>2</th>
<td>></td>
<td>-0.400654</td>
</tr>
</tbody>
</table>
| - | a | b |
|---|---|---|
| 0 | & | -0.474063 |
| 1 | < | -0.230305 |
| 2 | > | -0.400654 |
注意：
一些浏览器在渲染上面的两个HTML表格的时候可能看不出区别。
HTML表格解析陷阱
在使用顶级的pandas io函数read_html来解析HTML表格的时候，围绕这些库，存在一些版本的问题。
lxml问题:
优点：
缺点：
BeautifulSoup4使用lxml作为后端的问题：
- 以上问题仍然会存在因为**BeautifulSoup4**本质上是一个围绕后端解析的包装器。
BeautifulSoup4使用html5lib作为后端的问题：
优点：
缺点：
- 使用html5lib最大的缺点就是太慢了。但是考虑到网络上许多表格并不足以如解析算法运行时的那么重要，它更可能像是正在通过网络上的URL读取原始文本过程中的瓶颈。例如当IO（输入-输出） 时，对于非常大的表，事实可能并非如此。
Excel 文件
read_excel()方法使用Python的xlrd模块来读取Excel 2003（.xls)版的文件，而Excel 2007+ （.xlsx)版本的是用xlrd或者openpyxl模块来读取的。to_excel()方法则是用来把DataFrame数据存储为Excel格式。一般来说，它的语法同使用csv数据是类似的，更多高级的用法可以参考cookbook。
读取 Excel 文件
在大多数基本的使用案例中，read_excel会读取Excel文件通过一个路径，并且sheet_name 会表明需要解析哪一张表格。
# Returns a DataFrame
pd.read_excel('path_to_file.xls', sheet_name='Sheet1')
ExcelFile 类
为了更方便地读取同一个文件的多张表格，ExcelFile类可用来打包文件并传递给read_excel。因为仅需读取一次内存，所以这种方式读取一个文件的多张表格会有性能上的优势。
xlsx = pd.ExcelFile('path_to_file.xls')
df = pd.read_excel(xlsx, 'Sheet1')
ExcelFile类也能用来作为上下文管理器。
with pd.ExcelFile('path_to_file.xls') as xls:
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')
sheet_names属性能将文件中的所有表格名字生成一组列表。
ExcelFile一个主要的用法就是用来解析多张表格的不同参数：
data = {}
# For when Sheet1's format differs from Sheet2
with pd.ExcelFile('path_to_file.xls') as xls:
data['Sheet1'] = pd.read_excel(xls, 'Sheet1', index_col=None,
na_values=['NA'])
data['Sheet2'] = pd.read_excel(xls, 'Sheet2', index_col=1)
注意如果所有的表格解析同一个参数，那么这组表格名的列表能轻易地传递给read_excel且不会有性能上地损失。
# using the ExcelFile class
data = {}
with pd.ExcelFile('path_to_file.xls') as xls:
data['Sheet1'] = pd.read_excel(xls, 'Sheet1', index_col=None,
na_values=['NA'])
data['Sheet2'] = pd.read_excel(xls, 'Sheet2', index_col=None,
na_values=['NA'])
# equivalent using the read_excel function
data = pd.read_excel('path_to_file.xls', ['Sheet1', 'Sheet2'],
index_col=None, na_values=['NA'])
ExcelFile也能同xlrd.book.Book对象作为一个参数被调用。这种方法让用户可以控制Excel文件被如何读取。例如，表格可以根据需求加载通过调用xlrd.open_workbook()伴随on_demand=True。
import xlrd
xlrd_book = xlrd.open_workbook('path_to_file.xls', on_demand=True)
with pd.ExcelFile(xlrd_book) as xls:
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')
指定表格
注意
第二个参数是sheet_name，不要同ExcelFile.sheet_names搞混淆。
注意
ExcelFile's的属性sheet_names提供的是多张表格所生成的列表。
sheet_name参数允许指定单张表格或多张表格被读取。sheet_name的默认值是0，这表明读取的是第一张表格。在工作簿里面，使用字符串指向特定的表格名称。
使用整数指向表格的索引，索引遵守Python的约定是从0开始的。
无论是使用一组字符串还是整数的列表，返回的都是指定表格的字典。
使用
None值则会返回所有可用表格的一组字典。
# Returns a DataFrame
pd.read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA'])
使用表格索引：
# Returns a DataFrame
pd.read_excel('path_to_file.xls', 0, index_col=None, na_values=['NA'])
使用所有默认值：
# Returns a DataFrame
pd.read_excel('path_to_file.xls')
使用None获取所有表格：
# Returns a dictionary of DataFrames
pd.read_excel('path_to_file.xls', sheet_name=None)
使用列表获取多张表格：
# Returns the 1st and 4th sheet, as a dictionary of DataFrames.
pd.read_excel('path_to_file.xls', sheet_name=['Sheet1', 3])
read_excel能读取不止一张表格，通过sheet_name能设置为读取表格名称的列表，表格位置的列表，还能设置为None来读取所有表格。多张表格能通过表格索引或表格名称分别使用整数或字符串来指定读取。
MultiIndex读取
read_excel能用MultiIndex读取多个索引，通过index_col方法来传递列的列表和header将行的列表传递给MultiIndex的列。无论是index还是columns，如果已经具有序列化的层级名称，则可以通过指定组成层级的行/列来读取它们。
例如，用MultiIndex读取没有名称的索引：
In [314]: df = pd.DataFrame({'a': [1, 2, 3, 4], 'b': [5, 6, 7, 8]},
.....: index=pd.MultiIndex.from_product([['a', 'b'], ['c', 'd']]))
.....:
In [315]: df.to_excel('path_to_file.xlsx')
In [316]: df = pd.read_excel('path_to_file.xlsx', index_col=[0, 1])
In [317]: df
Out[317]:
a b
a c 1 5
d 2 6
b c 3 7
d 4 8
如果索引具有层级名称，它们将使用相同的参数进行解析：
In [318]: df.index = df.index.set_names(['lvl1', 'lvl2'])
In [319]: df.to_excel('path_to_file.xlsx')
In [320]: df = pd.read_excel('path_to_file.xlsx', index_col=[0, 1])
In [321]: df
Out[321]:
a b
lvl1 lvl2
a c 1 5
d 2 6
b c 3 7
d 4 8
如果源文件具有MultiIndex索引和多列，那么可以使用index_col和header指定列表的每个值。
In [322]: df.columns = pd.MultiIndex.from_product([['a'], ['b', 'd']],
.....: names=['c1', 'c2'])
.....:
In [323]: df.to_excel('path_to_file.xlsx')
In [324]: df = pd.read_excel('path_to_file.xlsx', index_col=[0, 1], header=[0, 1])
In [325]: df
Out[325]:
c1 a
c2 b d
lvl1 lvl2
a c 1 5
d 2 6
b c 3 7
d 4 8
解析特定的列
常常会有这样的情况，当用户想要插入几列数据到Excel表格里面作为临时计算，但是你又不想要读取这些列的时候，read_excel提供的usecols方法就派上用场了，它让你可以解析指定的列。
Deprecated since version 0.24.0.
不推荐usecols方法使用单个整数值，请在usecols中使用包括从0开始的整数列表。
如果usecols是一个整数，那么它将被认为是暗示解析最后一列。
pd.read_excel('path_to_file.xls', 'Sheet1', usecols=2)
你也可以将逗号分隔的一组Excel列和范围指定为字符串：
pd.read_excel('path_to_file.xls', 'Sheet1', usecols='A,C:E')
如果usecols是一组整数列，那么将认为是解析的文件列索引。
pd.read_excel('path_to_file.xls', 'Sheet1', usecols=[0, 2, 3])
元素的顺序是可以忽略的，因此usecols=[0, 1]是等价于[1, 0]的。
New in version 0.24.
如果usecols是字符串列表，那么可以认为每个字符串对应的就是表格的每一个列名，列名是由name中的用户提供或从文档标题行推断出来。这些字符串定义了那些列将要被解析：
pd.read_excel('path_to_file.xls', 'Sheet1', usecols=['foo', 'bar'])
元素的顺序同样被忽略，因此usecols=['baz', 'joe']等同于['joe', 'baz']。
New in version 0.24.
如果usecols是可调用的，那么该调用函数将会根据列名来调用，也会返回根据可调用函数为True的列名。
pd.read_excel('path_to_file.xls', 'Sheet1', usecols=lambda x: x.isalpha())
解析日期
当读取excel文件的时候，像日期时间的值通常会自动转换为恰当的dtype(数据类型)。但是如果你有一列字符串看起来很像日期（实际上并不是excel里面的日期格式），那么你就能使用parse_dates方法来解析这些字符串为日期：
pd.read_excel('path_to_file.xls', 'Sheet1', parse_dates=['date_strings'])
单元格转换
Excel里面的单元格内容是可以通过converters方法来进行转换的。例如，把一列转换为布尔值：
pd.read_excel('path_to_file.xls', 'Sheet1', converters={'MyBools': bool})
这个方法可以处理缺失值并且能对缺失的数据进行如期的转换。由于转换是在单元格之间发生而不是整列，因此不能保证dtype为数组。例如一列含有缺失值的整数是不能转换为具有整数dtype的数组，因为NaN严格的被认为是浮点数。你能够手动地标记缺失数据为恢复整数dtype：
def cfun(x):
return int(x) if x else -1
pd.read_excel('path_to_file.xls', 'Sheet1', converters={'MyInts': cfun})
数据类型规范
New in version 0.20.
作为另一个种转换器，使用dtype能指定整列地类型，它能让字典映射列名为数据类型。使用str或object来转译不能判断类型的数据：
pd.read_excel('path_to_file.xls', dtype={'MyInts': 'int64', 'MyText': str})
写入Excel文件
写入Excel文件到磁盘
你可以使用to_excel方法把DataFrame对象写入到Excel文件的一张表格中。它的参数大部分同前面to_csv 提到的相同，第一个参数是excel文件的名字，而可选的第二个参数是DataFrame应该写入的表格名称，例如：
df.to_excel('path_to_file.xlsx', sheet_name='Sheet1')
文件以.xls 结尾的将用xlwt写入，而那些以.xlsx结尾的则使用xlsxwriter（如果可用的话）或openpyxl来写入。
DataFrame 将尝试以模拟REPL（“读取-求值-输出" 循环的简写）输出的方式写入。index_label将代替第一行放置到第二行，你也能放置它到第一行通过在to_excel()里设置merge_cells选项为False:
df.to_excel('path_to_file.xlsx', index_label='label', merge_cells=False)
为了把DataFrames数据分开写入Excel文件的不同表格中，可以使用ExcelWriter方法。
with pd.ExcelWriter('path_to_file.xlsx') as writer:
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
注意
为了从read_excel内部获取更多点的性能，Excel存储所有数值型数据为浮点数。但这会产生意外的情况当读取数据的时候，如果没有损失信息的话（1.0 --> 1)，pandas默认的转换整数为浮点数。你可以通过convert_float=False禁止这种行为，这可能会在性能上有轻微的优化。
写入Excel文件到内存
Pandas支持写入Excel文件到类缓存区对象如StringIO或BytesIO，使用ExcelWriter方法。
# Safe import for either Python 2.x or 3.x
try:
from io import BytesIO
except ImportError:
from cStringIO import StringIO as BytesIO
bio = BytesIO()
# By setting the 'engine' in the ExcelWriter constructor.
writer = pd.ExcelWriter(bio, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
# Save the workbook
writer.save()
# Seek to the beginning and read to copy the workbook to a variable in memory
bio.seek(0)
workbook = bio.read()
注意
虽然engine是可选方法，但是推荐使用。设置engine决定了工作簿生成的版本。设置engine='xlrd'将生成 Excel 2003版的工作簿（xls）。而使用'openpyxl'或'xlsxwriter'将生成Excel 2007版的工作簿（xlsx)。如果省略，将直接生成Excel 2007版的。
Excel写入引擎
Pandas选择Excel写入有两种方式：
- 使用
engine参数 - 文件名的扩展（通过默认的配置方式指定）
默认的，pandas使用 XlsxWriter为.xlsx，使用openpyxl为.xlsm，并且使用xlwt为.xls文件。如果你安装了多个引擎，你可以通过setting the config optionsio.excel.xlsx.writer和io.excel.xls.writer方法设置默认引擎。如果 XlsxWriter不可用，pandas将回退使用openpyxl为xlsx文件。
为了指定你想要使用的写入方式，你可以设置引擎的主要参数为to_excel和ExcelWriter。内置引擎是：
openpyxl: 要求2.4或者更高的版本。xlsxwriterxlwt
# By setting the 'engine' in the DataFrame 'to_excel()' methods.
df.to_excel('path_to_file.xlsx', sheet_name='Sheet1', engine='xlsxwriter')
# By setting the 'engine' in the ExcelWriter constructor.
writer = pd.ExcelWriter('path_to_file.xlsx', engine='xlsxwriter')
# Or via pandas configuration.
from pandas import options # noqa: E402
options.io.excel.xlsx.writer = 'xlsxwriter'
df.to_excel('path_to_file.xlsx', sheet_name='Sheet1')
样式
通过pandas产生的Excel工作表的样式可以使用DataFrame的to_excel方法的以下参数进行修改。
float_format：格式化字符串用于浮点数（默认是None)。freeze_panes：两个整数的元组，表示要固化的最底行和最右列。这些参数中的每个都是以1为底，因此(1, 1)将固化第一行和第一列（默认是None)。
使用 XlsxWriter引擎提供的多种方法来修改用to_excel方法创建的Excel工作表的样式。你能在 XlsxWriter文档里面找到绝佳的例子：https://xlsxwriter.readthedocs.io/working_with_pandas.html
OpenDocument 电子表格
New in version 0.25.
read_excel方法也能使用odfpy模块来读取OpenDocument电子表格。读取OpenDocument电子表格的语法和方法同使用engine='odf'来操作Excel files的方法类似。
# Returns a DataFrame
pd.read_excel('path_to_file.ods', engine='odf')
注意
目前pandas仅支持读取OpenDocument电子表格，写入是不行的。
剪贴板
使用read_clipboard()方法是一种便捷的获取数据的方式，通过把剪贴的内容暂存，然后传递给read_csv方法。例如，你可以复制以下文本来剪贴（在许多操作系统上是CTRL-C）：
A B C
x 1 4 p
y 2 5 q
z 3 6 r
接着直接使用DataFrame来导入数据：
>>> clipdf = pd.read_clipboard()
>>> clipdf
A B C
x 1 4 p
y 2 5 q
z 3 6 r
to_clipboard方法可以把DataFrame内容写入到剪贴板。使用下面的方法你可以粘贴剪贴板的内容到其他应用（在许多系统中用的是CTRL-V）。这里我们解释一下如何使用DataFrame把内容写入到剪贴板并读回。
>>> df = pd.DataFrame({'A': [1, 2, 3],
... 'B': [4, 5, 6],
... 'C': ['p', 'q', 'r']},
... index=['x', 'y', 'z'])
>>> df
A B C
x 1 4 p
y 2 5 q
z 3 6 r
>>> df.to_clipboard()
>>> pd.read_clipboard()
A B C
x 1 4 p
y 2 5 q
z 3 6 r
我们可以看到返回了同样的内容，那就是我们早先写入剪贴板的内容。
注意
要使用上面的这些方法，你可能需要在Linux上面安装(带有PyQt5, PyQt4 or qtpy)的xclip或者xsel 。
序列化(Pickling)
所有的pandas对象都具有to_pickle方法，该方法使用Python的 cPickle模块以序列化格式存储数据结构到磁盘上。
In [326]: df
Out[326]:
c1 a
c2 b d
lvl1 lvl2
a c 1 5
d 2 6
b c 3 7
d 4 8
In [327]: df.to_pickle('foo.pkl')
在pandas中命名的read_pickle函数能够从文件中加载任意序列化的pandas对象（或者任何其他的序列化对象）：
In [328]: pd.read_pickle('foo.pkl')
Out[328]:
c1 a
c2 b d
lvl1 lvl2
a c 1 5
d 2 6
b c 3 7
d 4 8
警告
加载来自不信任来源的序列化数据是不安全的。 参见：https://docs.python.org/3/library/pickle.html
警告
read_pickle()仅在pandas的0.20.3版本及以下版本兼容。
压缩序列化文件
New in version 0.20.0.
read_pickle()，DataFrame.to_pickle()和Series.to_pickle()能够读取和写入压缩的序列化文件。读取和写入所支持的压缩文件类型有gzip, bz2, xz。zip文件格式仅支持读取，并且必须仅包含一个要读取的数据文件。
压缩类型可以是显式参数，也可以从文件扩展名中推断出来。如果文件名是以'.gz'， '.bz2'， '.zip'， 或者 '.xz'结尾的，那么可以推断出应分别使用gzip， bz2，zip，或 xz压缩类型。
In [329]: df = pd.DataFrame({
.....: 'A': np.random.randn(1000),
.....: 'B': 'foo',
.....: 'C': pd.date_range('20130101', periods=1000, freq='s')})
.....:
In [330]: df
Out[330]:
A B C
0 -0.288267 foo 2013-01-01 00:00:00
1 -0.084905 foo 2013-01-01 00:00:01
2 0.004772 foo 2013-01-01 00:00:02
3 1.382989 foo 2013-01-01 00:00:03
4 0.343635 foo 2013-01-01 00:00:04
.. ... ... ...
995 -0.220893 foo 2013-01-01 00:16:35
996 0.492996 foo 2013-01-01 00:16:36
997 -0.461625 foo 2013-01-01 00:16:37
998 1.361779 foo 2013-01-01 00:16:38
999 -1.197988 foo 2013-01-01 00:16:39
[1000 rows x 3 columns]
使用显式压缩类型：
In [331]: df.to_pickle("data.pkl.compress", compression="gzip")
In [332]: rt = pd.read_pickle("data.pkl.compress", compression="gzip")
In [333]: rt
Out[333]:
A B C
0 -0.288267 foo 2013-01-01 00:00:00
1 -0.084905 foo 2013-01-01 00:00:01
2 0.004772 foo 2013-01-01 00:00:02
3 1.382989 foo 2013-01-01 00:00:03
4 0.343635 foo 2013-01-01 00:00:04
.. ... ... ...
995 -0.220893 foo 2013-01-01 00:16:35
996 0.492996 foo 2013-01-01 00:16:36
997 -0.461625 foo 2013-01-01 00:16:37
998 1.361779 foo 2013-01-01 00:16:38
999 -1.197988 foo 2013-01-01 00:16:39
[1000 rows x 3 columns]
从扩展名推断压缩类型：
In [334]: df.to_pickle("data.pkl.xz", compression="infer")
In [335]: rt = pd.read_pickle("data.pkl.xz", compression="infer")
In [336]: rt
Out[336]:
A B C
0 -0.288267 foo 2013-01-01 00:00:00
1 -0.084905 foo 2013-01-01 00:00:01
2 0.004772 foo 2013-01-01 00:00:02
3 1.382989 foo 2013-01-01 00:00:03
4 0.343635 foo 2013-01-01 00:00:04
.. ... ... ...
995 -0.220893 foo 2013-01-01 00:16:35
996 0.492996 foo 2013-01-01 00:16:36
997 -0.461625 foo 2013-01-01 00:16:37
998 1.361779 foo 2013-01-01 00:16:38
999 -1.197988 foo 2013-01-01 00:16:39
[1000 rows x 3 columns]
默认是使用“推断”：
In [337]: df.to_pickle("data.pkl.gz")
In [338]: rt = pd.read_pickle("data.pkl.gz")
In [339]: rt
Out[339]:
A B C
0 -0.288267 foo 2013-01-01 00:00:00
1 -0.084905 foo 2013-01-01 00:00:01
2 0.004772 foo 2013-01-01 00:00:02
3 1.382989 foo 2013-01-01 00:00:03
4 0.343635 foo 2013-01-01 00:00:04
.. ... ... ...
995 -0.220893 foo 2013-01-01 00:16:35
996 0.492996 foo 2013-01-01 00:16:36
997 -0.461625 foo 2013-01-01 00:16:37
998 1.361779 foo 2013-01-01 00:16:38
999 -1.197988 foo 2013-01-01 00:16:39
[1000 rows x 3 columns]
In [340]: df["A"].to_pickle("s1.pkl.bz2")
In [341]: rt = pd.read_pickle("s1.pkl.bz2")
In [342]: rt
Out[342]:
0 -0.288267
1 -0.084905
2 0.004772
3 1.382989
4 0.343635
...
995 -0.220893
996 0.492996
997 -0.461625
998 1.361779
999 -1.197988
Name: A, Length: 1000, dtype: float64
msgpack（一种二进制格式）
pandas支持msgpack格式的对象序列化。他是一种轻量级可移植的二进制格式，同二进制的JSON类似，具有高效的空间利用率以及不错的写入（序列化）和读取（反序列化）性能。
警告
从0.25版本开始，不推荐使用msgpack格式，并且之后的版本也将删除它。推荐使用pyarrow对pandas对象进行在线的转换。
警告
read_msgpack()仅在pandas的0.20.3版本及以下版本兼容。
In [343]: df = pd.DataFrame(np.random.rand(5, 2), columns=list('AB'))
In [344]: df.to_msgpack('foo.msg')
In [345]: pd.read_msgpack('foo.msg')
Out[345]:
A B
0 0.275432 0.293583
1 0.842639 0.165381
2 0.608925 0.778891
3 0.136543 0.029703
4 0.318083 0.604870
In [346]: s = pd.Series(np.random.rand(5), index=pd.date_range('20130101', periods=5))
你可以传递一组对象列表并得到反序列化的结果。
In [347]: pd.to_msgpack('foo.msg', df, 'foo', np.array([1, 2, 3]), s)
In [348]: pd.read_msgpack('foo.msg')
Out[348]:
[ A B
0 0.275432 0.293583
1 0.842639 0.165381
2 0.608925 0.778891
3 0.136543 0.029703
4 0.318083 0.604870, 'foo', array([1, 2, 3]), 2013-01-01 0.330824
2013-01-02 0.790825
2013-01-03 0.308468
2013-01-04 0.092397
2013-01-05 0.703091
Freq: D, dtype: float64]
你能传递iterator=True参数来迭代解压后的结果：
In [349]: for o in pd.read_msgpack('foo.msg', iterator=True):
.....: print(o)
.....:
A B
0 0.275432 0.293583
1 0.842639 0.165381
2 0.608925 0.778891
3 0.136543 0.029703
4 0.318083 0.604870
foo
[1 2 3]
2013-01-01 0.330824
2013-01-02 0.790825
2013-01-03 0.308468
2013-01-04 0.092397
2013-01-05 0.703091
Freq: D, dtype: float64
你也能传递append=True参数，给现有的包添加写入：
In [350]: df.to_msgpack('foo.msg', append=True)
In [351]: pd.read_msgpack('foo.msg')
Out[351]:
[ A B
0 0.275432 0.293583
1 0.842639 0.165381
2 0.608925 0.778891
3 0.136543 0.029703
4 0.318083 0.604870, 'foo', array([1, 2, 3]), 2013-01-01 0.330824
2013-01-02 0.790825
2013-01-03 0.308468
2013-01-04 0.092397
2013-01-05 0.703091
Freq: D, dtype: float64, A B
0 0.275432 0.293583
1 0.842639 0.165381
2 0.608925 0.778891
3 0.136543 0.029703
4 0.318083 0.604870]
不像其他io方法，to_msgpack既可以基于每个对象使用df.to_msgpack()方法，也可以在混合pandas对象的时候使用顶层pd.to_msgpack(...)方法，该方法可以让你打包任意的Python列表、字典、标量的集合。
In [352]: pd.to_msgpack('foo2.msg', {'dict': [{'df': df}, {'string': 'foo'},
.....: {'scalar': 1.}, {'s': s}]})
.....:
In [353]: pd.read_msgpack('foo2.msg')
Out[353]:
{'dict': ({'df': A B
0 0.275432 0.293583
1 0.842639 0.165381
2 0.608925 0.778891
3 0.136543 0.029703
4 0.318083 0.604870},
{'string': 'foo'},
{'scalar': 1.0},
{'s': 2013-01-01 0.330824
2013-01-02 0.790825
2013-01-03 0.308468
2013-01-04 0.092397
2013-01-05 0.703091
Freq: D, dtype: float64})}
读/写API
Msgpacks也能读写字符串。
In [354]: df.to_msgpack()
Out[354]: b'\x84\xa3typ\xadblock_manager\xa5klass\xa9DataFrame\xa4axes\x92\x86\xa3typ\xa5index\xa5klass\xa5Index\xa4name\xc0\xa5dtype\xa6object\xa4data\x92\xa1A\xa1B\xa8compress\xc0\x86\xa3typ\xabrange_index\xa5klass\xaaRangeIndex\xa4name\xc0\xa5start\x00\xa4stop\x05\xa4step\x01\xa6blocks\x91\x86\xa4locs\x86\xa3typ\xa7ndarray\xa5shape\x91\x02\xa4ndim\x01\xa5dtype\xa5int64\xa4data\xd8\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\xa8compress\xc0\xa6values\xc7P\x00\xc84 \x84\xac\xa0\xd1?\x0f\xa4.\xb5\xe6\xf6\xea?\xb9\x85\x9aLO|\xe3?\xac\xf0\xd7\x81>z\xc1?\\\xca\x97\ty[\xd4?\x9c\x9b\x8a:\x11\xca\xd2?\x14zX\xd01+\xc5?4=\x19b\xad\xec\xe8?\xc0!\xe9\xf4\x8ej\x9e?\xa7>_\xac\x17[\xe3?\xa5shape\x92\x02\x05\xa5dtype\xa7float64\xa5klass\xaaFloatBlock\xa8compress\xc0'
此外你可以连接字符串生成一个原始的对象列表。
In [355]: pd.read_msgpack(df.to_msgpack() + s.to_msgpack())
Out[355]:
[ A B
0 0.275432 0.293583
1 0.842639 0.165381
2 0.608925 0.778891
3 0.136543 0.029703
4 0.318083 0.604870, 2013-01-01 0.330824
2013-01-02 0.790825
2013-01-03 0.308468
2013-01-04 0.092397
2013-01-05 0.703091
Freq: D, dtype: float64]
HDF5(PyTables) (一种以.h5结尾的分层数据格式）
HDFStore是一个能读写pandas的类似字典的对象，它能使用高性能的HDF5格式，该格式是用优秀的PyTables库写的。一些更高级的用法参考cookbook。
警告
pandas要求使用的PyTables版本要 > = 3.0.0。当使用索引来检索存储的时候，PyTables< 3.2的版本会出现索引bug。如果返回一个结果的子集，那么你就需要升级PyTables 的版本 >= 3.2才行。先前创建的存储数据将会使用更新后的版本再次写入。
In [356]: store = pd.HDFStore('store.h5')
In [357]: print(store)
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
对象能够被写入文件就像成对的键-值添加到字典里面一样：
In [358]: index = pd.date_range('1/1/2000', periods=8)
In [359]: s = pd.Series(np.random.randn(5), index=['a', 'b', 'c', 'd', 'e'])
In [360]: df = pd.DataFrame(np.random.randn(8, 3), index=index,
.....: columns=['A', 'B', 'C'])
.....:
# store.put('s', s) is an equivalent method
In [361]: store['s'] = s
In [362]: store['df'] = df
In [363]: store
Out[363]:
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
在当前或者之后的Python会话中，你都能检索存储的对象：
# store.get('df') is an equivalent method
In [364]: store['df']
Out[364]:
A B C
2000-01-01 -0.426936 -1.780784 0.322691
2000-01-02 1.638174 -2.184251 0.049673
2000-01-03 -1.022803 0.889445 2.827717
2000-01-04 1.767446 -1.305266 -0.378355
2000-01-05 0.486743 0.954551 0.859671
2000-01-06 -1.170458 -1.211386 -0.852728
2000-01-07 -0.450781 1.064650 1.014927
2000-01-08 -0.810399 0.254343 -0.875526
# dotted (attribute) access provides get as well
In [365]: store.df
Out[365]:
A B C
2000-01-01 -0.426936 -1.780784 0.322691
2000-01-02 1.638174 -2.184251 0.049673
2000-01-03 -1.022803 0.889445 2.827717
2000-01-04 1.767446 -1.305266 -0.378355
2000-01-05 0.486743 0.954551 0.859671
2000-01-06 -1.170458 -1.211386 -0.852728
2000-01-07 -0.450781 1.064650 1.014927
2000-01-08 -0.810399 0.254343 -0.875526
使用键删除指定的对象：
# store.remove('df') is an equivalent method
In [366]: del store['df']
In [367]: store
Out[367]:
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
关闭存储对象并使用环境管理器：
In [368]: store.close()
In [369]: store
Out[369]:
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
In [370]: store.is_open
Out[370]: False
# Working with, and automatically closing the store using a context manager
In [371]: with pd.HDFStore('store.h5') as store:
.....: store.keys()
.....:
读/写 API
HDFStore 支持顶层的API，用read_hdf来读取，和使用to_hdf来写入，类似于read_csv 和to_csv的用法。
In [372]: df_tl = pd.DataFrame({'A': list(range(5)), 'B': list(range(5))})
In [373]: df_tl.to_hdf('store_tl.h5', 'table', append=True)
In [374]: pd.read_hdf('store_tl.h5', 'table', where=['index>2'])
Out[374]:
A B
3 3 3
4 4 4
HDFStore默认不会删除全是缺失值的行，但是通过设置dropna=True参数就能改变。
In [375]: df_with_missing = pd.DataFrame({'col1': [0, np.nan, 2],
.....: 'col2': [1, np.nan, np.nan]})
.....:
In [376]: df_with_missing
Out[376]:
col1 col2
0 0.0 1.0
1 NaN NaN
2 2.0 NaN
In [377]: df_with_missing.to_hdf('file.h5', 'df_with_missing',
.....: format='table', mode='w')
.....:
In [378]: pd.read_hdf('file.h5', 'df_with_missing')
Out[378]:
col1 col2
0 0.0 1.0
1 NaN NaN
2 2.0 NaN
In [379]: df_with_missing.to_hdf('file.h5', 'df_with_missing',
.....: format='table', mode='w', dropna=True)
.....:
In [380]: pd.read_hdf('file.h5', 'df_with_missing')
Out[380]:
col1 col2
0 0.0 1.0
2 2.0 NaN
固定格式
上面的例子表明了使用put进行存储的情况，该存储将HDF5以固定数组格式写入PyTables，这就是所谓的fixed格式。这些类型的存储一旦被写入后将不能再添加数据（虽然你能轻易地删除它们并再次写入），也不能查询；必须全部检索它们。它们也不支持没有唯一列名的数据表。fixed格式提供了非常快速的写入功能，并且比table存储在读取方面更快捷。默认的指定格式是使用put 或者to_hdf 亦或通过 format='fixed'或 format='f'格式。
警告
如果你尝试使用where来检索，fixed格式将会报错 TypeError：
>>> pd.DataFrame(np.random.randn(10, 2)).to_hdf('test_fixed.h5', 'df')
>>> pd.read_hdf('test_fixed.h5', 'df', where='index>5')
TypeError: cannot pass a where specification when reading a fixed format.
this store must be selected in its entirety
表格格式
HDFStore 支持在磁盘上使用另一种PyTables格式，即table格式。从概念上来讲，table在外形上同具有行和列的DataFrame极度相似。table也能被添加到同样的或其他的会话中。此外，删除和查询操作也是支持的。通过指定格式为format='table'或format='t'到append方法或put或者to_hdf。
put/append/to_hdf方法中使用的格式也可以设置为可选pd.set_option('io.hdf.default_format','table')，以默认的table格式存储。
In [381]: store = pd.HDFStore('store.h5')
In [382]: df1 = df[0:4]
In [383]: df2 = df[4:]
# append data (creates a table automatically)
In [384]: store.append('df', df1)
In [385]: store.append('df', df2)
In [386]: store
Out[386]:
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
# select the entire object
In [387]: store.select('df')
Out[387]:
A B C
2000-01-01 -0.426936 -1.780784 0.322691
2000-01-02 1.638174 -2.184251 0.049673
2000-01-03 -1.022803 0.889445 2.827717
2000-01-04 1.767446 -1.305266 -0.378355
2000-01-05 0.486743 0.954551 0.859671
2000-01-06 -1.170458 -1.211386 -0.852728
2000-01-07 -0.450781 1.064650 1.014927
2000-01-08 -0.810399 0.254343 -0.875526
# the type of stored data
In [388]: store.root.df._v_attrs.pandas_type
Out[388]: 'frame_table'
注意
你也可以通过创建table来传递format='table'或者 format='t到put操作。
分层键
存储的键能够指定为字符串，这些分层的路径名就像这样的格式（例如：foo/bar/bah）。它将生成子存储的层次结构（或者在PyTables中叫做Groups ）。键可以不带前面的'/'指定而且总是单独的（例如：'foo' 指的就是'/foo'）。删除操作能够删除所有子存储及之后的数据，所以要小心该操作。
In [389]: store.put('foo/bar/bah', df)
In [390]: store.append('food/orange', df)
In [391]: store.append('food/apple', df)
In [392]: store
Out[392]:
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
# a list of keys are returned
In [393]: store.keys()
Out[393]: ['/df', '/food/apple', '/food/orange', '/foo/bar/bah']
# remove all nodes under this level
In [394]: store.remove('food')
In [395]: store
Out[395]:
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5
你能遍历组层次结构使用walk方法，该方法将为每个组键及其内容的相对键生成一个元组。
New in version 0.24.0.
In [396]: for (path, subgroups, subkeys) in store.walk():
.....: for subgroup in subgroups:
.....: print('GROUP: {}/{}'.format(path, subgroup))
.....: for subkey in subkeys:
.....: key = '/'.join([path, subkey])
.....: print('KEY: {}'.format(key))
.....: print(store.get(key))
.....:
GROUP: /foo
KEY: /df
A B C
2000-01-01 -0.426936 -1.780784 0.322691
2000-01-02 1.638174 -2.184251 0.049673
2000-01-03 -1.022803 0.889445 2.827717
2000-01-04 1.767446 -1.305266 -0.378355
2000-01-05 0.486743 0.954551 0.859671
2000-01-06 -1.170458 -1.211386 -0.852728
2000-01-07 -0.450781 1.064650 1.014927
2000-01-08 -0.810399 0.254343 -0.875526
GROUP: /foo/bar
KEY: /foo/bar/bah
A B C
2000-01-01 -0.426936 -1.780784 0.322691
2000-01-02 1.638174 -2.184251 0.049673
2000-01-03 -1.022803 0.889445 2.827717
2000-01-04 1.767446 -1.305266 -0.378355
2000-01-05 0.486743 0.954551 0.859671
2000-01-06 -1.170458 -1.211386 -0.852728
2000-01-07 -0.450781 1.064650 1.014927
2000-01-08 -0.810399 0.254343 -0.875526
警告
分层键对于存储在根节点下的项目，无法使用如上的方法将其作为点（属性）进行检索。
In [8]: store.foo.bar.bah
AttributeError: 'HDFStore' object has no attribute 'foo'
# you can directly access the actual PyTables node but using the root node
In [9]: store.root.foo.bar.bah
Out[9]:
/foo/bar/bah (Group) ''
children := ['block0_items' (Array), 'block0_values' (Array), 'axis0' (Array), 'axis1' (Array)]
相反，使用基于显式字符串的键：
In [397]: store['foo/bar/bah']
Out[397]:
A B C
2000-01-01 -0.426936 -1.780784 0.322691
2000-01-02 1.638174 -2.184251 0.049673
2000-01-03 -1.022803 0.889445 2.827717
2000-01-04 1.767446 -1.305266 -0.378355
2000-01-05 0.486743 0.954551 0.859671
2000-01-06 -1.170458 -1.211386 -0.852728
2000-01-07 -0.450781 1.064650 1.014927
2000-01-08 -0.810399 0.254343 -0.875526
存储类型
在表格中存储混合类型
支持混合数据类型存储。字符串使用添加列的最大尺寸以固定宽度进行存储。后面尝试添加更长的字符串将会报错ValueError。
添加参数min_itemsize={`values`: size}将给字符串列设置一个更大的最小值。目前支持的存储类型有 floats,strings, ints, bools, datetime64 。对于字符串列，添加参数 nan_rep = 'nan'将改变磁盘上默认的nan值（转变为np.nan），原本默认是nan。
In [398]: df_mixed = pd.DataFrame({'A': np.random.randn(8),
.....: 'B': np.random.randn(8),
.....: 'C': np.array(np.random.randn(8), dtype='float32'),
.....: 'string': 'string',
.....: 'int': 1,
.....: 'bool': True,
.....: 'datetime64': pd.Timestamp('20010102')},
.....: index=list(range(8)))
.....:
In [399]: df_mixed.loc[df_mixed.index[3:5],
.....: ['A', 'B', 'string', 'datetime64']] = np.nan
.....:
In [400]: store.append('df_mixed', df_mixed, min_itemsize={'values': 50})
In [401]: df_mixed1 = store.select('df_mixed')
In [402]: df_mixed1
Out[402]:
A B C string int bool datetime64
0 -0.980856 0.298656 0.151508 string 1 True 2001-01-02
1 -0.906920 -1.294022 0.587939 string 1 True 2001-01-02
2 0.988185 -0.618845 0.043096 string 1 True 2001-01-02
3 NaN NaN 0.362451 NaN 1 True NaT
4 NaN NaN 1.356269 NaN 1 True NaT
5 -0.772889 -0.340872 1.798994 string 1 True 2001-01-02
6 -0.043509 -0.303900 0.567265 string 1 True 2001-01-02
7 0.768606 -0.871948 -0.044348 string 1 True 2001-01-02
In [403]: df_mixed1.dtypes.value_counts()
Out[403]:
float64 2
float32 1
datetime64[ns] 1
int64 1
bool 1
object 1
dtype: int64
# we have provided a minimum string column size
In [404]: store.root.df_mixed.table
Out[404]:
/df_mixed/table (Table(8,)) ''
description := {
"index": Int64Col(shape=(), dflt=0, pos=0),
"values_block_0": Float64Col(shape=(2,), dflt=0.0, pos=1),
"values_block_1": Float32Col(shape=(1,), dflt=0.0, pos=2),
"values_block_2": Int64Col(shape=(1,), dflt=0, pos=3),
"values_block_3": Int64Col(shape=(1,), dflt=0, pos=4),
"values_block_4": BoolCol(shape=(1,), dflt=False, pos=5),
"values_block_5": StringCol(itemsize=50, shape=(1,), dflt=b'', pos=6)}
byteorder := 'little'
chunkshape := (689,)
autoindex := True
colindexes := {
"index": Index(6, medium, shuffle, zlib(1)).is_csi=False}
存储多层索引数据表
存储多层索引DataFrames为表格与从同类索引 DataFrames中存储/选取是非常类似的。
In [405]: index = pd.MultiIndex(levels=[['foo', 'bar', 'baz', 'qux'],
.....: ['one', 'two', 'three']],
.....: codes=[[0, 0, 0, 1, 1, 2, 2, 3, 3, 3],
.....: [0, 1, 2, 0, 1, 1, 2, 0, 1, 2]],
.....: names=['foo', 'bar'])
.....:
In [406]: df_mi = pd.DataFrame(np.random.randn(10, 3), index=index,
.....: columns=['A', 'B', 'C'])
.....:
In [407]: df_mi
Out[407]:
A B C
foo bar
foo one 0.031885 0.641045 0.479460
two -0.630652 -0.182400 -0.789979
three -0.282700 -0.813404 1.252998
bar one 0.758552 0.384775 -1.133177
two -1.002973 -1.644393 -0.311536
baz two -0.615506 -0.084551 -1.318575
three 0.923929 -0.105981 0.429424
qux one -1.034590 0.542245 -0.384429
two 0.170697 -0.200289 1.220322
three -1.001273 0.162172 0.376816
In [408]: store.append('df_mi', df_mi)
In [409]: store.select('df_mi')
Out[409]:
A B C
foo bar
foo one 0.031885 0.641045 0.479460
two -0.630652 -0.182400 -0.789979
three -0.282700 -0.813404 1.252998
bar one 0.758552 0.384775 -1.133177
two -1.002973 -1.644393 -0.311536
baz two -0.615506 -0.084551 -1.318575
three 0.923929 -0.105981 0.429424
qux one -1.034590 0.542245 -0.384429
two 0.170697 -0.200289 1.220322
three -1.001273 0.162172 0.376816
# the levels are automatically included as data columns
In [410]: store.select('df_mi', 'foo=bar')
Out[410]:
A B C
foo bar
bar one 0.758552 0.384775 -1.133177
two -1.002973 -1.644393 -0.311536
查询
查询表格
select 和 delete 操作有一个可选项即能指定选择/删除仅有数据的子集。 这允许用户拥有一个很大的磁盘表并仅检索一部分数据。
在底层里使用Term 类指定查询为布尔表达式。
- 支持的
DataFrames索引器有index和columns. - 如果指定为
data_columns，这些将作为额外的索引器。
有效的比较运算符有：
=, ==, !=, >, >=, <, <=
有效的布尔表达式包含如下几种：
|: 选择&: 并列(和): 用来分组
这些规则同在pandas的索引中使用布尔表达式是类似的。
注意
=将自动扩展为比较运算符==~不是运算符，且只在有限的条件下使用- 如果传递的表达式时列表/元组，他们将通过
&符号合并
以下都是有效的表达式：
'index >= date'"columns = ['A', 'D']""columns in ['A', 'D']"'columns = A''columns == A'"~(columns = ['A', 'B'])"'index > df.index[3] & string = "bar"''(index > df.index[3] & index <= df.index[6]) | string = "bar"'"ts >= Timestamp('2012-02-01')""major_axis>=20130101"
indexers在子表达式的左边的有： columns, major_axis, ts
（在比较运算符后面）子表达式可以是：
- 能被求值的函数，比如：
Timestamp('2012-02-01') - 字符串，比如：
"bar" - 类似日期，比如：
20130101或者"20130101" - 列表，比如：
"['A', 'B']" - 以本地命名空间定义的变量，比如：
date
注意
在查询表达式中插入字符串进行查询是不推荐的。如果将带有%的字符串分配给变量，然后在表达式中使用该变量。那么，这样做
string = "HolyMoly'"
store.select('df', 'index == string')
来代替下面这样
string = "HolyMoly'"
store.select('df', 'index == %s' % string)
因为后者将 不会 起作用并引起 SyntaxError。注意 string变量的双引号里面有一个单引号。
如果你一定要插入，使用说明符格式 '%r'
store.select('df', 'index == %r' % string)
它将会引用变量 string.
这儿有一些例子：
In [411]: dfq = pd.DataFrame(np.random.randn(10, 4), columns=list('ABCD'),
.....: index=pd.date_range('20130101', periods=10))
.....:
In [412]: store.append('dfq', dfq, format='table', data_columns=True)
使用布尔表达式同内联求值函数。
In [413]: store.select('dfq', "index>pd.Timestamp('20130104') & columns=['A', 'B']")
Out[413]:
A B
2013-01-05 0.450263 0.755221
2013-01-06 0.019915 0.300003
2013-01-07 1.878479 -0.026513
2013-01-08 3.272320 0.077044
2013-01-09 -0.398346 0.507286
2013-01-10 0.516017 -0.501550
内联列引用
In [414]: store.select('dfq', where="A>0 or C>0")
Out[414]:
A B C D
2013-01-01 -0.161614 -1.636805 0.835417 0.864817
2013-01-02 0.843452 -0.122918 -0.026122 -1.507533
2013-01-03 0.335303 -1.340566 -1.024989 1.125351
2013-01-05 0.450263 0.755221 -1.506656 0.808794
2013-01-06 0.019915 0.300003 -0.727093 -1.119363
2013-01-07 1.878479 -0.026513 0.573793 0.154237
2013-01-08 3.272320 0.077044 0.397034 -0.613983
2013-01-10 0.516017 -0.501550 0.138212 0.218366
关键词columns 能用来筛选列字段并返回为列表，这等价于传递'columns=list_of_columns_to_filter':
In [415]: store.select('df', "columns=['A', 'B']")
Out[415]:
A B
2000-01-01 -0.426936 -1.780784
2000-01-02 1.638174 -2.184251
2000-01-03 -1.022803 0.889445
2000-01-04 1.767446 -1.305266
2000-01-05 0.486743 0.954551
2000-01-06 -1.170458 -1.211386
2000-01-07 -0.450781 1.064650
2000-01-08 -0.810399 0.254343
start and stop 参数能指定总的搜索范围。这些是根据表中的总行数得出来的。
注意
如果查询表达式有未知的引用变量，那么select 将会报错 ValueError 。通常这就意味着你正在尝试选取的一列并不在当前数据列中。
如果查询表达式无效，那么select将会报错SyntaxError 。
timedelta64[ns]的用法
你能使用timedelta64[ns]进行存储和查询。使用()来指定查询的条目，浮点数可以带符号（和小数），timedelta的单位可以是D,s,ms,us,ns。看示例：
In [416]: from datetime import timedelta
In [417]: dftd = pd.DataFrame({'A': pd.Timestamp('20130101'),
.....: 'B': [pd.Timestamp('20130101') + timedelta(days=i,
.....: seconds=10)
.....: for i in range(10)]})
.....:
In [418]: dftd['C'] = dftd['A'] - dftd['B']
In [419]: dftd
Out[419]:
A B C
0 2013-01-01 2013-01-01 00:00:10 -1 days +23:59:50
1 2013-01-01 2013-01-02 00:00:10 -2 days +23:59:50
2 2013-01-01 2013-01-03 00:00:10 -3 days +23:59:50
3 2013-01-01 2013-01-04 00:00:10 -4 days +23:59:50
4 2013-01-01 2013-01-05 00:00:10 -5 days +23:59:50
5 2013-01-01 2013-01-06 00:00:10 -6 days +23:59:50
6 2013-01-01 2013-01-07 00:00:10 -7 days +23:59:50
7 2013-01-01 2013-01-08 00:00:10 -8 days +23:59:50
8 2013-01-01 2013-01-09 00:00:10 -9 days +23:59:50
9 2013-01-01 2013-01-10 00:00:10 -10 days +23:59:50
In [420]: store.append('dftd', dftd, data_columns=True)
In [421]: store.select('dftd', "C<'-3.5D'")
Out[421]:
A B C
4 2013-01-01 2013-01-05 00:00:10 -5 days +23:59:50
5 2013-01-01 2013-01-06 00:00:10 -6 days +23:59:50
6 2013-01-01 2013-01-07 00:00:10 -7 days +23:59:50
7 2013-01-01 2013-01-08 00:00:10 -8 days +23:59:50
8 2013-01-01 2013-01-09 00:00:10 -9 days +23:59:50
9 2013-01-01 2013-01-10 00:00:10 -10 days +23:59:50
索引
你能在表格中已经有数据的情况下（在append/put操作之后）使用create_table_index创建/修改表格的索引。给表格创建索引是强推荐的操作。当你使用带有索引的select当作where查询条件的时候，这将极大的加快你的查询速度。
注意
索引会自动创建在可索引对象和任意你指定的数据列。你可以传递index=False 到append来关闭这个操作。
# we have automagically already created an index (in the first section)
In [422]: i = store.root.df.table.cols.index.index
In [423]: i.optlevel, i.kind
Out[423]: (6, 'medium')
# change an index by passing new parameters
In [424]: store.create_table_index('df', optlevel=9, kind='full')
In [425]: i = store.root.df.table.cols.index.index
In [426]: i.optlevel, i.kind
Out[426]: (9, 'full')
通常当有大量数据添加保存的时候，关闭添加列的索引创建，等结束后再创建是非常有效的。
In [427]: df_1 = pd.DataFrame(np.random.randn(10, 2), columns=list('AB'))
In [428]: df_2 = pd.DataFrame(np.random.randn(10, 2), columns=list('AB'))
In [429]: st = pd.HDFStore('appends.h5', mode='w')
In [430]: st.append('df', df_1, data_columns=['B'], index=False)
In [431]: st.append('df', df_2, data_columns=['B'], index=False)
In [432]: st.get_storer('df').table
Out[432]:
/df/table (Table(20,)) ''
description := {
"index": Int64Col(shape=(), dflt=0, pos=0),
"values_block_0": Float64Col(shape=(1,), dflt=0.0, pos=1),
"B": Float64Col(shape=(), dflt=0.0, pos=2)}
byteorder := 'little'
chunkshape := (2730,)
当完成添加后再创建索引。
In [433]: st.create_table_index('df', columns=['B'], optlevel=9, kind='full')
In [434]: st.get_storer('df').table
Out[434]:
/df/table (Table(20,)) ''
description := {
"index": Int64Col(shape=(), dflt=0, pos=0),
"values_block_0": Float64Col(shape=(1,), dflt=0.0, pos=1),
"B": Float64Col(shape=(), dflt=0.0, pos=2)}
byteorder := 'little'