Created
January 12, 2017 12:21
-
-
Save zhouhoo/fe4ceaffac4357c6c9a2b85c014af4a9 to your computer and use it in GitHub Desktop.
Useful pandas snippets
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Next few examples show how to work with text data in Pandas. | |
| # Full list of .str functions: http://pandas.pydata.org/pandas-docs/stable/text.html | |
| # Slice values in a DataFrame column (aka Series) | |
| df.column.str[0:2] | |
| # Lower-case everything in a DataFrame column | |
| df.column_name = df.column_name.str.lower() | |
| # Get length of data in a DataFrame column | |
| df.column_name.str.len() | |
| # Sort dataframe by multiple columns | |
| df = df.sort(['col1','col2','col3'],ascending=[1,1,0]) | |
| # Get top n for each group of columns in a sorted dataframe | |
| # (make sure dataframe is sorted first) | |
| top5 = df.groupby(['groupingcol1', 'groupingcol2']).head(5) | |
| # Grab DataFrame rows where specific column is null/notnull | |
| newdf = df[df['column'].isnull()] | |
| # Select from DataFrame using multiple keys of a hierarchical index | |
| df.xs(('index level 1 value','index level 2 value'), level=('level 1','level 2')) | |
| # Change all NaNs to None (useful before | |
| # loading to a db) | |
| df = df.where((pd.notnull(df)), None) | |
| # Get quick count of rows in a DataFrame | |
| len(df.index) | |
| # Pivot data (with flexibility about what what | |
| # becomes a column and what stays a row). | |
| # Syntax works on Pandas >= .14 | |
| pd.pivot_table( | |
| df,values='cell_value', | |
| index=['col1', 'col2', 'col3'], #these stay as columns; will fail silently if any of these cols have null values | |
| columns=['col4']) #data values in this column become their own column | |
| # Change data type of DataFrame column | |
| df.column_name = df.column_name.astype(np.int64) | |
| # Get rid of non-numeric values throughout a DataFrame: | |
| for col in refunds.columns.values: | |
| refunds[col] = refunds[col].replace('[^0-9]+.-', '', regex=True) | |
| # Set DataFrame column values based on other column values (h/t: @mlevkov) | |
| df.loc[(df['column1'] == some_value) & (df['column2'] == some_other_value), ['column_to_change']] = new_value | |
| # Clean up missing values in multiple DataFrame columns | |
| df = df.fillna({ | |
| 'col1': 'missing', | |
| 'col2': '99.999', | |
| 'col3': '999', | |
| 'col4': 'missing', | |
| 'col5': 'missing', | |
| 'col6': '99' | |
| }) | |
| # Concatenate two DataFrame columns into a new, single column | |
| # (useful when dealing with composite keys, for example) | |
| df['newcol'] = df['col1'].map(str) + df['col2'].map(str) | |
| # Doing calculations with DataFrame columns that have missing values | |
| # In example below, swap in 0 for df['col1'] cells that contain null | |
| df['new_col'] = np.where(pd.isnull(df['col1']),0,df['col1']) + df['col2'] | |
| # Split delimited values in a DataFrame column into two new columns | |
| df['new_col1'], df['new_col2'] = zip(*df['original_col'].apply(lambda x: x.split(': ', 1))) | |
| # Collapse hierarchical column indexes | |
| df.columns = df.columns.get_level_values(0) | |
| # Convert Django queryset to DataFrame | |
| qs = DjangoModelName.objects.all() | |
| q = qs.values() | |
| df = pd.DataFrame.from_records(q) | |
| # Create a DataFrame from a Python dictionary | |
| df = pd.DataFrame(list(a_dictionary.items()), columns = ['column1', 'column2']) | |
| # Get a report of all duplicate records in a dataframe, based on specific columns | |
| dupes = df[df.duplicated(['col1', 'col2', 'col3'], keep=False)] | |
| # Set up formatting so larger numbers aren't displayed in scientific notation (h/t @thecapacity) | |
| pd.set_option('display.float_format', lambda x: '%.3f' % x) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment