19. Pandas_DataProcessingAndAnalysis_complete

Introduction to Pandas¶

The pandas library is a framework for data processing and analysis in Python. It provides convenient data structures for representing series and tables of data, and makes it easy to transform, split, merge, and convert data.

Features for handling data:

• labeled indexing
• hierarchical indices
• alignment of data for comparison and merging of datasets
• handling of missing data

and much more.

In [1]:

import numpy as np
import pandas as pd

import matplotlib.pyplot as plt
%matplotlib inline

Pandas Data Structures¶

The two main data structures in pandas are the Series (used to represent data series) and DataFrame objects (used to represent tabular data). Both of these objects have an index for accessing elements or rows in the data represented by the object.

Data Structure	Dimensionality	Spreadsheet Analog
Series	1D	Column
DataFrame	2D	Single sheet (tabular data)
Panel	3D	Multiple sheets

Note: There are actually 3 data structues but the third type (Panel), which is rarely used in the real world.

Series¶

We will show the advantage of being able to index a data series with labels rather than integers with the following example. Let's create a Series object that represents the data [909976, 8615246, 2872086, 2273305].

In [2]:

s = pd.Series([909976, 8615246, 2872086, 2273305])

If you type s or print(s), you will see the data of the Series with the corresponding indices. The object is a Series instance with data type int64.

In [3]:

s

Out[3]:

0     909976
1    8615246
2    2872086
3    2273305
dtype: int64

In [4]:

list_s =  [909976, 8615246, 2872086, 2273305]
list_s

Out[4]:

[909976, 8615246, 2872086, 2273305]

Using index and values attributes, we can extract the index and the values stored in the Series:

In [5]:

s.index

Out[5]:

RangeIndex(start=0, stop=4, step=1)

In [6]:

s.values

Out[6]:

array([ 909976, 8615246, 2872086, 2273305], dtype=int64)

Using integers as indices is not descriptive. For example, if the data represents the population of four European capitals, it's convenient and descriptive to use city names as indices.

In [7]:

s.index = ["Stockholm", "London", "Rome", "Paris"]

#we can also set the name attribute to the Series
s.name = "Population"
s

Out[7]:

Stockholm     909976
London       8615246
Rome         2872086
Paris        2273305
Name: Population, dtype: int64

We can also do it all once:

In [8]:

s = pd.Series([909976, 8615246, 2872086, 2273305],
              name="Population", index=["Stockholm", "London", "Rome", "Paris"])
s

Out[8]:

Stockholm     909976
London       8615246
Rome         2872086
Paris        2273305
Name: Population, dtype: int64

We can access elements in a Series by indexing:

In [9]:

s["Stockholm"]

Out[9]:

909976

or through an attribute with the same name as the index:

In [10]:

s.Stockholm

Out[10]:

909976

Indexing a Series object with a list of indices gives a new Series object which is a subset of the original one.

In [11]:

s2 = s[["London", "Paris"]]
s2

Out[11]:

London    8615246
Paris     2273305
Name: Population, dtype: int64

We can easily compute the statistics of a Series object:

In [12]:

 #the number of data points
s.count()

Out[12]:

4

In [13]:

# mean and standard deviation
s.mean(), s.std()

Out[13]:

(3667653.25, 3399048.5005155364)

In [14]:

# min and max
s.min(), s.max()

Out[14]:

(909976, 8615246)

In [15]:

# 25% quantile
s.quantile(q=0.25)

Out[15]:

1932472.75

The describe method gives a summary of a Series object:

In [16]:

s.describe()

Out[16]:

count    4.000000e+00
mean     3.667653e+06
std      3.399049e+06
min      9.099760e+05
25%      1.932473e+06
50%      2.572696e+06
75%      4.307876e+06
max      8.615246e+06
Name: Population, dtype: float64

We can use plot method to visualize the data.

In [17]:

fig, axes = plt.subplots(1, 3, figsize=(12, 3))
s.plot(ax=axes[0], kind='line', title='line')
s.plot(ax=axes[1], kind='bar', title='bar')
s.plot(ax=axes[2], kind='pie', title='pie')

Out[17]:

<AxesSubplot:title={'center':'pie'}, ylabel='Population'>

DataFrame¶

DataFrame object is the pandas data structure for two-dimensional arrays. Columns are really just Series objects.
There are various ways to initialize a DataFrame. For example, we will extend the previous dataset by including a column that specifies which country each city belongs to.

In [18]:

df = pd.DataFrame([[909976, "Sweden"], [8615246, "United Kingdom"],
                    [2872086, "Italy"], [2273305, "France"]],
                    index=["Stockholm", "London", "Rome", "Paris"],
                    columns=["Population", "State"])
df

Out[18]:

	Population	State
Stockholm	909976	Sweden
London	8615246	United Kingdom
Rome	2872086	Italy
Paris	2273305	France

Another way which can be more convenient is to pass a dictionary with column titles as keys and column data as values:

In [19]:

df2 = pd.DataFrame({"Population": [909976, 8615246, 2872086, 2273305],
                     "State": ["Sweden", "United Kingdom", "Italy", "France"]},
                    index=["Stockholm", "London", "Rome", "Paris"])
df2

Out[19]:

	Population	State
Stockholm	909976	Sweden
London	8615246	United Kingdom
Rome	2872086	Italy
Paris	2273305	France

Each column can be accessed using the column name as attribute (or by indexing with the column label). The result is a new Series object.

In [20]:

df.Population

Out[20]:

Stockholm     909976
London       8615246
Rome         2872086
Paris        2273305
Name: Population, dtype: int64

In [21]:

df["Population"] #another way to get column

Out[21]:

Stockholm     909976
London       8615246
Rome         2872086
Paris        2273305
Name: Population, dtype: int64

Rows can be accessed using the loc for label based indexing or iloc for positional indexing.

In [22]:

df.loc["London"]

Out[22]:

Population           8615246
State         United Kingdom
Name: London, dtype: object

In [23]:

df.iloc[2]

Out[23]:

Population    2872086
State           Italy
Name: Rome, dtype: object

We can pass a list of row labels (and/or column labels) which will give us a new DataFrame that is a subset of the original DataFrame:

In [24]:

df3 = df.loc[["London","Paris"]]
df3

Out[24]:

	Population	State
London	8615246	United Kingdom
Paris	2273305	France

In [25]:

df4 = df.loc[["London","Rome"], "Population"]
df4

Out[25]:

London    8615246
Rome      2872086
Name: Population, dtype: int64

We can compute statistics using the same methods (mean, min, max, std, etc) as for Series objects. The calculation is applied for each column with numbers:

In [26]:

df

Out[26]:

	Population	State
Stockholm	909976	Sweden
London	8615246	United Kingdom
Rome	2872086	Italy
Paris	2273305	France

In [27]:

df.Population.mean()

Out[27]:

3667653.25

The method DataFrame method info provides a summary of the content.

In [28]:

df.info()

<class 'pandas.core.frame.DataFrame'>
Index: 4 entries, Stockholm to Paris
Data columns (total 2 columns):
 #   Column      Non-Null Count  Dtype 
---  ------      --------------  ----- 
 0   Population  4 non-null      int64 
 1   State       4 non-null      object
dtypes: int64(1), object(1)
memory usage: 268.0+ bytes

Working with data from files¶

So far we've defined data as explicit lists or dictionaries. However, we often need to read data from a file. The pandas library supports various methods for reading data from files of different formats (see pandas I/O tools). Here we will read in data from a CSV (comma-seperated values) file using read_csv function. The first and only mandatory argument is a filename . Some other useful arguments are:

• header : specifies which row, if any, contains a header with column names
• skiprows : numbers of rows to skip before starting to read data, or a list of line numbers of lines to skip
• delimiter : the character that is used as a limiter between columns values

In [29]:

pd.read_csv?

Signature:
pd.read_csv(
    filepath_or_buffer: 'FilePath | ReadCsvBuffer[bytes] | ReadCsvBuffer[str]',
    sep=<no_default>,
    delimiter=None,
    header='infer',
    names=<no_default>,
    index_col=None,
    usecols=None,
    squeeze=None,
    prefix=<no_default>,
    mangle_dupe_cols=True,
    dtype: 'DtypeArg | None' = None,
    engine: 'CSVEngine | None' = None,
    converters=None,
    true_values=None,
    false_values=None,
    skipinitialspace=False,
    skiprows=None,
    skipfooter=0,
    nrows=None,
    na_values=None,
    keep_default_na=True,
    na_filter=True,
    verbose=False,
    skip_blank_lines=True,
    parse_dates=None,
    infer_datetime_format=False,
    keep_date_col=False,
    date_parser=None,
    dayfirst=False,
    cache_dates=True,
    iterator=False,
    chunksize=None,
    compression: 'CompressionOptions' = 'infer',
    thousands=None,
    decimal: 'str' = '.',
    lineterminator=None,
    quotechar='"',
    quoting=0,
    doublequote=True,
    escapechar=None,
    comment=None,
    encoding=None,
    encoding_errors: 'str | None' = 'strict',
    dialect=None,
    error_bad_lines=None,
    warn_bad_lines=None,
    on_bad_lines=None,
    delim_whitespace=False,
    low_memory=True,
    memory_map=False,
    float_precision=None,
    storage_options: 'StorageOptions' = None,
)
Docstring:
Read a comma-separated values (csv) file into DataFrame.

Also supports optionally iterating or breaking of the file
into chunks.

Additional help can be found in the online docs for
`IO Tools <https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html>`_.

Parameters
----------
filepath_or_buffer : str, path object or file-like object
    Any valid string path is acceptable. The string could be a URL. Valid
    URL schemes include http, ftp, s3, gs, and file. For file URLs, a host is
    expected. A local file could be: file://localhost/path/to/table.csv.

    If you want to pass in a path object, pandas accepts any ``os.PathLike``.

    By file-like object, we refer to objects with a ``read()`` method, such as
    a file handle (e.g. via builtin ``open`` function) or ``StringIO``.
sep : str, default ','
    Delimiter to use. If sep is None, the C engine cannot automatically detect
    the separator, but the Python parsing engine can, meaning the latter will
    be used and automatically detect the separator by Python's builtin sniffer
    tool, ``csv.Sniffer``. In addition, separators longer than 1 character and
    different from ``'\s+'`` will be interpreted as regular expressions and
    will also force the use of the Python parsing engine. Note that regex
    delimiters are prone to ignoring quoted data. Regex example: ``'\r\t'``.
delimiter : str, default ``None``
    Alias for sep.
header : int, list of int, None, default 'infer'
    Row number(s) to use as the column names, and the start of the
    data.  Default behavior is to infer the column names: if no names
    are passed the behavior is identical to ``header=0`` and column
    names are inferred from the first line of the file, if column
    names are passed explicitly then the behavior is identical to
    ``header=None``. Explicitly pass ``header=0`` to be able to
    replace existing names. The header can be a list of integers that
    specify row locations for a multi-index on the columns
    e.g. [0,1,3]. Intervening rows that are not specified will be
    skipped (e.g. 2 in this example is skipped). Note that this
    parameter ignores commented lines and empty lines if
    ``skip_blank_lines=True``, so ``header=0`` denotes the first line of
    data rather than the first line of the file.
names : array-like, optional
    List of column names to use. If the file contains a header row,
    then you should explicitly pass ``header=0`` to override the column names.
    Duplicates in this list are not allowed.
index_col : int, str, sequence of int / str, or False, optional, default ``None``
  Column(s) to use as the row labels of the ``DataFrame``, either given as
  string name or column index. If a sequence of int / str is given, a
  MultiIndex is used.

  Note: ``index_col=False`` can be used to force pandas to *not* use the first
  column as the index, e.g. when you have a malformed file with delimiters at
  the end of each line.
usecols : list-like or callable, optional
    Return a subset of the columns. If list-like, all elements must either
    be positional (i.e. integer indices into the document columns) or strings
    that correspond to column names provided either by the user in `names` or
    inferred from the document header row(s). If ``names`` are given, the document
    header row(s) are not taken into account. 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]``.
    To instantiate a DataFrame from ``data`` with element order preserved use
    ``pd.read_csv(data, usecols=['foo', 'bar'])[['foo', 'bar']]`` for columns
    in ``['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. An
    example of a valid callable argument would be ``lambda x: x.upper() in
    ['AAA', 'BBB', 'DDD']``. Using this parameter results in much faster
    parsing time and lower memory usage.
squeeze : bool, default False
    If the parsed data only contains one column then return a Series.

    .. deprecated:: 1.4.0
        Append ``.squeeze("columns")`` to the call to ``read_csv`` to squeeze
        the data.
prefix : str, optional
    Prefix to add to column numbers when no header, e.g. 'X' for X0, X1, ...

    .. deprecated:: 1.4.0
       Use a list comprehension on the DataFrame's columns after calling ``read_csv``.
mangle_dupe_cols : bool, 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 duplicate names in the columns.
dtype : Type name or dict of column -> type, optional
    Data type for data or columns. E.g. {'a': np.float64, 'b': np.int32,
    'c': 'Int64'}
    Use `str` or `object` together with suitable `na_values` settings
    to preserve and not interpret dtype.
    If converters are specified, they will be applied INSTEAD
    of dtype conversion.
engine : {'c', 'python', 'pyarrow'}, optional
    Parser engine to use. The C and pyarrow engines are faster, while the python engine
    is currently more feature-complete. Multithreading is currently only supported by
    the pyarrow engine.

    .. versionadded:: 1.4.0

        The "pyarrow" engine was added as an *experimental* engine, and some features
        are unsupported, or may not work correctly, with this engine.
converters : dict, optional
    Dict of functions for converting values in certain columns. Keys can either
    be integers or column labels.
true_values : list, optional
    Values to consider as True.
false_values : list, optional
    Values to consider as False.
skipinitialspace : bool, default False
    Skip spaces after delimiter.
skiprows : list-like, int or callable, optional
    Line numbers to skip (0-indexed) or number of lines to skip (int)
    at the start of the file.

    If callable, the callable function will be evaluated against the row
    indices, returning True if the row should be skipped and False otherwise.
    An example of a valid callable argument would be ``lambda x: x in [0, 2]``.
skipfooter : int, default 0
    Number of lines at bottom of file to skip (Unsupported with engine='c').
nrows : int, optional
    Number of rows of file to read. Useful for reading pieces of large files.
na_values : scalar, str, list-like, or dict, optional
    Additional strings to recognize as NA/NaN. If dict passed, specific
    per-column NA values.  By default the following values are interpreted as
    NaN: '', '#N/A', '#N/A N/A', '#NA', '-1.#IND', '-1.#QNAN', '-NaN', '-nan',
    '1.#IND', '1.#QNAN', '<NA>', 'N/A', 'NA', 'NULL', 'NaN', 'n/a',
    'nan', 'null'.
keep_default_na : bool, 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_filter : bool, default True
    Detect missing value markers (empty strings and the value of na_values). In
    data without any NAs, passing na_filter=False can improve the performance
    of reading a large file.
verbose : bool, default False
    Indicate number of NA values placed in non-numeric columns.
skip_blank_lines : bool, default True
    If True, skip over blank lines rather than interpreting as NaN values.
parse_dates : bool or list of int or names or list of lists or dict, default False
    The behavior is as follows:

    * boolean. If True -> try parsing the index.
    * list of int or names. e.g. If [1, 2, 3] -> try parsing columns 1, 2, 3
      each as a separate date column.
    * list of lists. e.g.  If [[1, 3]] -> combine columns 1 and 3 and parse as
      a single date column.
    * dict, e.g. {'foo' : [1, 3]} -> parse columns 1, 3 as date and call
      result 'foo'

    If a column or index cannot be represented as an array of datetimes,
    say because of an unparsable value or a mixture of timezones, the column
    or index will be returned unaltered as an object data type. For
    non-standard datetime parsing, use ``pd.to_datetime`` after
    ``pd.read_csv``. To parse an index or column with a mixture of timezones,
    specify ``date_parser`` to be a partially-applied
    :func:`pandas.to_datetime` with ``utc=True``. See
    :ref:`io.csv.mixed_timezones` for more.

    Note: A fast-path exists for iso8601-formatted dates.
infer_datetime_format : bool, default False
    If True and `parse_dates` is enabled, pandas will attempt to infer the
    format of the datetime strings in the columns, and if it can be inferred,
    switch to a faster method of parsing them. In some cases this can increase
    the parsing speed by 5-10x.
keep_date_col : bool, default False
    If True and `parse_dates` specifies combining multiple columns then
    keep the original columns.
date_parser : function, optional
    Function to use for converting a sequence of string columns to an array of
    datetime instances. The default uses ``dateutil.parser.parser`` to do the
    conversion. Pandas will try to call `date_parser` in three different ways,
    advancing to the next if an exception occurs: 1) Pass one or more arrays
    (as defined by `parse_dates`) as arguments; 2) concatenate (row-wise) the
    string values from the columns defined by `parse_dates` into a single array
    and pass that; and 3) call `date_parser` once for each row using one or
    more strings (corresponding to the columns defined by `parse_dates`) as
    arguments.
dayfirst : bool, default False
    DD/MM format dates, international and European format.
cache_dates : bool, default True
    If True, use a cache of unique, converted dates to apply the datetime
    conversion. May produce significant speed-up when parsing duplicate
    date strings, especially ones with timezone offsets.

    .. versionadded:: 0.25.0
iterator : bool, default False
    Return TextFileReader object for iteration or getting chunks with
    ``get_chunk()``.

    .. versionchanged:: 1.2

       ``TextFileReader`` is a context manager.
chunksize : int, optional
    Return TextFileReader object for iteration.
    See the `IO Tools docs
    <https://pandas.pydata.org/pandas-docs/stable/io.html#io-chunking>`_
    for more information on ``iterator`` and ``chunksize``.

    .. versionchanged:: 1.2

       ``TextFileReader`` is a context manager.
compression : str or dict, default 'infer'
    For on-the-fly decompression of on-disk data. If 'infer' and '%s' is
    path-like, then detect compression from the following extensions: '.gz',
    '.bz2', '.zip', '.xz', or '.zst' (otherwise no compression). 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'``, ``'zstd'``} and other
    key-value pairs are forwarded to ``zipfile.ZipFile``, ``gzip.GzipFile``,
    ``bz2.BZ2File``, or ``zstandard.ZstdDecompressor``, respectively. As an
    example, the following could be passed for Zstandard decompression using a
    custom compression dictionary:
    ``compression={'method': 'zstd', 'dict_data': my_compression_dict}``.

    .. versionchanged:: 1.4.0 Zstandard support.

thousands : str, optional
    Thousands separator.
decimal : str, default '.'
    Character to recognize as decimal point (e.g. use ',' for European data).
lineterminator : str (length 1), optional
    Character to break file into lines. Only valid with C parser.
quotechar : str (length 1), optional
    The character used to denote the start and end of a quoted item. Quoted
    items can include the delimiter and it will be ignored.
quoting : int or csv.QUOTE_* instance, default 0
    Control field quoting behavior per ``csv.QUOTE_*`` constants. Use one of
    QUOTE_MINIMAL (0), QUOTE_ALL (1), QUOTE_NONNUMERIC (2) or QUOTE_NONE (3).
doublequote : bool, 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.
escapechar : str (length 1), optional
    One-character string used to escape other characters.
comment : str, optional
    Indicates remainder of line should not be parsed. If found at the beginning
    of a line, the line will be ignored altogether. This parameter must be a
    single character. Like empty lines (as long as ``skip_blank_lines=True``),
    fully commented lines are ignored by the parameter `header` but not by
    `skiprows`. For example, if ``comment='#'``, parsing
    ``#empty\na,b,c\n1,2,3`` with ``header=0`` will result in 'a,b,c' being
    treated as the header.
encoding : str, optional
    Encoding to use for UTF when reading/writing (ex. 'utf-8'). `List of Python
    standard encodings
    <https://docs.python.org/3/library/codecs.html#standard-encodings>`_ .

    .. versionchanged:: 1.2

       When ``encoding`` is ``None``, ``errors="replace"`` is passed to
       ``open()``. Otherwise, ``errors="strict"`` is passed to ``open()``.
       This behavior was previously only the case for ``engine="python"``.

    .. versionchanged:: 1.3.0

       ``encoding_errors`` is a new argument. ``encoding`` has no longer an
       influence on how encoding errors are handled.

encoding_errors : str, optional, default "strict"
    How encoding errors are treated. `List of possible values
    <https://docs.python.org/3/library/codecs.html#error-handlers>`_ .

    .. versionadded:: 1.3.0

dialect : str or csv.Dialect, optional
    If provided, this parameter will override values (default or not) for the
    following parameters: `delimiter`, `doublequote`, `escapechar`,
    `skipinitialspace`, `quotechar`, and `quoting`. If it is necessary to
    override values, a ParserWarning will be issued. See csv.Dialect
    documentation for more details.
error_bad_lines : bool, optional, default ``None``
    Lines with too many fields (e.g. a csv line with too many commas) will by
    default cause an exception to be raised, and no DataFrame will be returned.
    If False, then these "bad lines" will be dropped from the DataFrame that is
    returned.

    .. deprecated:: 1.3.0
       The ``on_bad_lines`` parameter should be used instead to specify behavior upon
       encountering a bad line instead.
warn_bad_lines : bool, optional, default ``None``
    If error_bad_lines is False, and warn_bad_lines is True, a warning for each
    "bad line" will be output.

    .. deprecated:: 1.3.0
       The ``on_bad_lines`` parameter should be used instead to specify behavior upon
       encountering a bad line instead.
on_bad_lines : {'error', 'warn', 'skip'} or callable, default 'error'
    Specifies what to do upon encountering a bad line (a line with too many fields).
    Allowed values are :

        - 'error', raise an Exception when a bad line is encountered.
        - 'warn', raise a warning when a bad line is encountered and skip that line.
        - 'skip', skip bad lines without raising or warning when they are encountered.

    .. versionadded:: 1.3.0

    .. versionadded:: 1.4.0

        - callable, function with signature
          ``(bad_line: list[str]) -> list[str] | None`` that will process a single
          bad line. ``bad_line`` is a list of strings split by the ``sep``.
          If the function returns ``None``, the bad line will be ignored.
          If the function returns a new list of strings with more elements than
          expected, a ``ParserWarning`` will be emitted while dropping extra elements.
          Only supported when ``engine="python"``

delim_whitespace : bool, default False
    Specifies whether or not whitespace (e.g. ``' '`` or ``'    '``) will be
    used as the sep. Equivalent to setting ``sep='\s+'``. If this option
    is set to True, nothing should be passed in for the ``delimiter``
    parameter.
low_memory : bool, default True
    Internally process the file in chunks, resulting in lower memory use
    while parsing, but possibly mixed type inference.  To ensure no mixed
    types either set False, or specify the type with 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 : bool, default False
    If a filepath is provided for `filepath_or_buffer`, map the file object
    directly onto memory and access the data directly from there. Using this
    option can improve performance because there is no longer any I/O overhead.
float_precision : str, optional
    Specifies which converter the C engine should use for floating-point
    values. The options are ``None`` or 'high' for the ordinary converter,
    'legacy' for the original lower precision pandas converter, and
    'round_trip' for the round-trip converter.

    .. versionchanged:: 1.2

storage_options : dict, optional
    Extra options that make sense for a particular storage connection, e.g.
    host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
    are forwarded to ``urllib`` as header options. For other URLs (e.g.
    starting with "s3://", and "gcs://") the key-value pairs are forwarded to
    ``fsspec``. Please see ``fsspec`` and ``urllib`` for more details.

    .. versionadded:: 1.2

Returns
-------
DataFrame or TextParser
    A comma-separated values (csv) file is returned as two-dimensional
    data structure with labeled axes.

See Also
--------
DataFrame.to_csv : Write DataFrame to a comma-separated values (csv) file.
read_csv : Read a comma-separated values (csv) file into DataFrame.
read_fwf : Read a table of fixed-width formatted lines into DataFrame.

Examples
--------
>>> pd.read_csv('data.csv')  # doctest: +SKIP
File:      c:\users\xjiao\anaconda3\lib\site-packages\pandas\io\parsers\readers.py
Type:      function

We will read data from the csv file european_cities.csv which contains the european cities by population within city limits.

In [30]:

#See the first 5 lines 
!head -n 5 european_cities.csv

Rank,City,State,Population,Date of census/estimate
1,London[2], United Kingdom,"8,615,246",1 June 2014
2,Berlin, Germany,"3,437,916",31 May 2014
3,Madrid, Spain,"3,165,235",1 January 2014
4,Rome, Italy,"2,872,086",30 September 2014

In [31]:

# read in data from the csv file and create a DataFrame
df_pop = pd.read_csv("european_cities.csv")

We can inspect the summary by the info method.

In [32]:

df_pop.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 105 entries, 0 to 104
Data columns (total 5 columns):
 #   Column                   Non-Null Count  Dtype 
---  ------                   --------------  ----- 
 0   Rank                     105 non-null    int64 
 1   City                     105 non-null    object
 2   State                    105 non-null    object
 3   Population               105 non-null    object
 4   Date of census/estimate  105 non-null    object
dtypes: int64(1), object(4)
memory usage: 4.2+ KB

This dataset might be too long to display in full. We can use the head(n) and tail(n) methods to return the first and last n rows, respectively (the default for n is 5). Displaying a truncated DataFrame gives us a good idea of how the data looks.

In [33]:

df_pop.head(5)

Out[33]:

	Rank	City	State	Population	Date of census/estimate
0	1	London[2]	United Kingdom	8,615,246	1 June 2014
1	2	Berlin	Germany	3,437,916	31 May 2014
2	3	Madrid	Spain	3,165,235	1 January 2014
3	4	Rome	Italy	2,872,086	30 September 2014
4	5	Paris	France	2,273,305	1 January 2013

In [34]:

df_pop.tail(6)

Out[34]:

	Rank	City	State	Population	Date of census/estimate
99	100	Valladolid	Spain	311,501	1 January 2012
100	101	Bonn	Germany	309,869	31 December 2012
101	102	Malmö	Sweden	309,105	31 March 2013
102	103	Nottingham	United Kingdom	308,735	30 June 2012
103	104	Katowice	Poland	308,269	30 June 2012
104	105	Kaunas	Lithuania	306,888	1 January 2013

The "Population" column is not yet of numerical type. The apply method transforms the elements in a specified column, and returns a new Series object. Here we transform the elements in the "Population" column from strings to integers by passing a lambda function to remove the "," and casts the results to an integer, and then assign the transformed column to a new column named "NumericPopulation".

In [35]:

df_pop["NumericPopulation"] = df_pop.Population.apply( lambda x: int(x.replace(",", "")))

In [36]:

df_pop.head()

Out[36]:

	Rank	City	State	Population	Date of census/estimate	NumericPopulation
0	1	London[2]	United Kingdom	8,615,246	1 June 2014	8615246
1	2	Berlin	Germany	3,437,916	31 May 2014	3437916
2	3	Madrid	Spain	3,165,235	1 January 2014	3165235
3	4	Rome	Italy	2,872,086	30 September 2014	2872086
4	5	Paris	France	2,273,305	1 January 2013	2273305

In [37]:

df_pop.dtypes

Out[37]:

Rank                        int64
City                       object
State                      object
Population                 object
Date of census/estimate    object
NumericPopulation           int64
dtype: object

In [38]:

# If we look at the "State" column, we'll see that it contains extra white spaces
df_pop["State"].values[:3]

Out[38]:

array([' United Kingdom', ' Germany', ' Spain'], dtype=object)

In [39]:

#We can remove extra white spaces by the string method `strip`
df_pop["State"] = df_pop["State"].apply(lambda x: x.strip())

In [40]:

df_pop["State"].values[:3]

Out[40]:

array(['United Kingdom', 'Germany', 'Spain'], dtype=object)

In [41]:

df_pop.head()

Out[41]:

	Rank	City	State	Population	Date of census/estimate	NumericPopulation
0	1	London[2]	United Kingdom	8,615,246	1 June 2014	8615246
1	2	Berlin	Germany	3,437,916	31 May 2014	3437916
2	3	Madrid	Spain	3,165,235	1 January 2014	3165235
3	4	Rome	Italy	2,872,086	30 September 2014	2872086
4	5	Paris	France	2,273,305	1 January 2013	2273305

We can also change the index to one of the columns using the set_index method. Say we want to use the "City" column as an index, and then sort the data with respect to the index.

In [42]:

df_pop2 = df_pop.set_index("City")
df_pop2.head()

Out[42]:

	Rank	State	Population	Date of census/estimate	NumericPopulation
City
London[2]	1	United Kingdom	8,615,246	1 June 2014	8615246
Berlin	2	Germany	3,437,916	31 May 2014	3437916
Madrid	3	Spain	3,165,235	1 January 2014	3165235
Rome	4	Italy	2,872,086	30 September 2014	2872086
Paris	5	France	2,273,305	1 January 2013	2273305

In [43]:

df_pop2 = df_pop2.sort_index()

In [44]:

df_pop2.head()

Out[44]:

	Rank	State	Population	Date of census/estimate	NumericPopulation
City
Aarhus	92	Denmark	326,676	1 October 2014	326676
Alicante	86	Spain	334,678	1 January 2012	334678
Amsterdam	23	Netherlands	813,562	31 May 2014	813562
Antwerp	59	Belgium	510,610	1 January 2014	510610
Athens	34	Greece	664,046	24 May 2011	664046

We can also create a hierachical index with "State" and "City" as indices, and use the sort_index method to sort by the first index:

In [45]:

df_pop3 = df_pop.set_index(["State", "City"]).sort_index(level=0)

In [46]:

df_pop3.head(8)

Out[46]:

		Rank	Population	Date of census/estimate	NumericPopulation
State	City
Austria	Vienna	7	1,794,770	1 January 2015	1794770
Belgium	Antwerp	59	510,610	1 January 2014	510610
	Brussels[17]	16	1,175,831	1 January 2014	1175831
Bulgaria	Plovdiv	84	341,041	31 December 2013	341041
	Sofia	14	1,291,895	14 December 2014	1291895
	Varna	85	335,819	31 December 2013	335819
Croatia	Zagreb	24	790,017	31 March 2011	790017
Czech Republic	Brno	76	378,327	1 January 2013	378327

To sort by column, use sort_values method. Let's use "City" as index and sort the "State" column in descending order and the "NumericPopulation" in ascending order:

In [47]:

df_pop.set_index("City").sort_values(["State","NumericPopulation"], \
                                     ascending=[False, True]).head(10)
                                     

Out[47]:

	Rank	State	Population	Date of census/estimate	NumericPopulation
City
Nottingham	103	United Kingdom	308,735	30 June 2012	308735
Wirral	97	United Kingdom	320,229	30 June 2012	320229
Coventry	94	United Kingdom	323,132	30 June 2012	323132
Wakefield	91	United Kingdom	327,627	30 June 2012	327627
Leicester	87	United Kingdom	331,606	30 June 2012	331606
Cardiff	80	United Kingdom	348,493	30 June 2012	348493
Bristol	69	United Kingdom	432,451	30 June 2012	432451
Liverpool	64	United Kingdom	469,690	30 June 2012	469690
Edinburgh	60	United Kingdom	495,360	30 June 2011	495360
Manchester	58	United Kingdom	510,772	30 June 2012	510772

In [48]:

df_pop.head()

Out[48]:

	Rank	City	State	Population	Date of census/estimate	NumericPopulation
0	1	London[2]	United Kingdom	8,615,246	1 June 2014	8615246
1	2	Berlin	Germany	3,437,916	31 May 2014	3437916
2	3	Madrid	Spain	3,165,235	1 January 2014	3165235
3	4	Rome	Italy	2,872,086	30 September 2014	2872086
4	5	Paris	France	2,273,305	1 January 2013	2273305

To count how many values of each category a column contains, we can use the value_counts method. For example, we can count the number of cites each country has:

In [49]:

city_counts = df_pop["State"].value_counts()
city_counts.head()

Out[49]:

Germany           19
United Kingdom    16
Spain             13
Poland            10
Italy             10
Name: State, dtype: int64

Question: How large is the total population of all cities within a state?

First way: create a hierarchical index using "State" and "City", and sum over all entries within the index level "State" which eliminates the "City" index.

In [50]:

df_pop4 = df_pop[["State", "City","NumericPopulation"]].set_index(["State","City"])
df_pop5 = df_pop4.sum(level='State')  # Note that this is deprecated
df_pop5.head(10)

C:\Users\xjiao\AppData\Local\Temp\ipykernel_29828\1471375156.py:2: FutureWarning: Using the level keyword in DataFrame and Series aggregations is deprecated and will be removed in a future version. Use groupby instead. df.sum(level=1) should use df.groupby(level=1).sum().
  df_pop5 = df_pop4.sum(level='State')  # Note that this is deprecated

Out[50]:

	NumericPopulation
State
United Kingdom	16011877
Germany	15119548
Spain	10041639
Italy	8764067
France	4395271
Romania	2527280
Austria	1794770
Hungary	1744665
Poland	6267409
Bulgaria	1968755

Second way: use the groupby method to group rows by the values of of a given column, and apply a reduction fuction (e.g. sum, max) on the object.

In [51]:

df_pop6 = df_pop.drop("Rank", axis=1).groupby("State").sum().   \
             sort_values("NumericPopulation", ascending=False)
df_pop6.head(10)

Out[51]:

	NumericPopulation
State
United Kingdom	16011877
Germany	15119548
Spain	10041639
Italy	8764067
Poland	6267409
France	4395271
Romania	2527280
Netherlands	2271771
Bulgaria	1968755
Austria	1794770

We can plot bar graphs for the city count and the total population.

In [52]:

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12,4))
city_counts.plot(kind='barh', ax=ax1)  # 'barh' means horizontal bar plot
ax1.set_xlabel("# cities in top 105")
df_pop5.NumericPopulation.plot(kind='barh', ax=ax2)
ax2.set_xlabel("Total pop. in top 105 cities")
plt.tight_layout()

Dealing with Missing Data¶

Let's look at a simple data frame with missing data.

In [53]:

import io
data = ''' Name|Age|Color
           Ed|22|Red
           Sara|29|Blue
           Jason|24|
           Dan||Black'''

In [54]:

df = pd.read_table(io.StringIO(data), sep='|')

This data is missing some values:

In [55]:

df

Out[55]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	NaN
3	Dan	NaN	Black

Note: A few reasons why data can be missing. 1. User didn't enter data. 2. Storage devices out of space. 3. When integrating data systems, syncing is broken etc

Finding missing data:

In [56]:

df.isnull()

Out[56]:

	Name	Age	Color
0	False	False	False
1	False	False	False
2	False	False	True
3	False	True	False

For larger datasets, we can use .any method:

In [57]:

df.isnull().any()

Out[57]:

 Name    False
Age       True
Color     True
dtype: bool

Dropping missing data

Drop rows with missing data:

In [58]:

df.dropna()

Out[58]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue

We can use the result of .notnull (the complement of .isnull) to be more selective.

In [59]:

valid = df.notnull()
#get rows for valid ages
df[valid.Age]

Out[59]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	NaN

In [60]:

#get rows for valid colors
df[valid.Color]

Out[60]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
3	Dan	NaN	Black

Inserting data for missing data

In [61]:

df

Out[61]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	NaN
3	Dan	NaN	Black

In [62]:

df.fillna('missing')

Out[62]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	missing
3	Dan	missing	Black

To fill values on per column basis, we can pass in a dictionary:

In [63]:

df.fillna({'Age': df.Age.median(), 'Color': 'Pink'})

Out[63]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	Pink
3	Dan	24.0	Black

• forward fill: take the value before the missing value.
• backwards fill: use the value after the missing value.

In [64]:

df

Out[64]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	NaN
3	Dan	NaN	Black

In [65]:

df.fillna(method='ffill')

Out[65]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	Blue
3	Dan	24.0	Black

Another option is the .interpolate method with options (linear, time, values/index).

In [66]:

# add one more row to the data
data2 = ''' Name|Age|Color
           Greg|26|Red '''
df2 = pd.read_table(io.StringIO(data2), sep='|')

In [67]:

df3 = pd.concat([df, df2], ignore_index=True)
df3

Out[67]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	NaN
3	Dan	NaN	Black
4	Greg	26.0	Red

In [68]:

df3.interpolate()

Out[68]:

	Name	Age	Color
0	Ed	22.0	Red
1	Sara	29.0	Blue
2	Jason	24.0	NaN
3	Dan	25.0	Black
4	Greg	26.0	Red

References:¶

• Numerical Python: A Practical Techniques Approach for Industry by Robert Johansson (Chapter 12)
• Learning the Pandas Library: Python Tools for Data Munging, Data Analysis, and Visualization by Matt Harrison