comparison_with_spreadsheets.rst

{{ header }}

Comparison with spreadsheets

Since many potential pandas users have some familiarity with spreadsheet programs like Excel, this page is meant to provide some examples of how various spreadsheet operations would be performed using pandas. This page will use terminology and link to documentation for Excel, but much will be the same/similar in Google Sheets, LibreOffice Calc, Apple Numbers, and other Excel-compatible spreadsheet software.

Data structures

General terminology translation

pandas	Excel
DataFrame	worksheet
Series	column
Index	row headings
row	row
NaN	empty cell

DataFrame

A DataFrame in pandas is analogous to an Excel worksheet. While an Excel worksheet can contain multiple worksheets, pandas DataFrames exist independently.

Series

A Series is the data structure that represents one column of a DataFrame. Working with a Series is analogous to referencing a column of a spreadsheet.

Index

Every DataFrame and Series has an Index, which are labels on the rows of the data. In pandas, if no index is specified, a :class:`~pandas.RangeIndex` is used by default (first row = 0, second row = 1, and so on), analogous to row headings/numbers in spreadsheets.

In pandas, indexes can be set to one (or multiple) unique values, which is like having a column that use use as the row identifier in a worksheet. Unlike spreadsheets, these Index values can actually be used to reference the rows. For example, in spreadsheets, you would reference the first row as A1:Z1, while in pandas you could use populations.loc['Chicago'].

Index values are also persistent, so if you re-order the rows in a DataFrame, the label for a particular row don't change.

See the :ref:`indexing documentation<indexing>` for much more on how to use an Index effectively.

Commonly used spreadsheet functionalities

Importing data

Both Excel and :ref:`pandas <10min_tut_02_read_write>` can import data from various sources in various formats. Let's load and display the tips dataset from the pandas tests, which is a CSV file.

In Excel, you would download and then open the CSV. In pandas, you pass the URL or local path of the CSV file to :func:`~pandas.read_csv`:

.. ipython:: python

   url = (
       "https://raw.github.com/pandas-dev"
       "/pandas/master/pandas/tests/io/data/csv/tips.csv"
   )
   tips = pd.read_csv(url)
   tips

Fill Handle

Create a series of numbers following a set pattern in a certain set of cells. In a spreadsheet, this would be done by shift+drag after entering the first number or by entering the first two or three values and then dragging.

This can be achieved by creating a series and assigning it to the desired cells.

.. ipython:: python

    df = pd.DataFrame({"AAA": [1] * 8, "BBB": list(range(0, 8))})
    df

    series = list(range(1, 5))
    series

    df.loc[2:5, "AAA"] = series

    df

Filters

Filters can be achieved by using slicing.

The examples filter by 0 on column AAA, and also show how to filter by multiple values.

.. ipython:: python

   df[df.AAA == 0]

   df[(df.AAA == 0) | (df.AAA == 2)]

Drop Duplicates

Excel has built-in functionality for removing duplicate values. This is supported in pandas via :meth:`~DataFrame.drop_duplicates`.

.. ipython:: python

    df = pd.DataFrame(
        {
            "class": ["A", "A", "A", "B", "C", "D"],
            "student_count": [42, 35, 42, 50, 47, 45],
            "all_pass": ["Yes", "Yes", "Yes", "No", "No", "Yes"],
        }
    )

    df.drop_duplicates()

    df.drop_duplicates(["class", "student_count"])

Pivot Tables

PivotTables from spreadsheets can be replicated in pandas through :ref:`reshaping`. Using the tips dataset again, let's find the average gratuity by size of the party and sex of the server.

In Excel, we use the following configuration for the PivotTable:

The equivalent in pandas:

.. ipython:: python

    pd.pivot_table(
        tips, values="tip", index=["size"], columns=["sex"], aggfunc=np.average
    )

Formulas

In spreadsheets, formulas are often created in individual cells and then dragged into other cells to compute them for other columns. In pandas, you'll be doing more operations on full columns.

As an example, let's create a new column "girls_count" and try to compute the number of boys in each class.

.. ipython:: python

    df["girls_count"] = [21, 12, 21, 31, 23, 17]
    df
    df["boys_count"] = df["student_count"] - df["girls_count"]
    df

Note that we aren't having to tell it to do that subtraction cell-by-cell — pandas handles that for us. See :ref:`how to create new columns derived from existing columns <10min_tut_05_columns>`.

VLOOKUP

.. ipython:: python

    import random

    first_names = [
        "harry",
        "ron",
        "hermione",
        "rubius",
        "albus",
        "severus",
        "luna",
    ]
    keys = [1, 2, 3, 4, 5, 6, 7]
    df1 = pd.DataFrame({"keys": keys, "first_names": first_names})
    df1

    surnames = [
        "hadrid",
        "malfoy",
        "lovegood",
        "dumbledore",
        "grindelwald",
        "granger",
        "weasly",
        "riddle",
        "longbottom",
        "snape",
    ]
    keys = [random.randint(1, 7) for x in range(0, 10)]
    random_names = pd.DataFrame({"surnames": surnames, "keys": keys})

    random_names

    random_names.merge(df1, on="keys", how="left")

Adding a row

To appended a row, we can just assign values to an index using :meth:`~DataFrame.loc`.

NOTE: If the index already exists, the values in that index will be over written.

.. ipython:: python

    df1.loc[7] = [8, "tonks"]
    df1

Search and Replace

The replace method that comes associated with the DataFrame object can perform this function. Please see pandas.DataFrame.replace for examples.