1 Reading and Writing Data with Pandas Functions to read data are all named pd.read_* where * is the file type. Series and DataFrames can be saved to disk using their to_* method. Usage Patterns Use pd.read_clipboard() for one-off data extractions. Use the other pd.read_* methods in scripts for repeatable analyses. Reading Text Files into a DataFrame Colors highlight how different arguments map from the data file to a DataFrame. Other arguments: Possible values of parse_dates: • names: Set or override column names [0, 2]: Parse columns 0 and 2 as separate dates • parse_dates: Accepts multiple argument types [[0, 2]]: Group columns 0 and 2 and parse as single date • converters: Manually process each element in a column {'Date': [0, 2]}: Group columns 0 and 2, parse as single date in a • comment: Character indicating commented line column named Date • chunksize: Read only a certain number of rows each time Dates are parsed after the converters have been applied. Parsing Tables from the Web Writing Data Structures to Disk Writing Data Structures from and to a Database Write data structures to disk: Read, using SQLAlchemy. Supports multiple databases: > s_df.to_csv(filename) > from sqlalchemy import create_engine > s_df.to_excel(filename) > engine = create_engine(database_url) Write multiple DataFrames to single Excel file: > conn = engine.connect() > writer = pd.ExcelWriter(filename) > df = pd.read_sql(query_str_or_table_name, conn) > df1.to_excel(writer, sheet_name='First') Write: > df2.to_excel(writer, sheet_name='Second') > df.to_sql(table_name, conn) > writer.save() Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop ©2020 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0 International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com 2 DataFrames (df). s Pandas Data Structures: Series and DataFrames Within Pandas, there are two primary data structures: Series (s) and A Series, which maps an index to values. It can be thought of as an ordered dictionary or a Numpy array with row labels and a len(s_df) s_df.head() and s_df.tail() s.unique() s_df.describ df.info() Number of rows First/last rows Series of unique values Summary stats Memory usage name. df A DataFrame, which maps index and column labels to values. It Indexing and Slicing is like a dictionary of Series (columns) sharing the same index, Use these attributes on Series and DataFrames for indexing, slicing, or like a 2D Numpy array with row and column labels. and assignments: s_df Applies to both Series and DataFrames. s_df.loc[ ] Refers only to the index labels Manipulations of Pandas objects usually return copies. s_df.iloc[ ] Refers only to the integer location, similar to lists or Numpy arrays Creating Series and DataFrames s_df.xs(key, level=L) Select rows with label key in level L of an object with MultiIndex. Values Series n1 'Cary' 0 Masking and Boolean Indexing > pd.Series(values, index=index, name=name) n2 'Lynn' 1 Create masks with comparisons: > pd.Series({'idx1' : val1,'idx2' : val2} n3 'Sam 2 mask = df['X'] < 0 Where values, index, and name are sequences or arrays. Index Or isin, for membership mask: DataFrame mask = df['X'].isin(list_of_valid_values) Age Gender Coulmns > pd.DataFrame(values, Use masks for indexing: 'Cary' 32 M index=index, columns=col_names) df.loc[mask] = 0 'Lynn' 18 F > pd.DataFrame({'col1' : series1_or _seq, 'Sam 26 M Combine multiple masks with bitwise operators — and (&), or (|), or (^), 'col2': series2_or _seq}) Index not (~) — and group them with parentheses: Where values is a sequence of sequences or a 2D array. mask = (df['X'] < 0) & (df['Y'] == 0) Common Indexing and Slicing Patterns Manipulating Series and DataFrames rows and cols can be values, lists, Series, or masks. Manipulating Columns s_df.loc[rows] Some rows (all columns in a DataFrame) df.rename(columns={old_name:new_name}) Renames column df.loc[:, cols_list] All rows, some columns df.drop(name_or_names, axis='columns') Drops column name df.loc[rows, cols] Subset of rows and columns Manipulating Index s_df.loc[mask] Boolean mask of rows (all columns) s_df.reindex(new_index) Conform to new index df.loc[mask, cols] Boolean mask of rows, some columns s_df.drop(labels_to_drop) Drops index labels s_df.rename Using [ ] on Series and DataFrames (index={old_label: new_label}) Renames index labels s_df.sort_index() Sorts index labels On Series, [] refers to the index labels, or to a slice: df.set_index(column_name_or_names) s['a'] Value s_df.reset_index() Inserts index into columns, resets s[:2] Series, first two rows index to default integer index On DataFrames, [ ] refers to columns labels: df['X'] Series Manipulating Values df[['X', 'Y']] DataFrame All row values and the index will follow: df['new_or_old_col'] = series_or_array df.sort_values(col_name, ascending=True) df.sort_values(['X','Y'], ascending=[False, True]) Except with a slice or mask, as shown below: df[:2] DataFrame, first two rows Important Attributes and Methods df[mask] DataFrame, rows where mask is True s_df.index Array-like row labels Never chain brackets df.columns Array-like column labels NO > df[mask]['X'] = 1 s_df.values Numpy array, data SettingWithCopyWarning s_df.shape (n_rows, n_cols) YES > df.loc[mask, 'X'] = 1 s.dtype, df.dtypes Type of Series or of each column Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop ©2020 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0 International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com 3 Computation with Series and DataFrames Pandas objects do not behave exactly like Numpy arrays. They follow three main rules of binary operations. Rule 1: Operations between multiple Pandas objects implement auto-alignment based on index first. Apply a Function to Each Series Apply series_to_* function to every column by default > s1 + s2 > s1.add(s2, fill_value=0) (across rows): s1 s2 s1 s2 df.apply(series_to_value) → Series a 1 NaN a NaN a 1 0 a 1 df.apply(series_to_series) → DataFrame b 2 + b 4 = b 4 b 2 + b 4 = b 6 To apply the function to every row (across columns), set axis=1: NaN c 5 c NaN 0 c 5 c 5 df.apply(series_to_series, axis=1) Use add, sub, mul, and div, to set fill value. Apply a Function to a DataFrame Rule 2: Mathematical operators (+ - * / exp, log, ...) Apply a function that receives a DataFrame and returns a Series, apply element by element on the values. a DataFrame, or a single value: df + 1 df.abs() np.log(df) df.pipe(df_to_series) → Series df.pipe(df_to_df) → DataFrame X Y X Y X Y X Y df.pipe(df_to_value) → Value a -2 -2 a -1 -1 a 1 1 a 0 0 b -2 -2 b -1 -1 b 1 1 b 0 0 What Happens with Missing Values? c -2 -2 c -1 -1 c 1 1 c 0 0 Missing values are represented by NaN (not a number) or NaT (not a time). • They propagate in operations across Pandas objects Rule 3: Reduction operations (mean, std, skew, kurt, sum, prod, ...) are applied column by column by default. (1 + NaN → NaN). > df.sum() Series • They are ignored in a "sensible" way in computations; They equal 0 in sum, they're ignored in mean, etc. X Y > df.sum() X a → Y • They stay NaN with mathematical operations such as np.log(NaN) → NaN. b c count: Number of non-null observations Operates across rows by default (axis=0, or axis='rows'). sum: Sum of values Operate across columns with axis=1 or axis='columns'. mean: Mean of values mad: Mean absolute deviation Differences Between median: Arithmetic median of values Pandas Objects and Numpy Arrays min: Minimum When it comes to Pandas objects and Numpy arrays, aligning objects max: Maximum on the index (or columns) before calculations might be the most mode: Mode important difference. There are built-in methods for most common prod: Product of values statistical operations, such as mean or sum, and they apply across std: Bessel-corrected sample standard deviation one-dimension at a time. To apply custom functions, use one of var: Unbiased variance three methods to do tablewise (pipe), row or column-wise (apply), sem: Standard error of the mean or elementwise (applymap) operations. skew: Sample skewness (3rd moment) Apply a Function to Each Value kurt: Sample kurtosis (4th moment) Apply a function to each value in a Series or DataFrame: quartile: Sample quantile (Value at %) s.apply(value_to_value) → Series value_counts: Count of unique values df.applymap(value_to_value) → DataFrame Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop ©2020 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0 International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com 4 Plotting with Pandas Series and DataFrames Pandas uses Matplotlib to generate figures. Once a figure is generated with Pandas, all of Matplotlib's functions can be used to modify the title, labels, legend, etc. In a Jupyter notebook, all plotting calls for a given plot should be in the same cell. Parts of a Figure Setup An Axes object is what we Import packages: think of as a “plot”. It has a > import pandas as pd title and two Axis objects > import matplotlib.pyplot as plt that define data limits. Each Execute this at IPython prompt to display figures in new windows: Axis can have a label. There > %matplotlib can be multiple Axes objects Use this in Jupyter notebooks to display static images inline: in a Figure. > %matplotlib inline Use this in Jupyter notebooks to display zoomable images inline: > %matplotlib notebook Plotting with Pandas Objects Series DataFrame Labels With a Series, Pandas plots values against With a DataFrame, Pandas creates one line Use Matplotlib to override or add annotations: the index: per column: > ax.set_xlabel('Time') > ax = s.plot() > ax = df.plot() > ax.set_ylabel('Value') > ax.set_title('Experiment A') Note: When plotting the results of complex manipulations with groupby, it's often useful to Pass labels if you want to override the column stack/unstack the resulting DataFrame to fit the one-line-per-column assumption. names and set the legend location: > ax.legend(labels, loc='best') Useful Arguments to Plot • subplots=True: One subplot per column, instead of one line • figsize: Set figure size, in inches • x and y: Plot one column against another Kinds of Plots + df.plot.scatter(x, y) df.plot.bar() df.plot.hist() df.plot.box() Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop ©2020 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0 International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com 5 Manipulating Dates and Times Use a Datetime index for easy time-based indexing and slicing, as well as for powerful resampling and data alignment. Pandas makes a distinction between timestamps, called Datetime objects, and time spans, called Period objects. Converting Objects to Time Objects Creating Ranges of Timestamps Convert different types like strings, lists, or arrays to Datetime with: > pd.date_range(start=None, end=None, > pd.to_datetime(value) periods=None, freq=offset, Convert timestamps to time spans and set the period “duration” with tz='Europe/London') frequency offset. Specify either a start or end date, or both. Set number of "steps" with > date_obj.to_period(freq=freq_offset) periods. Set "step size" with freq. Specify time zones with tz. Frequency Offsets Save Yourself Some Pain: Use ISO 8601 Format Used by date_range, period_range and resample: To be consistent and minimize the risk of error or confusion, use ISO • B: Business day • A: Year end format YYYY-MM-DD when entering dates: • D: Calendar day • AS: Year start NO > pd.to_datetime('12/01/2000') # 1st December • W: Weekly • H: Hourly Timestamp('2000-12-01 00:00:00') • M: Month end • S: Secondly NO > pd.to_datetime('13/01/2000') # 13th January! • MS: Month start • L, ms: Milliseconds Timestamp('2000-01-13 00:00:00') • BM: Business month end • U, us: Microseconds YES > pd.to_datetime('2000-01-13') # 13th January • Q: Quarter end • N: Nanoseconds Timestamp('2000-01-13 00:00:00') Creating Ranges of Periods For more, look up "Pandas Offset Aliases" or check out the pandas. > pd.period_range(start=None, end=None, tseries.offsets and pandas.tseries.holiday modules. periods=None, freq=offset) Timestamps vs Periods Resampling > s_df.resample(freq_offset).mean() resample returns a groupby-like object that must be aggregated with mean, sum, std, apply, etc. (See also the Split-Apply-Combine cheat sheet.) VECTORIZED STRING OPERATIONS Pandas implements vectorized string operations named Splitting and Replacing after Python's string methods. Access them through the Split returns a Series of lists: str attribute of string Series. > s.str.split() Some String Methods Access an element of each list with get: > s.str.split(char).str.get(1) > s.str.lower() > s.str.strip() > s.str.isupper() > s.str.normalize() Return a DataFrame instead of a list: > s.str.split(expand=True) > s.str.len() Find and replace with string or regular expressions: Index by character position: > s.str.replace(str_or_regex, new) > s.str[0] > s.str.extract(regex) True if a regular expression pattern or string is in a Series: > s.str.findall(regex) > s.str.contains(str_or_pattern) Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop ©2020 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0 International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com 6 Combining DataFrames There are numerous tools for combining Series and DataFrames together, with SQL-type joins and concatena- tion. Use join if merging on indices, otherwise use merge. Join on Index > df.join(other) Merge DataFrames on indexes. Set on=columns to join on index of other and on columns of df. join uses pd.merge under the covers. Merge on Column Values > pd.merge(left, right, how='inner', on='id') Concatenating DataFrames Ignores index, unless on=None. See the section on the how keyword. > pd.concat(df_list) Use on if merging on same column in both DataFrames, otherwise use “Stacks” DataFrames on top of each other. left_on, right_on. Set ignore_index=True to replace index with RangeIndex. Note: Faster than repeated df.append(other_df). MERGE TYPES: THE HOW KEYWORD left left_on='X' right_on='Y' right LONG X LONG X Y SHORT Y SHORT 0 aaaa a 0 aaaa a — — 0 b bb left right how="outer" 1 bbbb b 1 bbbb b b bb 1 c cc 2 — — c cc LONG X LONG X Y SHORT Y SHORT left right how="inner" 0 aaaa a 0 bbbb b b bb 0 b bb 1 bbbb b 1 c cc LONG X LONG X Y SHORT Y SHORT left right how="left" 0 aaaa a 0 aaaa a — — 0 b bb 1 bbbb b 1 bbbb b b bb 1 c cc LONG X LONG X Y SHORT Y SHORT left right how="right" 0 aaaa a 0 bbbb b b bb 0 b bb 1 bbbb b 1 — — c cc 1 c ctc CLEANING DATA WITH MISSING VALUES Pandas represents missing values To find missing values, use: as NaN (Not a Number), which comes > s_df.isnull() or > pd.isnull(obj) from Numpy and is of type float64. > s_df.notnull() or > pd.notnull(obj) To find and replace these missing To replace missing values, use: values, you can use any number s_df.loc[s_df.isnull()] = 0 Use mask to replace NaN of methods. s_df.interpolate(method='linear') Interpolate using different methods s_df.fillna(method='ffill') Fill forward (last valid value) s_df.fillna(method='bfill') Or backward (next valid value) s_df.dropna(how='any') Drop rows if any value is NaN s_df.dropna(how='all') Drop rows if all values are NaN s_df.dropna(how='all', axis=1) Drop across columns instead of rows Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop ©2020 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0 International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com 7 1. Split the data based on some criteria. Split / Apply / Combine with DataFrames 2. Apply a function to each group to aggregate, transform, or filter. Apply/Combine: Aggregation Perform computations on each group. The shape changes; the categories in the grouping columns become the index. Can use built- 3. Combine the results. in aggregation methods: mean, sum, size, count, std, var, sem, describe, first, The apply and combine steps are last, nth, min, max, for example: typically done together in Pandas. > g.mean() Apply/Combine: Filtering ... or aggregate using custom function: Split: Group By > g.agg(series_to_value) Returns a group only if condition is true. Group by a single column: > g.filter(lambda x: len(x)>1) ... or aggregate with multiple functions at once: > g = df.groupby(col_name) > g.agg([s_to_v1, s_to_v2]) Grouping with list of column names creates a ... or use different functions on different DataFrame with a MultiIndex: columns: > g = df.groupby(list_col_names) > g.agg({'Y': s_to_v1, 'Z': s_to_ Pass a function to group based on the index: v2}) > g = df.groupby(function) Z 0 a 2 a Z 1 b a Split/Apply/Combine 3 b b c Z 4 ca Other Groupby-Like Operations: Window Functions Apply/Combine: • resample, rolling, and ewm (exponential General Tool: apply weighted function) methods behave like apply is more general than agg, transform, GroupBy objects. They keep track of which and filter. It can aggregate, transform or Split Apply Combine row is in which “group.” Results must be filter. The resulting dimensions can change, • Groupby • Apply • Window • Group-specific aggregated with sum, mean, count, etc. for example: Functions transformations • resample is often used before rolling, > g.apply(lambda x: x.describe()) • Aggregation expanding, and ewm when using a Date- • Group-specific Filtering Time index. Apply/Combine: Transformation The shape and the index do not change. Split: What’s a GroupBy Object? > g.transform(df_to_df) It keeps track of which rows are part of Example, normalization: which group. > def normalize(grp): > g.groups → Dictionary, where keys are . return ( group names, and values are indices of rows in . (grp - grp.mean()) a given group. . / grp.var() It is iterable: . ) > for group, sub_df in g: . ... > def normalize(grp): . return ((grp - grp.mean()) . / grp.var()) Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop ©2020 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0 International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com 8 Reshaping DataFrames and Pivot Tables Let’s explore some tools for reshaping DataFrames from the wide to the long format and back. The long format can be tidy, which means that each variable is a column, each observation is a row. It is easier to filter, aggregate, transform, sort, and pivot. Reshaping operations often produces multi-level indices or columns, which can be sliced and indexed. MultiIndex: A Multi-Level Hierarchical Index Long to Wide Format and Back with stack() and unstack() Often created as a result of: > df.groupby(list_of_columns) Pivot column level to index, i.e. “stacking the columns” (wide to long): > df.set_index(list_of_columns) > df.stack() Contiguous labels are displayed together but apply to each row. The Pivot index level to columns, “unstack the columns” (long to wide): concept is similar to multi-level columns. > df.unstack() A MultiIndex allows indexing and slicing one or multiple levels at once. If there are multiple indices or column levels, use level number or Using the Long example from the right: name to stack/unstack: long.loc[1900] All 1900 rows > df.unstack(1) or > df.unstack('Month') long.loc[(1900, 'March')] Value 2 A common use case for unstacking, plotting group data vs index long.xs('March', level='Month') All March rows after groupby: > (df.groupby(['A', 'B])['relevant'].mean() Simpler than using boolean indexing, for example: .unstack().plot()) > long[long.Month == 'March'] Pivot Tables > pd.pivot_table(df, . index=cols, keys to group by for index . columns=cols2, keys to group by for columns . values=cols3, columns to aggregate . aggfunc='mean') what to do with repeated values From Wide to Long with melt Specify which columns are identifiers (id_vars, values will be repeated Omitting index, columns, or values will use all remaining for each row) and which are “measured variables” (value_vars, will columns of df. You can “pivot” a table manually using groupby, stack, become values in variable column. All remaining columns by default). and unstack. > pd.melt(df, id_vars=id_cols, value_vars=value_columns) > pd.melt(team, id_vars=['Color'], . value_vars=['A', 'B', 'C'], . var_name='Team', . value_name='Score') df.pivot() vs pd.pivot_table df.pivot() Does not deal with repeated values in index. It's a declarative form of stack and unstack. pd.pivot_table() Use if you have repeated values in index (specify aggfunc argument). Take your Pandas skills to the next level! Register at enthought.com/pandas-mastery-workshop ©2020 Enthought, Inc., licensed under the Creative Commons Attribution – Non-Commercial, No Derivatives 4.0 International License. To view a copy of this license, visit creativecommons.org/licenses/by-nc-nd/4.0/ www.enthought.com