Basic#

filepath_or_buffervarious

Either a path to a file (astr,pathlib.Path,orpy:py._path.local.LocalPath), URL (including http, ftp, and S3locations), or any object with aread() method (such as an open file orStringIO).

sepstr, defaults to',' forread_csv(),\t forread_table()

Delimiter to use. If sep isNone, 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'.

delimiterstr, defaultNone

Alternative argument name for sep.

delim_whitespaceboolean, default False

Specifies whether or not whitespace (e.g.'' or'\t')will be used as the delimiter. Equivalent to settingsep='\s+'.If this option is set toTrue, nothing should be passed in for thedelimiter parameter.

Column and index locations and names#

headerint 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 toheader=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 passheader=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 ifskip_blank_lines=True, so header=0 denotes the firstline of data rather than the first line of the file.

namesarray-like, defaultNone

List of column names to use. If file contains no header row, then you shouldexplicitly passheader=None. Duplicates in this list are not allowed.

index_colint, str, sequence of int / str, or False, optional, defaultNone

Column(s) to use as the row labels of theDataFrame, 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 tonot use the firstcolumn as the index, e.g. when you have a malformed file with delimiters atthe end of each line.

The default value ofNone 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.

The first row after the header is used to determine the number of columns,which will go into the index. If the subsequent rows contain less columnsthan the first row, they are filled withNaN.

This can be avoided throughusecols. This ensures that the columns aretaken as is and the trailing data are ignored.

usecolslist-like or callable, defaultNone

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 innames orinferred from the document header row(s). Ifnames are given, the documentheader row(s) are not taken into account. For example, a valid list-likeusecols parameter would be[0,1,2] or['foo','bar','baz'].

Element order is ignored, sousecols=[0,1] is the same as[1,0]. Toinstantiate a DataFrame fromdata 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]:importpandasaspdIn [2]:fromioimportStringIOIn [3]:data="col1,col2,col3\na,b,1\na,b,2\nc,d,3"In [4]:pd.read_csv(StringIO(data))Out[4]:  col1 col2  col30    a    b     11    a    b     22    c    d     3In [5]:pd.read_csv(StringIO(data),usecols=lambdax:x.upper()in["COL1","COL3"])Out[5]:  col1  col30    a     11    a     22    c     3

Using this parameter results in much faster parsing time and lower memory usagewhen using the c engine. The Python engine loads the data first before decidingwhich columns to drop.

General parsing configuration#

dtypeType name or dict of column -> type, defaultNone

Data type for data or columns. E.g.{'a':np.float64,'b':np.int32,'c':'Int64'}Usestr orobject together with suitablena_values settings to preserveand not interpret dtype. If converters are specified, they will be applied INSTEADof dtype conversion.

Added in version 1.5.0:Support for defaultdict was added. Specify a defaultdict as input wherethe default determines the dtype of the columns which are not explicitlylisted.

dtype_backend{“numpy_nullable”, “pyarrow”}, defaults to NumPy backed DataFrames

Which dtype_backend to use, e.g. whether a DataFrame should have NumPyarrays, nullable dtypes are used for all dtypes that have a nullableimplementation when “numpy_nullable” is set, pyarrow is used for alldtypes if “pyarrow” is set.

The dtype_backends are still experimential.

Added in version 2.0.

engine{'c','python','pyarrow'}

Parser engine to use. The C and pyarrow engines are faster, while the python engineis currently more feature-complete. Multithreading is currently only supported bythe pyarrow engine.

Added in version 1.4.0:The “pyarrow” engine was added as anexperimental engine, and some featuresare unsupported, or may not work correctly, with this engine.

convertersdict, defaultNone

Dict of functions for converting values in certain columns. Keys can either beintegers or column labels.

true_valueslist, defaultNone

Values to consider asTrue.

false_valueslist, defaultNone

Values to consider asFalse.

skipinitialspaceboolean, defaultFalse

Skip spaces after delimiter.

skiprowslist-like or integer, defaultNone

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 [6]:data="col1,col2,col3\na,b,1\na,b,2\nc,d,3"In [7]:pd.read_csv(StringIO(data))Out[7]:  col1 col2  col30    a    b     11    a    b     22    c    d     3In [8]:pd.read_csv(StringIO(data),skiprows=lambdax:x%2!=0)Out[8]:  col1 col2  col30    a    b     2

skipfooterint, default0

Number of lines at bottom of file to skip (unsupported with engine=’c’).

nrowsint, defaultNone

Number of rows of file to read. Useful for reading pieces of large files.

low_memoryboolean, defaultTrue

Internally process the file in chunks, resulting in lower memory usewhile parsing, but possibly mixed type inference. To ensure no mixedtypes either setFalse, or specify the type with thedtype parameter.Note that the entire file is read into a singleDataFrame regardless,use thechunksize oriterator parameter to return the data in chunks.(Only valid with C parser)

memory_mapboolean, default False

If a filepath is provided forfilepath_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_valuesscalar, str, list-like, or dict, defaultNone

Additional strings to recognize as NA/NaN. If dict passed, specific per-columnNA values. Seena values const belowfor a list of the values interpreted as NaN by default.

keep_default_naboolean, defaultTrue

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

• Ifkeep_default_na isTrue, andna_values are specified,na_valuesis appended to the default NaN values used for parsing.

• Ifkeep_default_na isTrue, andna_values are not specified, onlythe default NaN values are used for parsing.

• Ifkeep_default_na isFalse, andna_values are specified, onlythe NaN values specifiedna_values are used for parsing.

• Ifkeep_default_na isFalse, andna_values are not specified, nostrings will be parsed as NaN.

Note that ifna_filter is passed in asFalse, thekeep_default_na andna_values parameters will be ignored.

na_filterboolean, defaultTrue

Detect missing value markers (empty strings and the value of na_values). Indata without any NAs, passingna_filter=False can improve the performanceof reading a large file.

verboseboolean, defaultFalse

Indicate number of NA values placed in non-numeric columns.

skip_blank_linesboolean, defaultTrue

IfTrue, skip over blank lines rather than interpreting as NaN values.

Datetime handling#

parse_datesboolean or list of ints or names or list of lists or dict, defaultFalse.

• IfTrue -> 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’.

Note

A fast-path exists for iso8601-formatted dates.

infer_datetime_formatboolean, defaultFalse

IfTrue and parse_dates is enabled for a column, attempt to infer thedatetime format to speed up the processing.

Deprecated since version 2.0.0:A strict version of this argument is now the default, passing it has no effect.

keep_date_colboolean, defaultFalse

IfTrue and parse_dates specifies combining multiple columns then keep theoriginal columns.

date_parserfunction, defaultNone

Function to use for converting a sequence of string columns to an array ofdatetime instances. The default usesdateutil.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.

Deprecated since version 2.0.0:Usedate_format instead, or read in asobject and then applyto_datetime() as-needed.

date_formatstr or dict of column -> format, defaultNone

If used in conjunction withparse_dates, will parse dates according to thisformat. For anything more complex,please read in asobject and then applyto_datetime() as-needed.

Added in version 2.0.0.

dayfirstboolean, defaultFalse

DD/MM format dates, international and European format.

cache_datesboolean, 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.

Iteration#

iteratorboolean, defaultFalse

ReturnTextFileReader object for iteration or getting chunks withget_chunk().

chunksizeint, defaultNone

ReturnTextFileReader object for iteration. Seeiterating and chunking below.

Quoting, compression, and file format#

compression{'infer','gzip','bz2','zip','xz','zstd',None,dict}, default'infer'

For on-the-fly decompression of on-disk data. If ‘infer’, then use gzip,bz2, zip, xz, or zstandard iffilepath_or_buffer is path-like ending in ‘.gz’, ‘.bz2’,‘.zip’, ‘.xz’, ‘.zst’, respectively, and no decompression otherwise. If using ‘zip’,the ZIP file must contain only one data file to be read in.Set toNone for no decompression. Can also be a dict with key'method'set to one of {'zip','gzip','bz2','zstd'} and other key-value pairs areforwarded tozipfile.ZipFile,gzip.GzipFile,bz2.BZ2File, orzstandard.ZstdDecompressor.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.2.0:Previous versions forwarded dict entries for ‘gzip’ togzip.open.

thousandsstr, defaultNone

Thousands separator.

decimalstr, default'.'

Character to recognize as decimal point. E.g. use',' for European data.

float_precisionstring, default None

Specifies which converter the C engine should use for floating-point values.The options areNone for the ordinary converter,high for thehigh-precision converter, andround_trip for the round-trip converter.

lineterminatorstr (length 1), defaultNone

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 orcsv.QUOTE_* instance, default0

Control field quoting behavior percsv.QUOTE_* constants. Use one ofQUOTE_MINIMAL (0),QUOTE_ALL (1),QUOTE_NONNUMERIC (2) orQUOTE_NONE (3).

doublequoteboolean, defaultTrue

Whenquotechar is specified andquoting is notQUOTE_NONE,indicate whether or not to interpret two consecutivequotechar elementsinside a field as a singlequotechar element.

escapecharstr (length 1), defaultNone

One-character string used to escape delimiter when quoting isQUOTE_NONE.

commentstr, defaultNone

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 asskip_blank_lines=True), fullycommented lines are ignored by the parameterheader but not byskiprows.For example, ifcomment='#', parsing ‘#empty\na,b,c\n1,2,3’ withheader=0 will result in ‘a,b,c’ being treated as the header.

encodingstr, defaultNone

Encoding to use for UTF when reading/writing (e.g.'utf-8').List ofPython standard encodings.

dialectstr orcsv.Dialect instance, defaultNone

If provided, this parameter will override values (default or not) for thefollowing parameters:delimiter,doublequote,escapechar,skipinitialspace,quotechar, andquoting. If it is necessary tooverride values, a ParserWarning will be issued. Seecsv.Dialectdocumentation for more details.

Error handling#

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.

Added in version 1.3.0.

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 [51]:data="a,b,c\n1,2,3\n4,5,6\n7,8,9"In [52]:print(data)a,b,c1,2,34,5,67,8,9In [53]:pd.read_csv(StringIO(data))Out[53]:   a  b  c0  1  2  31  4  5  62  7  8  9

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

In [54]:print(data)a,b,c1,2,34,5,67,8,9In [55]:pd.read_csv(StringIO(data),names=["foo","bar","baz"],header=0)Out[55]:   foo  bar  baz0    1    2    31    4    5    62    7    8    9In [56]:pd.read_csv(StringIO(data),names=["foo","bar","baz"],header=None)Out[56]:  foo bar baz0   a   b   c1   1   2   32   4   5   63   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 [57]:data="skip this skip it\na,b,c\n1,2,3\n4,5,6\n7,8,9"In [58]:pd.read_csv(StringIO(data),header=1)Out[58]:   a  b  c0  1  2  31  4  5  62  7  8  9

Filtering columns (usecols)#

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

In [61]:data="a,b,c,d\n1,2,3,foo\n4,5,6,bar\n7,8,9,baz"In [62]:pd.read_csv(StringIO(data))Out[62]:   a  b  c    d0  1  2  3  foo1  4  5  6  bar2  7  8  9  bazIn [63]:pd.read_csv(StringIO(data),usecols=["b","d"])Out[63]:   b    d0  2  foo1  5  bar2  8  bazIn [64]:pd.read_csv(StringIO(data),usecols=[0,2,3])Out[64]:   a  c    d0  1  3  foo1  4  6  bar2  7  9  bazIn [65]:pd.read_csv(StringIO(data),usecols=lambdax:x.upper()in["A","C"])Out[65]:   a  c0  1  31  4  62  7  9

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

In [66]:pd.read_csv(StringIO(data),usecols=lambdax:xnotin["a","c"])Out[66]:   b    d0  2  foo1  5  bar2  8  baz

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

Ignoring line comments and empty lines#

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

In [67]:data="\na,b,c\n\n# commented line\n1,2,3\n\n4,5,6"In [68]:print(data)a,b,c# commented line1,2,34,5,6In [69]:pd.read_csv(StringIO(data),comment="#")Out[69]:   a  b  c0  1  2  31  4  5  6

Ifskip_blank_lines=False, thenread_csv will not ignore blank lines:

In [70]:data="a,b,c\n\n1,2,3\n\n\n4,5,6"In [71]:pd.read_csv(StringIO(data),skip_blank_lines=False)Out[71]:     a    b    c0  NaN  NaN  NaN1  1.0  2.0  3.02  NaN  NaN  NaN3  NaN  NaN  NaN4  4.0  5.0  6.0

In [72]:data="#comment\na,b,c\nA,B,C\n1,2,3"In [73]:pd.read_csv(StringIO(data),comment="#",header=1)Out[73]:   A  B  C0  1  2  3In [74]:data="A,B,C\n#comment\na,b,c\n1,2,3"In [75]:pd.read_csv(StringIO(data),comment="#",skiprows=2)Out[75]:   a  b  c0  1  2  3

In [76]: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 [77]:print(data)# empty# second empty line# third emptylineX,Y,Z1,2,3A,B,C1,2.,4.5.,NaN,10.0In [78]:pd.read_csv(StringIO(data),comment="#",skiprows=4,header=1)Out[78]:     A    B     C0  1.0  2.0   4.01  5.0  NaN  10.0

Comments#

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

In [79]:data=(   ....:"ID,level,category\n"   ....:"Patient1,123000,x # really unpleasant\n"   ....:"Patient2,23000,y # wouldn't take his medicine\n"   ....:"Patient3,1234018,z # awesome"   ....:)   ....:In [80]:withopen("tmp.csv","w")asfh:   ....:fh.write(data)   ....:In [81]:print(open("tmp.csv").read())ID,level,categoryPatient1,123000,x # really unpleasantPatient2,23000,y # wouldn't take his medicinePatient3,1234018,z # awesome

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

In [82]:df=pd.read_csv("tmp.csv")In [83]:dfOut[83]:         ID    level                        category0  Patient1   123000           x # really unpleasant1  Patient2    23000  y # wouldn't take his medicine2  Patient3  1234018                     z # awesome

We can suppress the comments using thecomment keyword:

In [84]:df=pd.read_csv("tmp.csv",comment="#")In [85]:dfOut[85]:         ID    level category0  Patient1   123000       x1  Patient2    23000       y2  Patient3  1234018       z

Specifying date columns#

To better facilitate working with datetime data,read_csv()uses the keyword argumentsparse_dates anddate_formatto allow users to specify a variety of columns and date/time formats to turn theinput text data intodatetime objects.

The simplest case is to just pass inparse_dates=True:

In [104]:withopen("foo.csv",mode="w")asf:   .....:f.write("date,A,B,C\n20090101,a,1,2\n20090102,b,3,4\n20090103,c,4,5")   .....:# Use a column as an index, and parse it as dates.In [105]:df=pd.read_csv("foo.csv",index_col=0,parse_dates=True)In [106]:dfOut[106]:            A  B  Cdate2009-01-01  a  1  22009-01-02  b  3  42009-01-03  c  4  5# These are Python datetime objectsIn [107]:df.indexOut[107]: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. theparse_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 toparse_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 [108]: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"   .....:)   .....:In [109]:withopen("tmp.csv","w")asfh:   .....:fh.write(data)   .....:In [110]:df=pd.read_csv("tmp.csv",header=None,parse_dates=[[1,2],[1,3]])In [111]:dfOut[111]:                  1_2                 1_3     0     40 1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  0.811 1999-01-27 20:00:00 1999-01-27 19:56:00  KORD  0.012 1999-01-27 21:00:00 1999-01-27 20:56:00  KORD -0.593 1999-01-27 21:00:00 1999-01-27 21:18:00  KORD -0.994 1999-01-27 22:00:00 1999-01-27 21:56:00  KORD -0.595 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 thekeep_date_col keyword:

In [112]:df=pd.read_csv(   .....:"tmp.csv",header=None,parse_dates=[[1,2],[1,3]],keep_date_col=True   .....:)   .....:In [113]:dfOut[113]:                  1_2                 1_3     0  ...          2          3     40 1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  ...   19:00:00   18:56:00  0.811 1999-01-27 20:00:00 1999-01-27 19:56:00  KORD  ...   20:00:00   19:56:00  0.012 1999-01-27 21:00:00 1999-01-27 20:56:00  KORD  ...   21:00:00   20:56:00 -0.593 1999-01-27 21:00:00 1999-01-27 21:18:00  KORD  ...   21:00:00   21:18:00 -0.994 1999-01-27 22:00:00 1999-01-27 21:56:00  KORD  ...   22:00:00   21:56:00 -0.595 1999-01-27 23:00:00 1999-01-27 22:56:00  KORD  ...   23:00:00   22:56:00 -0.59[6 rows x 7 columns]

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 columnswhileparse_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 [114]:date_spec={"nominal":[1,2],"actual":[1,3]}In [115]:df=pd.read_csv("tmp.csv",header=None,parse_dates=date_spec)In [116]:dfOut[116]:              nominal              actual     0     40 1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  0.811 1999-01-27 20:00:00 1999-01-27 19:56:00  KORD  0.012 1999-01-27 21:00:00 1999-01-27 20:56:00  KORD -0.593 1999-01-27 21:00:00 1999-01-27 21:18:00  KORD -0.994 1999-01-27 22:00:00 1999-01-27 21:56:00  KORD -0.595 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. Theindex_colspecification is based off of this new set of columns rather than the originaldata columns:

In [117]:date_spec={"nominal":[1,2],"actual":[1,3]}In [118]:df=pd.read_csv(   .....:"tmp.csv",header=None,parse_dates=date_spec,index_col=0   .....:)# index is the nominal column   .....:In [119]:dfOut[119]:                                 actual     0     4nominal1999-01-27 19:00:00 1999-01-27 18:56:00  KORD  0.811999-01-27 20:00:00 1999-01-27 19:56:00  KORD  0.011999-01-27 21:00:00 1999-01-27 20:56:00  KORD -0.591999-01-27 21:00:00 1999-01-27 21:18:00  KORD -0.991999-01-27 22:00:00 1999-01-27 21:56:00  KORD -0.591999-01-27 23:00:00 1999-01-27 22:56:00  KORD -0.59

Date parsing functions#

Finally, the parser allows you to specify a customdate_format.Performance-wise, you should try these methods of parsing dates in order:

1. If you know the format, usedate_format, e.g.:date_format="%d/%m/%Y" ordate_format={column_name:"%d/%m/%Y"}.

2. If you different formats for different columns, or want to pass any extra options (suchasutc) toto_datetime, then you should read in your data asobject dtype, andthen useto_datetime.

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 withparse_dates.To parse the mixed-timezone values as a datetime column, read in asobject dtype andthen callto_datetime() withutc=True.

In [120]:content="""\   .....:a   .....:2000-01-01T00:00:00+05:00   .....:2000-01-01T00:00:00+06:00"""   .....:In [121]:df=pd.read_csv(StringIO(content))In [122]:df["a"]=pd.to_datetime(df["a"],utc=True)In [123]:df["a"]Out[123]:0   1999-12-31 19:00:00+00:001   1999-12-31 18:00:00+00:00Name: a, dtype: datetime64[ns, UTC]

Inferring datetime format#

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 format inference is sensitive todayfirst. 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.

If you try to parse a column of date strings, pandas will attempt to guess the formatfrom the first non-NaN element, and will then parse the rest of the column with thatformat. If pandas fails to guess the format (for example if your first string is'01DecemberUS/Pacific2000'), then a warning will be raised and eachrow will be parsed individually bydateutil.parser.parse. The safestway to parse dates is to explicitly setformat=.

In [124]:df=pd.read_csv(   .....:"foo.csv",   .....:index_col=0,   .....:parse_dates=True,   .....:)   .....:In [125]:dfOut[125]:            A  B  Cdate2009-01-01  a  1  22009-01-02  b  3  42009-01-03  c  4  5

In the case that you have mixed datetime formats within the same column, you canpassformat='mixed'

In [126]:data=StringIO("date\n12 Jan 2000\n2000-01-13\n")In [127]:df=pd.read_csv(data)In [128]:df['date']=pd.to_datetime(df['date'],format='mixed')In [129]:dfOut[129]:        date0 2000-01-121 2000-01-13

or, if your datetime formats are all ISO8601 (possibly not identically-formatted):

In [130]:data=StringIO("date\n2020-01-01\n2020-01-01 03:00\n")In [131]:df=pd.read_csv(data)In [132]:df['date']=pd.to_datetime(df['date'],format='ISO8601')In [133]:dfOut[133]:                 date0 2020-01-01 00:00:001 2020-01-01 03:00:00

International date formats#

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

In [134]:data="date,value,cat\n1/6/2000,5,a\n2/6/2000,10,b\n3/6/2000,15,c"In [135]:print(data)date,value,cat1/6/2000,5,a2/6/2000,10,b3/6/2000,15,cIn [136]:withopen("tmp.csv","w")asfh:   .....:fh.write(data)   .....:In [137]:pd.read_csv("tmp.csv",parse_dates=[0])Out[137]:        date  value cat0 2000-01-06      5   a1 2000-02-06     10   b2 2000-03-06     15   cIn [138]:pd.read_csv("tmp.csv",dayfirst=True,parse_dates=[0])Out[138]:        date  value cat0 2000-06-01      5   a1 2000-06-02     10   b2 2000-06-03     15   c

Writing CSVs to binary file objects#

df.to_csv(...,mode="wb") allows writing a CSV to a file objectopened binary mode. In most cases, it is not necessary to specifymode as Pandas will auto-detect whether the file object isopened in text or binary mode.

In [139]:importioIn [140]:data=pd.DataFrame([0,1,2])In [141]:buffer=io.BytesIO()In [142]:data.to_csv(buffer,encoding="utf-8",compression="gzip")

Files with an “implicit” index column#

Consider a file with one less entry in the header than the number of datacolumn:

In [199]:data="A,B,C\n20090101,a,1,2\n20090102,b,3,4\n20090103,c,4,5"In [200]:print(data)A,B,C20090101,a,1,220090102,b,3,420090103,c,4,5In [201]:withopen("foo.csv","w")asf:   .....:f.write(data)   .....:

In this special case,read_csv assumes that the first column is to be usedas the index of theDataFrame:

In [202]:pd.read_csv("foo.csv")Out[202]:          A  B  C20090101  a  1  220090102  b  3  420090103  c  4  5

Note that the dates weren’t automatically parsed. In that case you would needto do as before:

In [203]:df=pd.read_csv("foo.csv",parse_dates=True)In [204]:df.indexOut[204]:DatetimeIndex(['2009-01-01', '2009-01-02', '2009-01-03'], dtype='datetime64[ns]', freq=None)

Reading an index with aMultiIndex#

Suppose you have data indexed by two columns:

In [205]:data='year,indiv,zit,xit\n1977,"A",1.2,.6\n1977,"B",1.5,.5'In [206]:print(data)year,indiv,zit,xit1977,"A",1.2,.61977,"B",1.5,.5In [207]:withopen("mindex_ex.csv",mode="w")asf:   .....:f.write(data)   .....:

Theindex_col argument toread_csv can take a list ofcolumn numbers to turn multiple columns into aMultiIndex for the index of thereturned object:

In [208]:df=pd.read_csv("mindex_ex.csv",index_col=[0,1])In [209]:dfOut[209]:            zit  xityear indiv1977 A      1.2  0.6     B      1.5  0.5In [210]:df.loc[1977]Out[210]:       zit  xitindivA      1.2  0.6B      1.5  0.5

Reading columns with aMultiIndex#

By specifying list of row locations for theheader argument, youcan read in aMultiIndex for the columns. Specifying non-consecutiverows will skip the intervening rows.

In [211]:mi_idx=pd.MultiIndex.from_arrays([[1,2,3,4],list("abcd")],names=list("ab"))In [212]:mi_col=pd.MultiIndex.from_arrays([[1,2],list("ab")],names=list("cd"))In [213]:df=pd.DataFrame(np.ones((4,2)),index=mi_idx,columns=mi_col)In [214]:df.to_csv("mi.csv")In [215]:print(open("mi.csv").read())c,,1,2d,,a,ba,b,,1,a,1.0,1.02,b,1.0,1.03,c,1.0,1.04,d,1.0,1.0In [216]:pd.read_csv("mi.csv",header=[0,1,2,3],index_col=[0,1])Out[216]:c                    1                  2d                    a                  ba   Unnamed: 2_level_2 Unnamed: 3_level_21                  1.0                1.02 b                1.0                1.03 c                1.0                1.04 d                1.0                1.0

read_csv is also able to interpret a more common formatof multi-columns indices.

In [217]: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"In [218]:print(data),a,a,a,b,c,c,q,r,s,t,u,vone,1,2,3,4,5,6two,7,8,9,10,11,12In [219]:withopen("mi2.csv","w")asfh:   .....:fh.write(data)   .....:In [220]:pd.read_csv("mi2.csv",header=[0,1],index_col=0)Out[220]:     a         b   c     q  r  s   t   u   vone  1  2  3   4   5   6two  7  8  9  10  11  12

Writing to CSV format#

TheSeries andDataFrame objects have an instance methodto_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 withnewline=''

• 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), andheader andindex are True, then the index names areused. (A sequence should be given if theDataFrame uses MultiIndex).

• mode : Python write mode, default ‘w’

• encoding: a string representing the encoding to use if the contents arenon-ASCII, for Python versions prior to 3

• lineterminator: Character sequence denoting line end (defaultos.linesep)

• quoting: Set quoting rules as in csv module (default csv.QUOTE_MINIMAL). Note that if you have set afloat_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 ofquotechar in fields (default True)

• escapechar: Character used to escapesep andquotechar whenappropriate (default None)

• chunksize: Number of rows to write at a time

• date_format: Format string for datetime objects

Writing a formatted string#

TheDataFrame object has an instance methodto_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 defaultNaN, representation of NA value

• formatters default None, a dictionary (by column) of functions each ofwhich 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 theDataFrame.

• sparsify default True, set to False for aDataFrame with a hierarchicalindex 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 defaultleft, will print column headers left- orright-justified

TheSeries object also has ato_string method, but with only thebuf,na_rep,float_format arguments. There is also alength argumentwhich, if set toTrue, will additionally output the length of the Series.

Orient options#

There are a number of different options for the format of the resulting JSONfile / string. Consider the followingDataFrame andSeries:

In [233]:dfjo=pd.DataFrame(   .....:dict(A=range(1,4),B=range(4,7),C=range(7,10)),   .....:columns=list("ABC"),   .....:index=list("xyz"),   .....:)   .....:In [234]:dfjoOut[234]:   A  B  Cx  1  4  7y  2  5  8z  3  6  9In [235]:sjo=pd.Series(dict(x=15,y=16,z=17),name="D")In [236]:sjoOut[236]:x    15y    16z    17Name: D, dtype: int64

Column oriented (the default forDataFrame) serializes the data asnested JSON objects with column labels acting as the primary index:

In [237]:dfjo.to_json(orient="columns")Out[237]:'{"A":{"x":1,"y":2,"z":3},"B":{"x":4,"y":5,"z":6},"C":{"x":7,"y":8,"z":9}}'# Not available for Series

Index oriented (the default forSeries) similar to column orientedbut the index labels are now primary:

In [238]:dfjo.to_json(orient="index")Out[238]:'{"x":{"A":1,"B":4,"C":7},"y":{"A":2,"B":5,"C":8},"z":{"A":3,"B":6,"C":9}}'In [239]:sjo.to_json(orient="index")Out[239]:'{"x":15,"y":16,"z":17}'

Record oriented serializes the data to a JSON array of column -> value records,index labels are not included. This is useful for passingDataFrame data to plottinglibraries, for example the JavaScript libraryd3.js:

In [240]:dfjo.to_json(orient="records")Out[240]:'[{"A":1,"B":4,"C":7},{"A":2,"B":5,"C":8},{"A":3,"B":6,"C":9}]'In [241]:sjo.to_json(orient="records")Out[241]:'[15,16,17]'

Value oriented is a bare-bones option which serializes to nested JSON arrays ofvalues only, column and index labels are not included:

In [242]:dfjo.to_json(orient="values")Out[242]:'[[1,4,7],[2,5,8],[3,6,9]]'# Not available for Series

Split oriented serializes to a JSON object containing separate entries forvalues, index and columns. Name is also included forSeries:

In [243]:dfjo.to_json(orient="split")Out[243]:'{"columns":["A","B","C"],"index":["x","y","z"],"data":[[1,4,7],[2,5,8],[3,6,9]]}'In [244]:sjo.to_json(orient="split")Out[244]:'{"name":"D","index":["x","y","z"],"data":[15,16,17]}'

Table oriented serializes to the JSONTable Schema, allowing for thepreservation of metadata including but not limited to dtypes and index names.

Date handling#

Writing in ISO date format:

In [245]:dfd=pd.DataFrame(np.random.randn(5,2),columns=list("AB"))In [246]:dfd["date"]=pd.Timestamp("20130101")In [247]:dfd=dfd.sort_index(axis=1,ascending=False)In [248]:json=dfd.to_json(date_format="iso")In [249]:jsonOut[249]:'{"date":{"0":"2013-01-01T00:00:00.000","1":"2013-01-01T00:00:00.000","2":"2013-01-01T00:00:00.000","3":"2013-01-01T00:00:00.000","4":"2013-01-01T00:00:00.000"},"B":{"0":0.403309524,"1":0.3016244523,"2":-1.3698493577,"3":1.4626960492,"4":-0.8265909164},"A":{"0":0.1764443426,"1":-0.1549507744,"2":-2.1798606054,"3":-0.9542078401,"4":-1.7431609117}}'

Writing in ISO date format, with microseconds:

In [250]:json=dfd.to_json(date_format="iso",date_unit="us")In [251]:jsonOut[251]:'{"date":{"0":"2013-01-01T00:00:00.000000","1":"2013-01-01T00:00:00.000000","2":"2013-01-01T00:00:00.000000","3":"2013-01-01T00:00:00.000000","4":"2013-01-01T00:00:00.000000"},"B":{"0":0.403309524,"1":0.3016244523,"2":-1.3698493577,"3":1.4626960492,"4":-0.8265909164},"A":{"0":0.1764443426,"1":-0.1549507744,"2":-2.1798606054,"3":-0.9542078401,"4":-1.7431609117}}'

Epoch timestamps, in seconds:

In [252]:json=dfd.to_json(date_format="epoch",date_unit="s")In [253]:jsonOut[253]:'{"date":{"0":1,"1":1,"2":1,"3":1,"4":1},"B":{"0":0.403309524,"1":0.3016244523,"2":-1.3698493577,"3":1.4626960492,"4":-0.8265909164},"A":{"0":0.1764443426,"1":-0.1549507744,"2":-2.1798606054,"3":-0.9542078401,"4":-1.7431609117}}'

Writing to a file, with a date index and a date column:

In [254]:dfj2=dfj.copy()In [255]:dfj2["date"]=pd.Timestamp("20130101")In [256]:dfj2["ints"]=list(range(5))In [257]:dfj2["bools"]=TrueIn [258]:dfj2.index=pd.date_range("20130101",periods=5)In [259]:dfj2.to_json("test.json")In [260]:withopen("test.json")asfh:   .....:print(fh.read())   .....:{"A":{"1356998400000":-0.1213062281,"1357084800000":0.6957746499,"1357171200000":0.9597255933,"1357257600000":-0.6199759194,"1357344000000":-0.7323393705},"B":{"1356998400000":-0.0978826728,"1357084800000":0.3417343559,"1357171200000":-1.1103361029,"1357257600000":0.1497483186,"1357344000000":0.6877383895},"date":{"1356998400000":1356,"1357084800000":1356,"1357171200000":1356,"1357257600000":1356,"1357344000000":1356},"ints":{"1356998400000":0,"1357084800000":1,"1357171200000":2,"1357257600000":3,"1357344000000":4},"bools":{"1356998400000":true,"1357084800000":true,"1357171200000":true,"1357257600000":true,"1357344000000":true}}

Fallback behavior#

If 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 thedefault_handler, if provided, will be calledfor each value, otherwise an exception is raised.

• if an object is unsupported it will attempt the following:

  • check if the object has defined atoDict method and call it.AtoDict method should return adict which will then be JSON serialized.

  • invoke thedefault_handler if one was provided.

  • convert the object to adict by traversing its contents. However this will often failwith anOverflowError or give unexpected results.

In general the best approach for unsupported objects or dtypes is to provide adefault_handler.For example:

>>>DataFrame([1.0,2.0,complex(1.0,2.0)]).to_json()# raisesRuntimeError: Unhandled numpy dtype 15

can be dealt with by specifying a simpledefault_handler:

In [261]:pd.DataFrame([1.0,2.0,complex(1.0,2.0)]).to_json(default_handler=str)Out[261]:'{"0":{"0":"(1+0j)","1":"(2+0j)","2":"(1+2j)"}}'

Data conversion#

The default ofconvert_axes=True,dtype=True, andconvert_dates=Truewill 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 todtype.convert_axes should only be set toFalse if you need topreserve string-like numbers (e.g. ‘1’, ‘2’) in an axes.

Reading from a JSON string:

In [262]:fromioimportStringIOIn [263]:pd.read_json(StringIO(json))Out[263]:   date         B         A0     1  0.403310  0.1764441     1  0.301624 -0.1549512     1 -1.369849 -2.1798613     1  1.462696 -0.9542084     1 -0.826591 -1.743161

Reading from a file:

In [264]:pd.read_json("test.json")Out[264]:                   A         B  date  ints  bools2013-01-01 -0.121306 -0.097883  1356     0   True2013-01-02  0.695775  0.341734  1356     1   True2013-01-03  0.959726 -1.110336  1356     2   True2013-01-04 -0.619976  0.149748  1356     3   True2013-01-05 -0.732339  0.687738  1356     4   True

Don’t convert any data (but still convert axes and dates):

In [265]:pd.read_json("test.json",dtype=object).dtypesOut[265]:A        objectB        objectdate     objectints     objectbools    objectdtype: object

Specify dtypes for conversion:

In [266]:pd.read_json("test.json",dtype={"A":"float32","bools":"int8"}).dtypesOut[266]:A        float32B        float64date       int64ints       int64bools       int8dtype: object

Preserve string indices:

In [267]:fromioimportStringIOIn [268]:si=pd.DataFrame(   .....:np.zeros((4,4)),columns=list(range(4)),index=[str(i)foriinrange(4)]   .....:)   .....:In [269]:siOut[269]:     0    1    2    30  0.0  0.0  0.0  0.01  0.0  0.0  0.0  0.02  0.0  0.0  0.0  0.03  0.0  0.0  0.0  0.0In [270]:si.indexOut[270]:Index(['0', '1', '2', '3'], dtype='object')In [271]:si.columnsOut[271]:Index([0, 1, 2, 3], dtype='int64')In [272]:json=si.to_json()In [273]:sij=pd.read_json(StringIO(json),convert_axes=False)In [274]:sijOut[274]:   0  1  2  30  0  0  0  01  0  0  0  02  0  0  0  03  0  0  0  0In [275]:sij.indexOut[275]:Index(['0', '1', '2', '3'], dtype='object')In [276]:sij.columnsOut[276]:Index(['0', '1', '2', '3'], dtype='object')

Dates written in nanoseconds need to be read back in nanoseconds:

In [277]:fromioimportStringIOIn [278]:json=dfj2.to_json(date_unit="ns")# Try to parse timestamps as milliseconds -> Won't WorkIn [279]:dfju=pd.read_json(StringIO(json),date_unit="ms")In [280]:dfjuOut[280]:                            A         B        date  ints  bools1356998400000000000 -0.121306 -0.097883  1356998400     0   True1357084800000000000  0.695775  0.341734  1356998400     1   True1357171200000000000  0.959726 -1.110336  1356998400     2   True1357257600000000000 -0.619976  0.149748  1356998400     3   True1357344000000000000 -0.732339  0.687738  1356998400     4   True# Let pandas detect the correct precisionIn [281]:dfju=pd.read_json(StringIO(json))In [282]:dfjuOut[282]:                   A         B       date  ints  bools2013-01-01 -0.121306 -0.097883 2013-01-01     0   True2013-01-02  0.695775  0.341734 2013-01-01     1   True2013-01-03  0.959726 -1.110336 2013-01-01     2   True2013-01-04 -0.619976  0.149748 2013-01-01     3   True2013-01-05 -0.732339  0.687738 2013-01-01     4   True# Or specify that all timestamps are in nanosecondsIn [283]:dfju=pd.read_json(StringIO(json),date_unit="ns")In [284]:dfjuOut[284]:                   A         B        date  ints  bools2013-01-01 -0.121306 -0.097883  1356998400     0   True2013-01-02  0.695775  0.341734  1356998400     1   True2013-01-03  0.959726 -1.110336  1356998400     2   True2013-01-04 -0.619976  0.149748  1356998400     3   True2013-01-05 -0.732339  0.687738  1356998400     4   True

By setting thedtype_backend argument you can control the default dtypes used for the resulting DataFrame.

In [285]:data=(   .....:'{"a":{"0":1,"1":3},"b":{"0":2.5,"1":4.5},"c":{"0":true,"1":false},"d":{"0":"a","1":"b"},'   .....:'"e":{"0":null,"1":6.0},"f":{"0":null,"1":7.5},"g":{"0":null,"1":true},"h":{"0":null,"1":"a"},'   .....:'"i":{"0":"12-31-2019","1":"12-31-2019"},"j":{"0":null,"1":null}}'   .....:)   .....:In [286]:df=pd.read_json(StringIO(data),dtype_backend="pyarrow")In [287]:dfOut[287]:   a    b      c  d     e     f     g     h           i     j0  1  2.5   True  a  <NA>  <NA>  <NA>  <NA>  12-31-2019  None1  3  4.5  False  b     6   7.5  True     a  12-31-2019  NoneIn [288]:df.dtypesOut[288]:a     int64[pyarrow]b    double[pyarrow]c      bool[pyarrow]d    string[pyarrow]e     int64[pyarrow]f    double[pyarrow]g      bool[pyarrow]h    string[pyarrow]i    string[pyarrow]j      null[pyarrow]dtype: object