In Class Activity - Data and Dataframes (Pandas)#
Note
This exercise authored by Todd M. Gureckis is released under the license. Based on a notebook written by Jin Cheong & Luke Chang
In this lab we are going to learn how to load and manipulate datasets in a dataframe format using Pandas and create beautiful plots using Matplotlib and Seaborn. Pandas is akin to a dataframe in R and provides an intuitive way to interact with data in a 2D dataframe. Matplotlib is a standard plotting library that is similar in functionality to Matlab’s object-oriented plotting. Seaborn is also a plotting library built on the Matplotlib framework that carries useful pre-configured plotting schemes.
After the tutorial you will have the chance to apply the methods to a new set of data.
Also, here is a great set of notebooks that also covers the topic. In addition, here is a brief video providing a useful introduction to pandas.
First, we load the basic packages we will be using in this tutorial. Notice how we import the modules using an abbreviated name. This is to reduce the amount of text we type when we use the functions.
Note: %matplotlib inline is an example of ‘cell magic’ and enables plotting within the notebook and not opening a separate window. In addition, you may want to try using %matplotlib notebook, which will allow more interactive plotting.
%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
2+2
4
In the first part of this exercise you just have to go through each cell and execute it. However, read the surrounding explanation and think about what each line of the code is doing. This is a great way to learn. You can ask your group members if you don’t understand anything and also feel free to change the code slightly to experiment with what happens if you repeat an analysis on a different column, etc.
Pandas#
Loading Data#
We use the pd.read_csv() to load a .csv file into a dataframe. Note that read_csv() has many options that can be used to make sure you load the data correctly.
Pandas has many ways to read data in different data formats into a dataframe. Here we will use the pd.read_csv function.
df = pd.read_csv(
"salary.csv",
sep=",",
header="infer",
)
You can always use the ? to access the brief documentation for a function for more information about the inputs and general usage guidelines.
pd.read_csv?
Signature:
pd.read_csv(
filepath_or_buffer: 'FilePath | ReadCsvBuffer[bytes] | ReadCsvBuffer[str]',
*,
sep: 'str | None | lib.NoDefault' = <no_default>,
delimiter: 'str | None | lib.NoDefault' = None,
header: "int | Sequence[int] | None | Literal['infer']" = 'infer',
names: 'Sequence[Hashable] | None | lib.NoDefault' = <no_default>,
index_col: 'IndexLabel | Literal[False] | None' = None,
usecols: 'UsecolsArgType' = None,
dtype: 'DtypeArg | None' = None,
engine: 'CSVEngine | None' = None,
converters: 'Mapping[Hashable, Callable] | None' = None,
true_values: 'list | None' = None,
false_values: 'list | None' = None,
skipinitialspace: 'bool' = False,
skiprows: 'list[int] | int | Callable[[Hashable], bool] | None' = None,
skipfooter: 'int' = 0,
nrows: 'int | None' = None,
na_values: 'Hashable | Iterable[Hashable] | Mapping[Hashable, Iterable[Hashable]] | None' = None,
keep_default_na: 'bool' = True,
na_filter: 'bool' = True,
verbose: 'bool | lib.NoDefault' = <no_default>,
skip_blank_lines: 'bool' = True,
parse_dates: 'bool | Sequence[Hashable] | None' = None,
infer_datetime_format: 'bool | lib.NoDefault' = <no_default>,
keep_date_col: 'bool | lib.NoDefault' = <no_default>,
date_parser: 'Callable | lib.NoDefault' = <no_default>,
date_format: 'str | dict[Hashable, str] | None' = None,
dayfirst: 'bool' = False,
cache_dates: 'bool' = True,
iterator: 'bool' = False,
chunksize: 'int | None' = None,
compression: 'CompressionOptions' = 'infer',
thousands: 'str | None' = None,
decimal: 'str' = '.',
lineterminator: 'str | None' = None,
quotechar: 'str' = '"',
quoting: 'int' = 0,
doublequote: 'bool' = True,
escapechar: 'str | None' = None,
comment: 'str | None' = None,
encoding: 'str | None' = None,
encoding_errors: 'str | None' = 'strict',
dialect: 'str | csv.Dialect | None' = None,
on_bad_lines: 'str' = 'error',
delim_whitespace: 'bool | lib.NoDefault' = <no_default>,
low_memory: 'bool' = True,
memory_map: 'bool' = False,
float_precision: "Literal['high', 'legacy'] | None" = None,
storage_options: 'StorageOptions | None' = None,
dtype_backend: 'DtypeBackend | lib.NoDefault' = <no_default>,
) -> 'DataFrame | TextFileReader'
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 ','
Character or regex pattern to treat as the delimiter. If ``sep=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 from only the first valid
row of the file 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, optional
Alias for ``sep``.
header : int, Sequence of int, 'infer' or None, default 'infer'
Row number(s) containing column labels and marking the start of the
data (zero-indexed). 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 to ``names`` 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 :class:`~pandas.MultiIndex` 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 : Sequence of Hashable, optional
Sequence of column labels to apply. 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 : Hashable, Sequence of Hashable or False, optional
Column(s) to use as row label(s), denoted either by column labels or column
indices. If a sequence of labels or indices is given, :class:`~pandas.MultiIndex`
will be formed for the row labels.
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 : Sequence of Hashable or Callable, optional
Subset of columns to select, denoted either by column labels or column indices.
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 :class:`~pandas.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.
dtype : dtype or dict of {Hashable : dtype}, optional
Data type(s) to apply to either the whole dataset or individual 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.
.. versionadded:: 1.5.0
Support for ``defaultdict`` was added. Specify a ``defaultdict`` as input where
the default determines the ``dtype`` of the columns which are not explicitly
listed.
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 of {Hashable : Callable}, optional
Functions for converting values in specified columns. Keys can either
be column labels or column indices.
true_values : list, optional
Values to consider as ``True`` in addition to case-insensitive variants of 'True'.
false_values : list, optional
Values to consider as ``False`` in addition to case-insensitive variants of 'False'.
skipinitialspace : bool, default False
Skip spaces after delimiter.
skiprows : int, list of 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 : Hashable, Iterable of Hashable or dict of {Hashable : Iterable}, 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", "None",
"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 ``NA`` values, 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.
.. deprecated:: 2.2.0
skip_blank_lines : bool, default True
If ``True``, skip over blank lines rather than interpreting as ``NaN`` values.
parse_dates : bool, list of Hashable, list of lists or dict of {Hashable : list}, default False
The behavior is as follows:
* ``bool``. If ``True`` -> try parsing the index. Note: Automatically set to
``True`` if ``date_format`` or ``date_parser`` arguments have been passed.
* ``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 ``list``. e.g. If ``[[1, 3]]`` -> combine columns 1 and 3 and parse
as a single date column. Values are joined with a space before parsing.
* ``dict``, e.g. ``{'foo' : [1, 3]}`` -> parse columns 1, 3 as date and call
result 'foo'. Values are joined with a space before parsing.
If a column or index cannot be represented as an array of ``datetime``,
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 :func:`~pandas.to_datetime` after
:func:`~pandas.read_csv`.
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.
.. deprecated:: 2.0.0
A strict version of this argument is now the default, passing it has no effect.
keep_date_col : bool, default False
If ``True`` and ``parse_dates`` specifies combining multiple columns then
keep the original columns.
date_parser : Callable, 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.
.. deprecated:: 2.0.0
Use ``date_format`` instead, or read in as ``object`` and then apply
:func:`~pandas.to_datetime` as-needed.
date_format : str or dict of column -> format, optional
Format to use for parsing dates when used in conjunction with ``parse_dates``.
The strftime to parse time, e.g. :const:`"%d/%m/%Y"`. See
`strftime documentation
<https://docs.python.org/3/library/datetime.html
#strftime-and-strptime-behavior>`_ for more information on choices, though
note that :const:`"%f"` will parse all the way up to nanoseconds.
You can also pass:
- "ISO8601", to parse any `ISO8601 <https://en.wikipedia.org/wiki/ISO_8601>`_
time string (not necessarily in exactly the same format);
- "mixed", to infer the format for each element individually. This is risky,
and you should probably use it along with `dayfirst`.
.. versionadded:: 2.0.0
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.
iterator : bool, default False
Return ``TextFileReader`` object for iteration or getting chunks with
``get_chunk()``.
chunksize : int, optional
Number of lines to read from the file per chunk. Passing a value will cause the
function to return a ``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``.
compression : str or dict, default 'infer'
For on-the-fly decompression of on-disk data. If 'infer' and 'filepath_or_buffer' is
path-like, then detect compression from the following extensions: '.gz',
'.bz2', '.zip', '.xz', '.zst', '.tar', '.tar.gz', '.tar.xz' or '.tar.bz2'
(otherwise no compression).
If using 'zip' or 'tar', 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'``, ``'xz'``, ``'tar'``} and
other key-value pairs are forwarded to
``zipfile.ZipFile``, ``gzip.GzipFile``,
``bz2.BZ2File``, ``zstandard.ZstdDecompressor``, ``lzma.LZMAFile`` or
``tarfile.TarFile``, 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}``.
.. versionadded:: 1.5.0
Added support for `.tar` files.
.. versionchanged:: 1.4.0 Zstandard support.
thousands : str (length 1), optional
Character acting as the thousands separator in numerical values.
decimal : str (length 1), default '.'
Character to recognize as decimal point (e.g., use ',' for European data).
lineterminator : str (length 1), optional
Character used to denote a line break. Only valid with C parser.
quotechar : str (length 1), optional
Character used to denote the start and end of a quoted item. Quoted
items can include the ``delimiter`` and it will be ignored.
quoting : {0 or csv.QUOTE_MINIMAL, 1 or csv.QUOTE_ALL, 2 or csv.QUOTE_NONNUMERIC, 3 or csv.QUOTE_NONE}, default csv.QUOTE_MINIMAL
Control field quoting behavior per ``csv.QUOTE_*`` constants. Default is
``csv.QUOTE_MINIMAL`` (i.e., 0) which implies that only fields containing special
characters are quoted (e.g., characters defined in ``quotechar``, ``delimiter``,
or ``lineterminator``.
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
Character used to escape other characters.
comment : str (length 1), optional
Character indicating that the 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, default 'utf-8'
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>`_ .
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.
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'``
.. versionchanged:: 2.2.0
- Callable, function with signature
as described in `pyarrow documentation
<https://arrow.apache.org/docs/python/generated/pyarrow.csv.ParseOptions.html
#pyarrow.csv.ParseOptions.invalid_row_handler>`_ when ``engine='pyarrow'``
delim_whitespace : bool, default False
Specifies whether or not whitespace (e.g. ``' '`` or ``'\t'``) will be
used as the ``sep`` delimiter. Equivalent to setting ``sep='\s+'``. If this option
is set to ``True``, nothing should be passed in for the ``delimiter``
parameter.
.. deprecated:: 2.2.0
Use ``sep="\s+"`` instead.
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 :class:`~pandas.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 : {'high', 'legacy', 'round_trip'}, 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.
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.request.Request`` as header options. For other
URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
details, and for more examples on storage options refer `here
<https://pandas.pydata.org/docs/user_guide/io.html?
highlight=storage_options#reading-writing-remote-files>`_.
dtype_backend : {'numpy_nullable', 'pyarrow'}, default 'numpy_nullable'
Back-end data type applied to the resultant :class:`DataFrame`
(still experimental). Behaviour is as follows:
* ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
(default).
* ``"pyarrow"``: returns pyarrow-backed nullable :class:`ArrowDtype`
DataFrame.
.. versionadded:: 2.0
Returns
-------
DataFrame or TextFileReader
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_table : Read general delimited file into DataFrame.
read_fwf : Read a table of fixed-width formatted lines into DataFrame.
Examples
--------
>>> pd.read_csv('data.csv') # doctest: +SKIP
File: /opt/conda/envs/cognition/lib/python3.12/site-packages/pandas/io/parsers/readers.py
Type: function
Ways to check the dataframe#
There are many ways to examine your dataframe. One easy way is to execute the dataframe itself.
df
| salary | gender | departm | years | age | publications | |
|---|---|---|---|---|---|---|
| 0 | 86285 | 0 | bio | 26.0 | 64.0 | 72 |
| 1 | 77125 | 0 | bio | 28.0 | 58.0 | 43 |
| 2 | 71922 | 0 | bio | 10.0 | 38.0 | 23 |
| 3 | 70499 | 0 | bio | 16.0 | 46.0 | 64 |
| 4 | 66624 | 0 | bio | 11.0 | 41.0 | 23 |
| ... | ... | ... | ... | ... | ... | ... |
| 72 | 53662 | 1 | neuro | 1.0 | 31.0 | 3 |
| 73 | 57185 | 1 | stat | 9.0 | 39.0 | 7 |
| 74 | 52254 | 1 | stat | 2.0 | 32.0 | 9 |
| 75 | 61885 | 1 | math | 23.0 | 60.0 | 9 |
| 76 | 49542 | 1 | math | 3.0 | 33.0 | 5 |
77 rows × 6 columns
However, often the dataframes can be large and we may be only interested in seeing the first few rows. df.head() is useful for this purpose. shape is another useful method for getting the dimensions of the matrix. We will print the number of rows and columns in this data set by using output formatting. Use the % sign to indicate the type of data (e.g., %i=integer, %d=float, %s=string), then use the % followed by a tuple of the values you would like to insert into the text. See here for more info about formatting text.
print("There are %i rows and %i columns in this data set" % df.shape)
There are 77 rows and 6 columns in this data set
df.head()
| salary | gender | departm | years | age | publications | |
|---|---|---|---|---|---|---|
| 0 | 86285 | 0 | bio | 26.0 | 64.0 | 72 |
| 1 | 77125 | 0 | bio | 28.0 | 58.0 | 43 |
| 2 | 71922 | 0 | bio | 10.0 | 38.0 | 23 |
| 3 | 70499 | 0 | bio | 16.0 | 46.0 | 64 |
| 4 | 66624 | 0 | bio | 11.0 | 41.0 | 23 |
On the top row, you have column names that can be called like a dictionary (a dataframe can be essentially thought of as a dictionary with column names as the keys). The leftmost column (0,1,2,3,4…) is called the index of the dataframe. The default index is sequential integers, but it can be set to anything as long as each row is unique (e.g., subject IDs).
print("Indexes")
print(df.index)
print("Columns")
print(df.columns)
print("Columns are like keys of a dictionary")
print(df.keys())
Indexes
RangeIndex(start=0, stop=77, step=1)
Columns
Index(['salary', 'gender', 'departm', 'years', 'age', 'publications'], dtype='object')
Columns are like keys of a dictionary
Index(['salary', 'gender', 'departm', 'years', 'age', 'publications'], dtype='object')
You can access the values of a column by calling it directly. Double bracket returns a dataframe.
df[["salary"]]
| salary | |
|---|---|
| 0 | 86285 |
| 1 | 77125 |
| 2 | 71922 |
| 3 | 70499 |
| 4 | 66624 |
| ... | ... |
| 72 | 53662 |
| 73 | 57185 |
| 74 | 52254 |
| 75 | 61885 |
| 76 | 49542 |
77 rows × 1 columns
Single bracket returns a Series.
df["salary"]
0 86285
1 77125
2 71922
3 70499
4 66624
...
72 53662
73 57185
74 52254
75 61885
76 49542
Name: salary, Length: 77, dtype: int64
You can also call a column like an attribute if the column name is a string.
df.salary
0 86285
1 77125
2 71922
3 70499
4 66624
...
72 53662
73 57185
74 52254
75 61885
76 49542
Name: salary, Length: 77, dtype: int64
You can create new columns to fit your needs. For instance you can initialize a new column with zeros.
df["pubperyear"] = 0
df.head()
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 0 | 86285 | 0 | bio | 26.0 | 64.0 | 72 | 0 |
| 1 | 77125 | 0 | bio | 28.0 | 58.0 | 43 | 0 |
| 2 | 71922 | 0 | bio | 10.0 | 38.0 | 23 | 0 |
| 3 | 70499 | 0 | bio | 16.0 | 46.0 | 64 | 0 |
| 4 | 66624 | 0 | bio | 11.0 | 41.0 | 23 | 0 |
Here, we create a new column pubperyear, which is the ratio of the number of papers published per year.
df["pubperyear"] = df["publications"] / df["years"]
df.head()
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 0 | 86285 | 0 | bio | 26.0 | 64.0 | 72 | 2.769231 |
| 1 | 77125 | 0 | bio | 28.0 | 58.0 | 43 | 1.535714 |
| 2 | 71922 | 0 | bio | 10.0 | 38.0 | 23 | 2.300000 |
| 3 | 70499 | 0 | bio | 16.0 | 46.0 | 64 | 4.000000 |
| 4 | 66624 | 0 | bio | 11.0 | 41.0 | 23 | 2.090909 |
Indexing and slicing#
Indexing in Pandas can be tricky. There are four ways to index: loc, iloc, and explicit indexing (useful for Booleans).
First, we will try using .loc. This method references the explicit index. it works for both index names and also column names.
df.loc[0, ["salary"]]
salary 86285
Name: 0, dtype: object
Next we will try .iloc. This method references the implicit python index (starting from 0, exclusive of the last number). You can think of this like row by column indexing using integers.
df.iloc[0:3, 0:3]
| salary | gender | departm | |
|---|---|---|---|
| 0 | 86285 | 0 | bio |
| 1 | 77125 | 0 | bio |
| 2 | 71922 | 0 | bio |
Let’s make a new dataframe with just Males and another for just Females. Notice how we added the .reset_index(drop=True) method? This is because assigning a new dataframe based on indexing another dataframe will retain the original index. We need to explicitly tell pandas to reset the index if we want it to start from zero.
male_df = df.query("gender == 0").reset_index(drop=True)
female_df = df.query("gender == 1").reset_index(drop=True)
Boolean or logical indexing is useful if you need to sort the data based on some True or False value.
For instance, who are the people with salaries greater than 90K but lower than 100K ?
df.query("salary > 90000 & salary < 100000")
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 14 | 97630 | 0 | chem | 34.0 | 64.0 | 43 | 1.264706 |
| 30 | 92951 | 0 | neuro | 11.0 | 41.0 | 20 | 1.818182 |
| 54 | 96936 | 0 | physics | 15.0 | 50.0 | 17 | 1.133333 |
Dealing with missing values#
It is easy to quickly count the number of missing values for each column in the dataset using the isnull() method. One thing that is nice about Python is that you can chain commands, which means that the output of one method can be the input into the next method. This allows us to write intuitive and concise code. Notice how we take the sum() of all of the null cases.
The isnull() method will return a dataframe with True/False values on whether a datapoint is null or not a number (nan).
x = df.isnull()
x.head()
#x.shape
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 0 | False | False | False | False | False | False | False |
| 1 | False | False | False | False | False | False | False |
| 2 | False | False | False | False | False | False | False |
| 3 | False | False | False | False | False | False | False |
| 4 | False | False | False | False | False | False | False |
We can chain the .null() and .sum() methods to see how many null values are added up.
df.isnull().sum()
salary 0
gender 0
departm 0
years 1
age 1
publications 0
pubperyear 1
dtype: int64
You can use the Boolean indexing once again to see the datapoints that have missing values. We chained the method .any(), which will check if there are any True values for a given axis. Axis=0 indicates rows, while Axis=1 indicates columns. So, here we are creating a Boolean index for row where any column has a missing value.
df[df.isnull().any(axis=1)]
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 18 | 64762 | 0 | chem | 25.0 | NaN | 29 | 1.16 |
| 24 | 104828 | 0 | geol | NaN | 50.0 | 44 | NaN |
You may look at where the values are not null. Note that indexes 18 and 24 are missing.
df[~df.isnull().any(axis=1)]
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 0 | 86285 | 0 | bio | 26.0 | 64.0 | 72 | 2.769231 |
| 1 | 77125 | 0 | bio | 28.0 | 58.0 | 43 | 1.535714 |
| 2 | 71922 | 0 | bio | 10.0 | 38.0 | 23 | 2.300000 |
| 3 | 70499 | 0 | bio | 16.0 | 46.0 | 64 | 4.000000 |
| 4 | 66624 | 0 | bio | 11.0 | 41.0 | 23 | 2.090909 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 72 | 53662 | 1 | neuro | 1.0 | 31.0 | 3 | 3.000000 |
| 73 | 57185 | 1 | stat | 9.0 | 39.0 | 7 | 0.777778 |
| 74 | 52254 | 1 | stat | 2.0 | 32.0 | 9 | 4.500000 |
| 75 | 61885 | 1 | math | 23.0 | 60.0 | 9 | 0.391304 |
| 76 | 49542 | 1 | math | 3.0 | 33.0 | 5 | 1.666667 |
75 rows × 7 columns
There are different techniques for dealing with missing data. An easy one is to simply remove rows that have any missing values using the dropna() method.
df = df.dropna()
Now we can check to make sure the missing rows are removed. Let’s also check the new dimensions of the dataframe.
print("There are %i rows and %i columns in this data set" % df.shape)
df.isnull().sum()
There are 75 rows and 7 columns in this data set
salary 0
gender 0
departm 0
years 0
age 0
publications 0
pubperyear 0
dtype: int64
Describing the data#
We can use the .describe() method to get a quick summary of the continuous values of the data frame. We will .transpose() the output to make it slightly easier to read.
df.describe().transpose()
| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| salary | 75.0 | 67293.946667 | 14672.455177 | 44687.000000 | 56638.500000 | 62557.000000 | 74733.500000 | 112800.0 |
| gender | 75.0 | 0.146667 | 0.392268 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 2.0 |
| years | 75.0 | 14.840000 | 8.596102 | 1.000000 | 8.000000 | 14.000000 | 22.500000 | 34.0 |
| age | 75.0 | 45.426667 | 9.051166 | 31.000000 | 38.000000 | 44.000000 | 53.000000 | 65.0 |
| publications | 75.0 | 21.440000 | 15.200676 | 3.000000 | 9.000000 | 18.000000 | 32.500000 | 72.0 |
| pubperyear | 75.0 | 1.926595 | 1.602968 | 0.173913 | 0.775253 | 1.653846 | 2.563187 | 8.0 |
We can also get a quick summary of a pandas series, or of a specific column of a pandas dataframe.
df.departm.describe()
count 75
unique 7
top bio
freq 16
Name: departm, dtype: object
Manipulating data in Groups#
One manipulation we often do is to look at variables in groups.
One way to do this is to use the .groupby(key) method.
The key is a column that is used to group the variables together.
For instance, if we want to group the data by gender and get group means, we perform the following.
df.groupby("gender").mean(numeric_only=True)
| salary | years | age | publications | pubperyear | |
|---|---|---|---|---|---|
| gender | |||||
| 0 | 69108.492308 | 15.846154 | 46.492308 | 23.061538 | 1.924709 |
| 1 | 55719.666667 | 8.666667 | 38.888889 | 11.555556 | 2.043170 |
| 2 | 53517.000000 | 5.000000 | 35.000000 | 5.000000 | 1.000000 |
Other default aggregation methods include .count(), .mean(), .median(), .min(), .max(), .std(), .var(), and .sum()
Before we move on, it looks like there were more than 2 genders specified in our data. This is likely an error in the data collection process so let recap on how we might remove this datapoint.
df.query("gender==1")
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 68 | 59139 | 1 | bio | 8.0 | 38.0 | 23 | 2.875000 |
| 69 | 52968 | 1 | bio | 18.0 | 48.0 | 32 | 1.777778 |
| 70 | 55949 | 1 | chem | 4.0 | 34.0 | 12 | 3.000000 |
| 71 | 58893 | 1 | neuro | 10.0 | 35.0 | 4 | 0.400000 |
| 72 | 53662 | 1 | neuro | 1.0 | 31.0 | 3 | 3.000000 |
| 73 | 57185 | 1 | stat | 9.0 | 39.0 | 7 | 0.777778 |
| 74 | 52254 | 1 | stat | 2.0 | 32.0 | 9 | 4.500000 |
| 75 | 61885 | 1 | math | 23.0 | 60.0 | 9 | 0.391304 |
| 76 | 49542 | 1 | math | 3.0 | 33.0 | 5 | 1.666667 |
Replace the original dataframe with one without the miscoded data.
df = df.query("gender!=2")
Now we have a corrected dataframe!
df.groupby("gender").mean(numeric_only=True)
| salary | years | age | publications | pubperyear | |
|---|---|---|---|---|---|
| gender | |||||
| 0 | 69108.492308 | 15.846154 | 46.492308 | 23.061538 | 1.924709 |
| 1 | 55719.666667 | 8.666667 | 38.888889 | 11.555556 | 2.043170 |
Another powerful tool in Pandas is the split-apply-combine method.
For instance, let’s say we also want to look at how much each professor is earning with respect to the department.
Let’s say we want to subtract the departmental mean from the professor’s salary and divide it by the departmental standard deviation.
We can do this by using the groupby(key) method chained with the .transform(function) method.
It will group the dataframe by the key column, perform the “function” transformation of the data and return data in same format.
To learn more, see link here.
# key: We use the departm as the grouping factor.
key = df["departm"]
# Let's create an anonmyous function for calculating zscores using lambda:
# We want to standardize salary for each department.
zscore = lambda x: (x - x.mean()) / x.std()
# Now let's calculate zscores separately within each department
transformed = df.groupby(key).transform(zscore)
transformed.head ()
#df["salary_in_departm"] = transformed["salary"]
#transformed.shape
| salary | gender | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|
| 0 | 2.468065 | -0.365963 | 1.287596 | 1.922980 | 2.261625 | 1.039967 |
| 1 | 1.493198 | -0.365963 | 1.528550 | 1.246767 | 0.849251 | -0.229795 |
| 2 | 0.939461 | -0.365963 | -0.640033 | -1.007275 | -0.124800 | 0.556949 |
| 3 | 0.788016 | -0.365963 | 0.082828 | -0.105658 | 1.872005 | 2.306902 |
| 4 | 0.375613 | -0.365963 | -0.519556 | -0.669169 | -0.124800 | 0.341714 |
Now we have a salary_in_departm column showing standardized salary per department.
df.head()
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 0 | 86285 | 0 | bio | 26.0 | 64.0 | 72 | 2.769231 |
| 1 | 77125 | 0 | bio | 28.0 | 58.0 | 43 | 1.535714 |
| 2 | 71922 | 0 | bio | 10.0 | 38.0 | 23 | 2.300000 |
| 3 | 70499 | 0 | bio | 16.0 | 46.0 | 64 | 4.000000 |
| 4 | 66624 | 0 | bio | 11.0 | 41.0 | 23 | 2.090909 |
Combining datasets - pd.concat#
Recall that we sliced the dataframes into male and female dataframes in Indexing and Slicing. Now we will learn how to put dataframes together, which is done by the pd.concat method. Note how the index of this output retains the old index.
female_df.head()
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 0 | 59139 | 1 | bio | 8.0 | 38.0 | 23 | 2.875000 |
| 1 | 52968 | 1 | bio | 18.0 | 48.0 | 32 | 1.777778 |
| 2 | 55949 | 1 | chem | 4.0 | 34.0 | 12 | 3.000000 |
| 3 | 58893 | 1 | neuro | 10.0 | 35.0 | 4 | 0.400000 |
| 4 | 53662 | 1 | neuro | 1.0 | 31.0 | 3 | 3.000000 |
male_df.head()
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 0 | 86285 | 0 | bio | 26.0 | 64.0 | 72 | 2.769231 |
| 1 | 77125 | 0 | bio | 28.0 | 58.0 | 43 | 1.535714 |
| 2 | 71922 | 0 | bio | 10.0 | 38.0 | 23 | 2.300000 |
| 3 | 70499 | 0 | bio | 16.0 | 46.0 | 64 | 4.000000 |
| 4 | 66624 | 0 | bio | 11.0 | 41.0 | 23 | 2.090909 |
We can reset the index to start at zero using the .reset_index() method.
pd.concat([male_df, female_df], axis=0).reset_index(drop=True)
| salary | gender | departm | years | age | publications | pubperyear | |
|---|---|---|---|---|---|---|---|
| 0 | 86285 | 0 | bio | 26.0 | 64.0 | 72 | 2.769231 |
| 1 | 77125 | 0 | bio | 28.0 | 58.0 | 43 | 1.535714 |
| 2 | 71922 | 0 | bio | 10.0 | 38.0 | 23 | 2.300000 |
| 3 | 70499 | 0 | bio | 16.0 | 46.0 | 64 | 4.000000 |
| 4 | 66624 | 0 | bio | 11.0 | 41.0 | 23 | 2.090909 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 71 | 53662 | 1 | neuro | 1.0 | 31.0 | 3 | 3.000000 |
| 72 | 57185 | 1 | stat | 9.0 | 39.0 | 7 | 0.777778 |
| 73 | 52254 | 1 | stat | 2.0 | 32.0 | 9 | 4.500000 |
| 74 | 61885 | 1 | math | 23.0 | 60.0 | 9 | 0.391304 |
| 75 | 49542 | 1 | math | 3.0 | 33.0 | 5 | 1.666667 |
76 rows × 7 columns