IO tools (text, CSV, HDF5, …)

The pandas I/O API is a set of top level reader functions accessed likepandas.read_csv() that generally return a pandas object. The correspondingwriter functions are object methods that are accessed likeDataFrame.to_csv(). Below is a table containing available readers andwriters.

Here is an informal performance comparison for some of these IO methods.

Note

For examples that use the StringIO class, make sure you import itaccording to your Python version, i.e. from StringIO import StringIO forPython 2 and from io import StringIO for Python 3.

CSV & text files

The workhorse function for reading text files (a.k.a. flat files) isread_csv(). See the cookbook for some advanced strategies.

Parsing options

read_csv() accepts the following common arguments:

Basic

• filepath_or_buffer :various
• Either a path to a file (a str, pathlib.Path,or py._path.local.LocalPath), URL (including http, ftp, and S3locations), or any object with a read() method (such as an open file orStringIO).
• sep :str, 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,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'.
• delimiter :str, default None
• Alternative argument name for sep.
• delim_whitespace :boolean, default False
• Specifies 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 thedelimiter parameter.

New in version 0.18.1: support for the Python parser.

Column and index locations and names

• header :int 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 toheader=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.

• names :array-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_col :int, 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: indexcol=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.

• usecols :list-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-likeusecols 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 usepd.read_csv(data, usecols=['foo', 'bar'])[['foo', 'bar']] for columnsin ['foo', 'bar'] order orpd.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:

In [1]: from io import StringIO, BytesIO
 
In [2]: data = ('col1,col2,col3\n'
   ...:         'a,b,1\n'
   ...:         'a,b,2\n'
   ...:         'c,d,3')
   ...: 
 
In [3]: pd.read_csv(StringIO(data))
Out[3]: 
  col1 col2  col3
0    a    b     1
1    a    b     2
2    c    d     3
 
In [4]: pd.read_csv(StringIO(data), usecols=lambda x: x.upper() in ['COL1', 'COL3'])
Out[4]: 
  col1  col3
0    a     1
1    a     2
2    c     3

Using this parameter results in much faster parsing time and lower memory usage.

• squeeze :boolean, default False
• If the parsed data only contains one column then return a Series.
• prefix :str, default None
• Prefix to add to column numbers when no header, e.g. ‘X’ for X0, X1, …
• mangle_dupe_cols :boolean, 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 configuration

• dtype :Type 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.

New in version 0.20.0: support for the Python parser.

• engine :{'c', 'python'}
• Parser engine to use. The C engine is faster while the Python engine iscurrently more feature-complete.
• converters :dict, default None
• Dict of functions for converting values in certain columns. Keys can either beintegers or column labels.
• true_values :list, default None
• Values to consider as True.
• false_values :list, default None
• Values to consider as False.
• skipinitialspace :boolean, default False
• Skip spaces after delimiter.
• skiprows :list-like or integer, default None
• Line numbers to skip (0-indexed) or number of lines to skip (int) at the 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:

In [5]: data = ('col1,col2,col3\n'
   ...:         'a,b,1\n'
   ...:         'a,b,2\n'
   ...:         'c,d,3')
   ...: 
 
In [6]: pd.read_csv(StringIO(data))
Out[6]: 
  col1 col2  col3
0    a    b     1
1    a    b     2
2    c    d     3
 
In [7]: pd.read_csv(StringIO(data), skiprows=lambda x: x % 2 != 0)
Out[7]: 
  col1 col2  col3
0    a    b     2

• skipfooter :int, default 0
• Number of lines at bottom of file to skip (unsupported with engine=’c’).
• nrows :int, default None
• Number of rows of file to read. Useful for reading pieces of large files.
• low_memory :boolean, default True
• Internally process the file in chunks, resulting in lower memory 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_map :boolean, default False
• If 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 handling

• na_values :scalar, 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 belowfor a list of the values interpreted as NaN by default.
• keep_default_na :boolean, default True

• Whether or not to include the default NaN values when parsing the data.Depending on whether na_values is passed in, the behavior is as follows:

  • If keep_default_na is True, and na_values are specified, _na_values_is appended to the default NaN values used for parsing.
  • If keep_default_na is True, and na_values are not specified, onlythe default NaN values are used for parsing.
  • If keep_default_na is False, and na_values are specified, onlythe NaN values specified na_values are used for parsing.
  • If keep_default_na is False, and na_values are not specified, nostrings will be parsed as NaN.Note that if na_filter is passed in as False, the keep_default_na andna_values parameters will be ignored.

• na_filter :boolean, 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.
• verbose :boolean, default False
• Indicate number of NA values placed in non-numeric columns.
• skip_blank_lines :boolean, default True
• If True, skip over blank lines rather than interpreting as NaN values.

Datetime handling

• parse_dates :boolean or list of ints or names or list of lists or dict, default False.
• • If True -> try parsing the index.
  • If [1, 2, 3] -> try parsing columns 1, 2, 3 each as a separate datecolumn.
  • If [[1, 3]] -> combine columns 1 and 3 and parse as a single datecolumn.
  • If {'foo': [1, 3]} -> parse columns 1, 3 as date and call result ‘foo’.A fast-path exists for iso8601-formatted dates.
• infer_datetime_format :boolean, default False
• If True and parse_dates is enabled for a column, attempt to infer thedatetime format to speed up the processing.
• keep_date_col :boolean, default False
• If True and parse_dates specifies combining multiple columns then keep theoriginal columns.
• date_parser :function, 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.
• dayfirst :boolean, default False
• DD/MM format dates, international and European format.
• cache_dates :boolean, default True
• If True, use a cache of unique, converted dates to apply the datetimeconversion. May produce significant speed-up when parsing duplicatedate strings, especially ones with timezone offsets.

New in version 0.25.0.

Iteration

• iterator :boolean, default False
• Return TextFileReader object for iteration or getting chunks withget_chunk().
• chunksize :int, default None
• Return TextFileReader object for iteration. See iterating and chunking below.

Quoting, compression, and file format

• compression :{'infer', 'gzip', 'bz2', 'zip', 'xz', None}, default 'infer'
• For on-the-fly decompression of on-disk data. If ‘infer’, then use gzip,bz2, zip, or xz if filepath_or_buffer is a string ending in ‘.gz’, ‘.bz2’,‘.zip’, or ‘.xz’, respectively, and no decompression otherwise. If using ‘zip’,the ZIP file must contain only one data file to be read in.Set to None for no decompression.

New in version 0.18.1: support for ‘zip’ and ‘xz’ compression.

Changed in version 0.24.0: ‘infer’ option added and set to default.

• thousands :str, default None
• Thousands separator.
• decimal :str, default '.'
• Character to recognize as decimal point. E.g. use ',' for European data.
• float_precision :string, default None
• Specifies which converter the C engine should use for floating-point values.The options are None for the ordinary converter, high for thehigh-precision converter, and round_trip for the round-trip converter.
• lineterminator :str (length 1), default None
• Character to break file into lines. Only valid with C parser.
• quotechar :str (length 1)
• The character used to denote the start and end of a quoted item. Quoted itemscan include the delimiter and it will be ignored.
• quoting :int or csv.QUOTE_* instance, default 0
• Control field quoting behavior per csv.QUOTE_* constants. Use one ofQUOTE_MINIMAL (0), QUOTE_ALL (1), QUOTE_NONNUMERIC (2) orQUOTE_NONE (3).
• doublequote :boolean, default True
• When quotechar is specified and quoting is not QUOTE_NONE,indicate whether or not to interpret two consecutive quotechar elementsinside a field as a single quotechar element.
• escapechar :str (length 1), default None
• One-character string used to escape delimiter when quoting is QUOTE_NONE.
• comment :str, default None
• Indicates remainder of line should not be parsed. If found at the beginning ofa line, the line will be ignored altogether. This parameter must be a singlecharacter. Like empty lines (as long as skipblank_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’ withheader=0 will result in ‘a,b,c’ being treated as the header.
• encoding :str, default None
• Encoding to use for UTF when reading/writing (e.g. 'utf-8'). List ofPython standard encodings.
• dialect :str or 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 csv.Dialectdocumentation for more details.

Error handling

• error_bad_lines :boolean, default True
• Lines with too many fields (e.g. a csv line with too many commas) will bydefault cause an exception to be raised, and no DataFrame will bereturned. If False, then these “bad lines” will dropped from theDataFrame that is returned. See bad linesbelow.
• warn_bad_lines :boolean, default True
• If error_bad_lines is False, and warn_bad_lines is True, a warning foreach “bad line” will be output.

Specifying column data types

You can indicate the data type for the whole DataFrame or individualcolumns:

In [8]: data = ('a,b,c,d\n'
   ...:         '1,2,3,4\n'
   ...:         '5,6,7,8\n'
   ...:         '9,10,11')
   ...: 
 
In [9]: print(data)
a,b,c,d
1,2,3,4
5,6,7,8
9,10,11
 
In [10]: df = pd.read_csv(StringIO(data), dtype=object)
 
In [11]: df
Out[11]: 
   a   b   c    d
0  1   2   3    4
1  5   6   7    8
2  9  10  11  NaN
 
In [12]: df['a'][0]
Out[12]: '1'
 
In [13]: df = pd.read_csv(StringIO(data),
   ....:                  dtype={'b': object, 'c': np.float64, 'd': 'Int64'})
   ....: 
 
In [14]: df.dtypes
Out[14]: 
a      int64
b     object
c    float64
d      Int64
dtype: object

Fortunately, pandas offers more than one way to ensure that your column(s)contain only one dtype. If you’re unfamiliar with these concepts, you 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():

In [15]: data = ("col_1\n"
   ....:         "1\n"
   ....:         "2\n"
   ....:         "'A'\n"
   ....:         "4.22")
   ....: 
 
In [16]: df = pd.read_csv(StringIO(data), converters={'col_1': str})
 
In [17]: df
Out[17]: 
  col_1
0     1
1     2
2   'A'
3  4.22
 
In [18]: df['col_1'].apply(type).value_counts()
Out[18]: 
<class 'str'>    4
Name: col_1, dtype: int64

Or you can use the to_numeric() function to coerce thedtypes after reading in the data,

In [19]: df2 = pd.read_csv(StringIO(data))
 
In [20]: df2['col_1'] = pd.to_numeric(df2['col_1'], errors='coerce')
 
In [21]: df2
Out[21]: 
   col_1
0   1.00
1   2.00
2    NaN
3   4.22
 
In [22]: df2['col_1'].apply(type).value_counts()
Out[22]: 
<class 'float'>    4
Name: col_1, dtype: int64

which will convert all valid parsing to floats, leaving the invalid 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.

New in version 0.20.0: support for the Python parser.

The dtype option is supported by the ‘python’ engine.

Note

In some cases, reading in abnormal data with columns containing mixed 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,

In [23]: col_1 = list(range(500000)) + ['a', 'b'] + list(range(500000))
 
In [24]: df = pd.DataFrame({'col_1': col_1})
 
In [25]: df.to_csv('foo.csv')
 
In [26]: mixed_df = pd.read_csv('foo.csv')
 
In [27]: mixed_df['col_1'].apply(type).value_counts()
Out[27]: 
<class 'int'>    737858
<class 'str'>    262144
Name: col_1, dtype: int64
 
In [28]: mixed_df['col_1'].dtype
Out[28]: dtype('O')

will result with mixed_df containing an int dtype for certain 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.

Specifying categorical dtype

New in version 0.19.0.

Categorical columns can be parsed directly by specifying dtype='category' ordtype=CategoricalDtype(categories, ordered).

In [29]: data = ('col1,col2,col3\n'
   ....:         'a,b,1\n'
   ....:         'a,b,2\n'
   ....:         'c,d,3')
   ....: 
 
In [30]: pd.read_csv(StringIO(data))
Out[30]: 
  col1 col2  col3
0    a    b     1
1    a    b     2
2    c    d     3
 
In [31]: pd.read_csv(StringIO(data)).dtypes
Out[31]: 
col1    object
col2    object
col3     int64
dtype: object
 
In [32]: pd.read_csv(StringIO(data), dtype='category').dtypes
Out[32]: 
col1    category
col2    category
col3    category
dtype: object

Individual columns can be parsed as a Categorical using a dictspecification:

In [33]: pd.read_csv(StringIO(data), dtype={'col1': 'category'}).dtypes
Out[33]: 
col1    category
col2      object
col3       int64
dtype: object

New in version 0.21.0.

Specifying dtype='category' will result in an unordered Categoricalwhose categories are the unique values observed in the data. For morecontrol on the categories and order, create aCategoricalDtype ahead of time, and pass that forthat column’s dtype.

In [34]: from pandas.api.types import CategoricalDtype
 
In [35]: dtype = CategoricalDtype(['d', 'c', 'b', 'a'], ordered=True)
 
In [36]: pd.read_csv(StringIO(data), dtype={'col1': dtype}).dtypes
Out[36]: 
col1    category
col2      object
col3       int64
dtype: object

When using dtype=CategoricalDtype, “unexpected” values outside ofdtype.categories are treated as missing values.

In [37]: dtype = CategoricalDtype(['a', 'b', 'd'])  # No 'c'
 
In [38]: pd.read_csv(StringIO(data), dtype={'col1': dtype}).col1
Out[38]: 
0      a
1      a
2    NaN
Name: col1, dtype: category
Categories (3, object): [a, b, d]

This matches the behavior of Categorical.set_categories().

Note

With dtype='category', the resulting categories will always be 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.

In [39]: df = pd.read_csv(StringIO(data), dtype='category')
 
In [40]: df.dtypes
Out[40]: 
col1    category
col2    category
col3    category
dtype: object
 
In [41]: df['col3']
Out[41]: 
0    1
1    2
2    3
Name: col3, dtype: category
Categories (3, object): [1, 2, 3]
 
In [42]: df['col3'].cat.categories = pd.to_numeric(df['col3'].cat.categories)
 
In [43]: df['col3']
Out[43]: 
0    1
1    2
2    3
Name: col3, dtype: category
Categories (3, int64): [1, 2, 3]

Naming and using columns

Handling column names

A file may or may not have a header row. pandas assumes the first row should beused as the column names:

In [44]: data = ('a,b,c\n'
   ....:         '1,2,3\n'
   ....:         '4,5,6\n'
   ....:         '7,8,9')
   ....: 
 
In [45]: print(data)
a,b,c
1,2,3
4,5,6
7,8,9
 
In [46]: pd.read_csv(StringIO(data))
Out[46]: 
   a  b  c
0  1  2  3
1  4  5  6
2  7  8  9

By specifying the names argument in conjunction with header you canindicate other names to use and whether or not to throw away the header row (ifany):

In [47]: print(data)
a,b,c
1,2,3
4,5,6
7,8,9
 
In [48]: pd.read_csv(StringIO(data), names=['foo', 'bar', 'baz'], header=0)
Out[48]: 
   foo  bar  baz
0    1    2    3
1    4    5    6
2    7    8    9
 
In [49]: pd.read_csv(StringIO(data), names=['foo', 'bar', 'baz'], header=None)
Out[49]: 
  foo bar baz
0   a   b   c
1   1   2   3
2   4   5   6
3   7   8   9

If the header is in a row other than the first, pass the row number toheader. This will skip the preceding rows:

In [50]: data = ('skip this skip it\n'
   ....:         'a,b,c\n'
   ....:         '1,2,3\n'
   ....:         '4,5,6\n'
   ....:         '7,8,9')
   ....: 
 
In [51]: pd.read_csv(StringIO(data), header=1)
Out[51]: 
   a  b  c
0  1  2  3
1  4  5  6
2  7  8  9

Note

Default behavior is to infer the column names: if no names 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 toheader=None.

Duplicate names parsing

If the file or header contains duplicate names, pandas will by defaultdistinguish between them so as to prevent overwriting data:

In [52]: data = ('a,b,a\n'
   ....:         '0,1,2\n'
   ....:         '3,4,5')
   ....: 
 
In [53]: pd.read_csv(StringIO(data))
Out[53]: 
   a  b  a.1
0  0  1    2
1  3  4    5

There is no more duplicate data because mangle_dupe_cols=True by default,which modifies a series of duplicate columns ‘X’, …, ‘X’ to become‘X’, ‘X.1’, …, ‘X.N’. If mangle_dupe_cols=False, duplicate data canarise:

In [2]: data = 'a,b,a\n0,1,2\n3,4,5'
In [3]: pd.read_csv(StringIO(data), mangle_dupe_cols=False)
Out[3]:
   a  b  a
0  2  1  2
1  5  4  5

To prevent users from encountering this problem with duplicate data, a ValueErrorexception is raised if mangle_dupe_cols != True:

In [2]: data = 'a,b,a\n0,1,2\n3,4,5'
In [3]: pd.read_csv(StringIO(data), mangle_dupe_cols=False)
...
ValueError: Setting mangle_dupe_cols=False is not supported yet

Filtering columns (usecols)

The usecols argument allows you to select any subset of the columns in afile, either using the column names, position numbers or a callable:

New in version 0.20.0: support for callable usecols arguments

In [54]: data = 'a,b,c,d\n1,2,3,foo\n4,5,6,bar\n7,8,9,baz'
 
In [55]: pd.read_csv(StringIO(data))
Out[55]: 
   a  b  c    d
0  1  2  3  foo
1  4  5  6  bar
2  7  8  9  baz
 
In [56]: pd.read_csv(StringIO(data), usecols=['b', 'd'])
Out[56]: 
   b    d
0  2  foo
1  5  bar
2  8  baz
 
In [57]: pd.read_csv(StringIO(data), usecols=[0, 2, 3])
Out[57]: 
   a  c    d
0  1  3  foo
1  4  6  bar
2  7  9  baz
 
In [58]: pd.read_csv(StringIO(data), usecols=lambda x: x.upper() in ['A', 'C'])
Out[58]: 
   a  c
0  1  3
1  4  6
2  7  9

The usecols argument can also be used to specify which columns not touse in the final result:

In [59]: pd.read_csv(StringIO(data), usecols=lambda x: x not in ['a', 'c'])
Out[59]: 
   b    d
0  2  foo
1  5  bar
2  8  baz

In this case, the callable is specifying that we exclude the “a” and “c”columns from the output.

Comments and empty lines

Ignoring line comments and empty lines

If the comment parameter is specified, then completely commented lines willbe ignored. By default, completely blank lines will be ignored as well.

In [60]: data = ('\n'
   ....:         'a,b,c\n'
   ....:         '  \n'
   ....:         '# commented line\n'
   ....:         '1,2,3\n'
   ....:         '\n'
   ....:         '4,5,6')
   ....: 
 
In [61]: print(data)
 
a,b,c
 
# commented line
1,2,3
 
4,5,6
 
In [62]: pd.read_csv(StringIO(data), comment='#')
Out[62]: 
   a  b  c
0  1  2  3
1  4  5  6

If skip_blank_lines=False, then read_csv will not ignore blank lines:

In [63]: data = ('a,b,c\n'
   ....:         '\n'
   ....:         '1,2,3\n'
   ....:         '\n'
   ....:         '\n'
   ....:         '4,5,6')
   ....: 
 
In [64]: pd.read_csv(StringIO(data), skip_blank_lines=False)
Out[64]: 
     a    b    c
0  NaN  NaN  NaN
1  1.0  2.0  3.0
2  NaN  NaN  NaN
3  NaN  NaN  NaN
4  4.0  5.0  6.0

Warning

The presence of ignored lines might create ambiguities involving line numbers;the parameter header uses row numbers (ignoring commented/emptylines), while skiprows uses line numbers (including commented/empty lines):

In [65]: data = ('#comment\n'
   ....:         'a,b,c\n'
   ....:         'A,B,C\n'
   ....:         '1,2,3')
   ....: 
 
In [66]: pd.read_csv(StringIO(data), comment='#', header=1)
Out[66]: 
   A  B  C
0  1  2  3
 
In [67]: data = ('A,B,C\n'
   ....:         '#comment\n'
   ....:         'a,b,c\n'
   ....:         '1,2,3')
   ....: 
 
In [68]: pd.read_csv(StringIO(data), comment='#', skiprows=2)
Out[68]: 
   a  b  c
0  1  2  3

If both header and skiprows are specified, header will berelative to the end of skiprows. For example:

In [69]: data = ('# empty\n'
   ....:         '# second empty line\n'
   ....:         '# third emptyline\n'
   ....:         'X,Y,Z\n'
   ....:         '1,2,3\n'
   ....:         'A,B,C\n'
   ....:         '1,2.,4.\n'
   ....:         '5.,NaN,10.0\n')
   ....: 
 
In [70]: print(data)
# empty
# second empty line
# third emptyline
X,Y,Z
1,2,3
A,B,C
1,2.,4.
5.,NaN,10.0
 
 
In [71]: pd.read_csv(StringIO(data), comment='#', skiprows=4, header=1)
Out[71]: 
     A    B     C
0  1.0  2.0   4.0
1  5.0  NaN  10.0

Comments

Sometimes comments or meta data may be included in a file:

In [72]: print(open('tmp.csv').read())
ID,level,category
Patient1,123000,x # really unpleasant
Patient2,23000,y # wouldn't take his medicine
Patient3,1234018,z # awesome

By default, the parser includes the comments in the output:

In [73]: df = pd.read_csv('tmp.csv')
 
In [74]: df
Out[74]: 
         ID    level                        category
0  Patient1   123000           x # really unpleasant
1  Patient2    23000  y # wouldn't take his medicine
2  Patient3  1234018                     z # awesome

We can suppress the comments using the comment keyword:

In [75]: df = pd.read_csv('tmp.csv', comment='#')
 
In [76]: df
Out[76]: 
         ID    level category
0  Patient1   123000       x 
1  Patient2    23000       y 
2  Patient3  1234018       z 

Dealing with Unicode data

The encoding argument should be used for encoded unicode data, which willresult in byte strings being decoded to unicode in the result:

In [77]: data = (b'word,length\n'
   ....:         b'Tr\xc3\xa4umen,7\n'
   ....:         b'Gr\xc3\xbc\xc3\x9fe,5')
   ....: 
 
In [78]: data = data.decode('utf8').encode('latin-1')
 
In [79]: df = pd.read_csv(BytesIO(data), encoding='latin-1')
 
In [80]: df
Out[80]: 
      word  length
0  Träumen       7
1    Grüße       5
 
In [81]: df['word'][1]
Out[81]: 'Grüße'

Some formats which encode all characters as multiple bytes, like UTF-16, won’tparse correctly at all without specifying the encoding. Full list of Pythonstandard encodings.

Index columns and trailing delimiters

If 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:

In [82]: data = ('a,b,c\n'
   ....:         '4,apple,bat,5.7\n'
   ....:         '8,orange,cow,10')
   ....: 
 
In [83]: pd.read_csv(StringIO(data))
Out[83]: 
        a    b     c
4   apple  bat   5.7
8  orange  cow  10.0

In [84]: data = ('index,a,b,c\n'
   ....:         '4,apple,bat,5.7\n'
   ....:         '8,orange,cow,10')
   ....: 
 
In [85]: pd.read_csv(StringIO(data), index_col=0)
Out[85]: 
            a    b     c
index                   
4       apple  bat   5.7
8      orange  cow  10.0

Ordinarily, you can achieve this behavior using the index_col option.

There are some exception cases when a file has been prepared with delimiters atthe end of each data line, confusing the parser. To explicitly disable theindex column inference and discard the last column, pass index_col=False:

In [86]: data = ('a,b,c\n'
   ....:         '4,apple,bat,\n'
   ....:         '8,orange,cow,')
   ....: 
 
In [87]: print(data)
a,b,c
4,apple,bat,
8,orange,cow,
 
In [88]: pd.read_csv(StringIO(data))
Out[88]: 
        a    b   c
4   apple  bat NaN
8  orange  cow NaN
 
In [89]: pd.read_csv(StringIO(data), index_col=False)
Out[89]: 
   a       b    c
0  4   apple  bat
1  8  orange  cow

If a subset of data is being parsed using the usecols option, theindex_col specification is based on that subset, not the original data.

In [90]: data = ('a,b,c\n'
   ....:         '4,apple,bat,\n'
   ....:         '8,orange,cow,')
   ....: 
 
In [91]: print(data)
a,b,c
4,apple,bat,
8,orange,cow,
 
In [92]: pd.read_csv(StringIO(data), usecols=['b', 'c'])
Out[92]: 
     b   c
4  bat NaN
8  cow NaN
 
In [93]: pd.read_csv(StringIO(data), usecols=['b', 'c'], index_col=0)
Out[93]: 
     b   c
4  bat NaN
8  cow NaN

Date Handling

Specifying date columns

To better facilitate working with datetime data, read_csv()uses the keyword arguments parse_dates and date_parserto 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:

# Use a column as an index, and parse it as dates.
In [94]: df = pd.read_csv('foo.csv', index_col=0, parse_dates=True)
 
In [95]: df
Out[95]: 
            A  B  C
date               
2009-01-01  a  1  2
2009-01-02  b  3  4
2009-01-03  c  4  5
 
# These are Python datetime objects
In [96]: df.index
Out[96]: DatetimeIndex(['2009-01-01', '2009-01-02', '2009-01-03'], dtype='datetime64[ns]', name='date', freq=None)

It is often the case that we may want to store date and time data separately,or store various date fields separately. the parse_dates keyword can 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:

In [97]: print(open('tmp.csv').read())
KORD,19990127, 19:00:00, 18:56:00, 0.8100
KORD,19990127, 20:00:00, 19:56:00, 0.0100
KORD,19990127, 21:00:00, 20:56:00, -0.5900
KORD,19990127, 21:00:00, 21:18:00, -0.9900
KORD,19990127, 22:00:00, 21:56:00, -0.5900
KORD,19990127, 23:00:00, 22:56:00, -0.5900
 
In [98]: df = pd.read_csv('tmp.csv', header=None, parse_dates=[[1, 2], [1, 3]])
 
In [99]: df
Out[99]: 
                  1_2                 1_3     0     4
0 1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  0.81
1 1999-01-27 20:00:00 1999-01-27 19:56:00  KORD  0.01
2 1999-01-27 21:00:00 1999-01-27 20:56:00  KORD -0.59
3 1999-01-27 21:00:00 1999-01-27 21:18:00  KORD -0.99
4 1999-01-27 22:00:00 1999-01-27 21:56:00  KORD -0.59
5 1999-01-27 23:00:00 1999-01-27 22:56:00  KORD -0.59

By default the parser removes the component date columns, but you can chooseto retain them via the keep_date_col keyword:

In [100]: df = pd.read_csv('tmp.csv', header=None, parse_dates=[[1, 2], [1, 3]],
   .....:                  keep_date_col=True)
   .....: 
 
In [101]: df
Out[101]: 
                  1_2                 1_3     0         1          2          3     4
0 1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  19990127   19:00:00   18:56:00  0.81
1 1999-01-27 20:00:00 1999-01-27 19:56:00  KORD  19990127   20:00:00   19:56:00  0.01
2 1999-01-27 21:00:00 1999-01-27 20:56:00  KORD  19990127   21:00:00   20:56:00 -0.59
3 1999-01-27 21:00:00 1999-01-27 21:18:00  KORD  19990127   21:00:00   21:18:00 -0.99
4 1999-01-27 22:00:00 1999-01-27 21:56:00  KORD  19990127   22:00:00   21:56:00 -0.59
5 1999-01-27 23:00:00 1999-01-27 22:56:00  KORD  19990127   23:00:00   22:56:00 -0.59

Note that if you wish to combine multiple columns into a single date column, 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:

In [102]: date_spec = {'nominal': [1, 2], 'actual': [1, 3]}
 
In [103]: df = pd.read_csv('tmp.csv', header=None, parse_dates=date_spec)
 
In [104]: df
Out[104]: 
              nominal              actual     0     4
0 1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  0.81
1 1999-01-27 20:00:00 1999-01-27 19:56:00  KORD  0.01
2 1999-01-27 21:00:00 1999-01-27 20:56:00  KORD -0.59
3 1999-01-27 21:00:00 1999-01-27 21:18:00  KORD -0.99
4 1999-01-27 22:00:00 1999-01-27 21:56:00  KORD -0.59
5 1999-01-27 23:00:00 1999-01-27 22:56:00  KORD -0.59

It is important to remember that if multiple text columns are to be parsed 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:

In [105]: date_spec = {'nominal': [1, 2], 'actual': [1, 3]}
 
In [106]: df = pd.read_csv('tmp.csv', header=None, parse_dates=date_spec,
   .....:                  index_col=0)  # index is the nominal column
   .....: 
 
In [107]: df
Out[107]: 
                                 actual     0     4
nominal                                            
1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  0.81
1999-01-27 20:00:00 1999-01-27 19:56:00  KORD  0.01
1999-01-27 21:00:00 1999-01-27 20:56:00  KORD -0.59
1999-01-27 21:00:00 1999-01-27 21:18:00  KORD -0.99
1999-01-27 22:00:00 1999-01-27 21:56:00  KORD -0.59
1999-01-27 23:00:00 1999-01-27 22:56:00  KORD -0.59

Note

If a column or index contains an unparsable date, the entire column 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.

Note

When passing a dict as the parse_dates argument, the order ofthe columns prepended is not guaranteed, because dict objects do not imposean ordering on their keys. On Python 2.7+ you may use collections.OrderedDict_instead of a regular _dict if this matters to you. Because of this, when using adict for ‘parsedates’ in conjunction with the _index_col argument, it’s best tospecify index_col as a column label rather then as an index on the resulting frame.

Date parsing functions

Finally, the parser allows you to specify a custom date_parser function totake full advantage of the flexibility of the date parsing API:

In [108]: df = pd.read_csv('tmp.csv', header=None, parse_dates=date_spec,
   .....:                  date_parser=pd.io.date_converters.parse_date_time)
   .....: 
 
In [109]: df
Out[109]: 
              nominal              actual     0     4
0 1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  0.81
1 1999-01-27 20:00:00 1999-01-27 19:56:00  KORD  0.01
2 1999-01-27 21:00:00 1999-01-27 20:56:00  KORD -0.59
3 1999-01-27 21:00:00 1999-01-27 21:18:00  KORD -0.99
4 1999-01-27 22:00:00 1999-01-27 21:56:00  KORD -0.59
5 1999-01-27 23:00:00 1999-01-27 22:56:00  KORD -0.59

Pandas will try to call the date_parser function in three different ways. Ifan exception is raised, the next one is tried:

• dateparser is first called with one or more arrays as arguments,as defined using _parse_dates (e.g., date_parser(['2013', '2013'], ['1', '2'])).
• If #1 fails, date_parser is called with all the columnsconcatenated row-wise into a single array (e.g., date_parser(['2013 1', '2013 2'])).

• If #2 fails, dateparser is called once for every row with one or morestring arguments from the columns indicated with _parse_dates(e.g., date_parser('2013', '1') for the first row, date_parser('2013', '2')for the second, etc.).Note that performance-wise, you should try these methods of parsing dates in order:

• Try to infer the format using infer_datetime_format=True (see section below).

• If you know the format, use pd.to_datetime():date_parser=lambda x: pd.to_datetime(x, format=…).
• If you have a really non-standard format, use a custom date_parser function.For optimal performance, this should be vectorized, i.e., it should accept arraysas arguments.You can explore the date parsing functionality indate_converters.pyand add your own. We would love to turn this module into a community supportedset of date/time parsers. To get you started, date_converters.py containsfunctions to parse dual date and time columns, year/month/day columns,and year/month/day/hour/minute/second columns. It also contains ageneric_parser function so you can curry it with a function that deals witha single date rather than the entire array.

Parsing a CSV with mixed timezones

Pandas cannot natively represent a column or index with mixed timezones. If your CSVfile contains columns with a mixture of timezones, the default result will bean object-dtype column with strings, even with parse_dates.

In [110]: content = """\
   .....: a
   .....: 2000-01-01T00:00:00+05:00
   .....: 2000-01-01T00:00:00+06:00"""
   .....: 
 
In [111]: df = pd.read_csv(StringIO(content), parse_dates=['a'])
 
In [112]: df['a']
Out[112]: 
0    2000-01-01 00:00:00+05:00
1    2000-01-01 00:00:00+06:00
Name: a, dtype: object

To parse the mixed-timezone values as a datetime column, pass a partially-appliedto_datetime() with utc=True as the date_parser.

In [113]: df = pd.read_csv(StringIO(content), parse_dates=['a'],
   .....:                  date_parser=lambda col: pd.to_datetime(col, utc=True))
   .....: 
 
In [114]: df['a']
Out[114]: 
0   1999-12-31 19:00:00+00:00
1   1999-12-31 18:00:00+00:00
Name: a, dtype: datetime64[ns, UTC]

Inferring datetime format

If you have parse_dates enabled for some or all of your columns, and 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. Withdayfirst=True, it will guess “01/12/2011” to be December 1st. Withdayfirst=False (default) it will guess “01/12/2011” to be January 12th.

# Try to infer the format for the index column
In [115]: df = pd.read_csv('foo.csv', index_col=0, parse_dates=True,
   .....:                  infer_datetime_format=True)
   .....: 
 
In [116]: df
Out[116]: 
            A  B  C
date               
2009-01-01  a  1  2
2009-01-02  b  3  4
2009-01-03  c  4  5

International date formats

While US date formats tend to be MM/DD/YYYY, many international formats useDD/MM/YYYY instead. For convenience, a dayfirst keyword is provided:

In [117]: print(open('tmp.csv').read())
date,value,cat
1/6/2000,5,a
2/6/2000,10,b
3/6/2000,15,c
 
In [118]: pd.read_csv('tmp.csv', parse_dates=[0])
Out[118]: 
        date  value cat
0 2000-01-06      5   a
1 2000-02-06     10   b
2 2000-03-06     15   c
 
In [119]: pd.read_csv('tmp.csv', dayfirst=True, parse_dates=[0])
Out[119]: 
        date  value cat
0 2000-06-01      5   a
1 2000-06-02     10   b
2 2000-06-03     15   c

Specifying method for floating-point conversion

The parameter float_precision can be specified in order to 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:

In [120]: val = '0.3066101993807095471566981359501369297504425048828125'
 
In [121]: data = 'a,b,c\n1,2,{0}'.format(val)
 
In [122]: abs(pd.read_csv(StringIO(data), engine='c',
   .....:                 float_precision=None)['c'][0] - float(val))
   .....: 
Out[122]: 1.1102230246251565e-16
 
In [123]: abs(pd.read_csv(StringIO(data), engine='c',
   .....:                 float_precision='high')['c'][0] - float(val))
   .....: 
Out[123]: 5.551115123125783e-17
 
In [124]: abs(pd.read_csv(StringIO(data), engine='c',
   .....:                 float_precision='round_trip')['c'][0] - float(val))
   .....: 
Out[124]: 0.0

Thousand separators

For large numbers that have been written with a thousands separator, you canset the thousands keyword to a string of length 1 so that integers will be parsedcorrectly:

By default, numbers with a thousands separator will be parsed as strings:

In [125]: print(open('tmp.csv').read())
ID|level|category
Patient1|123,000|x
Patient2|23,000|y
Patient3|1,234,018|z
 
In [126]: df = pd.read_csv('tmp.csv', sep='|')
 
In [127]: df
Out[127]: 
         ID      level category
0  Patient1    123,000        x
1  Patient2     23,000        y
2  Patient3  1,234,018        z
 
In [128]: df.level.dtype
Out[128]: dtype('O')

The thousands keyword allows integers to be parsed correctly:

In [129]: print(open('tmp.csv').read())
ID|level|category
Patient1|123,000|x
Patient2|23,000|y
Patient3|1,234,018|z
 
In [130]: df = pd.read_csv('tmp.csv', sep='|', thousands=',')
 
In [131]: df
Out[131]: 
         ID    level category
0  Patient1   123000        x
1  Patient2    23000        y
2  Patient3  1234018        z
 
In [132]: df.level.dtype
Out[132]: dtype('int64')

NA values

To control which values are parsed as missing values (which are signified byNaN), 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:

pd.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 numerical5, then as a NaN.

pd.read_csv('path_to_file.csv', keep_default_na=False, na_values=[""])

Above, only an empty field will be recognized as NaN.

pd.read_csv('path_to_file.csv', keep_default_na=False, na_values=["NA", "0"])

Above, both NA and 0 as strings are NaN.

pd.read_csv('path_to_file.csv', na_values=["Nope"])

The default values, in addition to the string "Nope" are recognized asNaN.

Infinity

inf like values will be parsed as np.inf (positive infinity), and -inf as -np.inf (negative infinity).These will ignore the case of the value, meaning Inf, will also be parsed as np.inf.

Returning Series

Using the squeeze keyword, the parser will return output with a single columnas a Series:

In [133]: print(open('tmp.csv').read())
level
Patient1,123000
Patient2,23000
Patient3,1234018
 
In [134]: output = pd.read_csv('tmp.csv', squeeze=True)
 
In [135]: output
Out[135]: 
Patient1     123000
Patient2      23000
Patient3    1234018
Name: level, dtype: int64
 
In [136]: type(output)
Out[136]: pandas.core.series.Series

Boolean values

The common values True, False, TRUE, and FALSE are allrecognized as boolean. Occasionally you might want to recognize other valuesas being boolean. To do this, use the true_values and false_valuesoptions as follows:

In [137]: data = ('a,b,c\n'
   .....:         '1,Yes,2\n'
   .....:         '3,No,4')
   .....: 
 
In [138]: print(data)
a,b,c
1,Yes,2
3,No,4
 
In [139]: pd.read_csv(StringIO(data))
Out[139]: 
   a    b  c
0  1  Yes  2
1  3   No  4
 
In [140]: pd.read_csv(StringIO(data), true_values=['Yes'], false_values=['No'])
Out[140]: 
   a      b  c
0  1   True  2
1  3  False  4

Handling “bad” lines

Some files may have malformed lines with too few fields or too many. Lines withtoo few fields will have NA values filled in the trailing fields. Lines withtoo many fields will raise an error by default:

In [141]: data = ('a,b,c\n'
   .....:         '1,2,3\n'
   .....:         '4,5,6,7\n'
   .....:         '8,9,10')
   .....: 
 
In [142]: pd.read_csv(StringIO(data))
---------------------------------------------------------------------------
ParserError                               Traceback (most recent call last)
<ipython-input-142-6388c394e6b8> in <module>
----> 1 pd.read_csv(StringIO(data))
 
/pandas/pandas/io/parsers.py in parser_f(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision)
    683         )
    684 
--> 685         return _read(filepath_or_buffer, kwds)
    686 
    687     parser_f.__name__ = name
 
/pandas/pandas/io/parsers.py in _read(filepath_or_buffer, kwds)
    461 
    462     try:
--> 463         data = parser.read(nrows)
    464     finally:
    465         parser.close()
 
/pandas/pandas/io/parsers.py in read(self, nrows)
   1152     def read(self, nrows=None):
   1153         nrows = _validate_integer("nrows", nrows)
-> 1154         ret = self._engine.read(nrows)
   1155 
   1156         # May alter columns / col_dict
 
/pandas/pandas/io/parsers.py in read(self, nrows)
   2057     def read(self, nrows=None):
   2058         try:
-> 2059             data = self._reader.read(nrows)
   2060         except StopIteration:
   2061             if self._first_chunk:
 
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader.read()
 
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._read_low_memory()
 
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._read_rows()
 
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._tokenize_rows()
 
/pandas/pandas/_libs/parsers.pyx in pandas._libs.parsers.raise_parser_error()
 
ParserError: Error tokenizing data. C error: Expected 3 fields in line 3, saw 4