<jupyter_start><jupyter_text>Pandas TricksA Notebook to collect useful code snippets for various data munging tasks in Pandas. 1. [Reading CSV](read_csv)1. [Value Counts](value_counts)1. [Select columns by type](select_types)1. [Using `map` to transform columns](map)1. [Using `apply` for multi-column functions by row](apply)1. [Missing values](missing_values)1. [Unique values](unique)1. [SQL where clause-like row selection](SQL)1. [Rename columns](rename)1. [Drop columns](drop_cols)1. [Sort DataFrame](sort)1. [Find row containing min/max of columns](find_max)1. [Iterate over rows of a DataFrame](iterate_rows)1. [Percentiles of a column](percentiles)1. [Missing Dates in Time Series](TS_Missing_Dates)Below is a collection of sources I've used:1. [10 Python Pandas tricks that make your work more efficient](https://towardsdatascience.com/10-python-pandas-tricks-that-make-your-work-more-efficient-2e8e483808ba)1. [A step-by-step guide for creating advanced Python data visualizations with Seaborn / Matplotlib](https://towardsdatascience.com/a-step-by-step-guide-for-creating-advanced-python-data-visualizations-with-seaborn-matplotlib-1579d6a1a7d0)1. [Pandas tips and tricks](https://towardsdatascience.com/pandas-tips-and-tricks-33bcc8a40bb9)1. [Pandas for time series data — tricks and tips](https://medium.com/@bingobee01/pandas-tricks-and-tips-a7b87c3748ea)1. [Python Pandas tips and tricks for data science](http://behsys.com/mohsen/Python-Pandas-tips-tricks-for-data-science.html)1. [12 Useful Pandas Techniques in Python for Data Manipulation](https://www.analyticsvidhya.com/blog/2016/01/12-pandas-techniques-python-data-manipulation/)1. [Basic Time Series Manipulation with Pandas](https://towardsdatascience.com/basic-time-series-manipulation-with-pandas-4432afee64ea)<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Useful `read_csv` optionsThe file `census.csv` contains a small sample of data from the Adult census dataset.<jupyter_code># Only read a few lines using 'nrows'
df = pd.read_csv('census.csv', nrows=5)
df
# Only read selected columns using 'usecols'
all_cols = df.columns.tolist()
print('All columns:', all_cols)
selected_cols = all_cols[:4]
pd.read_csv('census.csv', usecols=selected_cols).head()
# Specify types using 'dtype'

col_types = {
    'age': int,
    'workclass': str,
    'cap-gain': float,
    'cap-loss': float
}
df = pd.read_csv('census.csv', dtype=col_types)
df.head()
# Squash spaces - especially important for headers and str fields!
df = pd.read_csv('census.csv', dtype=col_types, skipinitialspace=True)
df.head()<jupyter_output><empty_output><jupyter_text>Value Counts<jupyter_code># Basic
df['workclass'].value_counts()
# Stats by distinct values
df['workclass'].value_counts().sort_index()
# Frequency instead of counts
df['workclass'].value_counts(normalize=True)
# Create DataFrame
df['workclass'].value_counts().reset_index()<jupyter_output><empty_output><jupyter_text>Unique values<jupyter_code>df.workclass.unique()<jupyter_output><empty_output><jupyter_text>SQL-like selection of rows<jupyter_code># Make sure to wrap conditions in parenthesis if multiple clauses
df_where = (df['workclass'].isin(['Federal-gov', 'State-gov', 'Local-gov'])) & (df['age'] >=30)
df[df_where]<jupyter_output><empty_output><jupyter_text>Select columns of specific types in a DataFrame<jupyter_code># Let's look at the types in the DataFrame
print(df.dtypes.value_counts())

# Only the integers
df.select_dtypes(include=['int64']).head()<jupyter_output>object     9
int64      4
float64    2
dtype: int64<jupyter_text>Use `map` to transform values in a series in useful ways<jupyter_code># What values do we see in race column
print(df['race'].value_counts())

# Map to binary. Note what happens to non-matches
race_map = {
    'White': 'Other',
    'Black': 'Black'
}

df['race'].map(race_map).value_counts(dropna=False)
# Using a function
def age_map(age: int):
    if(age < 16):
        rv = 'child'
    elif(age < 25):
        rv = 'young adult'
    elif(age < 40):
        rv = 'adult'
    elif(age < 65):
        rv = 'middle-aged'
    else:
        rv = 'elderly'
    return(rv)

df['age-map'] = df['age'].map(age_map)
print(df['age-map'].value_counts())
df[['age', 'age-map']].head()<jupyter_output>middle-aged    19
adult          17
young adult     8
Name: age-map, dtype: int64<jupyter_text>Using `apply` to implement function by row using multiple columns<jupyter_code># Note setting axis to apply by row, not column
df['investments'] = df.apply(lambda x: (x['cap-gain']>0 or x['cap-loss']>0), axis=1)
df[['cap-gain', 'cap-loss', 'investments']].head()<jupyter_output><empty_output><jupyter_text>Finding and processing missing values<jupyter_code># Create some missing values using map
df2 = df.copy()
df2['age-young'] = df2['age'].map(lambda x: x if(x > 30) else np.nan)
df2['US'] =df2['country'].map({'United-States':'US'})
df2[['age-young', 'US']].head()
# Missing data in columns
df2.isna().any()
# Columns with missing data
df2.columns[df2.isna().any()].tolist()
# Select only columns with missing data
df2.loc[:, df2.isna().any()].head()
# Missing by row
df2['missing'] = df2.isnull().sum(axis=1)
df2['missing'].value_counts()
# Total
df2['missing'].sum()
# Count missing in col
df2['US'].isna().sum()
# Replace missing
df2['US'].fillna('Foreign', inplace=False).head()<jupyter_output><empty_output><jupyter_text>Rename columns<jupyter_code>name_map = {
    'age-young': 'is-old',
    'missing': 'missing-count'
}
df2.rename(columns=name_map, inplace=True)
df2.head()<jupyter_output><empty_output><jupyter_text>Drop columns from data frame<jupyter_code>df2.drop(columns=['is-old', 'US', 'missing-count'], inplace=True)
df2.columns.tolist()<jupyter_output><empty_output><jupyter_text>Sort DataFrame by column value<jupyter_code>df.sort_values(by=['age', 'education-num'], ascending=False).head()<jupyter_output><empty_output><jupyter_text>Find row that is min/max<jupyter_code>df.iloc[df['age'].idxmax()]<jupyter_output><empty_output><jupyter_text>Iterate over rows<jupyter_code>for index, row in df.iterrows():
    print(index, row['age'], row['income'])
    if(index > 5):
        break<jupyter_output>0 39 <=50K
1 50 <=50K
2 38 <=50K
3 53 <=50K
4 28 <=50K
5 37 <=50K
6 49 <=50K<jupyter_text>Percentiles<jupyter_code>percentiles = [50, 80, 95]
column = 'age'

cut_points = [np.percentile(df[column], i) for i in percentiles]
df['age-percentile'] = '0-{}'.format(percentiles[0])
for i in range(len(percentiles)):
    selection = df[column] > cut_points[i]
    high_limit = 100 if i==len(percentiles)-1 else percentiles[i+1]
    df.loc[selection, 'age-percentile'] = '{}-{:2.0f}'.format(percentiles[i], high_limit)
    
df.head()<jupyter_output><empty_output><jupyter_text>Time Series Missing DatesCreating proper time series from data with missing dates.<jupyter_code>from datetime import datetime

# Create range for Jan
date_rng = pd.date_range(start='1/1/2019', end='1/15/2019', freq='D')
# Second range has only first and last 5 days
rng2 = date_rng[:5].append(date_rng[-5:])
# Df indexed on dates with missing dates
df = pd.DataFrame(np.random.randint(0,100,size=len(rng2)), columns=['data'], index=rng2)

df
# Create reference DF 
dref = pd.DataFrame(index=date_rng)
# Create full by joining
full_df = dref.join(df)
full_df
# Fill NA using forward fill
full_df.fillna(method='ffill')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15014812819822246, 'fraction_numerical': 0.02747104767034743, 'mean_word_length': 5.960637300843486, 'pattern_counts': {'":': 0, '<': 73, '<?xml version=': 0, '>': 68, 'https://': 6, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '343542', 'license': 'MIT', 'n_tokens_mistral': 2797, 'n_tokens_neox': 2666, 'n_words': 595, 'path': 'PandasHacks.ipynb', 'repo_name': 'jspoelstra/notebooks', 'size': '7426'}