.. _compare_with_spreadsheets: {{ 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. .. include:: includes/introduction.rst Data structures --------------- General terminology translation ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. csv-table:: :header: "pandas", "Excel" :widths: 20, 20 ``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 ``DataFrame``\s 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` for much more on how to use an ``Index`` effectively. Copies vs. in place operations ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. include:: includes/copies.rst Data input / output ------------------- Constructing a DataFrame from values ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ In a spreadsheet, `values can be typed directly into cells `_. .. include:: includes/construct_dataframe.rst 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.githubusercontent.com/pandas-dev" "/pandas/main/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: .. code-block:: python 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 `. Let's first :ref:`create a new Excel file ` based on the ``tips`` dataframe in the above example: .. code-block:: python 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 .. code-block:: python tips_df = pd.read_excel("./tips.xlsx", index_col=0) 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 ``DataFrame``\s are displayed. .. include:: includes/limit.rst 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 `, :ref:`CSV `, or :ref:`a number of other formats `. 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. .. include:: includes/column_operations.rst 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. `_ .. image:: ../../_static/spreadsheets/filter.png :alt: Screenshot showing filtering of the total_bill column to values greater than 10 :align: center .. include:: includes/filtering.rst 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. .. image:: ../../_static/spreadsheets/conditional.png :alt: Screenshot showing the formula from above in a bucket column of the tips spreadsheet :align: center .. include:: includes/if_then.rst 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 ` 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: .. list-table:: :header-rows: 1 :widths: auto * - 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. .. include:: includes/time_date.rst 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. .. include:: includes/column_selection.rst Sorting by values ~~~~~~~~~~~~~~~~~ Sorting in spreadsheets is accomplished via `the sort dialog `_. .. image:: ../../_static/spreadsheets/sort.png :alt: Screenshot of dialog from Excel showing sorting by the sex then total_bill columns :align: center .. include:: includes/sorting.rst 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)) .. include:: includes/length.rst 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``. .. image:: ../../_static/spreadsheets/sort.png :alt: Screenshot of FIND formula being used in Excel :align: center .. include:: includes/find_substring.rst 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) .. include:: includes/extract_substring.rst 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 `_.) .. include:: includes/nth_word.rst Changing case ~~~~~~~~~~~~~ Spreadsheets provide `UPPER, LOWER, and PROPER functions `_ for converting text to upper, lower, and title case, respectively. .. include:: includes/case.rst Merging ------- .. include:: includes/merge_setup.rst In Excel, there are `merging of tables can be done through a VLOOKUP `_. .. image:: ../../_static/spreadsheets/vlookup.png :alt: Screenshot showing a VLOOKUP formula between two tables in Excel, with some values being filled in and others with "#N/A" :align: center .. include:: includes/merge.rst ``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 ` 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: .. image:: ../../_static/spreadsheets/pivot.png :alt: Screenshot showing a PivotTable in Excel, using sex as the column, size as the rows, then average tip as the values :align: center 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 :func:`concat` to add a row to the bottom of a ``DataFrame``. .. ipython:: python df new_row = pd.DataFrame([["E", 51, True]], columns=["class", "student_count", "all_pass"]) pd.concat([df, new_row]) 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("Thu", "Thursday")