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 workbook 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 is used as the row identifier in a worksheet. Unlike most spreadsheets, these Index values can actually be used to reference the rows. (Note that this can be done in Excel with structured references.) 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.

Copies vs. in place operations

Data input / output

Constructing a DataFrame from values

In a spreadsheet, values can be typed directly into cells.

Reading external data

Both Excel and :ref:`pandas <10min_tut_02_read_write>` can import data from various sources in various formats.

CSV

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

Like Excel's Text Import Wizard, read_csv can take a number of parameters to specify how the data should be parsed. For example, if the data was instead tab delimited, and did not have column names, the pandas command would be:

tips = pd.read_csv("tips.csv", sep="\t", header=None)

# alternatively, read_table is an alias to read_csv with tab delimiter
tips = pd.read_table("tips.csv", header=None)

Excel files

Excel opens various Excel file formats by double-clicking them, or using the Open menu. In pandas, you use :ref:`special methods for reading and writing from/to Excel files <io.excel>`.

Let's first :ref:`create a new Excel file <io.excel_writer>` based on the tips dataframe in the above example:

tips.to_excel("./tips.xlsx")

Should you wish to subsequently access the data in the tips.xlsx file, you can read it into your module using

tips_df = read_excel("./tips.xlsx", header=None)

You have just read in an Excel file using pandas!

Limiting output

Spreadsheet programs will only show one screenful of data at a time and then allow you to scroll, so there isn't really a need to limit output. In pandas, you'll need to put a little more thought into controlling how your DataFrames are displayed.

Exporting data

By default, desktop spreadsheet software will save to its respective file format (.xlsx, .ods, etc). You can, however, save to other file formats.

:ref:`pandas can create Excel files <io.excel_writer>`, :ref:`CSV <io.store_in_csv>`, or :ref:`a number of other formats <io>`.

Data operations

Operations on columns

In spreadsheets, formulas are often created in individual cells and then dragged into other cells to compute them for other columns. In pandas, you're able to do operations on whole columns directly.

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>`.

Filtering

In Excel, filtering is done through a graphical menu.

If/then logic

Let's say we want to make a bucket column with values of low and high, based on whether the total_bill is less or more than $10.

In spreadsheets, logical comparison can be done with conditional formulas. We'd use a formula of =IF(A2 < 10, "low", "high"), dragged to all cells in a new bucket column.

Date functionality

This section will refer to "dates", but timestamps are handled similarly.

We can think of date functionality in two parts: parsing, and output. In spreadsheets, date values are generally parsed automatically, though there is a DATEVALUE function if you need it. In pandas, you need to explicitly convert plain text to datetime objects, either :ref:`while reading from a CSV <io.read_csv_table.datetime>` or :ref:`once in a DataFrame <10min_tut_09_timeseries.properties>`.

Once parsed, spreadsheets display the dates in a default format, though the format can be changed. In pandas, you'll generally want to keep dates as datetime objects while you're doing calculations with them. Outputting parts of dates (such as the year) is done through date functions in spreadsheets, and :ref:`datetime properties <10min_tut_09_timeseries.properties>` in pandas.

Given date1 and date2 in columns A and B of a spreadsheet, you might have these formulas:

column	formula
date1_year	=YEAR(A2)
date2_month	=MONTH(B2)
date1_next	=DATE(YEAR(A2),MONTH(A2)+1,1)
months_between	=DATEDIF(A2,B2,"M")

The equivalent pandas operations are shown below.

See :ref:`timeseries` for more details.

Selection of columns

In spreadsheets, you can select columns you want by:

• Hiding columns
• Deleting columns
• Referencing a range from one worksheet into another

Since spreadsheet columns are typically named in a header row, renaming a column is simply a matter of changing the text in that first cell.

Sorting by values

Sorting in spreadsheets is accomplished via the sort dialog.

String processing

Finding length of string

In spreadsheets, the number of characters in text can be found with the LEN function. This can be used with the TRIM function to remove extra whitespace.

=LEN(TRIM(A2))

Note this will still include multiple spaces within the string, so isn't 100% equivalent.

Finding position of substring

The FIND spreadsheet function returns the position of a substring, with the first character being 1.

Extracting substring by position

Spreadsheets have a MID formula for extracting a substring from a given position. To get the first character:

=MID(A2,1,1)

Extracting nth word

In Excel, you might use the Text to Columns Wizard for splitting text and retrieving a specific column. (Note it's possible to do so through a formula as well.)

Changing case

Spreadsheets provide UPPER, LOWER, and PROPER functions for converting text to upper, lower, and title case, respectively.

Merging

In Excel, there are merging of tables can be done through a VLOOKUP.

merge has a number of advantages over VLOOKUP:

• The lookup value doesn't need to be the first column of the lookup table
• If multiple rows are matched, there will be one row for each match, instead of just the first
• It will include all columns from the lookup table, instead of just a single specified column
• It supports :ref:`more complex join operations <merging.join>`

Other considerations

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

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
    )

Adding a row

Assuming we are using a :class:`~pandas.RangeIndex` (numbered 0, 1, etc.), we can use :meth:`DataFrame.append` to add a row to the bottom of a DataFrame.

.. ipython:: python

    df
    new_row = {"class": "E", "student_count": 51, "all_pass": True}
    df.append(new_row, ignore_index=True)

Find and Replace

Excel's Find dialog takes you to cells that match, one by one. In pandas, this operation is generally done for an entire column or DataFrame at once through :ref:`conditional expressions <10min_tut_03_subset.rows_and_columns>`.

.. ipython:: python

    tips
    tips == "Sun"
    tips["day"].str.contains("S")

pandas' :meth:`~DataFrame.replace` is comparable to Excel's Replace All.

.. ipython:: python

    tips.replace("Thur", "Thu")