Data Analysis and Plotting in Python with Pandas

Carolin Penke, Jülich Supercomputing Centre, Forschungszentrum Jülich, 23 October 2024 Based on material by Andreas Herten

Version: Slides

My Motivation

• I like Python
• I like plotting data
• I like sharing
• I think Pandas is awesome and you should use it too
• …but I'm no Python expert!

Motto: »Pandas as early as possible!«

Task Outline

• Task 1
• Task 2
• Task 3
• Task 4
• Task 5
• Task 6
• Task 7
• Task 7B
• Task 8
• Task 8B

Course Setup

• 3½ hours, including break around 10:30
• Alternating between lecture and hands-on
• Please give status of hands-ons via 👍 as BigBlueButton status
• TAs and me in the room can help with issues, either in public chat or in 1:1 chat

• Please now open Jupyter Notebook of this session: https://go.fzj.de/jsc-pd
• Give thumbs up! 👍

About Pandas

• Python package
• For data analysis and manipulation
• With data structures (multi-dimensional table; time series), operations
• Name from »Panel Data« (multi-dimensional time series in economics)
• Since 2008
• Now at Pandas 2.2.3
• https://pandas.pydata.org/
• Install via PyPI: pip install pandas
• Cheatsheet: https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf

Pandas Cohabitation

• Pandas works great together with other established Python tools
  • Jupyter Notebooks
  • Plotting with matplotlib
  • Numerical analysis with numpy
  • Modelling with statsmodels, scikit-learn
  • Nicer plots with seaborn, altair, plotly
  • Performance enhancement with Cython, Numba, …
• Tools building up on Pandas: cuDF (GPU-accelerated DataFrames in Rapids, now as drop-in replacement), pyarrow (Apache Arrow bindings in Python) …
• Faster alternatives with similar syntax: Polars, …

First Steps

DataFrames

It's all about DataFrames

• Data containers of Pandas:
  • Linear: Series
  • Multi Dimension: DataFrame
• Series is only special (1D) case of DataFrame
• → We use DataFrames as the more general case here

DataFrames

Construction

• To show features of DataFrame, let's construct one and show by example!
• Many construction possibilities
  • From lists, dictionaries, numpy objects
  • From CSV, HDF5, JSON, Excel, HTML, fixed-width files
  • From pickled Pandas data
  • From clipboard
  • From Feather, Parquet, SAS, SQL, Google BigQuery, STATA

DataFrames

Examples, finally

• Let's add names to ages; put everything into a dict()

• Automatically creates columns from dictionary
• Two columns now; one for names, one for ages

• First column is index
• DataFrame always have indexes; auto-generated or custom

• Make Name be index with .set_index()
• inplace=True will modifiy the parent frame (I don't like it)

• Some more operations

• Also: Arithmetic operations

Logical operations allowed as well

Task 1

TASK

• Create data frame with
  • 6 names of dinosaurs,
  • their favourite prime number,
  • and their favorite color.
• Play around with the frame
• Tell me when you're done with status icon in BigBlueButton: 👍

More DataFrame examples

Reading External Data

(Links to documentation)

• .read_json()
• .read_csv()
• .read_hdf5()
• .read_excel()

Example:

{
    "Character": ["Sawyer", "…", "Walt"],
    "Actor": ["Josh Holloway", "…", "Malcolm David Kelley"],
    "Main Cast": [true,  "…", false]
}

Task 2

TASK

• Read in data-nest.csv to DataFrame; call it df
  (Data was produced with JUBE)
• Get to know it and play a bit with it
• Tell me when you're done with status icon in BigBlueButton: 👍

Read CSV Options

• See also full API documentation
• Important parameters
  • sep: Set separator (for example : instead of ,)
  • header: Specify info about headers for columns; able to use multi-index for columns!
  • names: Alternative to header – provide your own column titles
  • usecols: Don't read whole set of columns, but only these; works with any list (range(0:20:2))…
  • skiprows: Don't read in these rows
  • na_values: What string(s) to recognize as N/A values (which will be ignored during operations on data frame)
  • parse_dates: Try to parse dates in CSV; different behaviours as to provided data structure; optionally used together with date_parser
  • compression: Treat input file as compressed file ("infer", "gzip", "zip", …)
  • decimal: Decimal point divider – for German data…

pandas.read_csv(filepath_or_buffer, *, sep=_NoDefault.no_default, delimiter=None, header='infer', names=_NoDefault.no_default, index_col=None, usecols=None, dtype=None, engine=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=_NoDefault.no_default, keep_date_col=False, date_parser=_NoDefault.no_default, date_format=None, dayfirst=False, cache_dates=True, iterator=False, chunksize=None, compression='infer', thousands=None, decimal='.', lineterminator=None, quotechar='"', quoting=0, doublequote=True, escapechar=None, comment=None, encoding=None, encoding_errors='strict', dialect=None, on_bad_lines='error', delim_whitespace=False, low_memory=True, memory_map=False, float_precision=None, storage_options=None, dtype_backend=_NoDefault.no_default)

Slicing of Data Frames

• Slicing: Select a sub-range / sub-set of entire data frame
• Pandas documentation: Detailed documentation, short documentation

Quick Slices

• Use square-bracket operators to slice data frame quickly: []
  • Use column name to select column
  • Use numerical value to select row
• Example: Select only columnn C from df_demo

• Instead of column name in quotes and square brackets: Name of column directly

• I'm not a friend, because no spaces allowed
  (And Pandas as early as possible means labelling columns well and adding spaces)

• Select more than one column by providing list to slice operator []
• Example: Select list of columns A and C, ['A', 'C'] from df_demo

• Use numerical values in brackets to slice along rows
• Use ranges just like with Python lists

• Attention: location might change after re-sorting!

Slicing of Data Frames

Better Slicing

• .iloc[] and .loc[]: Faster slicing interfaces with more options

• Also slice along columns (second argument)

• .iloc[]: Slice by position (numerical/integer)
• .loc[]: Slice by label (named)
• See difference with a proper index (and not the auto-generated default index from before)

Slicing of Data Frames

Advanced Slicing: Logical Slicing

• Slice can also be array of booleans

Adding to Existing Data Frame

• Add new columns with frame["new col"] = something or .insert()
• Combine data frames
  • Concat: Combine several data frames along an axis
  • Merge: Combine data frames on basis of common columns; database-style
  • (Join)
  • See user guide on merging

• .insert() allows to specify position of insertion
• .shape gives tuple of size of data frame, vertical, horizontal

Combining Frames

• First, create some simpler data frame to show .concat() and .merge()

• Concatenate list of data frame vertically (axis=0)

• Same, but re-index

• Concat, but horizontally

• Merge on common column

.concat() can also be used to append rows to a DataFrame:

Task 3

TASK

• Add a column to the Nest data frame form Task 2 called Threads which is the total number of threads across all nodes (i.e. the product of threads per task and tasks per node and nodes)
• Tell me when you're done with status icon in BigBlueButton: 👍

Aside: Plotting without Pandas

Matplotlib 101

• Matplotlib: de-facto standard for plotting in Python
• Main interface: pyplot; provides MATLAB-like interface
• Better: Use object-oriented API with Figure and Axis
• Great integration into Jupyter Notebooks
• Since v. 3: Only support for Python 3
• → https://matplotlib.org/

• Plot multiple lines into one canvas
• Call ax.plot() multiple times

• Matplotlib can also plot DataFrame data
• Because DataFrame data is only array-like data with stuff on top

Task 4

TASK

• Sort the Nest data frame by threads
• Plot "Presim. Time / s" and "Sim. Time / s" of our data frame df as a function of threads
• Use a dashed, red line for "Presim. Time / s", a blue line for "Sim. Time / s" (see API description)
• Don't forget to label your axes and to add a legend (1st rule of plotting)
• Tell me when you're done with status icon in BigBlueButton: 👍

Plotting with Pandas

• Each data frame has a .plot() function (see API)
• Plots with Matplotlib
• Important API options:
  • kind: 'line' (default), 'bar[h]', 'hist', 'box', 'kde', 'scatter', 'hexbin'
  • subplots: Make a sub-plot for each column (good together with sharex, sharey)
  • figsize
  • grid: Add a grid to plot (use Matplotlib options)
  • style: Line style per column (accepts list or dict)
  • logx, logy, loglog: Logarithmic plots
  • xticks, yticks: Use values for ticks
  • xlim, ylim: Limits of axes
  • yerr, xerr: Add uncertainty to data points
  • stacked: Stack a bar plot
  • secondary_y: Use a secondary y axis for this plot
  • Labeling
    • title: Add title to plot (Use a list of strings if subplots=True)
    • legend: Add a legend
    • table: If true, add table of data under plot
  • **kwds: Non-parsed keyword passed to Matplotlib's plotting methods

• Either slice and plot…

• … or plot and select

• I prefer slicing first:
  → Allows for further operations on the sliced data frame

• There are pseudo-sub-functions for each of the plot kinds
• I prefer to just call .plot(kind="smthng")

Task 5

TASK

Use the Nest data frame df to:

1. Make threads index of the data frame (.set_index())
2. Plot "Presim. Time / s" and "Sim. Time / s" individually
3. Plot them onto one common canvas!
4. Make them have the same line colors and styles as before
5. Add a legend, add missing axes labels
6. Tell me when you're done with status icon in BigBlueButton: 👍

More Plotting with Pandas

Recap: Our first proper Pandas plot

• That's why I think Pandas is great!
• It has great defaults to quickly plot data; basically publication-grade already
• Plotting functionality is very versatile
• Before plotting, data can be massaged within data frames, if needed

More Plotting with Pandas

Some versatility

Combine Pandas with Matplotlib

• Pandas shortcuts very handy
• But sometimes, one needs to access underlying Matplotlib functionality
• No problemo!
• Option 1: Pandas always returns axis
  • Use this to manipulate the canvas
  • Get underlying figure with ax.get_figure() (for fig.savefig())
• Option 2: Create figure and axes with Matplotlib, use when drawing
  • .plot(): Use ax option

Option 1: Pandas Returns Axis

Option 2: Draw on Matplotlib Axes

• We can also get fancy!

Aside: Seaborn

• Python package on top of Matplotlib
• Powerful API shortcuts for plotting of statistical data
• Manipulate color palettes
• Works well together with Pandas
• Also: New, well-looking defaults for Matplotlib (IMHO)
• → https://seaborn.pydata.org/

Seaborn Color Palette Example

• Documentation

Seaborn Plot Examples

• Most of the time, I use a regression plot from Seaborn

• A joint plot combines two plots relating to distribution of values into one
• Very handy for showing a fuller picture of two-dimensionally scattered variables

Task 6

TASK

• To your df Nest data frame, add a column with the unaccounted time (Unaccounted Time / s), which is the difference of program runtime, average neuron build time, minimal edge build time, minimal initialization time, presimulation time, and simulation time.
  (I know this is technically not super correct, but it will do for our example.)
• Plot a stacked bar plot of all these columns (except for program runtime) over the threads
• Tell me when you're done with status icon in BigBlueButton: 👍

• Make it relative to the total program run time
• Slight complication: Our threads as indexes are not unique; we need to find new unique indexes
• Could be anythig, but we use a multi index!

Next Level: Hierarchical Data

• MultiIndex only a first level
• More powerful:
  • Grouping: .groupby() ("Split-apply-combine", API, User Guide)
  • Pivoting: .pivot_table() (API, User Guide); also .pivot() (specialized version of .pivot_table(), API)

Grouping

• Group a frame by common values of column(s)
• Use operations on this group
• Grouped frame is not directly a new frame, but only through an applied operation

Pivoting

• Combine categorically-similar columns
• Creates hierarchical index
• Respected during plotting with Pandas!
• A pivot table has three layers; if confused, think about the related questions
  • index: »What's on the x axis?«
  • values: »What value do I want to plot [on the y axis]?«
  • columns: »What categories do I want [to be in the legend]?«
• All can be populated from base data frame
• Might be aggregated, if needed

Task 7

TASK

• Create a pivot table based on the Nest df data frame
• Let the x axis show the number of nodes; display the values of the simulation time "Sim. Time / s" for the tasks per node and threads per task configurations
• Please plot a bar plot
• Tell me when you're done with status icon in BigBlueButton: 👍

Task 7B (like Bonus)

TASK

• Same pivot table as before (that is, x with nodes, and columns for Tasks/Node and Threads/Task)
• But now, use Sim. Time / s and Presim. Time / s as values to show
• Show them as a stack of those two values inside the pivot table
• Use Panda's functionality as much as possible!

Pandas 2

• Pandas 2.0 was released in April 2023

• Only limited deprecations (i.e. an upgrade is probably safe)

• Key new feature: Apache Arrow support (via PyArrow)

• Fine-grained installation options python3 -m pip install 'pandas[performance, excel]'

• However: Currently [10/2024] a dependency mismatch in default version

• Get a reasonably large data source (larger would be better, though)
• Example: Train stations as provided by Deutsche Bahn

Polars

Large Data & Mangling

• Pandas can read data directly in tar form
• Pandas can read data directly from online resource
• Let's combine that to an advanced task!
• It works also with the PyArrow backend (remember to download the online resource when testing; there is no cache!)

Task 8 (Super Bonus)

TASK

• Create bar chart of top 10 actors (on x) and average ratings of their top movies (y) based on IMDb data (only if they play in at least two movies)

• IMDb provides data sets at datasets.imdbws.com
• Can directly be loaded like

pd.read_table('https://datasets.imdbws.com/dataset.tsv.gz', sep="\\t", low_memory=False, na_values=["\\\\N","nan"])

• Needed:
  • name.basics.tsv.gz (for names of actors and movies they are known for)
  • title.ratings.tsv.gz (for ratings of titles)
• Strategy suggestions:
  • Use df.apply() with custom function
  • Custom function: Compute average rating and determine if this entry is eligible for plotting (this can be done at once, but does not need to be)
  • Average rating: Look up title IDs as listed in knownForTitles in titles dataframe

df_names = pd.read_table('imdb-data/name.basics.tsv.gz', sep="\\t", low_memory=False, na_values=["\\\\N","nan"])
df_ratings = pd.read_table('https://datasets.imdbws.com/title.ratings.tsv.gz', sep="\\t", low_memory=False, na_values=["\\\\N","nan"])
                           
df_names_i = df_names.set_index('nconst')
df_ratings_i = df_ratings.set_index('tconst')
                           
df_names_i = pd.concat(
    [
        df_names_i, 
        df_names_i.apply(lambda line: valid_and_avg_rating(line), axis=1, result_type='expand')
    ]
    , axis=1
)
df_names_i[df_names_i['toPlot'] == True].sort_values('avgRating', ascending=False).iloc[0:10].reset_index().set_index('primaryName')['avgRating'].plot(kind='bar')

def valid_and_avg_rating(row):
    rating = 0
    ntitles = 0
    _titles = row['knownForTitles']
    _professions = row['primaryProfession']
    if not isinstance(_titles, str):
        _titles = str(_titles)
    if not isinstance(_professions, str):
        _professions = str(_professions)
    titles = _titles.split(',')
    professions = _professions.split(',')
    for title in titles:
        if title in df_ratings_i.index:
            rating += df_ratings_i.loc[title]['averageRating']
            ntitles += 1
    if ntitles > 0:
        plot = False
        if ntitles > 2:
            if 'actor' in professions:
                plot = True
        return {'toPlot': plot, 'avgRating': rating / ntitles}
    else:
        return {'toPlot': False, 'avgRating': pd.NA}

Task 8B (Bonuseption)

TASK

All of the following are ideas for unique sub-tasks, which can be done individually

• In addition to Task 8, restrict the top titles to those with more than 10000 votes
• For 30 top-rated actors, plot rating vs. age
• For 30 top-rated actors, plot rating vs. average runtime of the known-for-titles (using title.basics.tsv.gz)

Random Features Not Shown

This are all links:

• df.drop()
• df.corr()
• df.boxplot()
• pd.read_sql_query("SELECT * FROM purchases", con)
• df.duplicated() and df.drop_duplicates()
• Aliases for categorical data
• Working with time
  • ts.tz_convert
  • pd.period_range()
  • pd.period_range().asfreq()

Conclusion

• Pandas works with and on data frames, which are central
• Slice frames to your likings
• Plot frames
  • Together with Matplotlib, Seaborn, others
• Pivot tables are next level greatness
• Remember: Pandas as early as possible!
• Thanks for being here! 😍

Feedback to a.herten@fz-juelich.de

Next slide: Further reading

Further Reading

• Pandas User Guide
• Matplotlib and LaTeX Plots
• towardsdatascience.com:
  • Pandas DataFrame: A lightweight Intro
  • Introduction to Data Visualization in Python
  • Basic Time Series Manipulation with Pandas
  • An Introduction to Scikit Learn: The Gold Standard of Python Machine Learning
  • Mapping with Matplotlib, Pandas, Geopandas and Basemap in Python
  • Whats new in Pandas 2