<jupyter_start><jupyter_text>IO tools (text, CSV, HDF5, …)The pandas I/O API is a set of top level `reader` functions accessed like`pandas.read_csv()` that generally return a pandas object. The corresponding`writer` functions are object methods that are accessed like`DataFrame.to_csv()`. Below is a table containing available `readers` and`writers`.[](https://en.wikipedia.org/wiki/Comma-separated_values)[](io-read-csv-table)[](io-store-in-csv)[](io-fwf-reader)[](https://www.json.org/)[](io-json-reader)[](io-json-writer)[](https://en.wikipedia.org/wiki/HTML)[](io-read-html)[](io-html)[](https://en.wikipedia.org/wiki/LaTeX)[](io-latex)[](https://www.w3.org/standards/xml/core)[](io-read-xml)[](io-xml)[](io-clipboard)[](io-clipboard)[](https://en.wikipedia.org/wiki/Microsoft_Excel)[](io-excel-reader)[](io-excel-writer)[](http://www.opendocumentformat.org)[](io-ods)[](https://support.hdfgroup.org/HDF5/whatishdf5.html)[](io-hdf5)[](io-hdf5)[](https://github.com/wesm/feather)[](io-feather)[](io-feather)[](https://parquet.apache.org/)[](io-parquet)[](io-parquet)[](https://orc.apache.org/)[](io-orc)[](https://en.wikipedia.org/wiki/Stata)[](io-stata-reader)[](io-stata-writer)[](https://en.wikipedia.org/wiki/SAS_%28software%29)[](io-sas-reader)[](https://en.wikipedia.org/wiki/SPSS)[](io-spss-reader)[](https://docs.python.org/3/library/pickle.html)[](io-pickle)[](io-pickle)[](https://en.wikipedia.org/wiki/SQL)[](io-sql)[](io-sql)[](https://en.wikipedia.org/wiki/BigQuery)[](io-bigquery)[](io-bigquery)|Format Type|Data Description|Reader|Writer||:----------------------------:|:--------------------------------------------------------------------------------------------------:|:----------------------------------------------------------:|:----------------------------------------------------------:||text|CSV|read_csv|to_csv||text|Fixed-Width Text File|read_fwf|||text|JSON|read_json|to_json||text|HTML|read_html|to_html||text|LaTeX||Styler.to_latex||text|XML|read_xml|to_xml||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|ORC Format|read_orc|||binary|Stata|read_stata|to_stata||binary|SAS|read_sas|||binary|SPSS|read_spss|||binary|Python Pickle Format|read_pickle|to_pickle||SQL|SQL|read_sql|to_sql||SQL|Google BigQuery|read_gbq|to_gbq|[Here](io-perf) is an informal performance comparison for some of these IO methods.>**Note**>>For examples that use the `StringIO` class, make sure you import itwith `from io import StringIO` for Python 3.  CSV & text filesThe workhorse function for reading text files (a.k.a. flat files) is`read_csv()`. See the [cookbook](44_cookbook.ipynbcookbook-csv) for some advanced strategies.  Parsing options`read_csv()` accepts the following common arguments:  Basicfilepath_or_buffervariousEither a path to a file (a `python:str`, `python:pathlib.Path`,or `py:py._path.local.LocalPath`), URL (including http, ftp, and S3locations), or any object with a `read()` method (such as an open file or`StringIO`).sepstr, defaults to `','` for `read_csv()`, `\t` for `read_table()`Delimiter to use. If sep is `None`, the C engine cannot automatically detectthe separator, but the Python parsing engine can, meaning the latter will beused and automatically detect the separator by Python’s builtin sniffer tool,`python:csv.Sniffer`. In addition, separators longer than 1 character anddifferent from `'\s+'` will be interpreted as regular expressions andwill also force the use of the Python parsing engine. Note that regexdelimiters are prone to ignoring quoted data. Regex example: `'\\r\\t'`.delimiterstr, default `None`Alternative argument name for sep.delim_whitespaceboolean, default FalseSpecifies whether or not whitespace (e.g. `' '` or `'\t'`)will be used as the delimiter. Equivalent to setting `sep='\s+'`.If this option is set to `True`, nothing should be passed in for the`delimiter` parameter.  Column and index locations and namesheaderint or list of ints, default `'infer'`Row number(s) to use as the column names, and the start of thedata. Default behavior is to infer the column names: if no names arepassed the behavior is identical to `header=0` and column namesare inferred from the first line of the file, if column names arepassed explicitly then the behavior is identical to`header=None`. Explicitly pass `header=0` to be able to replaceexisting names.The header can be a list of ints that specify row locationsfor a MultiIndex on the columns e.g. `[0,1,3]`. Intervening rowsthat are not specified will be skipped (e.g. 2 in this example isskipped). Note that this parameter ignores commented lines and emptylines if `skip_blank_lines=True`, so header=0 denotes the firstline of data rather than the first line of the file.namesarray-like, default `None`List of column names to use. If file contains no header row, then you shouldexplicitly pass `header=None`. Duplicates in this list are not allowed.index_colint, str, sequence of int / str, or False, default `None`Column(s) to use as the row labels of the `DataFrame`, either given asstring name or column index. If a sequence of int / str is given, aMultiIndex is used.Note: `index_col=False` can be used to force pandas to *not* use the firstcolumn as the index, e.g. when you have a malformed file with delimiters atthe end of each line.The default value of `None` instructs pandas to guess. If the number offields in the column header row is equal to the number of fields in the bodyof the data file, then a default index is used.  If it is larger, thenthe first columns are used as index so that the remaining number of fields inthe body are equal to the number of fields in the header.usecolslist-like or callable, default `None`Return a subset of the columns. If list-like, all elements must eitherbe positional (i.e. integer indices into the document columns) or stringsthat correspond to column names provided either by the user in `names` orinferred from the document header row(s). For example, a valid list-like`usecols` parameter would be `[0, 1, 2]` or `['foo', 'bar', 'baz']`.Element order is ignored, so `usecols=[0, 1]` is the same as `[1, 0]`. Toinstantiate a DataFrame from `data` with element order preserved use`pd.read_csv(data, usecols=['foo', 'bar'])[['foo', 'bar']]` for columnsin `['foo', 'bar']` order or`pd.read_csv(data, usecols=['foo', 'bar'])[['bar', 'foo']]` for`['bar', 'foo']` order.If callable, the callable function will be evaluated against the column names,returning names where the callable function evaluates to True:<jupyter_code>import pandas as pd
from io import StringIO

data = "col1,col2,col3\na,b,1\na,b,2\nc,d,3"
pd.read_csv(StringIO(data))
pd.read_csv(StringIO(data), usecols=lambda x: x.upper() in ["COL1", "COL3"])<jupyter_output><empty_output><jupyter_text>Using this parameter results in much faster parsing time and lower memory usage.squeezeboolean, default `False`If the parsed data only contains one column then return a `Series`.prefixstr, default `None`Prefix to add to column numbers when no header, e.g. ‘X’ for X0, X1, …mangle_dupe_colsboolean, default `True`Duplicate columns will be specified as ‘X’, ‘X.1’…’X.N’, rather than ‘X’…’X’.Passing in `False` will cause data to be overwritten if there are duplicatenames in the columns.  General parsing configurationdtypeType name or dict of column -> type, default `None`Data type for data or columns. E.g. `{'a': np.float64, 'b': np.int32}`(unsupported with `engine='python'`). Use `str` or `object` togetherwith suitable `na_values` settings to preserve andnot interpret dtype.engine{`'c'`, `'python'`}Parser engine to use. The C engine is faster while the Python engine iscurrently more feature-complete.convertersdict, default `None`Dict of functions for converting values in certain columns. Keys can either beintegers or column labels.true_valueslist, default `None`Values to consider as `True`.false_valueslist, default `None`Values to consider as `False`.skipinitialspaceboolean, default `False`Skip spaces after delimiter.skiprowslist-like or integer, default `None`Line numbers to skip (0-indexed) or number of lines to skip (int) at the startof the file.If callable, the callable function will be evaluated against the rowindices, returning True if the row should be skipped and False otherwise:<jupyter_code>data = "col1,col2,col3\na,b,1\na,b,2\nc,d,3"
pd.read_csv(StringIO(data))
pd.read_csv(StringIO(data), skiprows=lambda x: x % 2 != 0)<jupyter_output><empty_output><jupyter_text>skipfooterint, default `0`Number of lines at bottom of file to skip (unsupported with engine=’c’).nrowsint, default `None`Number of rows of file to read. Useful for reading pieces of large files.low_memoryboolean, default `True`Internally process the file in chunks, resulting in lower memory usewhile parsing, but possibly mixed type inference.  To ensure no mixedtypes either set `False`, or specify the type with the `dtype` parameter.Note that the entire file is read into a single `DataFrame` regardless,use the `chunksize` or `iterator` parameter to return the data in chunks.(Only valid with C parser)memory_mapboolean, default FalseIf a filepath is provided for `filepath_or_buffer`, map the file objectdirectly onto memory and access the data directly from there. Using thisoption can improve performance because there is no longer any I/O overhead.  NA and missing data handlingna_valuesscalar, str, list-like, or dict, default `None`Additional strings to recognize as NA/NaN. If dict passed, specific per-columnNA values. See [na values const](io-navaluesconst) belowfor a list of the values interpreted as NaN by default.keep_default_naboolean, 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.na_filterboolean, default `True`Detect missing value markers (empty strings and the value of na_values). Indata without any NAs, passing `na_filter=False` can improve the performanceof reading a large file.verboseboolean, default `False`Indicate number of NA values placed in non-numeric columns.skip_blank_linesboolean, default `True`If `True`, skip over blank lines rather than interpreting as NaN values.  Datetime handlingparse_datesboolean 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_formatboolean, default `False`If `True` and parse_dates is enabled for a column, attempt to infer thedatetime format to speed up the processing.keep_date_colboolean, default `False`If `True` and parse_dates specifies combining multiple columns then keep theoriginal columns.date_parserfunction, default `None`Function to use for converting a sequence of string columns to an array ofdatetime instances. The default uses `dateutil.parser.parser` to do theconversion. 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 (asdefined by parse_dates) as arguments; 2) concatenate (row-wise) the stringvalues from the columns defined by parse_dates into a single array and passthat; and 3) call date_parser once for each row using one or more strings(corresponding to the columns defined by parse_dates) as arguments.dayfirstboolean, default `False`DD/MM format dates, international and European format.cache_datesboolean, default TrueIf True, use a cache of unique, converted dates to apply the datetimeconversion. May produce significant speed-up when parsing duplicatedate strings, especially ones with timezone offsets.New in version 0.25.0.  Iterationiteratorboolean, default `False`Return `TextFileReader` object for iteration or getting chunks with`get_chunk()`.chunksizeint, default `None`Return `TextFileReader` object for iteration. See [iterating and chunking](io-chunking) below.  Quoting, compression, and file formatcompression{`'infer'`, `'gzip'`, `'bz2'`, `'zip'`, `'xz'`, `None`, `dict`}, 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 path-like 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 `None` for no decompression. Can also be a dict with key `'method'`set to one of {`'zip'`, `'gzip'`, `'bz2'`} and other key-value pairs areforwarded to `zipfile.ZipFile`, `gzip.GzipFile`, or `bz2.BZ2File`.As an example, the following could be passed for faster compression and tocreate a reproducible gzip archive:`compression={'method': 'gzip', 'compresslevel': 1, 'mtime': 1}`.Changed in version 1.1.0: dict option extended to support `gzip` and `bz2`.Changed in version 1.2.0: Previous versions forwarded dict entries for ‘gzip’ to `gzip.open`.thousandsstr, default `None`Thousands separator.decimalstr, default `'.'`Character to recognize as decimal point. E.g. use `','` for European data.float_precisionstring, default NoneSpecifies which converter the C engine should use for floating-point values.The options are `None` for the ordinary converter, `high` for thehigh-precision converter, and `round_trip` for the round-trip converter.lineterminatorstr (length 1), default `None`Character to break file into lines. Only valid with C parser.quotecharstr (length 1)The character used to denote the start and end of a quoted item. Quoted itemscan include the delimiter and it will be ignored.quotingint or `csv.QUOTE_*` instance, default `0`Control field quoting behavior per `csv.QUOTE_*` constants. Use one of`QUOTE_MINIMAL` (0), `QUOTE_ALL` (1), `QUOTE_NONNUMERIC` (2) or`QUOTE_NONE` (3).doublequoteboolean, default `True`When `quotechar` is specified and `quoting` is not `QUOTE_NONE`,indicate whether or not to interpret two consecutive `quotechar` elements**inside** a field as a single `quotechar` element.escapecharstr (length 1), default `None`One-character string used to escape delimiter when quoting is `QUOTE_NONE`.commentstr, default `None`Indicates remainder of line should not be parsed. If found at the beginning ofa line, the line will be ignored altogether. This parameter must be a singlecharacter. Like empty lines (as long as `skip_blank_lines=True`), fullycommented lines are ignored by the parameter `header` but not by `skiprows`.For example, if `comment=''`, parsing ‘empty\na,b,c\n1,2,3’ with`header=0` will result in ‘a,b,c’ being treated as the header.encodingstr, default `None`Encoding to use for UTF when reading/writing (e.g. `'utf-8'`). [List ofPython standard encodings](https://docs.python.org/3/library/codecs.htmlstandard-encodings).dialectstr or `python:csv.Dialect` instance, default `None`If provided, this parameter will override values (default or not) for thefollowing parameters: `delimiter`, `doublequote`, `escapechar`,`skipinitialspace`, `quotechar`, and `quoting`. If it is necessary tooverride values, a ParserWarning will be issued. See `python:csv.Dialect`documentation for more details.  Error handlingerror_bad_linesboolean, default `None`Lines with too many fields (e.g. a csv line with too many commas) will bydefault cause an exception to be raised, and no `DataFrame` will bereturned. If `False`, then these “bad lines” will dropped from the`DataFrame` that is returned. See [bad lines](io-bad-lines)below.Deprecated since version 1.3.0: The `on_bad_lines` parameter should be used instead to specify behavior uponencountering a bad line instead.warn_bad_linesboolean, default `None`If error_bad_lines is `False`, and warn_bad_lines is `True`, a warning foreach “bad line” will be output.Deprecated since version 1.3.0: The `on_bad_lines` parameter should be used instead to specify behavior uponencountering a bad line instead.on_bad_lines{{‘error’, ‘warn’, ‘skip’}}, default ‘error’Specifies what to do upon encountering a bad line (a line with too many fields).Allowed values are :> - ‘error’, raise an ParserError when a bad line is encountered.  - ‘warn’, print a warning when a bad line is encountered and skip that line.  - ‘skip’, skip bad lines without raising or warning when they are encountered.  New in version 1.3.0.  Specifying column data typesYou can indicate the data type for the whole `DataFrame` or individualcolumns:<jupyter_code>import numpy as np

data = "a,b,c,d\n1,2,3,4\n5,6,7,8\n9,10,11"
print(data)

df = pd.read_csv(StringIO(data), dtype=object)
df
df["a"][0]
df = pd.read_csv(StringIO(data), dtype={"b": object, "c": np.float64, "d": "Int64"})
df.dtypes<jupyter_output>a,b,c,d
1,2,3,4
5,6,7,8
9,10,11<jupyter_text>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 cansee here to learn more about dtypes, andhere to learn more about `object` conversion inpandas.For instance, you can use the `converters` argumentof `read_csv()`:<jupyter_code>data = "col_1\n1\n2\n'A'\n4.22"
df = pd.read_csv(StringIO(data), converters={"col_1": str})
df
df["col_1"].apply(type).value_counts()<jupyter_output><empty_output><jupyter_text>Or you can use the `to_numeric()` function to coerce thedtypes after reading in the data,<jupyter_code>df2 = pd.read_csv(StringIO(data))
df2["col_1"] = pd.to_numeric(df2["col_1"], errors="coerce")
df2
df2["col_1"].apply(type).value_counts()<jupyter_output><empty_output><jupyter_text>which will convert all valid parsing to floats, leaving the invalid parsingas `NaN`.Ultimately, how you deal with reading in columns containing mixed dtypesdepends on your specific needs. In the case above, if you wanted to `NaN` outthe 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, thenusing the `converters` argument of `read_csv()` would certainly beworth trying.>**Note**>>In some cases, reading in abnormal data with columns containing mixed dtypeswill result in an inconsistent dataset. If you rely on pandas to infer thedtypes of your columns, the parsing engine will go and infer the dtypes fordifferent chunks of the data, rather than the whole dataset at once. Consequently,you can end up with column(s) with mixed dtypes. For example,<jupyter_code>col_1 = list(range(500000)) + ["a", "b"] + list(range(500000))
df = pd.DataFrame({"col_1": col_1})
df.to_csv("foo.csv")

mixed_df = pd.read_csv("foo.csv")
mixed_df["col_1"].apply(type).value_counts()
mixed_df["col_1"].dtype<jupyter_output><empty_output><jupyter_text>will result with `mixed_df` containing an `int` dtype for certain chunksof the column, and `str` for others due to the mixed dtypes from thedata that was read in. It is important to note that the overall column will bemarked with a `dtype` of `object`, which is used for columns with mixed dtypes.<jupyter_code>import os

os.remove("foo.csv")<jupyter_output><empty_output><jupyter_text>Specifying categorical dtype`Categorical` columns can be parsed directly by specifying `dtype='category'` or`dtype=CategoricalDtype(categories, ordered)`.<jupyter_code>data = "col1,col2,col3\na,b,1\na,b,2\nc,d,3"

pd.read_csv(StringIO(data))
pd.read_csv(StringIO(data)).dtypes
pd.read_csv(StringIO(data), dtype="category").dtypes<jupyter_output><empty_output><jupyter_text>Individual columns can be parsed as a `Categorical` using a dictspecification:<jupyter_code>pd.read_csv(StringIO(data), dtype={"col1": "category"}).dtypes<jupyter_output><empty_output><jupyter_text>Specifying `dtype='category'` will result in an unordered `Categorical`whose `categories` are the unique values observed in the data. For morecontrol on the categories and order, create a`CategoricalDtype` ahead of time, and pass that forthat column’s `dtype`.<jupyter_code>from pandas.api.types import CategoricalDtype

dtype = CategoricalDtype(["d", "c", "b", "a"], ordered=True)
pd.read_csv(StringIO(data), dtype={"col1": dtype}).dtypes<jupyter_output><empty_output><jupyter_text>When using `dtype=CategoricalDtype`, “unexpected” values outside of`dtype.categories` are treated as missing values.<jupyter_code>dtype = CategoricalDtype(["a", "b", "d"])  # No 'c'
pd.read_csv(StringIO(data), dtype={"col1": dtype}).col1<jupyter_output><empty_output><jupyter_text>This matches the behavior of `Categorical.set_categories()`.>**Note**>>With `dtype='category'`, the resulting categories will always be parsedas strings (object dtype). If the categories are numeric they can beconverted using the `to_numeric()` function, or as appropriate, anotherconverter such as `to_datetime()`.When `dtype` is a `CategoricalDtype` with homogeneous `categories` (all numeric, all datetimes, etc.), the conversion is done automatically.<jupyter_code>df = pd.read_csv(StringIO(data), dtype="category")
df.dtypes
df["col3"]
df["col3"].cat.categories = pd.to_numeric(df["col3"].cat.categories)
df["col3"]<jupyter_output><empty_output><jupyter_text>Naming and using columns  Handling column namesA file may or may not have a header row. pandas assumes the first row should beused as the column names:<jupyter_code>data = "a,b,c\n1,2,3\n4,5,6\n7,8,9"
print(data)
pd.read_csv(StringIO(data))<jupyter_output>a,b,c
1,2,3
4,5,6
7,8,9<jupyter_text>By specifying the `names` argument in conjunction with `header` you canindicate other names to use and whether or not to throw away the header row (ifany):<jupyter_code>print(data)
pd.read_csv(StringIO(data), names=["foo", "bar", "baz"], header=0)
pd.read_csv(StringIO(data), names=["foo", "bar", "baz"], header=None)<jupyter_output>a,b,c
1,2,3
4,5,6
7,8,9<jupyter_text>If the header is in a row other than the first, pass the row number to`header`. This will skip the preceding rows:<jupyter_code>data = "skip this skip it\na,b,c\n1,2,3\n4,5,6\n7,8,9"
pd.read_csv(StringIO(data), header=1)<jupyter_output><empty_output><jupyter_text>>**Note**>>Default behavior is to infer the column names: if no names arepassed the behavior is identical to `header=0` and column namesare inferred from the first non-blank line of the file, if columnnames are passed explicitly then the behavior is identical to`header=None`.  Duplicate names parsingIf the file or header contains duplicate names, pandas will by defaultdistinguish between them so as to prevent overwriting data:<jupyter_code>data = "a,b,a\n0,1,2\n3,4,5"
pd.read_csv(StringIO(data))<jupyter_output><empty_output><jupyter_text>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 canarise: ```pythonIn [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  a0  2  1  21  5  4  5``` To prevent users from encountering this problem with duplicate data, a `ValueError`exception is raised if `mangle_dupe_cols != True`: ```pythonIn [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 afile, either using the column names, position numbers or a callable:<jupyter_code>data = "a,b,c,d\n1,2,3,foo\n4,5,6,bar\n7,8,9,baz"
pd.read_csv(StringIO(data))
pd.read_csv(StringIO(data), usecols=["b", "d"])
pd.read_csv(StringIO(data), usecols=[0, 2, 3])
pd.read_csv(StringIO(data), usecols=lambda x: x.upper() in ["A", "C"])<jupyter_output><empty_output><jupyter_text>The `usecols` argument can also be used to specify which columns not touse in the final result:<jupyter_code>pd.read_csv(StringIO(data), usecols=lambda x: x not in ["a", "c"])<jupyter_output><empty_output><jupyter_text>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 linesIf the `comment` parameter is specified, then completely commented lines willbe ignored. By default, completely blank lines will be ignored as well.<jupyter_code>data = "\na,b,c\n  \n# commented line\n1,2,3\n\n4,5,6"
print(data)
pd.read_csv(StringIO(data), comment="#")<jupyter_output>a,b,c
  
# commented line
1,2,3

4,5,6<jupyter_text>If `skip_blank_lines=False`, then `read_csv` will not ignore blank lines:<jupyter_code>data = "a,b,c\n\n1,2,3\n\n\n4,5,6"
pd.read_csv(StringIO(data), skip_blank_lines=False)<jupyter_output><empty_output><jupyter_text>The presence of ignored lines might create ambiguities involving line numbers;the parameter `header` uses row numbers (ignoring commented/emptylines), while `skiprows` uses line numbers (including commented/empty lines):<jupyter_code>data = "#comment\na,b,c\nA,B,C\n1,2,3"
pd.read_csv(StringIO(data), comment="#", header=1)
data = "A,B,C\n#comment\na,b,c\n1,2,3"
pd.read_csv(StringIO(data), comment="#", skiprows=2)<jupyter_output><empty_output><jupyter_text>If both `header` and `skiprows` are specified, `header` will berelative to the end of `skiprows`. For example:<jupyter_code>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"
)
print(data)
pd.read_csv(StringIO(data), comment="#", skiprows=4, header=1)<jupyter_output># empty
# second empty line
# third emptyline
X,Y,Z
1,2,3
A,B,C
1,2.,4.
5.,NaN,10.0<jupyter_text>CommentsSometimes comments or meta data may be included in a file:<jupyter_code>data = (
    "ID,level,category\n"
    "Patient1,123000,x # really unpleasant\n"
    "Patient2,23000,y # wouldn't take his medicine\n"
    "Patient3,1234018,z # awesome"
)

with open("tmp.csv", "w") as fh:
    fh.write(data)
print(open("tmp.csv").read())<jupyter_output>ID,level,category
Patient1,123000,x # really unpleasant
Patient2,23000,y # wouldn't take his medicine
Patient3,1234018,z # awesome<jupyter_text>By default, the parser includes the comments in the output:<jupyter_code>df = pd.read_csv("tmp.csv")
df<jupyter_output><empty_output><jupyter_text>We can suppress the comments using the `comment` keyword:<jupyter_code>df = pd.read_csv("tmp.csv", comment="#")
df
os.remove("tmp.csv")<jupyter_output><empty_output><jupyter_text>Dealing with Unicode dataThe `encoding` argument should be used for encoded unicode data, which willresult in byte strings being decoded to unicode in the result:<jupyter_code>from io import BytesIO

data = b"word,length\n" b"Tr\xc3\xa4umen,7\n" b"Gr\xc3\xbc\xc3\x9fe,5"
data = data.decode("utf8").encode("latin-1")
df = pd.read_csv(BytesIO(data), encoding="latin-1")
df
df["word"][1]<jupyter_output><empty_output><jupyter_text>Some formats which encode all characters as multiple bytes, like UTF-16, won’tparse correctly at all without specifying the encoding. [Full list of Pythonstandard encodings](https://docs.python.org/3/library/codecs.htmlstandard-encodings).  Index columns and trailing delimitersIf a file has one more column of data than the number of column names, thefirst column will be used as the `DataFrame`’s row names:<jupyter_code>data = "a,b,c\n4,apple,bat,5.7\n8,orange,cow,10"
pd.read_csv(StringIO(data))
data = "index,a,b,c\n4,apple,bat,5.7\n8,orange,cow,10"
pd.read_csv(StringIO(data), index_col=0)<jupyter_output><empty_output><jupyter_text>Ordinarily, you can achieve this behavior using the `index_col` option.There are some exception cases when a file has been prepared with delimiters atthe end of each data line, confusing the parser. To explicitly disable theindex column inference and discard the last column, pass `index_col=False`:<jupyter_code>data = "a,b,c\n4,apple,bat,\n8,orange,cow,"
print(data)
pd.read_csv(StringIO(data))
pd.read_csv(StringIO(data), index_col=False)<jupyter_output>a,b,c
4,apple,bat,
8,orange,cow,<jupyter_text>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.<jupyter_code>data = "a,b,c\n4,apple,bat,\n8,orange,cow,"
print(data)
pd.read_csv(StringIO(data), usecols=["b", "c"])
pd.read_csv(StringIO(data), usecols=["b", "c"], index_col=0)<jupyter_output>a,b,c
4,apple,bat,
8,orange,cow,<jupyter_text>Date Handling  Specifying date columnsTo 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 theinput text data into `datetime` objects.The simplest case is to just pass in `parse_dates=True`:<jupyter_code>f = open("foo.csv", "w")
f.write("date,A,B,C\n20090101,a,1,2\n20090102,b,3,4\n20090103,c,4,5")
f.close()
# Use a column as an index, and parse it as dates.
df = pd.read_csv("foo.csv", index_col=0, parse_dates=True)
df

# These are Python datetime objects
df.index<jupyter_output><empty_output><jupyter_text>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 beused 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 datecolumns will be prepended to the output (so as to not affect the existing columnorder) and the new column names will be the concatenation of the componentcolumn names:<jupyter_code>data = (
    "KORD,19990127, 19:00:00, 18:56:00, 0.8100\n"
    "KORD,19990127, 20:00:00, 19:56:00, 0.0100\n"
    "KORD,19990127, 21:00:00, 20:56:00, -0.5900\n"
    "KORD,19990127, 21:00:00, 21:18:00, -0.9900\n"
    "KORD,19990127, 22:00:00, 21:56:00, -0.5900\n"
    "KORD,19990127, 23:00:00, 22:56:00, -0.5900"
)

with open("tmp.csv", "w") as fh:
    fh.write(data)
print(open("tmp.csv").read())
df = pd.read_csv("tmp.csv", header=None, parse_dates=[[1, 2], [1, 3]])
df<jupyter_output>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<jupyter_text>By default the parser removes the component date columns, but you can chooseto retain them via the `keep_date_col` keyword:<jupyter_code>df = pd.read_csv(
    "tmp.csv", header=None, parse_dates=[[1, 2], [1, 3]], keep_date_col=True
)
df<jupyter_output><empty_output><jupyter_text>Note that if you wish to combine multiple columns into a single date column, anested list must be used. In other words, `parse_dates=[1, 2]` indicates thatthe second and third columns should each be parsed as separate date columnswhile `parse_dates=[[1, 2]]` means the two columns should be parsed into asingle column.You can also use a dict to specify custom name columns:<jupyter_code>date_spec = {"nominal": [1, 2], "actual": [1, 3]}
df = pd.read_csv("tmp.csv", header=None, parse_dates=date_spec)
df<jupyter_output><empty_output><jupyter_text>It is important to remember that if multiple text columns are to be parsed intoa 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 originaldata columns:<jupyter_code>date_spec = {"nominal": [1, 2], "actual": [1, 3]}
df = pd.read_csv(
    "tmp.csv", header=None, parse_dates=date_spec, index_col=0
)  # index is the nominal column
df<jupyter_output><empty_output><jupyter_text>>**Note**>>If a column or index contains an unparsable date, the entire column orindex will be returned unaltered as an object data type. For non-standarddatetime 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 arrangefor your data to store datetimes in this format, load times will besignificantly faster, ~20x has been observed.  Date parsing functionsFinally, the parser allows you to specify a custom `date_parser` function totake full advantage of the flexibility of the date parsing API:<jupyter_code>df = pd.read_csv(
    "tmp.csv", header=None, parse_dates=date_spec, date_parser=pd.to_datetime
)
df<jupyter_output><empty_output><jupyter_text>pandas will try to call the `date_parser` function in three different ways. Ifan exception is raised, the next one is tried:1. `date_parser` is first called with one or more arrays as arguments,  as defined using `parse_dates` (e.g., `date_parser(['2013', '2013'], ['1', '2'])`).  1. If 1 fails, `date_parser` is called with all the columns  concatenated row-wise into a single array (e.g., `date_parser(['2013 1', '2013 2'])`).  Note that performance-wise, you should try these methods of parsing dates in order:1. Try to infer the format using `infer_datetime_format=True` (see section below).  1. If you know the format, use `pd.to_datetime()`:  `date_parser=lambda x: pd.to_datetime(x, format=...)`.  1. If you have a really non-standard format, use a custom `date_parser` function.  For optimal performance, this should be vectorized, i.e., it should accept arrays  as arguments.<jupyter_code>os.remove("tmp.csv")<jupyter_output><empty_output><jupyter_text>Parsing a CSV with mixed timezonespandas cannot natively represent a column or index with mixed timezones. If your CSVfile contains columns with a mixture of timezones, the default result will bean object-dtype column with strings, even with `parse_dates`.<jupyter_code>content = """\
a
2000-01-01T00:00:00+05:00
2000-01-01T00:00:00+06:00"""
df = pd.read_csv(StringIO(content), parse_dates=["a"])
df["a"]<jupyter_output><empty_output><jupyter_text>To parse the mixed-timezone values as a datetime column, pass a partially-applied`to_datetime()` with `utc=True` as the `date_parser`.<jupyter_code>df = pd.read_csv(
    StringIO(content),
    parse_dates=["a"],
    date_parser=lambda col: pd.to_datetime(col, utc=True),
)
df["a"]<jupyter_output><empty_output><jupyter_text>Inferring datetime formatIf you have `parse_dates` enabled for some or all of your columns, and yourdatetime strings are all formatted the same way, you may get a large speedup by setting `infer_datetime_format=True`.  If set, pandas will attemptto guess the format of your datetime strings, and then use a faster meansof parsing the strings.  5-10x parsing speeds have been observed.  pandaswill fallback to the usual parsing if either the format cannot be guessedor the format that was guessed cannot properly parse the entire columnof strings.  So in general, `infer_datetime_format` should not have anynegative consequences if enabled.Here are some examples of datetime strings that can be guessed (Allrepresenting 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.<jupyter_code># Try to infer the format for the index column
df = pd.read_csv(
    "foo.csv",
    index_col=0,
    parse_dates=True,
    infer_datetime_format=True,
)
df
os.remove("foo.csv")<jupyter_output><empty_output><jupyter_text>International date formatsWhile US date formats tend to be MM/DD/YYYY, many international formats useDD/MM/YYYY instead. For convenience, a `dayfirst` keyword is provided:<jupyter_code>data = "date,value,cat\n1/6/2000,5,a\n2/6/2000,10,b\n3/6/2000,15,c"
with open("tmp.csv", "w") as fh:
    fh.write(data)
print(open("tmp.csv").read())

pd.read_csv("tmp.csv", parse_dates=[0])
pd.read_csv("tmp.csv", dayfirst=True, parse_dates=[0])<jupyter_output>date,value,cat
1/6/2000,5,a
2/6/2000,10,b
3/6/2000,15,c<jupyter_text>Writing CSVs to binary file objectsNew in version 1.2.0.`df.to_csv(..., mode="wb")` allows writing a CSV to a file objectopened binary mode. In most cases, it is not necessary to specify`mode` as Pandas will auto-detect whether the file object isopened in text or binary mode.<jupyter_code>import io

data = pd.DataFrame([0, 1, 2])
buffer = io.BytesIO()
data.to_csv(buffer, encoding="utf-8", compression="gzip")<jupyter_output><empty_output><jupyter_text>Specifying method for floating-point conversionThe parameter `float_precision` can be specified in order to usea specific floating-point converter during parsing with the C engine.The options are the ordinary converter, the high-precision converter, andthe round-trip converter (which is guaranteed to round-trip values afterwriting to a file). For example:<jupyter_code>val = "0.3066101993807095471566981359501369297504425048828125"
data = "a,b,c\n1,2,{0}".format(val)
abs(
    pd.read_csv(
        StringIO(data),
        engine="c",
        float_precision=None,
    )["c"][0] - float(val)
)
abs(
    pd.read_csv(
        StringIO(data),
        engine="c",
        float_precision="high",
    )["c"][0] - float(val)
)
abs(
    pd.read_csv(StringIO(data), engine="c", float_precision="round_trip")["c"][0]
    - float(val)
)<jupyter_output><empty_output><jupyter_text>Thousand separatorsFor large numbers that have been written with a thousands separator, you canset the `thousands` keyword to a string of length 1 so that integers will be parsedcorrectly:<jupyter_code>data = (
    "ID|level|category\n"
    "Patient1|123,000|x\n"
    "Patient2|23,000|y\n"
    "Patient3|1,234,018|z"
)

with open("tmp.csv", "w") as fh:
    fh.write(data)<jupyter_output><empty_output><jupyter_text>By default, numbers with a thousands separator will be parsed as strings:<jupyter_code>print(open("tmp.csv").read())
df = pd.read_csv("tmp.csv", sep="|")
df

df.level.dtype<jupyter_output>ID|level|category
Patient1|123,000|x
Patient2|23,000|y
Patient3|1,234,018|z<jupyter_text>The `thousands` keyword allows integers to be parsed correctly:<jupyter_code>print(open("tmp.csv").read())
df = pd.read_csv("tmp.csv", sep="|", thousands=",")
df

df.level.dtype
os.remove("tmp.csv")<jupyter_output><empty_output><jupyter_text>NA valuesTo 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 anumber (a `float`, like `5.0` or an `integer` like `5`), thecorresponding equivalent values will also imply a missing value (in this caseeffectively `[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: ```pythonpd.read_csv("path_to_file.csv", na_values=[5])``` In the example above `5` and `5.0` will be recognized as `NaN`, inaddition to the defaults. A string will first be interpreted as a numerical`5`, then as a `NaN`. ```pythonpd.read_csv("path_to_file.csv", keep_default_na=False, na_values=[""])``` Above, only an empty field will be recognized as `NaN`. ```pythonpd.read_csv("path_to_file.csv", keep_default_na=False, na_values=["NA", "0"])``` Above, both `NA` and `0` as strings are `NaN`. ```pythonpd.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 SeriesUsing the `squeeze` keyword, the parser will return output with a single columnas a `Series`:<jupyter_code>data = "level\nPatient1,123000\nPatient2,23000\nPatient3,1234018"

with open("tmp.csv", "w") as fh:
    fh.write(data)
print(open("tmp.csv").read())

output = pd.read_csv("tmp.csv", squeeze=True)
output

type(output)
os.remove("tmp.csv")<jupyter_output><empty_output><jupyter_text>Boolean valuesThe common values `True`, `False`, `TRUE`, and `FALSE` are allrecognized as boolean. Occasionally you might want to recognize other valuesas being boolean. To do this, use the `true_values` and `false_values`options as follows:<jupyter_code>data = "a,b,c\n1,Yes,2\n3,No,4"
print(data)
pd.read_csv(StringIO(data))
pd.read_csv(StringIO(data), true_values=["Yes"], false_values=["No"])<jupyter_output>a,b,c
1,Yes,2
3,No,4<jupyter_text>Handling “bad” linesSome files may have malformed lines with too few fields or too many. Lines withtoo few fields will have NA values filled in the trailing fields. Lines withtoo many fields will raise an error by default:<jupyter_code>data = "a,b,c\n1,2,3\n4,5,6,7\n8,9,10"
pd.read_csv(StringIO(data))
#results in : ParserError: Error tokenizing data. C error: Expected 3 fields in line 3, saw 4<jupyter_output><empty_output><jupyter_text>You can elect to skip bad lines:<jupyter_code>pd.read_csv(StringIO(data), on_bad_lines='warn')
# results in: TypeError: read_csv() got an unexpected keyword argument 'on_bad_lines'<jupyter_output><empty_output><jupyter_text>```pythonIn [29]: pd.read_csv(StringIO(data), on_bad_lines="warn")Skipping line 3: expected 3 fields, saw 4Out[29]:   a  b   c0  1  2   31  8  9  10``` You can also use the `usecols` parameter to eliminate extraneous columndata that appear in some lines but not others:<jupyter_code>pd.read_csv(StringIO(data), usecols=[0, 1, 2])<jupyter_output><empty_output><jupyter_text>DialectThe `dialect` keyword gives greater flexibility in specifying the file format.By default it uses the Excel dialect but you can specify either the dialect nameor a `python:csv.Dialect` instance.<jupyter_code>data = "label1,label2,label3\n" 'index1,"a,c,e\n' "index2,b,d,f"<jupyter_output><empty_output><jupyter_text>Suppose you had data with unenclosed quotes:<jupyter_code>print(data)<jupyter_output>label1,label2,label3
index1,"a,c,e
index2,b,d,f<jupyter_text>By default, `read_csv` uses the Excel dialect and treats the double quote asthe quote character, which causes it to fail when it finds a newline before itfinds the closing double quote.We can get around this using `dialect`:<jupyter_code>import csv

dia = csv.excel()
dia.quoting = csv.QUOTE_NONE
pd.read_csv(StringIO(data), dialect=dia)<jupyter_output><empty_output><jupyter_text>All of the dialect options can be specified separately by keyword arguments:<jupyter_code>data = "a,b,c~1,2,3~4,5,6"
pd.read_csv(StringIO(data), lineterminator="~")<jupyter_output><empty_output><jupyter_text>Another common dialect option is `skipinitialspace`, to skip any whitespaceafter a delimiter:<jupyter_code>data = "a, b, c\n1, 2, 3\n4, 5, 6"
print(data)
pd.read_csv(StringIO(data), skipinitialspace=True)<jupyter_output>a, b, c
1, 2, 3
4, 5, 6<jupyter_text>The parsers make every attempt to “do the right thing” and not be fragile. Typeinference is a pretty big deal. If a column can be coerced to integer dtypewithout altering the contents, the parser will do so. Any non-numericcolumns will come through as object dtype as with the rest of pandas objects.  Quoting and Escape CharactersQuotes (and other escape characters) in embedded fields can be handled in anynumber of ways. One way is to use backslashes; to properly parse this data, youshould pass the `escapechar` option:<jupyter_code>data = 'a,b\n"hello, \\"Bob\\", nice to see you",5'
print(data)
pd.read_csv(StringIO(data), escapechar="\\")<jupyter_output>a,b
"hello, \"Bob\", nice to see you",5<jupyter_text>Files with fixed width columnsWhile `read_csv()` reads delimited data, the `read_fwf()` function workswith data files that have known and fixed column widths. The function parametersto `read_fwf` are largely the same as `read_csv` with two extra parameters, anda 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., ‘~’).<jupyter_code>f = open("bar.csv", "w")
data1 = (
    "id8141    360.242940   149.910199   11950.7\n"
    "id1594    444.953632   166.985655   11788.4\n"
    "id1849    364.136849   183.628767   11806.2\n"
    "id1230    413.836124   184.375703   11916.8\n"
    "id1948    502.953953   173.237159   12468.3"
)
f.write(data1)
f.close()<jupyter_output><empty_output><jupyter_text>Consider a typical fixed-width data file:<jupyter_code>print(open("bar.csv").read())<jupyter_output>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<jupyter_text>In order to parse this file into a `DataFrame`, we simply need to supply thecolumn specifications to the `read_fwf` function along with the file name:<jupyter_code># Column specifications are a list of half-intervals
colspecs = [(0, 6), (8, 20), (21, 33), (34, 43)]
df = pd.read_fwf("bar.csv", colspecs=colspecs, header=None, index_col=0)
df<jupyter_output><empty_output><jupyter_text>Note how the parser automatically picks column names X. when`header=None` argument is specified. Alternatively, you can supply just thecolumn widths for contiguous columns:<jupyter_code># Widths are a list of integers
widths = [6, 14, 13, 10]
df = pd.read_fwf("bar.csv", widths=widths, header=None)
df<jupyter_output><empty_output><jupyter_text>The parser will take care of extra white spaces around the columnsso 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 thefirst 100 rows of the file. It can do it only in cases when the columns arealigned and correctly separated by the provided `delimiter` (default delimiteris whitespace).<jupyter_code>df = pd.read_fwf("bar.csv", header=None, index_col=0)
df<jupyter_output><empty_output><jupyter_text>`read_fwf` supports the `dtype` parameter for specifying the types ofparsed columns to be different from the inferred type.<jupyter_code>pd.read_fwf("bar.csv", header=None, index_col=0).dtypes
pd.read_fwf("bar.csv", header=None, dtype={2: "object"}).dtypes
os.remove("bar.csv")<jupyter_output><empty_output><jupyter_text>Indexes  Files with an “implicit” index column<jupyter_code>f = open("foo.csv", "w")
f.write("A,B,C\n20090101,a,1,2\n20090102,b,3,4\n20090103,c,4,5")
f.close()<jupyter_output><empty_output><jupyter_text>Consider a file with one less entry in the header than the number of datacolumn:<jupyter_code>print(open("foo.csv").read())<jupyter_output>A,B,C
20090101,a,1,2
20090102,b,3,4
20090103,c,4,5<jupyter_text>In this special case, `read_csv` assumes that the first column is to be usedas the index of the `DataFrame`:<jupyter_code>pd.read_csv("foo.csv")<jupyter_output><empty_output><jupyter_text>Note that the dates weren’t automatically parsed. In that case you would needto do as before:<jupyter_code>df = pd.read_csv("foo.csv", parse_dates=True)
df.index
os.remove("foo.csv")<jupyter_output><empty_output><jupyter_text>Reading an index with a `MultiIndex`Suppose you have data indexed by two columns:<jupyter_code>print(open("../data/mindex_ex.csv").read())<jupyter_output>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<jupyter_text>The `index_col` argument to `read_csv` can take a list ofcolumn numbers to turn multiple columns into a `MultiIndex` for the index of thereturned object:<jupyter_code>df = pd.read_csv("../data/mindex_ex.csv", index_col=[0, 1])
df
df.loc[1978]<jupyter_output><empty_output><jupyter_text>Reading columns with a `MultiIndex`By specifying list of row locations for the `header` argument, youcan read in a `MultiIndex` for the columns. Specifying non-consecutiverows will skip the intervening rows.<jupyter_code>from pandas._testing import makeCustomDataframe as mkdf

df = mkdf(5, 3, r_idx_nlevels=2, c_idx_nlevels=4)
df.to_csv("mi.csv")
print(open("mi.csv").read())
pd.read_csv("mi.csv", header=[0, 1, 2, 3], index_col=[0, 1])<jupyter_output>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<jupyter_text>`read_csv` is also able to interpret a more common formatof multi-columns indices.<jupyter_code>data = ",a,a,a,b,c,c\n,q,r,s,t,u,v\none,1,2,3,4,5,6\ntwo,7,8,9,10,11,12"
fh = open("mi2.csv", "w")
fh.write(data)
fh.close()
print(open("mi2.csv").read())
pd.read_csv("mi2.csv", header=[0, 1], index_col=0)<jupyter_output>,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<jupyter_text>Note: If an `index_col` is not specified (e.g. you don’t have an index, or wrote itwith `df.to_csv(..., index=False)`, then any `names` on the columns index will be *lost*.<jupyter_code>os.remove("mi.csv")
os.remove("mi2.csv")<jupyter_output><empty_output><jupyter_text>Automatically “sniffing” the delimiter`read_csv` is capable of inferring delimited (not necessarilycomma-separated) files, as pandas uses the `python:csv.Sniffer`class of the csv module. For this, you have to specify `sep=None`.<jupyter_code>df = pd.DataFrame(np.random.randn(10, 4))
df.to_csv("tmp.sv", sep="|")
df.to_csv("tmp2.sv", sep=":")
print(open("tmp2.sv").read())
pd.read_csv("tmp2.sv", sep=None, engine="python")<jupyter_output>:0:1:2:3
0:0.886121372040135:1.0030865236416298:0.14870717144370943:-0.6039936745536113
1:-1.0395355821804346:0.501221022889617:1.1675225280696364:-0.832221260486264
2:0.011046250708011572:0.165639385806293:-0.02426449746070842:0.47496852923744043
3:-0.7077003184807955:-0.18190498484389514:0.8084623036429452:-1.591379634995298
4:0.12899709808723453:0.7237787708548683:-0.06477345622853019:-1.4321129684916492
5:0.4433566675153994:1.637374163266085:1.2302716897644312:-0.0004767212976452429
6:2.526600528340001:-1.2014152421719946:-0.07174622673362896:0.4898921294423915
7:-0.17073379101946745:-0.8532016865060652:0.530791876377877:0.15891850649778158
8:-0.052236730200622826:-0.3599283898655735:1.5521291999189126:-2.071391248947006
9:-0.553024797726182:0.5781718097338505:0.67192783557143:0.06318729933574842<jupyter_text>Reading multiple files to create a single DataFrameIt’s best to use `concat()` to combine multiple files.See the [cookbook](44_cookbook.ipynbcookbook-csv-multiple-files) for an example.  Iterating through files chunk by chunkSuppose you wish to iterate through a (potentially very large) file lazilyrather than reading the entire file into memory, such as the following:<jupyter_code>print(open("tmp.sv").read())
table = pd.read_csv("tmp.sv", sep="|")
table<jupyter_output>|0|1|2|3
0|0.886121372040135|1.0030865236416298|0.14870717144370943|-0.6039936745536113
1|-1.0395355821804346|0.501221022889617|1.1675225280696364|-0.832221260486264
2|0.011046250708011572|0.165639385806293|-0.02426449746070842|0.47496852923744043
3|-0.7077003184807955|-0.18190498484389514|0.8084623036429452|-1.591379634995298
4|0.12899709808723453|0.7237787708548683|-0.06477345622853019|-1.4321129684916492
5|0.4433566675153994|1.637374163266085|1.2302716897644312|-0.0004767212976452429
6|2.526600528340001|-1.2014152421719946|-0.07174622673362896|0.4898921294423915
7|-0.17073379101946745|-0.8532016865060652|0.530791876377877|0.15891850649778158
8|-0.052236730200622826|-0.3599283898655735|1.5521291999189126|-2.071391248947006
9|-0.553024797726182|0.5781718097338505|0.67192783557143|0.06318729933574842<jupyter_text>By specifying a `chunksize` to `read_csv`, the returnvalue will be an iterable object of type `TextFileReader`:<jupyter_code>with pd.read_csv("tmp.sv", sep="|", chunksize=4) as reader:
    reader
    for chunk in reader:
        print(chunk)<jupyter_output>Unnamed: 0         0         1         2         3
0           0  0.886121  1.003087  0.148707 -0.603994
1           1 -1.039536  0.501221  1.167523 -0.832221
2           2  0.011046  0.165639 -0.024264  0.474969
3           3 -0.707700 -0.181905  0.808462 -1.591380
   Unnamed: 0         0         1         2         3
4           4  0.128997  0.723779 -0.064773 -1.432113
5           5  0.443357  1.637374  1.230272 -0.000477
6           6  2.526601 -1.201415 -0.071746  0.489892
7           7 -0.170734 -0.853202  0.530792  0.158919
   Unnamed: 0         0         1         2         3
8           8 -0.052237 -0.359928  1.552129 -2.071391
9           9 -0.553025  0.578172  0.671928  0.063187<jupyter_text>Changed in version 1.2: `read_csv/json/sas` return a context-manager when iterating through a file.Specifying `iterator=True` will also return the `TextFileReader` object:<jupyter_code>with pd.read_csv("tmp.sv", sep="|", iterator=True) as reader:
    reader.get_chunk(5)
os.remove("tmp.sv")
os.remove("tmp2.sv")<jupyter_output><empty_output><jupyter_text>Specifying the parser engineUnder the hood pandas uses a fast and efficient parser implemented in C as wellas a Python implementation which is currently more feature-complete. Wherepossible pandas uses the C parser (specified as `engine='c'`), but may fallback to Python if C-unsupported options are specified. Currently, C-unsupportedoptions include:- `sep` other than a single character (e.g. regex separators)  - `skipfooter`  - `sep=None` with `delim_whitespace=False`  Specifying any of the above options will produce a `ParserWarning` unless thepython engine is selected explicitly using `engine='python'`.  Reading/writing remote filesYou can pass in a URL to read or write remote files to many of pandas’ IOfunctions - the following example shows reading a CSV file: ```pythondf = pd.read_csv("https://download.bls.gov/pub/time.series/cu/cu.item", sep="\t")``` New in version 1.3.0.A custom header can be sent alongside HTTP(s) requests by passing a dictionaryof header key value mappings to the `storage_options` keyword argument as shown below: ```pythonheaders = {"User-Agent": "pandas"}df = pd.read_csv(    "https://download.bls.gov/pub/time.series/cu/cu.item",    sep="\t",    storage_options=headers)``` All URLs which are not local files or HTTP(s) are handled by[fsspec](https://filesystem-spec.readthedocs.io/en/latest/), if installed, and its various filesystem implementations(including Amazon S3, Google Cloud, SSH, FTP, webHDFS…).Some of these implementations will require additional packages to beinstalled, for exampleS3 URLs require the [s3fs](https://pypi.org/project/s3fs/) library: ```pythondf = pd.read_json("s3://pandas-test/adatafile.json")``` When dealing with remote storage systems, you might needextra configuration with environment variables or config files inspecial locations. For example, to access data in your S3 bucket,you will need to define credentials in one of the several ways listed inthe [S3Fs documentation](https://s3fs.readthedocs.io/en/latest/credentials). The same is truefor several of the storage backends, and you should follow the linksat [fsimpl1](https://filesystem-spec.readthedocs.io/en/latest/api.htmlbuilt-in-implementations) for implementations built into `fsspec` and [fsimpl2](https://filesystem-spec.readthedocs.io/en/latest/api.htmlother-known-implementations)for those not included in the main `fsspec`distribution.You can also pass parameters directly to the backend driver. For example,if you do *not* have S3 credentials, you can still access public data byspecifying an anonymous connection, such asNew in version 1.2.0. ```pythonpd.read_csv(    "s3://ncei-wcsd-archive/data/processed/SH1305/18kHz/SaKe2013"    "-D20130523-T080854_to_SaKe2013-D20130523-T085643.csv",    storage_options={"anon": True},)``` `fsspec` also allows complex URLs, for accessing data in compressedarchives, local caching of files, and more. To locally cache the aboveexample, you would modify the call to ```pythonpd.read_csv(    "simplecache::s3://ncei-wcsd-archive/data/processed/SH1305/18kHz/"    "SaKe2013-D20130523-T080854_to_SaKe2013-D20130523-T085643.csv",    storage_options={"s3": {"anon": True}},)``` where we specify that the “anon” parameter is meant for the “s3” part ofthe implementation, not to the caching implementation. Note that this caches to a temporarydirectory for the duration of the session only, but you can also specifya permanent store.  Writing out data  Writing to CSV formatThe `Series` and `DataFrame` objects have an instance method `to_csv` whichallows storing the contents of the object as a comma-separated-values file. Thefunction 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 numbers  - `columns`: 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 the `DataFrame` uses 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 3  - `line_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-numeric  - `quotechar`: Character used to quote fields (default ‘”’)  - `doublequote`: Control quoting of `quotechar` in fields (default True)  - `escapechar`: Character used to escape `sep` and `quotechar` when  appropriate (default None)  - `chunksize`: Number of rows to write at a time  - `date_format`: Format string for datetime objects    Writing a formatted stringThe `DataFrame` object has an instance method `to_string` which allows controlover the string representation of the object. All arguments are optional:- `buf` default None, for example a StringIO object  - `columns` default None, which columns to write  - `col_space` default None, minimum width of each column.  - `na_rep` default `NaN`, representation of NA value  - `formatters` default None, a dictionary (by column) of functions each of  which takes a single argument and returns a formatted string  - `float_format` default None, a function which takes a single (float)  argument and returns a formatted string; to be applied to floats in the  `DataFrame`.  - `sparsify` default True, set to False for a `DataFrame` with a hierarchical  index to print every MultiIndex key at each row.  - `index_names` default True, will print the names of the indices  - `index` default True, will print the index (ie, row labels)  - `header` default True, will print the column labels  - `justify` default `left`, 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` argumentwhich, if set to `True`, will additionally output the length of the Series.  JSONRead and write `JSON` format files and strings.  Writing JSONA `Series` or `DataFrame` can be converted to a valid JSON string. Use `to_json`with optional parameters:- `path_or_buf` : the pathname or buffer to write the output  This can be `None` in which case a JSON string is returned  - `orient` :    `Series`:  - default is `index`    - allowed values are {`split`, `records`, `index`}  `DataFrame`:  - default is `columns`    - allowed values are {`split`, `records`, `index`, `columns`, `values`, `table`}      The format of the JSON string   The format of the JSON string| `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` : string, type of date conversion, ‘epoch’ for timestamp, ‘iso’ for ISO8601.  - `double_precision` : The number of decimal places to use when encoding floating point values, default 10.  - `force_ascii` : force encoded string to be ASCII, default True.  - `date_unit` : The time unit to encode to, governs timestamp and ISO8601 precision. One of ‘s’, ‘ms’, ‘us’ or ‘ns’ for seconds, milliseconds, microseconds and nanoseconds respectively. Default ‘ms’.  - `default_handler` : The handler to call if an object cannot otherwise be converted to a suitable format for JSON. Takes a single argument, which is the object to convert, and returns a serializable object.  - `lines` : If `records` orient, then will write each record per line as json.  Note `NaN`’s, `NaT`’s and `None` will be converted to `null` and `datetime` objects will be converted based on the `date_format` and `date_unit` parameters.<jupyter_code>dfj = pd.DataFrame(np.random.randn(5, 2), columns=list("AB"))
json = dfj.to_json()
json<jupyter_output><empty_output><jupyter_text>Orient optionsThere are a number of different options for the format of the resulting JSONfile / string. Consider the following `DataFrame` and `Series`:<jupyter_code>dfjo = pd.DataFrame(
    dict(A=range(1, 4), B=range(4, 7), C=range(7, 10)),
    columns=list("ABC"),
    index=list("xyz"),
)
dfjo
sjo = pd.Series(dict(x=15, y=16, z=17), name="D")
sjo<jupyter_output><empty_output><jupyter_text>**Column oriented** (the default for `DataFrame`) serializes the data asnested JSON objects with column labels acting as the primary index:<jupyter_code>dfjo.to_json(orient="columns")
# Not available for Series<jupyter_output><empty_output><jupyter_text>**Index oriented** (the default for `Series`) similar to column orientedbut the index labels are now primary:<jupyter_code>dfjo.to_json(orient="index")
sjo.to_json(orient="index")<jupyter_output><empty_output><jupyter_text>**Record oriented** serializes the data to a JSON array of column -> value records,index labels are not included. This is useful for passing `DataFrame` data to plottinglibraries, for example the JavaScript library `d3.js`:<jupyter_code>dfjo.to_json(orient="records")
sjo.to_json(orient="records")<jupyter_output><empty_output><jupyter_text>**Value oriented** is a bare-bones option which serializes to nested JSON arrays ofvalues only, column and index labels are not included:<jupyter_code>dfjo.to_json(orient="values")
# Not available for Series<jupyter_output><empty_output><jupyter_text>**Split oriented** serializes to a JSON object containing separate entries forvalues, index and columns. Name is also included for `Series`:<jupyter_code>dfjo.to_json(orient="split")
sjo.to_json(orient="split")<jupyter_output><empty_output><jupyter_text>**Table oriented** serializes to the JSON [Table Schema](https://specs.frictionlessdata.io/table-schema/), allowing for thepreservation of metadata including but not limited to dtypes and index names.>**Note**>>Any orient option that encodes to a JSON object will not preserve the ordering ofindex and column labels during round-trip serialization. If you wish to preservelabel ordering use the `split` option as it uses ordered containers.  Date handlingWriting in ISO date format:<jupyter_code>dfd = pd.DataFrame(np.random.randn(5, 2), columns=list("AB"))
dfd["date"] = pd.Timestamp("20130101")
dfd = dfd.sort_index(axis=1, ascending=False)
json = dfd.to_json(date_format="iso")
json<jupyter_output><empty_output><jupyter_text>Writing in ISO date format, with microseconds:<jupyter_code>json = dfd.to_json(date_format="iso", date_unit="us")
json<jupyter_output><empty_output><jupyter_text>Epoch timestamps, in seconds:<jupyter_code>json = dfd.to_json(date_format="epoch", date_unit="s")
json<jupyter_output><empty_output><jupyter_text>Writing to a file, with a date index and a date column:<jupyter_code>dfj2 = dfj.copy()
dfj2["date"] = pd.Timestamp("20130101")
dfj2["ints"] = list(range(5))
dfj2["bools"] = True
dfj2.index = pd.date_range("20130101", periods=5)
dfj2.to_json("test.json")

with open("test.json") as fh:
    print(fh.read())<jupyter_output>{"A":{"1356998400000":1.1844539495,"1357084800000":0.4749144577,"1357171200000":-0.7177391336,"1357257600000":-1.267206694,"1357344000000":0.2650654028},"B":{"1356998400000":2.0947454256,"1357084800000":-0.0679918432,"1357171200000":-0.1784374159,"1357257600000":-0.3005342892,"1357344000000":0.2816821055},"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}}<jupyter_text>Fallback behaviorIf the JSON serializer cannot handle the container contents directly it willfall back in the following manner:- if the dtype is unsupported (e.g. `np.complex_`) then the `default_handler`, if provided, will be called  for each value, otherwise an exception is raised.  - if an object is unsupported it will attempt the following:      - check if the object has defined a `toDict` method and call it.    A `toDict` method should return a `dict` which will then be JSON serialized.    - invoke the `default_handler` if one was provided.    - convert the object to a `dict` by traversing its contents. However this will often fail    with an `OverflowError` or give unexpected results.    In general the best approach for unsupported objects or dtypes is to provide a `default_handler`.For example: ```python>>> DataFrame([1.0, 2.0, complex(1.0, 2.0)]).to_json()   raisesRuntimeError: Unhandled numpy dtype 15``` can be dealt with by specifying a simple `default_handler`:<jupyter_code>pd.DataFrame([1.0, 2.0, complex(1.0, 2.0)]).to_json(default_handler=str)<jupyter_output><empty_output><jupyter_text>Reading JSONReading a JSON string to pandas object can take a number of parameters.The parser will try to parse a `DataFrame` if `typ` is not supplied oris `None`. To explicitly force `Series` parsing, pass `typ=series`- `filepath_or_buffer` : a **VALID** JSON string or file handle / StringIO. The string could be  a URL. Valid URL schemes include http, ftp, S3, and file. For file URLs, a host  is expected. For instance, a local file could be  file ://localhost/path/to/table.json  - `typ`    : type of object to recover (series or frame), default ‘frame’  - `orient` :        Series :    - default is `index`    - allowed values are {`split`, `records`, `index`}      DataFrame    - default is `columns`    - allowed values are {`split`, `records`, `index`, `columns`, `values`, `table`}            The format of the JSON string  The format of the JSON string| `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](https://specs.frictionlessdata.io/table-schema/) |- `dtype` : if True, infer dtypes, if a dict of column to dtype, then use those, if `False`, then don’t infer dtypes at all, default is True, apply only to the data.  - `convert_axes` : boolean, try to convert the axes to the proper dtypes, default is `True`  - `convert_dates` : a list of columns to parse for dates; If `True`, then try to parse date-like columns, default is `True`.  - `keep_default_dates` : boolean, default `True`. If parsing dates, then parse the default date-like columns.  - `numpy` : direct decoding to NumPy arrays. default is `False`;  Supports numeric data only, although labels may be non-numeric. Also note that the JSON ordering **MUST** be the same for each term if `numpy=True`.  - `precise_float` : boolean, default `False`. Set to enable usage of higher precision (strtod) function when decoding string to double values. Default (`False`) is to use fast but less precise builtin functionality.  - `date_unit` : string, the timestamp unit to detect if converting dates. Default  None. By default the timestamp precision will be detected, if this is not desired  then pass one of ‘s’, ‘ms’, ‘us’ or ‘ns’ to force timestamp precision to  seconds, milliseconds, microseconds or nanoseconds respectively.  - `lines` : reads file as one json object per line.  - `encoding` : The encoding to use to decode py3 bytes.  - `chunksize` : when used in combination with `lines=True`, return a JsonReader which reads in `chunksize` lines per iteration.  The parser will raise one of `ValueError/TypeError/AssertionError` if the JSON is not parseable.If a non-default `orient` was used when encoding to JSON be sure to pass the sameoption here so that decoding produces sensible results, see [Orient Options](orient-options) for anoverview.  Data conversionThe default of `convert_axes=True`, `dtype=True`, and `convert_dates=True`will try to parse the axes, and all of the data into appropriate types,including dates. If you need to override specific dtypes, pass a dict to`dtype`. `convert_axes` should only be set to `False` if you need topreserve string-like numbers (e.g. ‘1’, ‘2’) in an axes.>**Note**>>Large integer values may be converted to dates if `convert_dates=True` and the data and / or column labels appear ‘date-like’. The exact threshold depends on the `date_unit` specified. ‘date-like’ means that the column label meets one of the following criteria:>> - it ends with `'_at'`  >- it ends with `'_time'`  >- it begins with `'timestamp'`  >- it is `'modified'`  >- it is `'date'`  >**Warning**>>When reading JSON data, automatic coercing into dtypes has some quirks:>> - an index can be reconstructed in a different order from serialization, that is, the returned order is not guaranteed to be the same as before serialization  >- a column that was `float` data will be converted to `integer` if it can be done safely, e.g. a column of `1.`  >- bool columns will be converted to `integer` on reconstruction  >>Thus there are times where you may want to specify specific dtypes via the `dtype` keyword argument.Reading from a JSON string:<jupyter_code>pd.read_json(json)<jupyter_output><empty_output><jupyter_text>Reading from a file:<jupyter_code>pd.read_json("test.json")<jupyter_output><empty_output><jupyter_text>Don’t convert any data (but still convert axes and dates):<jupyter_code>pd.read_json("test.json", dtype=object).dtypes<jupyter_output><empty_output><jupyter_text>Specify dtypes for conversion:<jupyter_code>pd.read_json("test.json", dtype={"A": "float32", "bools": "int8"}).dtypes<jupyter_output><empty_output><jupyter_text>Preserve string indices:<jupyter_code>si = pd.DataFrame(
    np.zeros((4, 4)), columns=list(range(4)), index=[str(i) for i in range(4)]
)
si
si.index
si.columns
json = si.to_json()

sij = pd.read_json(json, convert_axes=False)
sij
sij.index
sij.columns<jupyter_output><empty_output><jupyter_text>Dates written in nanoseconds need to be read back in nanoseconds:<jupyter_code>json = dfj2.to_json(date_unit="ns")

# Try to parse timestamps as milliseconds -> Won't Work
dfju = pd.read_json(json, date_unit="ms")
dfju

# Let pandas detect the correct precision
dfju = pd.read_json(json)
dfju

# Or specify that all timestamps are in nanoseconds
dfju = pd.read_json(json, date_unit="ns")
dfju<jupyter_output><empty_output><jupyter_text>The Numpy parameter>**Note**>>This param has been deprecated as of version 1.0.0 and will raise a `FutureWarning`.>>This supports numeric data only. Index and columns labels may be non-numeric, e.g. strings, dates etc.If `numpy=True` is passed to `read_json` an attempt will be made to sniffan appropriate dtype during deserialization and to subsequently decode directlyto NumPy arrays, bypassing the need for intermediate Python objects.This can provide speedups if you are deserialising a large amount of numericdata:<jupyter_code>randfloats = np.random.uniform(-100, 1000, 10000)
randfloats.shape = (1000, 10)
dffloats = pd.DataFrame(randfloats, columns=list("ABCDEFGHIJ"))
jsonfloats = dffloats.to_json()
%timeit pd.read_json(jsonfloats)
%timeit pd.read_json(jsonfloats, numpy=True)
# <magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
#%timeit pd.read_json(jsonfloats)<jupyter_output><magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be rem[...]<jupyter_text>The speedup is less noticeable for smaller datasets:<jupyter_code>jsonfloats = dffloats.head(100).to_json()
%timeit pd.read_json(jsonfloats)
%timeit pd.read_json(jsonfloats, numpy=True)
# <magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'<jupyter_output><magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be removed in a future version. Please take steps to stop the use of 'numpy'
<magic-timeit>:1: FutureWarning: the 'numpy' keyword is deprecated and will be rem[...]<jupyter_text>Direct NumPy decoding makes a number of assumptions and may fail or produceunexpected output if these assumptions are not satisfied:> - data is numeric.  - data is uniform. The dtype is sniffed from the first value decoded.  A `ValueError` may be raised, or incorrect output may be produced  if this condition is not satisfied.  - labels are ordered. Labels are only read from the first container, it is assumed  that each subsequent row / column has been encoded in the same order. This should be satisfied if the  data was encoded using `to_json` but may not be the case if the JSON  is from another source.<jupyter_code>os.remove("test.json")<jupyter_output><empty_output><jupyter_text>Normalizationpandas provides a utility function to take a dict or list of dicts and *normalize* this semi-structured datainto a flat table.<jupyter_code>data = [
    {"id": 1, "name": {"first": "Coleen", "last": "Volk"}},
    {"name": {"given": "Mark", "family": "Regner"}},
    {"id": 2, "name": "<NAME>"},
]
pd.json_normalize(data)
data = [
    {
        "state": "Florida",
        "shortname": "FL",
        "info": {"governor": "<NAME>"},
        "county": [
            {"name": "Dade", "population": 12345},
            {"name": "Broward", "population": 40000},
            {"name": "<NAME>", "population": 60000},
        ],
    },
    {
        "state": "Ohio",
        "shortname": "OH",
        "info": {"governor": "<NAME>"},
        "county": [
            {"name": "Summit", "population": 1234},
            {"name": "Cuyahoga", "population": 1337},
        ],
    },
]

pd.json_normalize(data, "county", ["state", "shortname", ["info", "governor"]])<jupyter_output><empty_output><jupyter_text>The max_level parameter provides more control over which level to end normalization.With max_level=1 the following snippet normalizes until 1st nesting level of the provided dict.<jupyter_code>data = [
    {
        "CreatedBy": {"Name": "User001"},
        "Lookup": {
            "TextField": "Some text",
            "UserField": {"Id": "ID001", "Name": "Name001"},
        },
        "Image": {"a": "b"},
    }
]
pd.json_normalize(data, max_level=1)<jupyter_output><empty_output><jupyter_text>Line delimited jsonpandas is able to read and write line-delimited json files that are common in data processing pipelinesusing Hadoop or Spark.For line-delimited json files, pandas can also return an iterator which reads in `chunksize` lines at a time. This can be useful for large files or to read from a stream.<jupyter_code>jsonl = """
    {"a": 1, "b": 2}
    {"a": 3, "b": 4}
"""
df = pd.read_json(jsonl, lines=True)
df
df.to_json(orient="records", lines=True)

# reader is an iterator that returns ``chunksize`` lines each iteration
with pd.read_json(StringIO(jsonl), lines=True, chunksize=1) as reader:
    reader
    for chunk in reader:
        print(chunk)<jupyter_output>Empty DataFrame
Columns: []
Index: []
   a  b
0  1  2
   a  b
1  3  4<jupyter_text>Table schema[Table Schema](https://specs.frictionlessdata.io/table-schema/) is a spec for describing tabular datasets as a JSONobject. The JSON includes information on the field names, types, andother attributes. You can use the orient `table` to builda JSON string with two fields, `schema` and `data`.<jupyter_code>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"),
)
df
df.to_json(orient="table", date_format="iso")<jupyter_output><empty_output><jupyter_text>The `schema` field contains the `fields` key, which itself containsa list of column name to type pairs, including the `Index` or `MultiIndex`(see below for a list of types).The `schema` field also contains a `primaryKey` field if the (Multi)indexis unique.The second field, `data`, contains the serialized data with the `records`orient.The index is included, and any datetimes are ISO 8601 formatted, as requiredby the Table Schema spec.The full list of types supported are described in the Table Schemaspec. This table shows the mapping from pandas types:|pandas type|Table Schema type||:-------------:|:---------------:||int64|integer||float64|number||bool|boolean||datetime64[ns]|datetime||timedelta64[ns]|duration||categorical|any||object|str|A few notes on the generated table schema:- The `schema` object contains a `pandas_version` field. This contains  the version of pandas’ dialect of the schema, and will be incremented  with each revision.  - All dates are converted to UTC when serializing. Even timezone naive values,which are treated as UTC with an offset of 0.<jupyter_code>from pandas.io.json import build_table_schema

s = pd.Series(pd.date_range("2016", periods=4))
build_table_schema(s)<jupyter_output><empty_output><jupyter_text>- datetimes with a timezone (before serializing), include an additional field`tz` with the time zone name (e.g. `'US/Central'`).<jupyter_code>s_tz = pd.Series(pd.date_range("2016", periods=12, tz="US/Central"))
build_table_schema(s_tz)<jupyter_output><empty_output><jupyter_text>- Periods are converted to timestamps before serialization, and so have thesame behavior of being converted to UTC. In addition, periods will containand additional field `freq` with the period’s frequency, e.g. `'A-DEC'`.<jupyter_code>s_per = pd.Series(1, index=pd.period_range("2016", freq="A-DEC", periods=4))
build_table_schema(s_per)<jupyter_output><empty_output><jupyter_text>- Categoricals use the `any` type and an `enum` constraint listingthe set of possible values. Additionally, an `ordered` field is included:<jupyter_code>s_cat = pd.Series(pd.Categorical(["a", "b", "a"]))
build_table_schema(s_cat)<jupyter_output><empty_output><jupyter_text>- A `primaryKey` field, containing an array of labels, is included*if the index is unique*:<jupyter_code>s_dupe = pd.Series([1, 2], index=[1, 1])
build_table_schema(s_dupe)<jupyter_output><empty_output><jupyter_text>- The `primaryKey` behavior is the same with MultiIndexes, but in thiscase the `primaryKey` is an array:<jupyter_code>s_multi = pd.Series(1, index=pd.MultiIndex.from_product([("a", "b"), (0, 1)]))
build_table_schema(s_multi)<jupyter_output><empty_output><jupyter_text>- The default naming roughly follows these rules:      - For series, the `object.name` is used. If that’s none, then the    name is `values`    - For `DataFrames`, the stringified version of the column name is used    - For `Index` (not `MultiIndex`), `index.name` is used, with a    fallback to `index` if that is None.    - For `MultiIndex`, `mi.names` is used. If any level has no name,    then `level_` is used.    `read_json` also accepts `orient='table'` as an argument. This allows forthe preservation of metadata such as dtypes and index names in around-trippable manner.><jupyter_code>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"),
)
df
df.dtypes

df.to_json("test.json", orient="table")
new_df = pd.read_json("test.json", orient="table")
new_df
new_df.dtypes<jupyter_output><empty_output><jupyter_text>Please note that the literal string ‘index’ as the name of an `Index`is not round-trippable, nor are any names beginning with `'level_'` within a`MultiIndex`. These are used by default in `DataFrame.to_json()` toindicate missing values and the subsequent read cannot distinguish the intent.<jupyter_code>df.index.name = "index"
df.to_json("test.json", orient="table")
new_df = pd.read_json("test.json", orient="table")
print(new_df.index.name)
os.remove("test.json")<jupyter_output><empty_output><jupyter_text>HTML  Reading HTML contentWe **highly encourage** you to read the [HTML Table Parsing gotchas](io-html-gotchas)below regarding the issues surrounding the BeautifulSoup4/html5lib/lxml parsers.The top-level `read_html()` function can accept an HTMLstring/file/URL and will parse HTML tables into list of pandas `DataFrames`.Let’s look at a few examples.>**Note**>>`read_html` returns a `list` of `DataFrame` objects, even if there isonly a single table contained in the HTML content.Read a URL with no options:<jupyter_code>url = (
    "https://raw.githubusercontent.com/pandas-dev/pandas/master/"
    "pandas/tests/io/data/html/spam.html"
)
dfs = pd.read_html(url)

dfs<jupyter_output><empty_output><jupyter_text>Read in the content of the “banklist.html” file and pass it to `read_html`as a string:<jupyter_code>rel_path = os.path.join("..", "data","FailedBankList.html")
# in jupyter you need a relative path, as you access through the server, not the filessystem
file_path = os.path.abspath(rel_path)
filename =rel_path  
file_path
with open(filename,"r",encoding='UTF-8', newline='\n') as f:
    dfs = pd.read_html(f.read())
dfs<jupyter_output><empty_output><jupyter_text>You can even pass in an instance of `StringIO` if you so desire:<jupyter_code>with open(rel_path, "r", encoding='UTF-8') as f:
    sio = StringIO(f.read())

dfs = pd.read_html(sio)
dfs<jupyter_output><empty_output><jupyter_text>Read a URL to HTML page with multiple tables and match a table that contains a specific text:<jupyter_code># provide your own URL here
url = 'https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/'
match = "Metcalf Bank"
df_list = pd.read_html(url, match=match)
type(df_list)
#df_list = df_list[df_list.columns[0:3]]<jupyter_output><empty_output><jupyter_text>Specify a header row (by default `` or `` elements located within a`` are used to form the column index, if multiple rows are contained within`` then a MultiIndex is created); if specified, the header row is takenfrom the data minus the parsed header elements (`` elements).<jupyter_code>dfs = pd.read_html(url, header=0)
type(dfs)<jupyter_output><empty_output><jupyter_text>Specify an index column:<jupyter_code>dfs = pd.read_html(url, index_col=0)<jupyter_output><empty_output><jupyter_text>Specify a number of rows to skip:<jupyter_code>dfs = pd.read_html(url, skiprows=0)<jupyter_output><empty_output><jupyter_text>Specify a number of rows to skip using a list (`range` worksas well):<jupyter_code>dfs = pd.read_html(url, skiprows=range(2))<jupyter_output><empty_output><jupyter_text>Specify an HTML attribute: ```pythondfs1 = 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``` Specify values that should be converted to NaN:<jupyter_code>dfs = pd.read_html(url, na_values=["No Acquirer"])<jupyter_output><empty_output><jupyter_text>Specify whether to keep the default set of NaN values:<jupyter_code>dfs = pd.read_html(url, keep_default_na=False)<jupyter_output><empty_output><jupyter_text>Specify converters for columns. This is useful for numerical text data that hasleading zeros.  By default columns that are numerical are cast to numerictypes and the leading zeros are lost. To avoid this, we can convert thesecolumns to strings.<jupyter_code>url_mcc = "https://en.wikipedia.org/wiki/Mobile_Network_Codes_in_ITU_region_2xx_(Europe)#Albania_%E2%80%93_AL"
dfs = pd.read_html(
    url_mcc,
    match="One Telecommunications",
    header=0,
    converters={"MNC": str},
)
dfs<jupyter_output><empty_output><jupyter_text>Use some combination of the above:<jupyter_code>dfs = pd.read_html(url_mcc, match="Telecom Liechtenstein AG", index_col=0)
dfs<jupyter_output><empty_output><jupyter_text>Read in pandas `to_html` output (with some loss of floating point precision):<jupyter_code>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)<jupyter_output><empty_output><jupyter_text>The `lxml` backend will raise an error on a failed parse if that is the onlyparser you provide. If you only have a single parser you can provide just astring, but it is considered good practice to pass a list with one string if,for example, the function expects a sequence of strings. You may use:<jupyter_code>dfs = pd.read_html(url, match="Metcalf Bank", index_col=0, flavor=["lxml"])<jupyter_output><empty_output><jupyter_text>Or you could pass `flavor='lxml'` without a list:<jupyter_code>dfs = pd.read_html(url,match= "Metcalf Bank", index_col=0, flavor="lxml")<jupyter_output><empty_output><jupyter_text>However, if you have bs4 and html5lib installed and pass `None` or `['lxml','bs4']` then the parse will most likely succeed. Note that *as soon as a parsesucceeds, the function will return*.<jupyter_code>dfs = pd.read_html(url, match="Metcalf Bank", index_col=0, flavor=["lxml", "bs4"])<jupyter_output><empty_output><jupyter_text>Writing to HTML files`DataFrame` objects have an instance method `to_html` which renders thecontents of the `DataFrame` as an HTML table. The function arguments are asin the method `to_string` described above.>**Note**>>Not all of the possible options for `DataFrame.to_html` are shown here forbrevity’s sake. See `to_html()` for thefull set of options.<jupyter_code>def write_html(df, filename, *args, **kwargs):
    static = os.path.abspath(os.path.join("..", "data"))
    with open(os.path.join(static, filename + ".html"), "w") as f:
        df.to_html(f, *args, **kwargs)
df = pd.DataFrame(np.random.randn(2, 2))
df
print(df.to_html())  # raw html
write_html(df, "basic")<jupyter_output><empty_output><jupyter_text>HTML:The `columns` argument will limit the columns shown:<jupyter_code>print(df.to_html(columns=[0]))
write_html(df, "columns", columns=[0])<jupyter_output><empty_output><jupyter_text>HTML:`float_format` takes a Python callable to control the precision of floatingpoint values:<jupyter_code>print(df.to_html(float_format="{0:.10f}".format))
write_html(df, "float_format", float_format="{0:.10f}".format)<jupyter_output><empty_output><jupyter_text>HTML:`bold_rows` will make the row labels bold by default, but you can turn thatoff:<jupyter_code>print(df.to_html(bold_rows=False))
write_html(df, "nobold", bold_rows=False)<jupyter_output><empty_output><jupyter_text>The `classes` argument provides the ability to give the resulting HTMLtable CSS classes. Note that these classes are *appended* to the existing`'dataframe'` class.<jupyter_code>print(df.to_html(classes=["awesome_table_class", "even_more_awesome_class"]))<jupyter_output><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.043628</td>
      <td>0.206519</td>
    </tr>
    <tr>
      <th>1</th>
      <td>-1.443929</td>
      <td>-1.107380</td>
    </tr>
  </tbody>
</table><jupyter_text>The `render_links` argument provides the ability to add hyperlinks to cellsthat contain URLs.<jupyter_code>url_df = pd.DataFrame(
    {
        "name": ["Python", "pandas"],
        "url": ["https://www.python.org/", "https://pandas.pydata.org"],
    }
)
print(url_df.to_html(render_links=True))
write_html(url_df, "render_links", render_links=True)<jupyter_output><empty_output><jupyter_text>HTML:Finally, the `escape` argument allows you to control whether the“” and “&” characters escaped in the resulting HTML (by default it is`True`). So to get the HTML without escaped characters pass `escape=False`<jupyter_code>df = pd.DataFrame({"a": list("&<>"), "b": np.random.randn(3)})
write_html(df, "escape")
write_html(df, "noescape", escape=False)<jupyter_output><empty_output><jupyter_text>Escaped:<jupyter_code>print(df.to_html())<jupyter_output><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>&amp;</td>
      <td>-1.629263</td>
    </tr>
    <tr>
      <th>1</th>
      <td>&lt;</td>
      <td>-0.860038</td>
    </tr>
    <tr>
      <th>2</th>
      <td>&gt;</td>
      <td>0.881681</td>
    </tr>
  </tbody>
</table><jupyter_text>Not escaped:<jupyter_code>print(df.to_html(escape=False))<jupyter_output><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>-1.629263</td>
    </tr>
    <tr>
      <th>1</th>
      <td><</td>
      <td>-0.860038</td>
    </tr>
    <tr>
      <th>2</th>
      <td>></td>
      <td>0.881681</td>
    </tr>
  </tbody>
</table><jupyter_text>>**Note**>>Some browsers may not show a difference in the rendering of the previous twoHTML tables.  HTML Table Parsing GotchasThere are some versioning issues surrounding the libraries that are used toparse HTML tables in the top-level pandas io function `read_html`.**Issues with** [**lxml**](https://lxml.de)- Benefits      - [**lxml**](https://lxml.de) is very fast.    - [**lxml**](https://lxml.de) requires Cython to install correctly.    - Drawbacks      - [**lxml**](https://lxml.de) does *not* make any guarantees about the results of its parse    *unless* it is given [**strictly valid markup**](https://validator.w3.org/docs/help.htmlvalidation_basics).    - In light of the above, we have chosen to allow you, the user, to use the    [**lxml**](https://lxml.de) backend, but **this backend will use** [**html5lib**](https://github.com/html5lib/html5lib-python) if [**lxml**](https://lxml.de)    fails to parse    - It is therefore *highly recommended* that you install both    [**BeautifulSoup4**](https://www.crummy.com/software/BeautifulSoup) and [**html5lib**](https://github.com/html5lib/html5lib-python), so that you will still get a valid    result (provided everything else is valid) even if [**lxml**](https://lxml.de) fails.    **Issues with** [**BeautifulSoup4**](https://www.crummy.com/software/BeautifulSoup) **using** [**lxml**](https://lxml.de) **as a backend**- The above issues hold here as well since [**BeautifulSoup4**](https://www.crummy.com/software/BeautifulSoup) is essentially  just a wrapper around a parser backend.  **Issues with** [**BeautifulSoup4**](https://www.crummy.com/software/BeautifulSoup) **using** [**html5lib**](https://github.com/html5lib/html5lib-python) **as a backend**- Benefits      - [**html5lib**](https://github.com/html5lib/html5lib-python) is far more lenient than [**lxml**](https://lxml.de) and consequently deals    with *real-life markup* in a much saner way rather than just, e.g.,    dropping an element without notifying you.    - [**html5lib**](https://github.com/html5lib/html5lib-python) *generates valid HTML5 markup from invalid markup    automatically*. This is extremely important for parsing HTML tables,    since it guarantees a valid document. However, that does NOT mean that    it is “correct”, since the process of fixing markup does not have a    single definition.    - [**html5lib**](https://github.com/html5lib/html5lib-python) is pure Python and requires no additional build steps beyond    its own installation.    - Drawbacks      - The biggest drawback to using [**html5lib**](https://github.com/html5lib/html5lib-python) is that it is slow as    molasses.  However consider the fact that many tables on the web are not    big enough for the parsing algorithm runtime to matter. It is more    likely that the bottleneck will be in the process of reading the raw    text from the URL over the web, i.e., IO (input-output). For very large    tables, this might not be true.    **strictly valid markup****html5lib****BeautifulSoup4****lxml**  LaTeXNew in version 1.3.0.Currently there are no methods to read from LaTeX, only output methods.  Writing to LaTeX files>**Note**>>DataFrame *and* Styler objects currently have a `to_latex` method. We recommendusing the [Styler.to_latex()](../reference/api/pandas.io.formats.style.Styler.to_latex.rst) methodover [DataFrame.to_latex()](../reference/api/pandas.DataFrame.to_latex.rst) due to the former’s greater flexibility withconditional styling, and the latter’s possible future deprecation.Review the documentation for [Styler.to_latex](../reference/api/pandas.io.formats.style.Styler.to_latex.rst),which gives examples of conditional styling and explains the operation of its keywordarguments.For simple application the following pattern is sufficient.<jupyter_code>df = pd.DataFrame([[1, 2], [3, 4]], index=["a", "b"], columns=["c", "d"])
print(df.to_latex())<jupyter_output>\begin{tabular}{lrr}
\toprule
{} &  c &  d \\
\midrule
a &  1 &  2 \\
b &  3 &  4 \\
\bottomrule
\end{tabular}<jupyter_text>To format values before output, chain the [Styler.format](../reference/api/pandas.io.formats.style.Styler.format.rst)method.<jupyter_code>print(df.style.format("€ {}").to_latex())
#AttributeError: 'Styler' object has no attribute 'to_latex'<jupyter_output><empty_output><jupyter_text>XML  Reading XMLNew in version 1.3.0.The top-level `read_xml()` function can accept an XMLstring/file/URL and will parse nodes and attributes into a pandas `DataFrame`.>**Note**>>Since there is no standard XML structure where design types can vary inmany ways, `read_xml` works best with flatter, shallow versions. Ifan XML document is deeply nested, use the `stylesheet` feature totransform XML into a flatter version.Let’s look at a few examples.Read an XML string:<jupyter_code>xml = """<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
  <book category="cooking">
    <title lang="en">Everyday Italian</title>
    <author><NAME></author>
    <year>2005</year>
    <price>30.00</price>
  </book>
  <book category="children">
    <title lang="en"><NAME></title>
    <author><NAME></author>
    <year>2005</year>
    <price>29.99</price>
  </book>
  <book category="web">
    <title lang="en">Learning XML</title>
    <author><NAME></author>
    <year>2003</year>
    <price>39.95</price>
  </book>
</bookstore>"""

# New in version 1.3.0.
df = pd.read_xml(xml)
df<jupyter_output><empty_output><jupyter_text>Read a URL with no options:<jupyter_code>df = pd.read_xml("https://www.w3schools.com/xml/books.xml")
df
print(pd.__version__)<jupyter_output>1.2.5<jupyter_text>Read in the content of the “books.xml” file and pass it to `read_xml`as a string:<jupyter_code>rel_path = os.path.join("..", "pandas", "tests", "io", "data", "xml",
                        "books.xml")
file_path = os.path.abspath(rel_path)
with open(file_path, "r") as f:
    df = pd.read_xml(f.read())
df<jupyter_output><empty_output><jupyter_text>Read in the content of the “books.xml” as instance of `StringIO` or`BytesIO` and pass it to `read_xml`:<jupyter_code>with open(file_path, "r") as f:
    sio = StringIO(f.read())

df = pd.read_xml(sio)
df
with open(file_path, "rb") as f:
    bio = BytesIO(f.read())

df = pd.read_xml(bio)
df<jupyter_output><empty_output><jupyter_text>Even read XML from AWS S3 buckets such as Python Software Foundation’s IRS 990 Form:<jupyter_code>df = pd.read_xml(
    "s3://irs-form-990/201923199349319487_public.xml",
    xpath=".//irs:Form990PartVIISectionAGrp",
    namespaces={"irs": "http://www.irs.gov/efile"}
)
df<jupyter_output><empty_output><jupyter_text>With [lxml](https://lxml.de) as default `parser`, you access the full-featured XML librarythat extends Python’s ElementTree API. One powerful tool is ability to querynodes selectively or conditionally with more expressive XPath:<jupyter_code>df = pd.read_xml(file_path, xpath="//book[year=2005]")
df<jupyter_output><empty_output><jupyter_text>Specify only elements or only attributes to parse:<jupyter_code>df = pd.read_xml(file_path, elems_only=True)
df
df = pd.read_xml(file_path, attrs_only=True)
df<jupyter_output><empty_output><jupyter_text>XML documents can have namespaces with prefixes and default namespaces withoutprefixes both of which are denoted with a special attribute `xmlns`. In orderto parse by node under a namespace context, `xpath` must reference a prefix.For example, below XML contains a namespace with prefix, `doc`, and URI at`https://example.com`. In order to parse `doc:row` nodes,`namespaces` must be used.<jupyter_code>xml = """<?xml version='1.0' encoding='utf-8'?>
<doc:data xmlns:doc="https://example.com">
  <doc:row>
    <doc:shape>square</doc:shape>
    <doc:degrees>360</doc:degrees>
    <doc:sides>4.0</doc:sides>
  </doc:row>
  <doc:row>
    <doc:shape>circle</doc:shape>
    <doc:degrees>360</doc:degrees>
    <doc:sides/>
  </doc:row>
  <doc:row>
    <doc:shape>triangle</doc:shape>
    <doc:degrees>180</doc:degrees>
    <doc:sides>3.0</doc:sides>
  </doc:row>
</doc:data>"""

df = pd.read_xml(xml,
                 xpath="//doc:row",
                 namespaces={"doc": "https://example.com"})
df<jupyter_output><empty_output><jupyter_text>Similarly, an XML document can have a default namespace without prefix. Failingto assign a temporary prefix will return no nodes and raise a `ValueError`.But assigning *any* temporary name to correct URI allows parsing by nodes.<jupyter_code>xml = """<?xml version='1.0' encoding='utf-8'?>
<data xmlns="https://example.com">
 <row>
   <shape>square</shape>
   <degrees>360</degrees>
   <sides>4.0</sides>
 </row>
 <row>
   <shape>circle</shape>
   <degrees>360</degrees>
   <sides/>
 </row>
 <row>
   <shape>triangle</shape>
   <degrees>180</degrees>
   <sides>3.0</sides>
 </row>
</data>"""

df = pd.read_xml(xml,
                 xpath="//pandas:row",
                 namespaces={"pandas": "https://example.com"})
df<jupyter_output><empty_output><jupyter_text>However, if XPath does not reference node names such as default, `/*`, then`namespaces` is not required.With [lxml](https://lxml.de) as parser, you can flatten nested XML documents with an XSLTscript which also can be string/file/URL types. As background, [XSLT](https://www.w3.org/TR/xslt/) isa special-purpose language written in a special XML file that can transformoriginal XML documents into other XML, HTML, even text (CSV, JSON, etc.)using an XSLT processor.For example, consider this somewhat nested structure of Chicago “L” Rideswhere station and rides elements encapsulate data in their own sections.With below XSLT, `lxml` can transform original nested document into a flatteroutput (as shown below for demonstration) for easier parse into `DataFrame`:<jupyter_code>xml = """<?xml version='1.0' encoding='utf-8'?>
 <response>
  <row>
    <station id="40850" name="Library"/>
    <month>2020-09-01T00:00:00</month>
    <rides>
      <avg_weekday_rides>864.2</avg_weekday_rides>
      <avg_saturday_rides>534</avg_saturday_rides>
      <avg_sunday_holiday_rides>417.2</avg_sunday_holiday_rides>
    </rides>
  </row>
  <row>
    <station id="41700" name="Washington/Wabash"/>
    <month>2020-09-01T00:00:00</month>
    <rides>
      <avg_weekday_rides>2707.4</avg_weekday_rides>
      <avg_saturday_rides>1909.8</avg_saturday_rides>
      <avg_sunday_holiday_rides>1438.6</avg_sunday_holiday_rides>
    </rides>
  </row>
  <row>
    <station id="40380" name="Clark/Lake"/>
    <month>2020-09-01T00:00:00</month>
    <rides>
      <avg_weekday_rides>2949.6</avg_weekday_rides>
      <avg_saturday_rides>1657</avg_saturday_rides>
      <avg_sunday_holiday_rides>1453.8</avg_sunday_holiday_rides>
    </rides>
  </row>
 </response>"""

xsl = """<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" omit-xml-declaration="no" indent="yes"/>
   <xsl:strip-space elements="*"/>
   <xsl:template match="/response">
      <xsl:copy>
        <xsl:apply-templates select="row"/>
      </xsl:copy>
   </xsl:template>
   <xsl:template match="row">
      <xsl:copy>
        <station_id><xsl:value-of select="station/@id"/></station_id>
        <station_name><xsl:value-of select="station/@name"/></station_name>
        <xsl:copy-of select="month|rides/*"/>
      </xsl:copy>
   </xsl:template>
 </xsl:stylesheet>"""

output = """<?xml version='1.0' encoding='utf-8'?>
 <response>
   <row>
      <station_id>40850</station_id>
      <station_name>Library</station_name>
      <month>2020-09-01T00:00:00</month>
      <avg_weekday_rides>864.2</avg_weekday_rides>
      <avg_saturday_rides>534</avg_saturday_rides>
      <avg_sunday_holiday_rides>417.2</avg_sunday_holiday_rides>
   </row>
   <row>
      <station_id>41700</station_id>
      <station_name>Washington/Wabash</station_name>
      <month>2020-09-01T00:00:00</month>
      <avg_weekday_rides>2707.4</avg_weekday_rides>
      <avg_saturday_rides>1909.8</avg_saturday_rides>
      <avg_sunday_holiday_rides>1438.6</avg_sunday_holiday_rides>
   </row>
   <row>
      <station_id>40380</station_id>
      <station_name>Clark/Lake</station_name>
      <month>2020-09-01T00:00:00</month>
      <avg_weekday_rides>2949.6</avg_weekday_rides>
      <avg_saturday_rides>1657</avg_saturday_rides>
      <avg_sunday_holiday_rides>1453.8</avg_sunday_holiday_rides>
   </row>
 </response>"""

df = pd.read_xml(xml, stylesheet=xsl)
df<jupyter_output><empty_output><jupyter_text>Writing XMLNew in version 1.3.0.`DataFrame` objects have an instance method `to_xml` which renders thecontents of the `DataFrame` as an XML document.>**Note**>>This method does not support special properties of XML including DTD,CData, XSD schemas, processing instructions, comments, and others.Only namespaces at the root level is supported. However, `stylesheet`allows design changes after initial output.Let’s look at a few examples.Write an XML without options:<jupyter_code>geom_df = pd.DataFrame(
    {
        "shape": ["square", "circle", "triangle"],
        "degrees": [360, 360, 180],
        "sides": [4, np.nan, 3],
    }
)

print(geom_df.to_xml())<jupyter_output><empty_output><jupyter_text>Write an XML with new root and row name:<jupyter_code>print(geom_df.to_xml(root_name="geometry", row_name="objects"))<jupyter_output><empty_output><jupyter_text>Write an attribute-centric XML:<jupyter_code>print(geom_df.to_xml(attr_cols=geom_df.columns.tolist()))<jupyter_output><empty_output><jupyter_text>Write a mix of elements and attributes:<jupyter_code>print(
    geom_df.to_xml(
        index=False,
        attr_cols=['shape'],
        elem_cols=['degrees', 'sides'])
)<jupyter_output><empty_output><jupyter_text>Any `DataFrames` with hierarchical columns will be flattened for XML element nameswith levels delimited by underscores:<jupyter_code>ext_geom_df = pd.DataFrame(
    {
        "type": ["polygon", "other", "polygon"],
        "shape": ["square", "circle", "triangle"],
        "degrees": [360, 360, 180],
        "sides": [4, np.nan, 3],
    }
)

pvt_df = ext_geom_df.pivot_table(index='shape',
                                 columns='type',
                                 values=['degrees', 'sides'],
                                 aggfunc='sum')
pvt_df

print(pvt_df.to_xml())<jupyter_output><empty_output><jupyter_text>Write an XML with default namespace:<jupyter_code>print(geom_df.to_xml(namespaces={"": "https://example.com"}))<jupyter_output><empty_output><jupyter_text>Write an XML with namespace prefix:<jupyter_code>print(
    geom_df.to_xml(namespaces={"doc": "https://example.com"},
                   prefix="doc")
)<jupyter_output><empty_output><jupyter_text>Write an XML without declaration or pretty print:<jupyter_code>print(
    geom_df.to_xml(xml_declaration=False,
                   pretty_print=False)
)<jupyter_output><empty_output><jupyter_text>Write an XML and transform with stylesheet:<jupyter_code>xsl = """<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" omit-xml-declaration="no" indent="yes"/>
   <xsl:strip-space elements="*"/>
   <xsl:template match="/data">
     <geometry>
       <xsl:apply-templates select="row"/>
     </geometry>
   </xsl:template>
   <xsl:template match="row">
     <object index="{index}">
       <xsl:if test="shape!='circle'">
           <xsl:attribute name="type">polygon</xsl:attribute>
       </xsl:if>
       <xsl:copy-of select="shape"/>
       <property>
           <xsl:copy-of select="degrees|sides"/>
       </property>
     </object>
   </xsl:template>
 </xsl:stylesheet>"""

print(geom_df.to_xml(stylesheet=xsl))<jupyter_output><empty_output><jupyter_text>XML Final Notes- All XML documents adhere to [W3C specifications](https://www.w3.org/TR/xml/). Both `etree` and `lxml`  parsers will fail to parse any markup document that is not well-formed or  follows XML syntax rules. Do be aware HTML is not an XML document unless it  follows XHTML specs. However, other popular markup types including KML, XAML,  RSS, MusicML, MathML are compliant [XML schemas](https://en.wikipedia.org/wiki/List_of_types_of_XML_schemas).  - For above reason, if your application builds XML prior to pandas operations,  use appropriate DOM libraries like `etree` and `lxml` to build the necessary  document and not by string concatenation or regex adjustments. Always remember  XML is a *special* text file with markup rules.  - With very large XML files (several hundred MBs to GBs), XPath and XSLT  can become memory-intensive operations. Be sure to have enough available  RAM for reading and writing to large XML files (roughly about 5 times the  size of text).  - Because XSLT is a programming language, use it with caution since such scripts  can pose a security risk in your environment and can run large or infinite  recursive operations. Always test scripts on small fragments before full run.  - The [etree](https://docs.python.org/3/library/xml.etree.elementtree.html) parser supports all functionality of both `read_xml` and  `to_xml` except for complex XPath and any XSLT. Though limited in features,  `etree` is still a reliable and capable parser and tree builder. Its  performance may trail `lxml` to a certain degree for larger files but  relatively unnoticeable on small to medium size files.    Excel filesThe `read_excel()` method can read Excel 2007+ (`.xlsx`) filesusing the `openpyxl` Python module. Excel 2003 (`.xls`) filescan be read using `xlrd`. Binary Excel (`.xlsb`)files can be read using `pyxlsb`.The `to_excel()` instance method is used forsaving a `DataFrame` to Excel.  Generally the semantics aresimilar to working with [csv](io-read-csv-table) data.See the [cookbook](44_cookbook.ipynbcookbook-excel) for some advanced strategies.The [xlwt](https://xlwt.readthedocs.io/en/latest/) package for writing old-style `.xls`excel files is no longer maintained.The [xlrd](https://xlrd.readthedocs.io/en/latest/) package is now only for readingold-style `.xls` files.Before pandas 1.3.0, the default argument `engine=None` to `read_excel()`would result in using the `xlrd` engine in many cases, including newExcel 2007+ (`.xlsx`) files. pandas will now default to using the[openpyxl](https://openpyxl.readthedocs.io/en/stable/) engine.It is strongly encouraged to install `openpyxl` to read Excel 2007+(`.xlsx`) files.**Please do not report issues when using ``xlrd`` to read ``.xlsx`` files.**This is no longer supported, switch to using `openpyxl` instead.Attempting to use the the `xlwt` engine will raise a `FutureWarning`unless the option `io.excel.xls.writer` is set to `"xlwt"`.While this option is now deprecated and will also raise a `FutureWarning`,it can be globally set and the warning suppressed. Users are recommended towrite `.xlsx` files using the `openpyxl` engine instead.  Reading Excel filesIn the most basic use-case, `read_excel` takes a path to an Excelfile, and the `sheet_name` indicating which sheet to parse. ```python Returns a DataFramepd.read_excel("path_to_file.xls", sheet_name="Sheet1")```   `ExcelFile` classTo facilitate working with multiple sheets from the same file, the `ExcelFile`class can be used to wrap the file and can be passed into `read_excel`There will be a performance benefit for reading multiple sheets as the file isread into memory only once. ```pythonxlsx = pd.ExcelFile("path_to_file.xls")df = pd.read_excel(xlsx, "Sheet1")``` The `ExcelFile` class can also be used as a context manager. ```pythonwith pd.ExcelFile("path_to_file.xls") as xls:    df1 = pd.read_excel(xls, "Sheet1")    df2 = pd.read_excel(xls, "Sheet2")``` The `sheet_names` property will generatea list of the sheet names in the file.The primary use-case for an `ExcelFile` is parsing multiple sheets withdifferent parameters: ```pythondata = {} For when Sheet1's format differs from Sheet2with 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)``` Note that if the same parsing parameters are used for all sheets, a listof sheet names can simply be passed to `read_excel` with no loss in performance. ```python using the ExcelFile classdata = {}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 functiondata = pd.read_excel(    "path_to_file.xls", ["Sheet1", "Sheet2"], index_col=None, na_values=["NA"])``` `ExcelFile` can also be called with a `xlrd.book.Book` objectas a parameter. This allows the user to control how the excel file is read.For example, sheets can be loaded on demand by calling `xlrd.open_workbook()`with `on_demand=True`. ```pythonimport xlrdxlrd_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")```   Specifying sheets>**Note**>>The second argument is `sheet_name`, not to be confused with `ExcelFile.sheet_names`.>**Note**>>An ExcelFile’s attribute `sheet_names` provides access to a list of sheets.- The arguments `sheet_name` allows specifying the sheet or sheets to read.  - The default value for `sheet_name` is 0, indicating to read the first sheet  - Pass a string to refer to the name of a particular sheet in the workbook.  - Pass an integer to refer to the index of a sheet. Indices follow Python  convention, beginning at 0.  - Pass a list of either strings or integers, to return a dictionary of specified sheets.  - Pass a `None` to return a dictionary of all available sheets.   ```python Returns a DataFramepd.read_excel("path_to_file.xls", "Sheet1", index_col=None, na_values=["NA"])``` Using the sheet index: ```python Returns a DataFramepd.read_excel("path_to_file.xls", 0, index_col=None, na_values=["NA"])``` Using all default values: ```python Returns a DataFramepd.read_excel("path_to_file.xls")``` Using None to get all sheets: ```python Returns a dictionary of DataFramespd.read_excel("path_to_file.xls", sheet_name=None)``` Using a list to get multiple sheets: ```python Returns the 1st and 4th sheet, as a dictionary of DataFrames.pd.read_excel("path_to_file.xls", sheet_name=["Sheet1", 3])``` `read_excel` can read more than one sheet, by setting `sheet_name` to eithera list of sheet names, a list of sheet positions, or `None` to read all sheets.Sheets can be specified by sheet index or sheet name, using an integer or string,respectively.  Reading a `MultiIndex``read_excel` can read a `MultiIndex` index, by passing a list of columns to `index_col`and a `MultiIndex` column by passing a list of rows to `header`.  If either the `index`or `columns` have serialized level names those will be read in as well by specifyingthe rows/columns that make up the levels.For example, to read in a `MultiIndex` index without names:<jupyter_code>df = pd.DataFrame(
    {"a": [1, 2, 3, 4], "b": [5, 6, 7, 8]},
    index=pd.MultiIndex.from_product([["a", "b"], ["c", "d"]]),
)
df.to_excel("path_to_file.xlsx")
df = pd.read_excel("path_to_file.xlsx", index_col=[0, 1])
df<jupyter_output><empty_output><jupyter_text>If the index has level names, they will parsed as well, using the sameparameters.<jupyter_code>df.index = df.index.set_names(["lvl1", "lvl2"])
df.to_excel("path_to_file.xlsx")
df = pd.read_excel("path_to_file.xlsx", index_col=[0, 1])
df<jupyter_output><empty_output><jupyter_text>If the source file has both `MultiIndex` index and columns, lists specifying eachshould be passed to `index_col` and `header`:<jupyter_code>df.columns = pd.MultiIndex.from_product([["a"], ["b", "d"]], names=["c1", "c2"])
df.to_excel("path_to_file.xlsx")
df = pd.read_excel("path_to_file.xlsx", index_col=[0, 1], header=[0, 1])
df
os.remove("path_to_file.xlsx")<jupyter_output><empty_output><jupyter_text>Parsing specific columnsIt is often the case that users will insert columns to do temporary computationsin Excel and you may not want to read in those columns. `read_excel` takesa `usecols` keyword to allow you to specify a subset of columns to parse.Changed in version 1.0.0.Passing in an integer for `usecols` will no longer work. Please pass in a listof ints from 0 to `usecols` inclusive instead.You can specify a comma-delimited set of Excel columns and ranges as a string: ```pythonpd.read_excel("path_to_file.xls", "Sheet1", usecols="A,C:E")``` If `usecols` is a list of integers, then it is assumed to be the file columnindices to be parsed. ```pythonpd.read_excel("path_to_file.xls", "Sheet1", usecols=[0, 2, 3])``` Element order is ignored, so `usecols=[0, 1]` is the same as `[1, 0]`.If `usecols` is a list of strings, it is assumed that each string correspondsto a column name provided either by the user in `names` or inferred from thedocument header row(s). Those strings define which columns will be parsed: ```pythonpd.read_excel("path_to_file.xls", "Sheet1", usecols=["foo", "bar"])``` Element order is ignored, so `usecols=['baz', 'joe']` is the same as `['joe', 'baz']`.If `usecols` is callable, the callable function will be evaluated againstthe column names, returning names where the callable function evaluates to `True`. ```pythonpd.read_excel("path_to_file.xls", "Sheet1", usecols=lambda x: x.isalpha())```  Parsing datesDatetime-like values are normally automatically converted to the appropriatedtype when reading the excel file. But if you have a column of strings that*look* like dates (but are not actually formatted as dates in excel), you canuse the `parse_dates` keyword to parse those strings to datetimes: ```pythonpd.read_excel("path_to_file.xls", "Sheet1", parse_dates=["date_strings"])```  Cell convertersIt is possible to transform the contents of Excel cells via the `converters`option. For instance, to convert a column to boolean: ```pythonpd.read_excel("path_to_file.xls", "Sheet1", converters={"MyBools": bool})``` This options handles missing values and treats exceptions in the convertersas missing data. Transformations are applied cell by cell rather than to thecolumn as a whole, so the array dtype is not guaranteed. For instance, acolumn of integers with missing values cannot be transformed to an arraywith integer dtype, because NaN is strictly a float. You can manually maskmissing data to recover integer dtype: ```pythondef cfun(x):    return int(x) if x else -1pd.read_excel("path_to_file.xls", "Sheet1", converters={"MyInts": cfun})```  Dtype specificationsAs an alternative to converters, the type for an entire column canbe specified using the `dtype` keyword, which takes a dictionarymapping column names to types.  To interpret data withno type inference, use the type `str` or `object`. ```pythonpd.read_excel("path_to_file.xls", dtype={"MyInts": "int64", "MyText": str})```   Writing Excel files  Writing Excel files to diskTo write a `DataFrame` object to a sheet of an Excel file, you can use the`to_excel` instance method.  The arguments are largely the same as `to_csv`described above, the first argument being the name of the excel file, and theoptional second argument the name of the sheet to which the `DataFrame` should bewritten. For example: ```pythondf.to_excel("path_to_file.xlsx", sheet_name="Sheet1")``` Files with a `.xls` extension will be written using `xlwt` and those with a`.xlsx` extension will be written using `xlsxwriter` (if available) or`openpyxl`.The `DataFrame` will be written in a way that tries to mimic the REPL output.The `index_label` will be placed in the secondrow instead of the first. You can place it in the first row by setting the`merge_cells` option in `to_excel()` to `False`: ```pythondf.to_excel("path_to_file.xlsx", index_label="label", merge_cells=False)``` In order to write separate `DataFrames` to separate sheets in a single Excel file,one can pass an `ExcelWriter`. ```pythonwith pd.ExcelWriter("path_to_file.xlsx") as writer:    df1.to_excel(writer, sheet_name="Sheet1")    df2.to_excel(writer, sheet_name="Sheet2")```   Writing Excel files to memorypandas supports writing Excel files to buffer-like objects such as `StringIO` or`BytesIO` using `ExcelWriter`. ```pythonfrom io import BytesIObio = BytesIO() By setting the 'engine' in the ExcelWriter constructor.writer = pd.ExcelWriter(bio, engine="xlsxwriter")df.to_excel(writer, sheet_name="Sheet1") Save the workbookwriter.save() Seek to the beginning and read to copy the workbook to a variable in memorybio.seek(0)workbook = bio.read()``` >**Note**>>`engine` is optional but recommended.  Setting the engine determinesthe version of workbook produced. Setting `engine='xlrd'` will produce anExcel 2003-format workbook (xls).  Using either `'openpyxl'` or`'xlsxwriter'` will produce an Excel 2007-format workbook (xlsx). Ifomitted, an Excel 2007-formatted workbook is produced.  Excel writer enginesDeprecated since version 1.2.0: As the [xlwt](https://pypi.org/project/xlwt/) package is no longermaintained, the `xlwt` engine will be removed from a future versionof pandas. This is the only engine in pandas that supports writing to`.xls` files.pandas chooses an Excel writer via two methods:1. the `engine` keyword argument  1. the filename extension (via the default specified in config options)  By default, pandas uses the [XlsxWriter](https://xlsxwriter.readthedocs.io)  for `.xlsx`, [openpyxl](https://openpyxl.readthedocs.io/)for `.xlsm`, and [xlwt](http://www.python-excel.org) for `.xls` files. If you have multipleengines installed, you can set the default engine through setting theconfig options `io.excel.xlsx.writer` and`io.excel.xls.writer`. pandas will fall back on [openpyxl](https://openpyxl.readthedocs.io/) for `.xlsx`files if [Xlsxwriter](https://xlsxwriter.readthedocs.io) is not available.To specify which writer you want to use, you can pass an engine keywordargument to `to_excel` and to `ExcelWriter`. The built-in engines are:- `openpyxl`: version 2.4 or higher is required  - `xlsxwriter`  - `xlwt`   ```python 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: E402options.io.excel.xlsx.writer = "xlsxwriter"df.to_excel("path_to_file.xlsx", sheet_name="Sheet1")```   Style and formattingThe look and feel of Excel worksheets created from pandas can be modified using the following parameters on the `DataFrame`’s `to_excel` method.- `float_format` : Format string for floating point numbers (default `None`).  - `freeze_panes` : A tuple of two integers representing the bottommost row and rightmost column to freeze. Each of these parameters is one-based, so (1, 1) will freeze the first row and first column (default `None`).  Using the [Xlsxwriter](https://xlsxwriter.readthedocs.io) engine provides many options for controlling theformat of an Excel worksheet created with the `to_excel` method.  Excellent examples can be found in the[Xlsxwriter](https://xlsxwriter.readthedocs.io) documentation here: [https://xlsxwriter.readthedocs.io/working_with_pandas.html](https://xlsxwriter.readthedocs.io/working_with_pandas.html)  OpenDocument SpreadsheetsNew in version 0.25.The `read_excel()` method can also read OpenDocument spreadsheetsusing the `odfpy` module. The semantics and features for readingOpenDocument spreadsheets match what can be done for [Excel files](excel-files) using`engine='odf'`. ```python Returns a DataFramepd.read_excel("path_to_file.ods", engine="odf")``` >**Note**>>Currently pandas only supports *reading* OpenDocument spreadsheets. Writingis not implemented.  Binary Excel (.xlsb) filesNew in version 1.0.0.The `read_excel()` method can also read binary Excel filesusing the `pyxlsb` module. The semantics and features for readingbinary Excel files mostly match what can be done for [Excel files](excel-files) using`engine='pyxlsb'`. `pyxlsb` does not recognize datetime typesin files and will return floats instead. ```python Returns a DataFramepd.read_excel("path_to_file.xlsb", engine="pyxlsb")``` >**Note**>>Currently pandas only supports *reading* binary Excel files. Writingis not implemented.  ClipboardA handy way to grab data is to use the `read_clipboard()` method,which takes the contents of the clipboard buffer and passes them to the`read_csv` method. For instance, you can copy the following text to theclipboard (CTRL-C on many operating systems): ```console  A B Cx 1 4 py 2 5 qz 3 6 r``` And then import the data directly to a `DataFrame` by calling: ```python>>> clipdf = pd.read_clipboard()>>> clipdf  A B Cx 1 4 py 2 5 qz 3 6 r``` The `to_clipboard` method can be used to write the contents of a `DataFrame` tothe clipboard. Following which you can paste the clipboard contents into otherapplications (CTRL-V on many operating systems). Here we illustrate writing a`DataFrame` into clipboard and reading it back.<jupyter_code>df = pd.DataFrame( {"A": [1, 2, 3], "B": [4, 5, 6], "C": ["p", "q", "r"]}, index=["x", "y", "z"])

df
df = df*10
df.to_clipboard()
pd.read_clipboard()<jupyter_output><empty_output><jupyter_text>We can see that we got the same content back, which we had earlier written to the clipboard.>**Note**>>You may need to install xclip or xsel (with PyQt5, PyQt4 or qtpy) on Linux to use these methods.  PicklingAll pandas objects are equipped with `to_pickle` methods which use Python’s`cPickle` module to save data structures to disk using the pickle format.<jupyter_code>df
df.to_pickle("foo.pkl")<jupyter_output><empty_output><jupyter_text>The `read_pickle` function in the `pandas` namespace can be used to loadany pickled pandas object (or any other pickled object) from file:<jupyter_code>pd.read_pickle("foo.pkl")
os.remove("foo.pkl")<jupyter_output><empty_output><jupyter_text>Loading pickled data received from untrusted sources can be unsafe.See: [https://docs.python.org/3/library/pickle.html](https://docs.python.org/3/library/pickle.html)`read_pickle()` is only guaranteed backwards compatible back to pandas version 0.20.3  Compressed pickle files`read_pickle()`, `DataFrame.to_pickle()` and `Series.to_pickle()` can readand write compressed pickle files. The compression types of `gzip`, `bz2`, `xz` are supported for reading and writing.The `zip` file format only supports reading and must contain only one data fileto be read.The compression type can be an explicit parameter or be inferred from the file extension.If ‘infer’, then use `gzip`, `bz2`, `zip`, or `xz` if filename ends in `'.gz'`, `'.bz2'`, `'.zip'`, or`'.xz'`, respectively.The compression parameter can also be a `dict` in order to pass options to thecompression protocol. It must have a `'method'` key set to the nameof the compression protocol, which must be one of{`'zip'`, `'gzip'`, `'bz2'`}. All other key-value pairs are passed tothe underlying compression library.<jupyter_code>df = pd.DataFrame(
    {
        "A": np.random.randn(1000),
        "B": "foo",
        "C": pd.date_range("20130101", periods=1000, freq="s"),
    }
)
df<jupyter_output><empty_output><jupyter_text>Using an explicit compression type:<jupyter_code>df.to_pickle("data.pkl.compress", compression="gzip")
rt = pd.read_pickle("data.pkl.compress", compression="gzip")
rt<jupyter_output><empty_output><jupyter_text>Inferring compression type from the extension:<jupyter_code>df.to_pickle("data.pkl.xz", compression="infer")
rt = pd.read_pickle("data.pkl.xz", compression="infer")
rt<jupyter_output><empty_output><jupyter_text>The default is to ‘infer’:<jupyter_code>df.to_pickle("data.pkl.gz")
rt = pd.read_pickle("data.pkl.gz")
rt

df["A"].to_pickle("s1.pkl.bz2")
rt = pd.read_pickle("s1.pkl.bz2")
rt<jupyter_output><empty_output><jupyter_text>Passing options to the compression protocol in order to speed up compression:<jupyter_code>df.to_pickle("data.pkl.gz", compression={"method": "gzip", "compresslevel": 1})
os.remove("data.pkl.compress")
os.remove("data.pkl.xz")
os.remove("data.pkl.gz")
os.remove("s1.pkl.bz2")<jupyter_output><empty_output><jupyter_text>msgpackpandas support for `msgpack` has been removed in version 1.0.0. It isrecommended to use [pickle](io-pickle) instead.Alternatively, you can also the Arrow IPC serialization format for on-the-wiretransmission of pandas objects. For documentation on pyarrow, see[here](https://arrow.apache.org/docs/python/ipc.html).  HDF5 (PyTables)`HDFStore` is a dict-like object which reads and writes pandas usingthe high performance HDF5 format using the excellent [PyTables](https://www.pytables.org/) library. See the [cookbook](44_cookbook.ipynbcookbook-hdf)for some advanced strategiespandas uses PyTables for reading and writing HDF5 files, which allowsserializing object-dtype data with pickle. Loading pickled data received fromuntrusted sources can be unsafe.See: [https://docs.python.org/3/library/pickle.html](https://docs.python.org/3/library/pickle.html) for more.<jupyter_code>store = pd.HDFStore("store.h5")
print(store)<jupyter_output><class 'pandas.io.pytables.HDFStore'>
File path: store.h5<jupyter_text>Objects can be written to the file just like adding key-value pairs to adict:<jupyter_code>index = pd.date_range("1/1/2000", periods=8)
s = pd.Series(np.random.randn(5), index=["a", "b", "c", "d", "e"])
df = pd.DataFrame(np.random.randn(8, 3), index=index, columns=["A", "B", "C"])

# store.put('s', s) is an equivalent method
store["s"] = s

store["df"] = df

store<jupyter_output><empty_output><jupyter_text>In a current or later Python session, you can retrieve stored objects:<jupyter_code># store.get('df') is an equivalent method
store["df"]

# dotted (attribute) access provides get as well
store.df<jupyter_output><empty_output><jupyter_text>Deletion of the object specified by the key:<jupyter_code># store.remove('df') is an equivalent method
del store["df"]

store<jupyter_output><empty_output><jupyter_text>Closing a Store and using a context manager:<jupyter_code>store.close()
store
store.is_open

# Working with, and automatically closing the store using a context manager
with pd.HDFStore("store.h5") as store:
    store.keys()
store.close()
os.remove("store.h5")<jupyter_output><empty_output><jupyter_text>Read/write API`HDFStore` supports a top-level API using  `read_hdf` for reading and `to_hdf` for writing,similar to how `read_csv` and `to_csv` work.<jupyter_code>df_tl = pd.DataFrame({"A": list(range(5)), "B": list(range(5))})
df_tl.to_hdf("store_tl.h5", "table", append=True)
pd.read_hdf("store_tl.h5", "table", where=["index>2"])
os.remove("store_tl.h5")<jupyter_output><empty_output><jupyter_text>HDFStore will by default not drop rows that are all missing. This behavior can be changed by setting `dropna=True`.<jupyter_code>df_with_missing = pd.DataFrame(
    {
        "col1": [0, np.nan, 2],
        "col2": [1, np.nan, np.nan],
    }
)
df_with_missing

df_with_missing.to_hdf("file.h5", "df_with_missing", format="table", mode="w")

pd.read_hdf("file.h5", "df_with_missing")

df_with_missing.to_hdf(
    "file.h5", "df_with_missing", format="table", mode="w", dropna=True
)
pd.read_hdf("file.h5", "df_with_missing")
os.remove("file.h5")<jupyter_output><empty_output><jupyter_text>Fixed formatThe examples above show storing using `put`, which write the HDF5 to `PyTables` in a fixed array format, calledthe `fixed` format. These types of stores are **not** appendable once written (though you can simplyremove them and rewrite). Nor are they **queryable**; they must beretrieved in their entirety. They also do not support dataframes with non-unique column names.The `fixed` format stores offer very fast writing and slightly faster reading than `table` stores.This format is specified by default when using `put` or `to_hdf` or by `format='fixed'` or `format='f'`.A `fixed` format will raise a `TypeError` if you try to retrieve using a `where`:<jupyter_code>>>> 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<jupyter_output><empty_output><jupyter_text>Table format`HDFStore` supports another `PyTables` format on disk, the `table`format. Conceptually a `table` is shaped very much like a DataFrame,with rows and columns. A `table` may be appended to in the same orother sessions.  In addition, delete and query type operations aresupported. This format is specified by `format='table'` or `format='t'`to `append` or `put` or `to_hdf`.This format can be set as an option as well `pd.set_option('io.hdf.default_format','table')` toenable `put/append/to_hdf` to by default store in the `table` format.<jupyter_code>try: 
    os.remove("store.h5")
except:
    pass
store = pd.HDFStore("store.h5")
df1 = df[0:4]
df2 = df[4:]

# append data (creates a table automatically)
store.append("df", df1)
store.append("df", df2)
store

# select the entire object
store.select("df")

# the type of stored data
store.root.df._v_attrs.pandas_type<jupyter_output><empty_output><jupyter_text>>**Note**>>You can also create a `table` by passing `format='table'` or `format='t'` to a `put` operation.  Hierarchical keysKeys to a store can be specified as a string. These can be in ahierarchical path-name like format (e.g. `foo/bar/bah`), which willgenerate a hierarchy of sub-stores (or `Groups` in PyTablesparlance). Keys can be specified without the leading ‘/’ and are **always**absolute (e.g. ‘foo’ refers to ‘/foo’). Removal operations can removeeverything in the sub-store and **below**, so be *careful*.<jupyter_code>store.put("foo/bar/bah", df)
store.append("food/orange", df)
store.append("food/apple", df)
store

# a list of keys are returned
store.keys()

# remove all nodes under this level
store.remove("food")
store<jupyter_output><empty_output><jupyter_text>You can walk through the group hierarchy using the `walk` method whichwill yield a tuple for each group key along with the relative keys of its contents.<jupyter_code>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))<jupyter_output>GROUP: /foo
KEY: /df
                   A         B         C
2000-01-01  0.505347 -0.842457  1.857260
2000-01-02  1.668166  1.472614 -0.443680
2000-01-03  1.696823 -1.259831 -1.118470
2000-01-04  0.018115 -0.898088  2.479052
2000-01-05  1.675007 -2.076437  0.356613
2000-01-06 -2.359801  0.151466  0.805762
2000-01-07 -0.358838 -0.976056  0.613318
2000-01-08  0.492533 -0.095502 -0.847238
GROUP: /foo/bar
KEY: /foo/bar/bah
                   A         B         C
2000-01-01  0.505347 -0.842457  1.857260
2000-01-02  1.668166  1.472614 -0.443680
2000-01-03  1.696823 -1.259831 -1.118470
2000-01-04  0.018115 -0.898088  2.479052
2000-01-05  1.675007 -2.076437  0.356613
2000-01-06 -2.359801  0.151466  0.805762
2000-01-07 -0.358838 -0.976056  0.613318
2000-01-08  0.492533 -0.095502 -0.847238<jupyter_text>> Hierarchical keys cannot be retrieved as dotted (attribute) access as described above for items stored under the root node.<jupyter_code># store.foo.bar.bah
"""
In [8]: store.foo.bar.bah
TypeError: cannot create a storer if the object is not existing nor a value are passed        
   AttributeError: 'HDFStore' object has no attribute 'foo'
"""
# you can directly access the actual PyTables node but using the root node
store.root.foo.bar.bah<jupyter_output><empty_output><jupyter_text>Instead, use explicit string based keys:<jupyter_code>store["foo/bar/bah"]<jupyter_output><empty_output><jupyter_text>Storing types  Storing mixed types in a tableStoring mixed-dtype data is supported. Strings are stored as afixed-width using the maximum size of the appended column. Subsequent attemptsat appending longer strings will raise a `ValueError`.Passing `min_itemsize={`values`: size}` as a parameter to appendwill set a larger minimum for the string columns. Storing `floats,strings, ints, bools, datetime64` are currently supported. For stringcolumns, passing `nan_rep = 'nan'` to append will change the defaultnan representation on disk (which converts to/from `np.nan`), thisdefaults to `nan`.<jupyter_code>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)),
)
df_mixed.loc[df_mixed.index[3:5], ["A", "B", "string", "datetime64"]] = np.nan

store.append("df_mixed", df_mixed, min_itemsize={"values": 50})
df_mixed1 = store.select("df_mixed")
df_mixed1
df_mixed1.dtypes.value_counts()

# we have provided a minimum string column size
store.root.df_mixed.table<jupyter_output><empty_output><jupyter_text>Storing MultiIndex DataFramesStoring MultiIndex `DataFrames` as tables is very similar tostoring/selecting from homogeneous index `DataFrames`.<jupyter_code>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"],
)
df_mi = pd.DataFrame(np.random.randn(10, 3), index=index, columns=["A", "B", "C"])
df_mi

store.append("df_mi", df_mi)
store.select("df_mi")

# the levels are automatically included as data columns
store.select("df_mi", "foo=bar")<jupyter_output><empty_output><jupyter_text>>**Note**>>The `index` keyword is reserved and cannot be use as a level name.  Querying  Querying a table`select` and `delete` operations have an optional criterion that canbe specified to select/delete only a subset of the data. This allows oneto have a very large on-disk table and retrieve only a portion of thedata.A query is specified using the `Term` class under the hood, as a boolean expression.- `index` and `columns` are supported indexers of `DataFrames`.  - if `data_columns` are specified, these can be used as additional indexers.  - level name in a MultiIndex, with default name  `level_0`, `level_1`, … if not provided.  Valid comparison operators are:`=, ==, !=, >, >=, <, <=`Valid boolean expressions are combined with:- `|` : or  - `&` : and  - `(` and `)` : for grouping  These rules are similar to how boolean expressions are used in pandas for indexing.>**Note**>>- `=` will be automatically expanded to the comparison operator `==`  - `~` is the not operator, but can only be used in very limited  circumstances  - If a list/tuple of expressions is passed they will be combined via `&`  The following are valid expressions:- `'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"`  The `indexers` are on the left-hand side of the sub-expression:`columns`, `major_axis`, `ts`The right-hand side of the sub-expression (after a comparison operator) can be:- functions that will be evaluated, e.g. `Timestamp('2012-02-01')`  - strings, e.g. `"bar"`  - date-like, e.g. `20130101`, or `"20130101"`  - lists, e.g. `"['A', 'B']"`  - variables that are defined in the local names space, e.g. `date`  >**Note**>>Passing a string to a query by interpolating it into the queryexpression is not recommended. Simply assign the string of interest to avariable and use that variable in an expression. For example, do this<jupyter_code>string = "HolyMoly'"
store.select("df", "index == string")
#ParserError: Unknown string format: HolyMoly'<jupyter_output><empty_output><jupyter_text>instead of this<jupyter_code>str = 'HolyMoly'
store.select('df', f'index == {0}',str)


The latter will **not** work and will raise a ``SyntaxError``.Note that
there's a single quote followed by a double quote in the ``string``
variable.

If you *must* interpolate, use the ``'%r'`` format specifier
   store.select("df", "index == %r" % string)

which will quote ``string``.<jupyter_output><empty_output><jupyter_text>Here are some examples:<jupyter_code>dfq = pd.DataFrame(
    np.random.randn(10, 4),
    columns=list("ABCD"),
    index=pd.date_range("20130101", periods=10),
)
store.append("dfq", dfq, format="table", data_columns=True)<jupyter_output><empty_output><jupyter_text>Use boolean expressions, with in-line function evaluation.<jupyter_code>store.select("dfq", "index>pd.Timestamp('20130104') & columns=['A', 'B']")<jupyter_output><empty_output><jupyter_text>Use inline column reference.<jupyter_code>store.select("dfq", where="A>0 or C>0")<jupyter_output><empty_output><jupyter_text>The `columns` keyword can be supplied to select a list of columns to bereturned, this is equivalent to passing a`'columns=list_of_columns_to_filter'`:<jupyter_code>store.select("df", "columns=['A', 'B']")<jupyter_output><empty_output><jupyter_text>`start` and `stop` parameters can be specified to limit the total searchspace. These are in terms of the total number of rows in a table.>**Note**>>`select` will raise a `ValueError` if the query expression has an unknownvariable reference. Usually this means that you are trying to select on a columnthat is **not** a data_column.`select` will raise a `SyntaxError` if the query expression is not valid.  Query timedelta64[ns]You can store and query using the `timedelta64[ns]` type. Terms can bespecified in the format: `()`, where float may be signed (and fractional), and unit can be`D,s,ms,us,ns` for the timedelta. Here’s an example:<jupyter_code>from datetime import timedelta

dftd = pd.DataFrame(
    {
        "A": pd.Timestamp("20130101"),
        "B": [
            pd.Timestamp("20130101") + timedelta(days=i, seconds=10)
            for i in range(10)
        ],
    }
)
dftd["C"] = dftd["A"] - dftd["B"]
dftd
store.append("dftd", dftd, data_columns=True)
store.select("dftd", "C<'-3.5D'")<jupyter_output><empty_output><jupyter_text>Query MultiIndexSelecting from a `MultiIndex` can be achieved by using the name of the level.<jupyter_code>df_mi.index.names
store.select("df_mi", "foo=baz and bar=two")<jupyter_output><empty_output><jupyter_text>If the `MultiIndex` levels names are `None`, the levels are automatically made available viathe `level_n` keyword with `n` the level of the `MultiIndex` you want to select from.<jupyter_code>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]],
)
df_mi_2 = pd.DataFrame(np.random.randn(10, 3), index=index, columns=["A", "B", "C"])
df_mi_2

store.append("df_mi_2", df_mi_2)

# the levels are automatically included as data columns with keyword level_n
store.select("df_mi_2", "level_0=foo and level_1=two")<jupyter_output><empty_output><jupyter_text>IndexingYou can create/modify an index for a table with `create_table_index`after data is already in the table (after and `append/put`operation). Creating a table index is **highly** encouraged. This willspeed your queries a great deal when you use a `select` with theindexed dimension as the `where`.>**Note**>>Indexes are automagically created on the indexablesand any data columns you specify. This behavior can be turned off by passing`index=False` to `append`.<jupyter_code># we have automagically already created an index (in the first section)
i = store.root.df.table.cols.index.index
i.optlevel, i.kind

# change an index by passing new parameters
store.create_table_index("df", optlevel=9, kind="full")
i = store.root.df.table.cols.index.index
i.optlevel, i.kind<jupyter_output><empty_output><jupyter_text>Oftentimes when appending large amounts of data to a store, it is useful to turn off index creation for each append, then recreate at the end.<jupyter_code>df_1 = pd.DataFrame(np.random.randn(10, 2), columns=list("AB"))
df_2 = pd.DataFrame(np.random.randn(10, 2), columns=list("AB"))

st = pd.HDFStore("appends.h5", mode="w")
st.append("df", df_1, data_columns=["B"], index=False)
st.append("df", df_2, data_columns=["B"], index=False)
st.get_storer("df").table<jupyter_output><empty_output><jupyter_text>Then create the index when finished appending.<jupyter_code>st.create_table_index("df", columns=["B"], optlevel=9, kind="full")
st.get_storer("df").table

st.close()
os.remove("appends.h5")<jupyter_output><empty_output><jupyter_text>See [here](https://stackoverflow.com/questions/17893370/ptrepack-sortby-needs-full-index) for how to create a completely-sorted-index (CSI) on an existing store.  Query via data columnsYou can designate (and index) certain columns that you want to be ableto perform queries (other than the `indexable` columns, which you canalways query). For instance say you want to perform this commonoperation, on-disk, and return just the frame that matches thisquery. You can specify `data_columns = True` to force all columns tobe `data_columns`.<jupyter_code>df_dc = df.copy()
df_dc["string"] = "foo"
df_dc.loc[df_dc.index[4:6], "string"] = np.nan
df_dc.loc[df_dc.index[7:9], "string"] = "bar"
df_dc["string2"] = "cool"
df_dc.loc[df_dc.index[1:3], ["B", "C"]] = 1.0
df_dc

# on-disk operations
store.append("df_dc", df_dc, data_columns=["B", "C", "string", "string2"])
store.select("df_dc", where="B > 0")

# getting creative
store.select("df_dc", "B > 0 & C > 0 & string == foo")

# this is in-memory version of this type of selection
df_dc[(df_dc.B > 0) & (df_dc.C > 0) & (df_dc.string == "foo")]

# we have automagically created this index and the B/C/string/string2
# columns are stored separately as ``PyTables`` columns
store.root.df_dc.table<jupyter_output><empty_output><jupyter_text>There is some performance degradation by making lots of columns into`data columns`, so it is up to the user to designate these. In addition,you cannot change data columns (nor indexables) after the firstappend/put operation (Of course you can simply read in the data andcreate a new table!).  IteratorYou can pass `iterator=True` or `chunksize=number_in_a_chunk`to `select` and `select_as_multiple` to return an iterator on the results.The default is 50,000 rows returned in a chunk.<jupyter_code>for df in store.select("df", chunksize=3):
    print(df)<jupyter_output>A         B        C
2000-01-01  0.505347 -0.842457  1.85726
2000-01-02  1.668166  1.472614 -0.44368
2000-01-03  1.696823 -1.259831 -1.11847
                   A         B         C
2000-01-04  0.018115 -0.898088  2.479052
2000-01-05  1.675007 -2.076437  0.356613
2000-01-06 -2.359801  0.151466  0.805762
                   A         B         C
2000-01-07 -0.358838 -0.976056  0.613318
2000-01-08  0.492533 -0.095502 -0.847238<jupyter_text>>**Note**>>You can also use the iterator with `read_hdf` which will open, thenautomatically close the store when finished iterating.<jupyter_code>for df in pd.read_hdf("store.h5", "df", chunksize=3):
    print(df)<jupyter_output><empty_output><jupyter_text>Note, that the chunksize keyword applies to the **source** rows. So if youare doing a query, then the chunksize will subdivide the total rows in the tableand the query applied, returning an iterator on potentially unequal sized chunks.Here is a recipe for generating a query and using it to create equal sized returnchunks.<jupyter_code>dfeq = pd.DataFrame({"number": np.arange(1, 11)})
dfeq

store.append("dfeq", dfeq, data_columns=["number"])

def chunks(l, n):
    return [l[i: i + n] for i in range(0, len(l), n)]

evens = [2, 4, 6, 8, 10]
coordinates = store.select_as_coordinates("dfeq", "number=evens")
for c in chunks(coordinates, 2):
    print(store.select("dfeq", where=c))<jupyter_output>number
1       2
3       4
   number
5       6
7       8
   number
9      10<jupyter_text>Advanced queries  Select a single columnTo retrieve a single indexable or data column, use themethod `select_column`. This will, for example, enable you to get the indexvery quickly. These return a `Series` of the result, indexed by the row number.These do not currently accept the `where` selector.<jupyter_code>store.select_column("df_dc", "index")
store.select_column("df_dc", "string")<jupyter_output><empty_output><jupyter_text>Selecting coordinatesSometimes you want to get the coordinates (a.k.a the index locations) of your query. This returns an`Int64Index` of the resulting locations. These coordinates can also be passed to subsequent`where` operations.<jupyter_code>df_coord = pd.DataFrame(
    np.random.randn(1000, 2), index=pd.date_range("20000101", periods=1000)
)
store.append("df_coord", df_coord)
c = store.select_as_coordinates("df_coord", "index > 20020101")
c
store.select("df_coord", where=c)<jupyter_output><empty_output><jupyter_text>Selecting using a where maskSometime your query can involve creating a list of rows to select. Usually this `mask` wouldbe a resulting `index` from an indexing operation. This example selects the months ofa datetimeindex which are 5.<jupyter_code>df_mask = pd.DataFrame(
    np.random.randn(1000, 2), index=pd.date_range("20000101", periods=1000)
)
store.append("df_mask", df_mask)
c = store.select_column("df_mask", "index")
where = c[pd.DatetimeIndex(c).month == 5].index
store.select("df_mask", where=where)<jupyter_output><empty_output><jupyter_text>Storer objectIf you want to inspect the stored object, retrieve via`get_storer`. You could use this programmatically to say get the numberof rows in an object.<jupyter_code>store.get_storer("df_dc").nrows<jupyter_output><empty_output><jupyter_text>Multiple table queriesThe methods `append_to_multiple` and`select_as_multiple` can perform appending/selecting frommultiple tables at once. The idea is to have one table (call it theselector table) that you index most/all of the columns, and perform yourqueries. The other table(s) are data tables with an index matching theselector table’s index. You can then perform a very fast queryon the selector table, yet get lots of data back. This method is similar tohaving a very wide table, but enables more efficient queries.The `append_to_multiple` method splits a given single DataFrameinto multiple tables according to `d`, a dictionary that maps thetable names to a list of ‘columns’ you want in that table. If `None`is used in place of a list, that table will have the remainingunspecified columns of the given DataFrame. The argument `selector`defines which table is the selector table (which you can make queries from).The argument `dropna` will drop rows from the input `DataFrame` to ensuretables are synchronized.  This means that if a row for one of the tablesbeing written to is entirely `np.NaN`, that row will be dropped from all tables.If `dropna` is False, **THE USER IS RESPONSIBLE FOR SYNCHRONIZING THE TABLES**.Remember that entirely `np.Nan` rows are not written to the HDFStore, so ifyou choose to call `dropna=False`, some tables may have more rows than others,and therefore `select_as_multiple` may not work or it may return unexpectedresults.<jupyter_code>df_mt = pd.DataFrame(
    np.random.randn(8, 6),
    index=pd.date_range("1/1/2000", periods=8),
    columns=["A", "B", "C", "D", "E", "F"],
)
df_mt["foo"] = "bar"
df_mt.loc[df_mt.index[1], ("A", "B")] = np.nan

# you can also create the tables individually
store.append_to_multiple(
    {"df1_mt": ["A", "B"], "df2_mt": None}, df_mt, selector="df1_mt"
)
store

# individual tables were created
store.select("df1_mt")
store.select("df2_mt")

# as a multiple
store.select_as_multiple(
    ["df1_mt", "df2_mt"],
    where=["A>0", "B>0"],
    selector="df1_mt",
)<jupyter_output><empty_output><jupyter_text>Delete from a tableYou can delete from a table selectively by specifying a `where`. Indeleting rows, it is important to understand the `PyTables` deletesrows by erasing the rows, then **moving** the following data. Thusdeleting can potentially be a very expensive operation depending on theorientation of your data. To get optimal performance, it’sworthwhile to have the dimension you are deleting be the first of the`indexables`.Data is ordered (on the disk) in terms of the `indexables`. Here’s asimple use case. You store panel-type data, with dates in the`major_axis` and ids in the `minor_axis`. The data is theninterleaved like this:-     date_1    - id_1    - id_2    - .    - id_n          -     date_2    - id_1    - .    - id_n          It should be clear that a delete operation on the `major_axis` will befairly quick, as one chunk is removed, then the following data moved. Onthe other hand a delete operation on the `minor_axis` will be veryexpensive. In this case it would almost certainly be faster to rewritethe table using a `where` that selects all but the missing data.Please note that HDF5 **DOES NOT RECLAIM SPACE** in the h5 filesautomatically. Thus, repeatedly deleting (or removing nodes) and addingagain, **WILL TEND TO INCREASE THE FILE SIZE**.To *repack and clean* the file, use [ptrepack](io-hdf5-ptrepack).  Notes & caveats  Compression`PyTables` allows the stored data to be compressed. This applies toall kinds of stores, not just tables. Two parameters are used tocontrol compression: `complevel` and `complib`.- `complevel` specifies if and how hard data is to be compressed.  `complevel=0` and `complevel=None` disables compression and  `0<complevel<10` enables compression.  - `complib` specifies which compression library to use.  If nothing is  specified the default library `zlib` is used. A  compression library usually optimizes for either good compression rates  or speed and the results will depend on the type of data. Which type of  compression to choose depends on your specific needs and data. The list  of supported compression libraries:    - [zlib](https://zlib.net/): The default compression library.    A classic in terms of compression, achieves good compression    rates but is somewhat slow.    - [lzo](https://www.oberhumer.com/opensource/lzo/): Fast    compression and decompression.    - [bzip2](http://bzip.org/): Good compression rates.    - [blosc](https://www.blosc.org/): Fast compression and    decompression.      Support for alternative blosc compressors:      - [blosc:blosclz](https://www.blosc.org/) This is the      default compressor for `blosc`      - [blosc:lz4](https://fastcompression.blogspot.dk/p/lz4.html):      A compact, very popular and fast compressor.      - [blosc:lz4hc](https://fastcompression.blogspot.dk/p/lz4.html):      A tweaked version of LZ4, produces better      compression ratios at the expense of speed.      - [blosc:snappy](https://google.github.io/snappy/):      A popular compressor used in many places.      - [blosc:zlib](https://zlib.net/): A classic;      somewhat slower than the previous ones, but      achieving better compression ratios.      - [blosc:zstd](https://facebook.github.io/zstd/): An      extremely well balanced codec; it provides the best      compression ratios among the others above, and at      reasonably fast speed.    If `complib` is defined as something other than the listed libraries a  `ValueError` exception is issued.  >**Note**>>If the library specified with the `complib` option is missing on your platform,compression defaults to `zlib` without further ado.Enable compression for all objects within the file: ```pythonstore_compressed = pd.HDFStore(    "store_compressed.h5", complevel=9, complib="blosc:blosclz")``` Or on-the-fly compression (this only applies to tables) in stores where compression is not enabled: ```pythonstore.append("df", df, complib="zlib", complevel=5)```   ptrepack`PyTables` offers better write performance when tables are compressed afterthey are written, as opposed to turning on compression at the verybeginning. You can use the supplied `PyTables` utility`ptrepack`. In addition, `ptrepack` can change compression levelsafter the fact. ```consoleptrepack --chunkshape=auto --propindexes --complevel=9 --complib=blosc in.h5 out.h5``` Furthermore `ptrepack in.h5 out.h5` will *repack* the file to allowyou to reuse previously deleted space. Alternatively, one can simplyremove the file and write again, or use the `copy` method.  Caveats`HDFStore` is **not-threadsafe for writing**. The underlying`PyTables` only supports concurrent reads (via threading orprocesses). If you need reading and writing *at the same time*, youneed to serialize these operations in a single thread in a singleprocess. You will corrupt your data otherwise. See the (:issue:`2397`) for more information.- If you use locks to manage write access between multiple processes, you  may want to use `fsync()` before releasing write locks. For  convenience you can use `store.flush(fsync=True)` to do this for you.  - Once a `table` is created columns (DataFrame)  are fixed; only exactly the same columns can be appended  - Be aware that timezones (e.g., `pytz.timezone('US/Eastern')`)  are not necessarily equal across timezone versions.  So if data is  localized to a specific timezone in the HDFStore using one version  of a timezone library and that data is updated with another version, the data  will be converted to UTC since these timezones are not considered  equal.  Either use the same version of timezone library or use `tz_convert` with  the updated timezone definition.  `PyTables` will show a `NaturalNameWarning` if a column namecannot be used as an attribute selector.*Natural* identifiers contain only letters, numbers, and underscores,and may not begin with a number.Other identifiers cannot be used in a `where` clauseand are generally a bad idea.  DataTypes`HDFStore` will map an object dtype to the `PyTables` underlyingdtype. This means the following types are known to work:``````````````````|Type|Represents missing values||:----------------------------------------------------:|:-----------------------:||floating : float64, float32, float16|np.nan||integer : int64, int32, int8, uint64,uint32, uint8|||boolean|||datetime64[ns]|NaT||timedelta64[ns]|NaT||categorical : see the section below|||object : strings|np.nan|`unicode` columns are not supported, and **WILL FAIL**.  Categorical dataYou can write data that contains `category` dtypes to a `HDFStore`.Queries work the same as if it was an object array. However, the `category` dtyped data isstored in a more efficient manner.<jupyter_code>dfcat = pd.DataFrame(
    {"A": pd.Series(list("aabbcdba")).astype("category"), "B": np.random.randn(8)}
)
dfcat
dfcat.dtypes
cstore = pd.HDFStore("cats.h5", mode="w")
cstore.append("dfcat", dfcat, format="table", data_columns=["A"])
result = cstore.select("dfcat", where="A in ['b', 'c']")
result
result.dtypes
cstore.close()
os.remove("cats.h5")<jupyter_output><empty_output><jupyter_text>String columns**min_itemsize**The underlying implementation of `HDFStore` uses a fixed column width (itemsize) for string columns.A string column itemsize is calculated as the maximum of thelength of data (for that column) that is passed to the `HDFStore`, **in the first append**. Subsequent appends,may introduce a string for a column **larger** than the column can hold, an Exception will be raised (otherwise youcould have a silent truncation of these columns, leading to loss of information). In the future we may relax this andallow a user-specified truncation to occur.Pass `min_itemsize` on the first table creation to a-priori specify the minimum length of a particular string column.`min_itemsize` can be an integer, or a dict mapping a column name to an integer. You can pass `values` as a key toallow all *indexables* or *data_columns* to have this min_itemsize.Passing a `min_itemsize` dict will cause all passed columns to be created as *data_columns* automatically.>**Note**>>If you are not passing any `data_columns`, then the `min_itemsize` will be the maximum of the length of any string passed<jupyter_code>dfs = pd.DataFrame({"A": "foo", "B": "bar"}, index=list(range(5)))
dfs

# A and B have a size of 30
store.append("dfs", dfs, min_itemsize=30)
store.get_storer("dfs").table

# A is created as a data_column with a size of 30
# B is size is calculated
store.append("dfs2", dfs, min_itemsize={"A": 30})
store.get_storer("dfs2").table<jupyter_output><empty_output><jupyter_text>**nan_rep**String columns will serialize a `np.nan` (a missing value) with the `nan_rep` string representation. This defaults to the string value `nan`.You could inadvertently turn an actual `nan` value into a missing value.<jupyter_code>dfss = pd.DataFrame({"A": ["foo", "bar", "nan"]})
dfss

store.append("dfss", dfss)
store.select("dfss")

# here you need to specify a different nan rep
store.append("dfss2", dfss, nan_rep="_nan_")
store.select("dfss2")<jupyter_output><empty_output><jupyter_text>External compatibility`HDFStore` writes `table` format objects in specific formats suitable forproducing loss-less round trips to pandas objects. For externalcompatibility, `HDFStore` can read native `PyTables` formattables.It is possible to write an `HDFStore` object that can easily be imported into `R` using the`rhdf5` library ([Package website](https://www.bioconductor.org/packages/release/bioc/html/rhdf5.html)). Create a table format store like this:<jupyter_code>df_for_r = pd.DataFrame(
    {
        "first": np.random.rand(100),
        "second": np.random.rand(100),
        "class": np.random.randint(0, 2, (100,)),
    },
    index=range(100),
)
df_for_r.head()

store_export = pd.HDFStore("export.h5")
store_export.append("df_for_r", df_for_r, data_columns=df_dc.columns)
store_export
store_export.close()
os.remove("export.h5")<jupyter_output><empty_output><jupyter_text>In R this file can be read into a `data.frame` object using the `rhdf5`library. The following example function reads the corresponding column namesand data values from the values and assembles them into a `data.frame`: ```R Load values and column names for all datasets from corresponding nodes and insert them into one data.frame object.library(rhdf5)loadhdf5data <- function(h5File) {listing <- h5ls(h5File) Find all data nodes, values are stored in *_values and corresponding column titles in *_itemsdata_nodes <- grep("_values", listing$name)name_nodes <- grep("_items", listing$name)data_paths = paste(listing$group[data_nodes], listing$name[data_nodes], sep = "/")name_paths = paste(listing$group[name_nodes], listing$name[name_nodes], sep = "/")columns = list()for (idx in seq(data_paths)) {   NOTE: matrices returned by h5read have to be transposed to obtain   required Fortran order!  data <- data.frame(t(h5read(h5File, data_paths[idx])))  names <- t(h5read(h5File, name_paths[idx]))  entry <- data.frame(data)  colnames(entry) <- names  columns <- append(columns, entry)}data <- data.frame(columns)return(data)}``` Now you can import the `DataFrame` into R: ```R> data = loadhdf5data("transfer.hdf5")> head(data)         first    second class1 0.4170220047 0.3266449     02 0.7203244934 0.5270581     03 0.0001143748 0.8859421     14 0.3023325726 0.3572698     15 0.1467558908 0.9085352     16 0.0923385948 0.6233601     1``` >**Note**>>The R function lists the entire HDF5 file’s contents and assembles the`data.frame` object from all matching nodes, so use this only as astarting point if you have stored multiple `DataFrame` objects to asingle HDF5 file.  Performance- `tables` format come with a writing performance penalty as compared to  `fixed` stores. The benefit is the ability to append/delete and  query (potentially very large amounts of data).  Write times are  generally longer as compared with regular stores. Query times can  be quite fast, especially on an indexed axis.  - You can pass `chunksize=` to `append`, specifying the  write chunksize (default is 50000). This will significantly lower  your memory usage on writing.  - You can pass `expectedrows=` to the first `append`,  to set the TOTAL number of rows that `PyTables` will expect.  This will optimize read/write performance.  - Duplicate rows can be written to tables, but are filtered out in  selection (with the last items being selected; thus a table is  unique on major, minor pairs)  - A `PerformanceWarning` will be raised if you are attempting to  store types that will be pickled by PyTables (rather than stored as  endemic types). See  [Here](https://stackoverflow.com/questions/14355151/how-to-make-pandas-hdfstore-put-operation-faster/1437019014370190)  for more information and some solutions.<jupyter_code>store.close()
os.remove("store.h5")<jupyter_output><empty_output><jupyter_text>FeatherFeather provides binary columnar serialization for data frames. It is designed to make reading and writing dataframes efficient, and to make sharing data across data analysis languages easy.Feather is designed to faithfully serialize and de-serialize DataFrames, supporting all of the pandasdtypes, including extension dtypes such as categorical and datetime with tz.Several caveats:- The format will NOT write an `Index`, or `MultiIndex` for the  `DataFrame` and will raise an error if a non-default one is provided. You  can `.reset_index()` to store the index or `.reset_index(drop=True)` to  ignore it.  - Duplicate column names and non-string columns names are not supported  - Actual Python objects in object dtype columns are not supported. These will  raise a helpful error message on an attempt at serialization.  See the [Full Documentation](https://github.com/wesm/feather).<jupyter_code>import warnings

# This can be removed once building with pyarrow >=0.15.0
warnings.filterwarnings("ignore", "The Sparse", FutureWarning)
df = pd.DataFrame(
    {
        "a": list("abc"),
        "b": list(range(1, 4)),
        "c": np.arange(3, 6).astype("u1"),
        "d": np.arange(4.0, 7.0, dtype="float64"),
        "e": [True, False, True],
        "f": pd.Categorical(list("abc")),
        "g": pd.date_range("20130101", periods=3),
        "h": pd.date_range("20130101", periods=3, tz="US/Eastern"),
        "i": pd.date_range("20130101", periods=3, freq="ns"),
    }
)

df
df.dtypes<jupyter_output><empty_output><jupyter_text>Write to a feather file.<jupyter_code>df.to_feather("example.feather")<jupyter_output><empty_output><jupyter_text>Read from a feather file.<jupyter_code>result = pd.read_feather("example.feather")
result

# we preserve dtypes
result.dtypes
os.remove("example.feather")<jupyter_output><empty_output><jupyter_text>Parquet[Apache Parquet](https://parquet.apache.org/) provides a partitioned binary columnar serialization for data frames. It is designed tomake reading and writing data frames efficient, and to make sharing data across data analysislanguages easy. Parquet can use a variety of compression techniques to shrink the file size as much as possiblewhile still maintaining good read performance.Parquet is designed to faithfully serialize and de-serialize `DataFrame` s, supporting all of the pandasdtypes, including extension dtypes such as datetime with tz.Several caveats.- Duplicate column names and non-string columns names are not supported.  - The `pyarrow` engine always writes the index to the output, but `fastparquet` only writes non-default  indexes. This extra column can cause problems for non-pandas consumers that are not expecting it. You can  force including or omitting indexes with the `index` argument, regardless of the underlying engine.  - Index level names, if specified, must be strings.  - In the `pyarrow` engine, categorical dtypes for non-string types can be serialized to parquet, but will de-serialize as their primitive dtype.  - The `pyarrow` engine preserves the `ordered` flag of categorical dtypes with string types. `fastparquet` does not preserve the `ordered` flag.  - Non supported types include `Interval` and actual Python object types. These will raise a helpful error message  on an attempt at serialization. `Period` type is supported with pyarrow >= 0.16.0.  - The `pyarrow` engine preserves extension data types such as the nullable integer and string data  type (requiring pyarrow >= 0.16.0, and requiring the extension type to implement the needed protocols,  see the extension types documentation).  You can specify an `engine` to direct the serialization. This can be one of `pyarrow`, or `fastparquet`, or `auto`.If the engine is NOT specified, then the `pd.options.io.parquet.engine` option is checked; if this is also `auto`,then `pyarrow` is tried, and falling back to `fastparquet`.See the documentation for [pyarrow](https://arrow.apache.org/docs/python/) and [fastparquet](https://fastparquet.readthedocs.io/en/latest/).>**Note**>>These engines are very similar and should read/write nearly identical parquet format files.Currently `pyarrow` does not support timedelta data, `fastparquet>=0.1.4` supports timezone aware datetimes.These libraries differ by having different underlying dependencies (`fastparquet` by using `numba`, while `pyarrow` uses a c-library).<jupyter_code>df = pd.DataFrame(
    {
        "a": list("abc"),
        "b": list(range(1, 4)),
        "c": np.arange(3, 6).astype("u1"),
        "d": np.arange(4.0, 7.0, dtype="float64"),
        "e": [True, False, True],
        "f": pd.date_range("20130101", periods=3),
        "g": pd.date_range("20130101", periods=3, tz="US/Eastern"),
        "h": pd.Categorical(list("abc")),
        "i": pd.Categorical(list("abc"), ordered=True),
    }
)

df
df.dtypes<jupyter_output><empty_output><jupyter_text>Write to a parquet file.<jupyter_code>df.to_parquet("example_pa.parquet", engine="pyarrow")
df.to_parquet("example_fp.parquet", engine="fastparquet")<jupyter_output><empty_output><jupyter_text>Read from a parquet file.<jupyter_code>result = pd.read_parquet("example_fp.parquet", engine="fastparquet")
result = pd.read_parquet("example_pa.parquet", engine="pyarrow")

result.dtypes<jupyter_output><empty_output><jupyter_text>Read only certain columns of a parquet file.<jupyter_code>result = pd.read_parquet(
    "example_fp.parquet",
    engine="fastparquet",
    columns=["a", "b"],
)
result = pd.read_parquet(
    "example_pa.parquet",
    engine="pyarrow",
    columns=["a", "b"],
)
result.dtypes
os.remove("example_pa.parquet")
os.remove("example_fp.parquet")<jupyter_output><empty_output><jupyter_text>Handling indexesSerializing a `DataFrame` to parquet may include the implicit index as one ormore columns in the output file. Thus, this code:<jupyter_code>df = pd.DataFrame({"a": [1, 2], "b": [3, 4]})
df.to_parquet("test.parquet", engine="pyarrow")<jupyter_output><empty_output><jupyter_text>creates a parquet file with *three* columns if you use `pyarrow` for serialization:`a`, `b`, and `__index_level_0__`. If you’re using `fastparquet`, theindex [may or may not](https://fastparquet.readthedocs.io/en/latest/api.htmlfastparquet.write)be written to the file.This unexpected extra column causes some databases like Amazon Redshift to rejectthe file, because that column doesn’t exist in the target table.If you want to omit a dataframe’s indexes when writing, pass `index=False` to`to_parquet()`:<jupyter_code>df.to_parquet("test.parquet", index=False)<jupyter_output><empty_output><jupyter_text>This creates a parquet file with just the two expected columns, `a` and `b`.If your `DataFrame` has a custom index, you won’t get it back when you loadthis file into a `DataFrame`.Passing `index=True` will *always* write the index, even if that’s not theunderlying engine’s default behavior.<jupyter_code>os.remove("test.parquet")<jupyter_output><empty_output><jupyter_text>Partitioning Parquet filesParquet supports partitioning of data based on the values of one or more columns.<jupyter_code>df = pd.DataFrame({"a": [0, 0, 1, 1], "b": [0, 1, 0, 1]})
df.to_parquet(path="test", engine="pyarrow", partition_cols=["a"], compression=None)<jupyter_output><empty_output><jupyter_text>The `path` specifies the parent directory to which data will be saved.The `partition_cols` are the column names by which the dataset will be partitioned.Columns are partitioned in the order they are given. The partition splits aredetermined by the unique values in the partition columns.The above example creates a partitioned dataset that may look like: ```texttest├── a=0│   ├── 0bac803e32dc42ae83fddfd029cbdebc.parquet│   └──  ...└── a=1    ├── e6ab24a4f45147b49b54a662f0c412a3.parquet    └── ...```<jupyter_code>from shutil import rmtree

try:
    rmtree("test")
except OSError:
    pass<jupyter_output><empty_output><jupyter_text>ORCNew in version 1.0.0.Similar to the [parquet](io-parquet) format, the [ORC Format](https://orc.apache.org/) is a binary columnar serializationfor data frames. It is designed to make reading data frames efficient. pandas provides *only* a reader for theORC format, `read_orc()`. This requires the [pyarrow](https://arrow.apache.org/docs/python/) library.- It is *highly recommended* to install pyarrow using conda due to some issues occurred by pyarrow.  - `read_orc()` is not supported on Windows yet, you can find valid environments on [install optional dependencies](../01_getting_started/install.ipynbinstall-warn-orc).    SQL queriesThe `pandas.io.sql` module provides a collection of query wrappers to bothfacilitate data retrieval and to reduce dependency on DB-specific API. Database abstractionis provided by SQLAlchemy if installed. In addition you will need a driver library foryour database. Examples of such drivers are [psycopg2](http://initd.org/psycopg/)for PostgreSQL or [pymysql](https://github.com/PyMySQL/PyMySQL) for MySQL.For [SQLite](https://docs.python.org/3/library/sqlite3.html) this isincluded in Python’s standard library by default.You can find an overview of supported drivers for each SQL dialect in the[SQLAlchemy docs](https://docs.sqlalchemy.org/en/latest/dialects/index.html).If SQLAlchemy is not installed, a fallback is only provided for sqlite (andfor mysql for backwards compatibility, but this is deprecated and will beremoved in a future version).This mode requires a Python database adapter which respect the [PythonDB-API](https://www.python.org/dev/peps/pep-0249/).See also some [cookbook examples](44_cookbook.ipynbcookbook-sql) for some advanced strategies.The key functions are:>**Note**>>The function `read_sql()` is a convenience wrapper around`read_sql_table()` and `read_sql_query()` (and forbackward compatibility) and will delegate to specific function depending onthe provided input (database table name or sql query).Table names do not need to be quoted if they have special characters.In the following example, we use the [SQlite](https://www.sqlite.org/) SQL databaseengine. You can use a temporary SQLite database where data are stored in“memory”.To connect with SQLAlchemy you use the `create_engine()` function to create an engineobject from database URI. You only need to create the engine once per database you areconnecting to.For more information on `create_engine()` and the URI formatting, see the examplesbelow and the SQLAlchemy [documentation](https://docs.sqlalchemy.org/en/latest/core/engines.html)<jupyter_code>from sqlalchemy import create_engine

# Create your engine.
engine = create_engine("sqlite:///:memory:")<jupyter_output><empty_output><jupyter_text>If you want to manage your own connections you can pass one of those instead. The example below opens aconnection to the database using a Python context manager that automatically closes the connection afterthe block has completed.See the [SQLAlchemy docs](https://docs.sqlalchemy.org/en/latest/core/connections.htmlbasic-usage)for an explanation of how the database connection is handled. ```pythonwith engine.connect() as conn, conn.begin():    data = pd.read_sql_table("data", conn)``` When you open a connection to a database you are also responsible for closing it.Side effects of leaving a connection open may include locking the database orother breaking behaviour.  Writing DataFramesAssuming the following data is in a `DataFrame` `data`, we can insert it intothe database using `to_sql()`.|id|Date|Col_1|Col_2|Col_3||:---:|:----------:|:-----:|:-----:|:-----:||26|2012-10-18|X|25.7|True||42|2012-10-19|Y|-12.4|False||63|2012-10-20|Z|5.73|True|<jupyter_code>import datetime

c = ["id", "Date", "Col_1", "Col_2", "Col_3"]
d = [
    (26, datetime.datetime(2010, 10, 18), "X", 27.5, True),
    (42, datetime.datetime(2010, 10, 19), "Y", -12.5, False),
    (63, datetime.datetime(2010, 10, 20), "Z", 5.73, True),
]

data = pd.DataFrame(d, columns=c)
data
data.to_sql("data", engine)<jupyter_output><empty_output><jupyter_text>With some databases, writing large DataFrames can result in errors due topacket size limitations being exceeded. This can be avoided by setting the`chunksize` parameter when calling `to_sql`.  For example, the followingwrites `data` to the database in batches of 1000 rows at a time:<jupyter_code>data.to_sql("data_chunked", engine, chunksize=1000)<jupyter_output><empty_output><jupyter_text>SQL data types`to_sql()` will try to map your data to an appropriateSQL data type based on the dtype of the data. When you have columns of dtype`object`, pandas will try to infer the data type.You can always override the default type by specifying the desired SQL type ofany of the columns by using the `dtype` argument. This argument needs adictionary mapping column names to SQLAlchemy types (or strings for the sqlite3fallback mode).For example, specifying to use the sqlalchemy `String` type instead of thedefault `Text` type for string columns:<jupyter_code>from sqlalchemy.types import String

data.to_sql("data_dtype", engine, dtype={"Col_1": String})<jupyter_output><empty_output><jupyter_text>>**Note**>>Due to the limited support for timedelta’s in the different databaseflavors, columns with type `timedelta64` will be written as integervalues as nanoseconds to the database and a warning will be raised.>**Note**>>Columns of `category` dtype will be converted to the dense representationas you would get with `np.asarray(categorical)` (e.g. for string categoriesthis gives an array of strings).Because of this, reading the database table back in does **not** generatea categorical.  Datetime data typesUsing SQLAlchemy, `to_sql()` is capable of writingdatetime data that is timezone naive or timezone aware. However, the resultingdata stored in the database ultimately depends on the supported data typefor datetime data of the database system being used.The following table lists supported data types for datetime data for somecommon databases. Other database dialects may have different data types fordatetime data.``````````|Database|SQL Datetime Types|Timezone Support||:---------:|:-------------------------------------------:|:-----------------:||SQLite|TEXT|No||MySQL|TIMESTAMP or DATETIME|No||PostgreSQL|TIMESTAMP or TIMESTAMP WITH TIME ZONE|Yes|When writing timezone aware data to databases that do not support timezones,the data will be written as timezone naive timestamps that are in local timewith respect to the timezone.`read_sql_table()` is also capable of reading datetime data that istimezone aware or naive. When reading `TIMESTAMP WITH TIME ZONE` types, pandaswill convert the data to UTC.  Insertion methodThe parameter `method` controls the SQL insertion clause used.Possible values are:- `None`: Uses standard SQL `INSERT` clause (one per row).  - `'multi'`: Pass multiple values in a single `INSERT` clause.  It uses a *special* SQL syntax not supported by all backends.  This usually provides better performance for analytic databases  like *Presto* and *Redshift*, but has worse performance for  traditional SQL backend if the table contains many columns.  For more information check the SQLAlchemy [documentation](https://docs.sqlalchemy.org/en/latest/core/dml.htmlsqlalchemy.sql.expression.Insert.values.params.*args).  - callable with signature `(pd_table, conn, keys, data_iter)`:  This can be used to implement a more performant insertion method based on  specific backend dialect features.  Example of a callable using PostgreSQL [COPY clause](https://www.postgresql.org/docs/current/static/sql-copy.html):<jupyter_code># Alternative to_sql() *method* for DBs that support COPY FROM
import csv
from io import StringIO

def psql_insert_copy(table, conn, keys, data_iter):
    """
    Execute SQL statement inserting data

    Parameters
    ----------
    table : pandas.io.sql.SQLTable
    conn : sqlalchemy.engine.Engine or sqlalchemy.engine.Connection
    keys : list of str
        Column names
    data_iter : Iterable that iterates the values to be inserted
    """
    # gets a DBAPI connection that can provide a cursor
    dbapi_conn = conn.connection
    with dbapi_conn.cursor() as cur:
        s_buf = StringIO()
        writer = csv.writer(s_buf)
        writer.writerows(data_iter)
        s_buf.seek(0)

        columns = ', '.join(['"{}"'.format(k) for k in keys])
        if table.schema:
            table_name = '{}.{}'.format(table.schema, table.name)
        else:
            table_name = table.name

        sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
            table_name, columns)
        cur.copy_expert(sql=sql, file=s_buf)<jupyter_output><empty_output><jupyter_text>Reading tables`read_sql_table()` will read a database table given thetable name and optionally a subset of columns to read.>**Note**>>In order to use `read_sql_table()`, you **must** have theSQLAlchemy optional dependency installed.<jupyter_code>pd.read_sql_table("data", engine)<jupyter_output><empty_output><jupyter_text>>**Note**>>Note that pandas infers column dtypes from query outputs, and not by lookingup data types in the physical database schema. For example, assume `userid`is an integer column in a table. Then, intuitively, `select userid ...` willreturn integer-valued series, while `select cast(userid as text) ...` willreturn object-valued (str) series. Accordingly, if the query output is empty,then all resulting columns will be returned as object-valued (since they aremost general). If you foresee that your query will sometimes generate an emptyresult, you may want to explicitly typecast afterwards to ensure dtypeintegrity.You can also specify the name of the column as the `DataFrame` index,and specify a subset of columns to be read.<jupyter_code>pd.read_sql_table("data", engine, index_col="id")
pd.read_sql_table("data", engine, columns=["Col_1", "Col_2"])<jupyter_output><empty_output><jupyter_text>And you can explicitly force columns to be parsed as dates:<jupyter_code>pd.read_sql_table("data", engine, parse_dates=["Date"])<jupyter_output><empty_output><jupyter_text>If needed you can explicitly specify a format string, or a dict of argumentsto pass to `pandas.to_datetime()`: ```pythonpd.read_sql_table("data", engine, parse_dates={"Date": "%Y-%m-%d"})pd.read_sql_table(    "data",    engine,    parse_dates={"Date": {"format": "%Y-%m-%d %H:%M:%S"}},)``` You can check if a table exists using `has_table()`  Schema supportReading from and writing to different schema’s is supported through the `schema`keyword in the `read_sql_table()` and `to_sql()`functions. Note however that this depends on the database flavor (sqlite does nothave schema’s). For example: ```pythondf.to_sql("table", engine, schema="other_schema")pd.read_sql_table("table", engine, schema="other_schema")```  QueryingYou can query using raw SQL in the `read_sql_query()` function.In this case you must use the SQL variant appropriate for your database.When using SQLAlchemy, you can also pass SQLAlchemy Expression language constructs,which are database-agnostic.<jupyter_code>pd.read_sql_query("SELECT * FROM data", engine)<jupyter_output><empty_output><jupyter_text>Of course, you can specify a more “complex” query.<jupyter_code>pd.read_sql_query("SELECT id, Col_1, Col_2 FROM data WHERE id = 42;", engine)<jupyter_output><empty_output><jupyter_text>The `read_sql_query()` function supports a `chunksize` argument.Specifying this will return an iterator through chunks of the query result:<jupyter_code>df = pd.DataFrame(np.random.randn(20, 3), columns=list("abc"))
df.to_sql("data_chunks", engine, index=False)
for chunk in pd.read_sql_query("SELECT * FROM data_chunks", engine, chunksize=5):
    print(chunk)<jupyter_output><empty_output><jupyter_text>You can also run a plain query without creating a `DataFrame` with`execute()`. This is useful for queries that don’t return values,such as INSERT. This is functionally equivalent to calling `execute` on theSQLAlchemy engine or db connection object. Again, you must use the SQL syntaxvariant appropriate for your database. ```pythonfrom pandas.io import sqlsql.execute("SELECT * FROM table_name", engine)sql.execute(    "INSERT INTO table_name VALUES(?, ?, ?)", engine, params=[("id", 1, 12.2, True)])```  Engine connection examplesTo connect with SQLAlchemy you use the `create_engine()` function to create an engineobject from database URI. You only need to create the engine once per database you areconnecting to. ```pythonfrom sqlalchemy import create_engineengine = create_engine("postgresql://scott:tiger@localhost:5432/mydatabase")engine = create_engine("mysql+mysqldb://scott:tiger@localhost/foo")engine = create_engine("oracle://scott:tiger@127.0.0.1:1521/sidname")engine = create_engine("mssql+pyodbc://mydsn") sqlite:/// where  is relative:engine = create_engine("sqlite:///foo.db") or absolute, starting with a slash:engine = create_engine("sqlite:////absolute/path/to/foo.db")``` For more information see the examples the SQLAlchemy [documentation](https://docs.sqlalchemy.org/en/latest/core/engines.html)  Advanced SQLAlchemy queriesYou can use SQLAlchemy constructs to describe your query.Use `sqlalchemy.text()` to specify query parameters in a backend-neutral way<jupyter_code>import sqlalchemy as sa

pd.read_sql(
    sa.text("SELECT * FROM data where Col_1=:col1"), engine, params={"col1": "X"}
)<jupyter_output><empty_output><jupyter_text>If you have an SQLAlchemy description of your database you can express where conditions using SQLAlchemy expressions<jupyter_code>metadata = sa.MetaData()
data_table = sa.Table(
    "data",
    metadata,
    sa.Column("index", sa.Integer),
    sa.Column("Date", sa.DateTime),
    sa.Column("Col_1", sa.String),
    sa.Column("Col_2", sa.Float),
    sa.Column("Col_3", sa.Boolean),
)

pd.read_sql(sa.select([data_table]).where(data_table.c.Col_3 is True), engine)<jupyter_output><empty_output><jupyter_text>You can combine SQLAlchemy expressions with parameters passed to `read_sql()` using `sqlalchemy.bindparam()`<jupyter_code>import datetime as dt

expr = sa.select([data_table]).where(data_table.c.Date > sa.bindparam("date"))
pd.read_sql(expr, engine, params={"date": dt.datetime(2010, 10, 18)})<jupyter_output><empty_output><jupyter_text>Sqlite fallbackThe use of sqlite is supported without using SQLAlchemy.This mode requires a Python database adapter which respect the [PythonDB-API](https://www.python.org/dev/peps/pep-0249/).You can create connections like so: ```pythonimport sqlite3con = sqlite3.connect(":memory:")``` And then issue the following queries: ```pythondata.to_sql("data", con)pd.read_sql_query("SELECT * FROM data", con)```   Google BigQueryStarting in 0.20.0, pandas has split off Google BigQuery support into theseparate package `pandas-gbq`. You can `pip install pandas-gbq` to get it.The `pandas-gbq` package provides functionality to read/write from Google BigQuery.pandas integrates with this external package. if `pandas-gbq` is installed, you canuse the pandas methods `pd.read_gbq` and `DataFrame.to_gbq`, which will call therespective functions from `pandas-gbq`.Full documentation can be found [here](https://pandas-gbq.readthedocs.io/).  Stata format  Writing to stata formatThe method `to_stata()` will write a DataFrameinto a .dta file. The format version of this file is always 115 (Stata 12).<jupyter_code>df = pd.DataFrame(np.random.randn(10, 2), columns=list("AB"))
df.to_stata("stata.dta")<jupyter_output><empty_output><jupyter_text>*Stata* data files have limited data type support; only strings with244 or fewer characters, `int8`, `int16`, `int32`, `float32`and `float64` can be stored in `.dta` files.  Additionally,*Stata* reserves certain values to represent missing data. Exporting anon-missing value that is outside of the permitted range in Stata fora particular data type will retype the variable to the next largersize.  For example, `int8` values are restricted to lie between -127and 100 in Stata, and so variables with values above 100 will triggera conversion to `int16`. `nan` values in floating points datatypes are stored as the basic missing data type (`.` in *Stata*).>**Note**>>It is not possible to export missing data values for integer data types.The *Stata* writer gracefully handles other data types including `int64`,`bool`, `uint8`, `uint16`, `uint32` by casting tothe smallest supported type that can represent the data.  For example, datawith a type of `uint8` will be cast to `int8` if all values are less than100 (the upper bound for non-missing `int8` data in *Stata*), or, if values areoutside of this range, the variable is cast to `int16`.Conversion from `int64` to `float64` may result in a loss of precisionif `int64` values are larger than 2**53.`StataWriter` and`to_stata()` only support fixed widthstrings containing up to 244 characters, a limitation imposed by the version115 dta file format. Attempting to write *Stata* dta files with stringslonger than 244 characters raises a `ValueError`.  Reading from Stata formatThe top-level function `read_stata` will read a dta file and returneither a `DataFrame` or a `StataReader` that canbe used to read the file incrementally.<jupyter_code>pd.read_stata("stata.dta")<jupyter_output><empty_output><jupyter_text>Specifying a `chunksize` yields a`StataReader` instance that can be used toread `chunksize` lines from the file at a time.  The `StataReader`object can be used as an iterator.<jupyter_code>with pd.read_stata("stata.dta", chunksize=3) as reader:
    for df in reader:
        print(df.shape)<jupyter_output><empty_output><jupyter_text>For more fine-grained control, use `iterator=True` and specify`chunksize` with each call to`read()`.<jupyter_code>with pd.read_stata("stata.dta", iterator=True) as reader:
    chunk1 = reader.read(5)
    chunk2 = reader.read(5)<jupyter_output><empty_output><jupyter_text>Currently the `index` is retrieved as a column.The parameter `convert_categoricals` indicates whether value labels should beread and used to create a `Categorical` variable from them. Value labels canalso be retrieved by the function `value_labels`, which requires `read()`to be called before use.The parameter `convert_missing` indicates whether missing valuerepresentations in Stata should be preserved.  If `False` (the default),missing values are represented as `np.nan`.  If `True`, missing values arerepresented using `StataMissingValue` objects, and columns containing missingvalues will have `object` data type.>**Note**>>`read_stata()` and`StataReader` support .dta formats 113-115(Stata 10-12), 117 (Stata 13), and 118 (Stata 14).>**Note**>>Setting `preserve_dtypes=False` will upcast to the standard pandas data types:`int64` for all integer types and `float64` for floating point data.  By default,the Stata data types are preserved when importing.<jupyter_code>os.remove("stata.dta")<jupyter_output><empty_output><jupyter_text>Categorical data`Categorical` data can be exported to *Stata* data files as value labeled data.The exported data consists of the underlying category codes as integer data valuesand the categories as value labels.  *Stata* does not have an explicit equivalentto a `Categorical` and information about *whether* the variable is orderedis lost when exporting.*Stata* only supports string value labels, and so `str` is called on thecategories when exporting data.  Exporting `Categorical` variables withnon-string categories produces a warning, and can result a loss ofinformation if the `str` representations of the categories are not unique.Labeled data can similarly be imported from *Stata* data files as `Categorical`variables using the keyword argument `convert_categoricals` (`True` by default).The keyword argument `order_categoricals` (`True` by default) determineswhether imported `Categorical` variables are ordered.>**Note**>>When importing categorical data, the values of the variables in the *Stata*data file are not preserved since `Categorical` variables alwaysuse integer data types between `-1` and `n-1` where `n` is the numberof categories. If the original values in the *Stata* data file are required,these can be imported by setting `convert_categoricals=False`, which willimport original data (but not the variable labels). The original values canbe matched to the imported categorical data since there is a simple mappingbetween the original *Stata* data values and the category codes of importedCategorical variables: missing values are assigned code `-1`, and thesmallest original value is assigned `0`, the second smallest is assigned`1` and so on until the largest original value is assigned the code `n-1`.>**Note**>>*Stata* supports partially labeled series. These series have value labels forsome but not all data values. Importing a partially labeled series will producea `Categorical` with string categories for the values that are labeled andnumeric categories for values with no label.  SAS formatsThe top-level function `read_sas()` can read (but not write) SASXPORT (.xpt) and (since *v0.18.0*) SAS7BDAT (.sas7bdat) format files.SAS files only contain two value types: ASCII text and floating pointvalues (usually 8 bytes but sometimes truncated).  For xport files,there is no automatic type conversion to integers, dates, orcategoricals.  For SAS7BDAT files, the format codes may allow datevariables to be automatically converted to dates.  By default thewhole file is read and returned as a `DataFrame`.Specify a `chunksize` or use `iterator=True` to obtain readerobjects (`XportReader` or `SAS7BDATReader`) for incrementallyreading the file.  The reader objects also have attributes thatcontain additional information about the file and its variables.Read a SAS7BDAT file: ```pythondf = pd.read_sas("sas_data.sas7bdat")``` Obtain an iterator and read an XPORT file 100,000 lines at a time: ```pythondef do_something(chunk):    passwith pd.read_sas("sas_xport.xpt", chunk=100000) as rdr:    for chunk in rdr:        do_something(chunk)``` The [specification](https://support.sas.com/techsup/technote/ts140.pdf) for the xport file format is available from the SASweb site.No official documentation is available for the SAS7BDAT format.  SPSS formatsNew in version 0.25.0.The top-level function `read_spss()` can read (but not write) SPSSSAV (.sav) and  ZSAV (.zsav) format files.SPSS files contain column names. By default thewhole file is read, categorical columns are converted into `pd.Categorical`,and a `DataFrame` with all columns is returned.Specify the `usecols` parameter to obtain a subset of columns. Specify `convert_categoricals=False`to avoid converting categorical columns into `pd.Categorical`.Read an SPSS file: ```pythondf = pd.read_spss("spss_data.sav")``` Extract a subset of columns contained in `usecols` from an SPSS file andavoid converting categorical columns into `pd.Categorical`: ```pythondf = pd.read_spss(    "spss_data.sav",    usecols=["foo", "bar"],    convert_categoricals=False,)``` More information about the SAV and ZSAV file formats is available [here](https://www.ibm.com/support/knowledgecenter/en/SSLVMB_22.0.0/com.ibm.spss.statistics.help/spss/base/savedatatypes.htm).  Other file formatspandas itself only supports IO with a limited set of file formats that mapcleanly to its tabular data model. For reading and writing other file formatsinto and from pandas, we recommend these packages from the broader community.  netCDF[xarray](https://xarray.pydata.org/) provides data structures inspired by the pandas `DataFrame` for workingwith multi-dimensional datasets, with a focus on the netCDF file format andeasy conversion to and from pandas.  Performance considerationsThis is an informal comparison of various IO methods, using pandas0.24.2. Timings are machine dependent and small differences should beignored. ```pythonIn [1]: sz = 1000000In [2]: df = pd.DataFrame({'A': np.random.randn(sz), 'B': [1] * sz})In [3]: df.info()RangeIndex: 1000000 entries, 0 to 999999Data columns (total 2 columns):A    1000000 non-null float64B    1000000 non-null int64dtypes: float64(1), int64(1)memory usage: 15.3 MB``` The following test functions will be used below to compare the performance of several IO methods:<jupyter_code>import numpy as np

import os, sqlite3


sz = 1000000
df = pd.DataFrame({"A": np.random.randn(sz), "B": [1] * sz})

sz = 1000000
np.random.seed(42)
df = pd.DataFrame({"A": np.random.randn(sz), "B": [1] * sz})


def test_sql_write(df):
    if os.path.exists("test.sql"):
        os.remove("test.sql")
    sql_db = sqlite3.connect("test.sql")
    df.to_sql(name="test_table", con=sql_db)
    sql_db.close()


def test_sql_read():
    sql_db = sqlite3.connect("test.sql")
    pd.read_sql_query("select * from test_table", sql_db)
    sql_db.close()


def test_hdf_fixed_write(df):
    df.to_hdf("test_fixed.hdf", "test", mode="w")


def test_hdf_fixed_read():
    pd.read_hdf("test_fixed.hdf", "test")


def test_hdf_fixed_write_compress(df):
    df.to_hdf("test_fixed_compress.hdf", "test", mode="w", complib="blosc")


def test_hdf_fixed_read_compress():
    pd.read_hdf("test_fixed_compress.hdf", "test")


def test_hdf_table_write(df):
    df.to_hdf("test_table.hdf", "test", mode="w", format="table")


def test_hdf_table_read():
    pd.read_hdf("test_table.hdf", "test")


def test_hdf_table_write_compress(df):
    df.to_hdf(
        "test_table_compress.hdf", "test", mode="w", complib="blosc", format="table"
    )


def test_hdf_table_read_compress():
    pd.read_hdf("test_table_compress.hdf", "test")


def test_csv_write(df):
    df.to_csv("test.csv", mode="w")


def test_csv_read():
    pd.read_csv("test.csv", index_col=0)


def test_feather_write(df):
    df.to_feather("test.feather")


def test_feather_read():
    pd.read_feather("test.feather")


def test_pickle_write(df):
    df.to_pickle("test.pkl")


def test_pickle_read():
    pd.read_pickle("test.pkl")


def test_pickle_write_compress(df):
    df.to_pickle("test.pkl.compress", compression="xz")


def test_pickle_read_compress():
    pd.read_pickle("test.pkl.compress", compression="xz")


def test_parquet_write(df):
    df.to_parquet("test.parquet")


def test_parquet_read():
    pd.read_parquet("test.parquet")<jupyter_output><empty_output><jupyter_text>When writing, the top three functions in terms of speed are `test_feather_write`, `test_hdf_fixed_write` and `test_hdf_fixed_write_compress`.<jupyter_code>%timeit test_sql_write(df)

%timeit test_hdf_fixed_write(df)

%timeit test_hdf_fixed_write_compress(df)

%timeit test_hdf_table_write(df)

%timeit test_hdf_table_write_compress(df)

%timeit test_csv_write(df)

%timeit test_feather_write(df)

%timeit test_pickle_write(df)

%timeit test_pickle_write_compress(df)

%timeit test_parquet_write(df)<jupyter_output>4.91 s ± 178 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
63.2 ms ± 4.61 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
63.6 ms ± 8.39 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
784 ms ± 37.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
792 ms ± 65.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
3.62 s ± 99.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)<jupyter_text>When reading, the top three functions in terms of speed are `test_feather_read`, `test_pickle_read` and`test_hdf_fixed_read`.<jupyter_code>%timeit test_sql_read()

%timeit test_hdf_fixed_read()


%timeit test_hdf_fixed_read_compress()


%timeit test_hdf_table_read()


%timeit test_hdf_table_read_compress()


%timeit test_csv_read()


%timeit test_feather_read()


%timeit test_pickle_read()


%timeit test_pickle_read_compress()


%timeit test_parquet_read()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12030287423489096, 'fraction_numerical': 0.03457599587980112, 'mean_word_length': 5.213514261977291, 'pattern_counts': {'":': 190, '<': 1286, '<?xml version=': 5, '>': 1484, 'https://': 120, 'lorem ipsum': 0, 'www.': 26, 'xml': 99}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '240', 'dir': 'jupyter-structured-clean-dedup', 'id': '656774', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 69026, 'n_tokens_neox': 64028, 'n_words': 21357, 'path': '04_user_guide/22_io.ipynb', 'repo_name': 'olippuner/Pandas_Docu_as_Notebooks', 'size': '201979'}