Pandas

Get the version

https://stackoverflow.com/questions/36794433/python-using-multiprocessing-on-a-pandas-dataframe http://www.racketracer.com/2016/07/06/pandas-in-parallel/

  import pandas as pd

  pd.__version__

Creating Dataframes

  df = pd.DataFrame()
  df_perf = pd.DataFrame({
      "lgbm": lgbm_times,
      "xgb": xgb_times,
      "catb": catb_times
  })
  pd.DataFrame(topic_words, columns=["topic", "word", "signifigance"])

load only part of data: nrows

  pd.read_csv("timeseries.csv", nrows=1000)

#https://towardsdatascience.com/10-python-pandas-tricks-that-make-your-work-more-efficient-2e8e483808ba

load only part of data: columns

  pd.read_csv("timeseries.csv", usecols=["c1", "c2"]])

set datatypes on load for all columns:

  pd.read_csv("timeseries.csv", dtype="float32")

set datatypes on load for specific columns:

  pd.read_csv("timeseries.csv", dtype={"c1":str, "c2":int}])

set specific value as NaN ("?" here):

  pd.read_csv("timeseries.csv", na_values=["?"], dtype={"c1":str, "c2":int}])

Selecting

select multiple columns as a dataframe from a bigger dataframe:

  df2 = df[['Id', 'team', 'winPlacePerc']]

select a single column as a dataframe:

  df2 = df[['name']] 
#double square brackets make the results dataframe, 
#single makes it series

Selecting a Single Column by Column Names (rangeindex value work too)

  columnsData = dfObj.loc[ : , 'Age' ]

Select all but single column:

  df_train = df_train.drop("Survived", axis=1)
  df_train.loc[:, df_train.columns != 'Survived']

Select all but multiple columns:

  df_train = df_train.drop(["Survived", "Pclass"], axis=1)

Select rows by index in list:

  top10 = df_top_misses.iloc[0:10].index
  df_train.iloc[top10]

select by datatype:

	df.select_dtypes(include=['float64', 'int64'])

pandas axis:

  axis 1 = columns, axis 0 = rows

get a series from a dataframe column filtered by another column:

  zero_names = df[df["weights"] < 10000]["names"]

turn it into a list:

  zn_list = zero_names.tolist()

N-way split:

  X_train, X_test, y_train, y_test, r_train, r_test = 
   model_selection.train_test_split(X, y, r, test_size=0.25, random_state=99)

Filter out items that only occur given number of times:

min_count = 10
df_2019 = df_2019[df_2019['comp1'].isin(counts[counts >= min_count].index)]

Show all rows with duplicates:

df[df.duplicated(['key1','key2'],keep=False)]

//https://stackoverflow.com/questions/26244309/how-to-analyze-all-duplicate-entries-in-this-pandas-dataframe/33381151#33381151 Just show the first or last of each duplicate:

df[df.duplicated(['key1','key2'],keep='first')]
df[df.duplicated(['key1','key2'],keep='last')]

Count duplicates:

df_train.duplicated().sum()

Change number of rows shown

pd.set_option('display.max_rows', 500)

Make it local only:

with pd.option_context('display.height', 500, 'display.max_rows', 500):
  df.head(100)

Reset option to default:

pd.reset_option('display.max_rows') #to reset one
pd.reset_option('all') #to reset all

Filter all rows where the line_race columns is zero.

df = df[df.line_race != 0]

Filter all rows where the line_race columns is not zero.

df = df[df.line_race == 0]

Accessing Values

set/reset how many rows does pandas print with df.head(), iloc[], etc. before clipping display.

  pd.set_option('display.max_rows', 5)
  pd.reset_option('display.max_rows')

find row at 1111th index in dataframe:

  df.iloc[1111]

get value in specific row and cell:

  doc_meta["title"].values[0]

find row with value 1111 for index in dataframe:

  df.loc[1111]

select multiple ranges of rows:

def ranges(*args):
    indices = []
    for rt in args:
        indices += list(range(rt[0], rt[1]))
    return indices

tmp_df.iloc[ranges((0,10), (90,110))]

set value in cell (stackoverflow):

  children_df.at[555, "visited"] = True 
    #555 here refers to row index

modify row while iterating over it:

  for index, row in df.iterrows():
    df.at[index,'open_change_p1'] = day_change_op1

select 55th column:

  df_train[df_train.columns[55]]

drop last 5 columns

  df_train.drop(df_train.columns[-5:], axis=1)

print numpy data types in multidimensional array (in jupyter return value is printed):

[type(row) for row in values[0]]

Aggregates

calculate mean for ‘team’ and ‘winPlacePerc’ columns, after grouping them by match id and group id:

agg_cols = ['groupId', 'matchId', 'team', 'winPlacePerc']
df_mean = df_test[agg_cols].groupby(["groupId", "matchId"],
  as_index=False).mean().add_suffix("_mean")

run multiple such aggregations at once:

agg = df_train.groupby(['matchId'])
  .agg({'players_in_team': ['min', 'max', 'mean', 'median']})

specify the name suffixes of the generated aggregation column names:

agg_train = df_train[agg_cols].groupby(["groupId", "matchId"], 
                      as_index=False)
  .agg([('_min', 'min'), ('_max', 'max'), ('_mean', 'mean')])

multiple aggregations will create a 2-level column header (see df.head()). to change it into one level (for merge etc):

  mi = agg_train.columns #mi for multi-index
  ind = pd.Index([e[0] + e[1] for e in mi.tolist()])
  agg_train.columns = ind

custom aggregation function:

def q90(x):
    return x.quantile(0.9)

agg = df_train.groupby(['matchId'])
  .agg({'players_in_team': 
      ['min', 'max', 'mean', 'median', q90]})

create new column as number of rows in a group:

  agg = df_train.groupby(['matchId'])
          .size().to_frame('players_in_match')

group and sum column for groups:

revenues = df_train.groupby("geoNetwork_subContinent")
    ['totals_transactionRevenue'].sum()
    .sort_values(ascending=False)

Merge

pandas concatenate dataframes (axis=1 is columns, so concat sideways):

  pd.concat([df_a,df_b], axis=1)

merge two dataframes (here df_train and agg) by a single column:

  df_train = df_train.merge(agg, how='left', on=['groupId'])

merge on multiple columns:

  dfg_train = dfg_train.merge(agg_train, how='left',
     on=["groupId", "matchId"])

set merge suffixes = ["", "_rank"] <- left and right side on overlapping columns

  dfg_test = dfg_test.merge(agg_test_rank, suffixes=["", "_rank"],
        how='left', on=["groupId", "matchId"])

above sets columns from dfg_test to have no suffix ("") and columns from agg_test_rank to have suffix "_rank"

merge columns by value:

df_train['rankPoints'] = np.where(df_train['rankPoints'] < 0,
      df_train['winPoints'], df_train['rankPoints'])

->above sets value as winpoints if rankpoints is below zero, else rankpoints

merge by defining the column names to match on left and right:

  pd.merge(left, right, left_on='key1', right_on='key2')

merge types (the how=merge_type in pd.merge)(link):

  • inner: keep rows that match in both left and right
  • outer: keep all rows in both left and right
  • left: keep all rows from left and matching ones from right
  • right: keep all rows from right and matching ones from left

Rename, Delete, Compare Dataframes

rename columns to remove a suffix (here remove _mean):

  df_mean = df_mean.rename(columns={'groupId_mean': 'groupId',
               'matchId_mean': 'matchId'}) 

delete dataframes to save memory:

  del df_agg

find all columns in one dataframe but not in another

  diff_set = set(train_df.columns).difference(set(test_df.columns))
  print(diff_set)

find all columns in both dataframes and drop them

  common_set = set(train_df.columns).intersection(set(df2.columns))
  train_df = train_df.drop(common_set, axis=1)

find all columns not in both dataframes and drop them

  common_set = set(df_train.columns).intersection(set(df_test.columns))
  df_train = df_train[list(common_set)]

drop rows with index in list (stackoverflow):

  df.drop(df.index[[1,3]])

replace nulls/nans with 0:

  X.fillna(0)
  X_test.fillna(0)

only for specific columns:

  df[['a', 'b']] = df[['a','b']].fillna(value=0)

drop nan, rows with null values for specific column:

  df_train.dropna(subset=['winPlacePerc'], inplace=True)

drop columns B and C:

  df.drop(['B', 'C'], axis=1)
  df.drop(['B', 'C'], axis=1, inplace = True)

Dataframe Statistics

this df has 800k rows (values) and 999 columns (features):

  df_train_subset.shape
  (800000, 999)

data types:

  df_train.dtypes

number of rows, columns, memory size (light, fast):

  df.info()

statistics per feature/column (cpu/mem heavy):

  df.describe()

number of unique values:

  df.nunique()

bounds:

  df.min()
  df.max()

replace infinity with 0. esp scalers can have issues with inf:

    X[col] = X[col].replace(np.inf, 0)

replace positive and negative inf with nan:

  df_pct.replace([np.inf, -np.inf], np.nan)

number of non-nulls per row in dataframe:

  df_non_null = df.notnull().sum(axis=1)

find the actual rows with null values:

  df_train[df_train.isnull().any(axis=1)]

print columns with nulls in them

  nan_cols = df_team_feats.columns[df_team_feats.isna().any()].tolist()
  nan_cols

number of unique values

  df.nunique()

number of times different values appear in column:

  df_train["device_operatingSystem"].value_counts()

sorted by index (e.g., 0,1,2,…)

  phase_counts = df_tgt0["phase"].value_counts().sort_index()

number of nans per column in dataframe:

  df.isnull().sum()

find columns with only one unique value

  const_cols = [c for c in train_df.columns 
                if train_df[c].nunique(dropna=False)==1 ]

drop them

  train_df = train_df.drop(const_cols, axis=1)

replace outlier values over 3std with 3std:

outlier_thresholds = {}
def remove_outliers(df, col):
    upper = df[col].mean()+3*df[col].std()
    if col in outlier_thresholds:
        upper = outlier_thresholds[col]
    else:
        outlier_thresholds[col] = upper
    mask = np.abs(df[col] - df[col].mean()) > (3*df[col].std())
    df.loc[mask, col] = upper

  upper = df_train['totals_transactionRevenue'].mean()
            +3*df_train['totals_transactionRevenue'].std()
  mask = np.abs(df_train['totals_transactionRevenue']
           -df_train['totals_transactionRevenue'].mean()) >
           (3*df_train['totals_transactionRevenue'].std())
  df_train.loc[mask, 'totals_transactionRevenue'] = upper

or use zscore:

  df['zscore'] = (df.a - df.a.mean())/df.a.std(ddof=0)

from scipy (stackoverflow)

  from scipy import stats
  import numpy as np
  z = np.abs(stats.zscore(boston_df))
  print(z)

show groupby object data statistics for each column by grouped element:

  grouped.describe()

create dataframe from classifier column names and importances (where supported), sort by weight:

  df_feats = pd.DataFrame()
  df_feats["names"] = X.columns
  df_feats["weights"] = clf.feature_importances_
  df_feats.sort_values(by="weights")

lag columns to show absolute value they changed over rows:

  for col in input_col_names:
    df_diff[col] = df_sig[col].diff().abs()

unique datatypes in dataframe:

  train_df.dtypes.unique()

show columns of selected type:

  train_df.select_dtypes(include=['float64'])

Numpy

Get numpy matrix from dataframe:

  data_diffs = df_diff.values

get column from numpy matrix as row

  for sig in range(0, 3):
      data_sig = data_measure[:,sig]

slice numpy array:

  bin_data_raw = data_sig[i:i + bin_size]

calculate statistics over numpy array:

bin_avg_raw = bin_data_raw.mean()
bin_sum_raw = bin_data_raw.sum()
bin_std_raw = bin_data_raw.std()
bin_percentiles = np.percentile(bin_data_raw, [0, 1, 25, 50, 75, 99, 100])
bin_range = bin_percentiles[-1] - bin_percentiles[0]
bin_rel_perc = bin_percentiles - bin_avg_raw

count outliers at different scales:

  outliers = []
  for r in range(1, 7):
      t = bin_std_diff * r
      o_count = np.sum(np.abs(bin_data_diff-bin_avg_diff) >= t)
      outliers.append(o_count)

concatenate multiple numpy arrays into one (rows=axis 0, cols=axis 1):

  bin_row = np.concatenate([raw_features, diff_features, bin_percentiles, bin_rel_perc, outliers])

limit values between min/max:

  df_sig.clip(upper=127, lower=-127)

value range in column (ptp = peak to peak):

  df.groupby('GROUP')['VALUE'].agg(np.ptp)

replace nan:

  my_arr[np.isnan(my_arr)] = 0

disable e-notation (scientific notation, decimals, fractions):

  np.set_printoptions(suppress=True)
  #above only works for numpy arrays, not specific numpy values, so this should work:
  print(np.array([0.111111111]))  
  #but not this
  print(np.float32(0.111111111))  

disable e-notation and set precision locally:

with np.printoptions(precision=20, suppress=True):
    print(np.array([d_min, d_max, d_mean, d_med]))

shuffle two+ arrays: #https://stackoverflow.com/questions/4601373/better-way-to-shuffle-two-numpy-arrays-in-unison

  from sklearn.utils import shuffle
  X, y = shuffle(X, y, random_state=0)

reshape numpy array:

  my_arr.reshape((2096, 160, 22)) #not sure how this works wihtout extra parenthesis

Time-Series

create values for percentage changes over time (row to row):

df_pct = pd.DataFrame()
for col in df_train_subset.columns[:30]:
    df_pct['pct_chg_'+col] =
        df_train_subset[col].pct_change().abs()

pct_change() gives the change in percentage over time, abs() makes it absolute if just looking for overall change as in negative or positive.

also possible to set number of rows to count pct_change over:

  df_pct['pct_chg_'+col] =
          df_train_subset[col].pct_change(periods=10)

pct_change on a set of items with specific values:

  df['pct_chg_open1'] = df.groupby('assetCode')['open']
           .apply(lambda x: x.pct_change())

TODO: try different ways to calculate ewm to see how this all works ewm ([stackoverflow]( EWMA: https://stackoverflow.com/questions/37924377/does-pandas-calculate-ewm-wrong)):

  df["close_ewma_10"] = df.groupby('assetName')['pct_chg_close1']
            .apply(lambda x: x.ewm(span=10).mean())

shift by 10 backwards

  y = mt_df["mket_close_10"].shift(-10)

Date Types

convert seconds into datetime

  start_col = pd.to_datetime(df_train.visitStartTime, unit='s')

parse specific columns as specific date types:

df_train = pd.read_csv("../input/train-flat.csv.gz",
                        parse_dates=["date"], 
                        dtype={'fullVisitorId': 'str',
                            'date': 'str'
                            },
                      )

or if need to parse specific format:

  pd.to_datetime('13000101', format='%Y%m%d', errors='ignore')

access elements of datetime objects:

  data_df["weekday"] = data_df['date'].dt.weekday
  data_df["hour"] = data_df['date'].dt.hour

Data Type Manipulations

one-hot (one hot, onehot) encode / convert categorical:

  dfg_train = pd.get_dummies( dfg_train, columns = ['matchType'] )
  dfg_test = pd.get_dummies( dfg_test, columns = ['matchType'] )

multiple columns:

  pd.get_dummies(data=df, columns=['A', 'B'])

set category value by range:

  here 1 = solo game, 2 = duo game, 3 = squad, 4 = custom
  df_train['team'] = 
     [1 if i == 1 else 2 if i == 2 else 4 if i > 4 else 3 
              for i in df_train["players_in_team_q90"]]

calculate value over two columns and make it a new column:

dfg_train['killsNorm'] = 
    dfg_train['kills_mean']*
    ((100-dfg_train['players_in_match'])/100 + 1)

data_df['hit_view_ratio'] =
    data_df['totals_pageviews']/data_df['totals_hits']

mark a set of columns as category type:

for col in X_cat_cols:
    df_train[col] = df_train[col].astype('category')

set category value based on set of values in column:

X_test['matchType'] = X_test['matchType'].astype('str')
X_test.loc[X_test['matchType'] == "squad-fpp", 'matchType'] = "squad"
X_test['matchType'] = X_test['matchType'].astype('category')

how to get the indices from a dataframe as a list (e.g., collect a subset):

  list(outlier_collection.index.values)

to see it all on one line in Jupyter (easier copying, e.g. to drop all in list):

print(list(outlier_collection.index.values))

drop all rows where a field is empty:

  mail_df.drop(email_df.query(
      "body == '' | to == '' | from_ == ''"
  ).index, inplace=True)

Jupyter

show dataframe as visual table in notebook (stackoverflow):

  from IPython.display import display, HTML

  display(df1)
  display(HTML(df2.to_html()))

SKLearn

from sklearn.preprocessing import LabelEncoder
le = LabelEncoder()
df_all["Sex"] = le.fit_transform(df_all["Sex"])
#if need in two parts
#df_test["Sex] = le.transform(df_text["Sex])

le_name_mapping = dict(zip(le.classes_, le.transform(le.classes_))) print(le_name_mapping)

le_value_mapping = dict(zip(le.transform(le.classes_), le.classes_))

LGBM, Catboost, XGboost categorical: https://towardsdatascience.com/catboost-vs-light-gbm-vs-xgboost-5f93620723db

Memory Reducer (From Kaggler):

def reduce_mem_usage(df):
    """ iterate through all the columns of a dataframe and modify the data type
        to reduce memory usage.        
    """
    start_mem = df.memory_usage().sum() / 1024**2
    print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))
    
    for col in df.columns:
        col_type = df[col].dtype
        
        if col_type != object and col_type.name != 'category':
            #print(col_type)
            c_min = df[col].min()
            c_max = df[col].max()
            if str(col_type)[:3] == 'int':
                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:
                    df[col] = df[col].astype(np.int8)
                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:
                    df[col] = df[col].astype(np.int16)
                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:
                    df[col] = df[col].astype(np.int32)
                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:
                    df[col] = df[col].astype(np.int64)  
            else:
                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:
                    df[col] = df[col].astype(np.float16)
                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:
                    df[col] = df[col].astype(np.float32)
                else:
                    df[col] = df[col].astype(np.float64)
        else:
            df[col] = df[col].astype('category')

    end_mem = df.memory_usage().sum() / 1024**2
    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))
    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))
    
    return df

#https://stackoverflow.com/questions/25351968/how-to-display-full-non-truncated-dataframe-information-in-html-when-convertin

def print_full(x):
    pd.set_option('display.max_rows', len(x))
    pd.set_option('display.max_columns', None)
    pd.set_option('display.width', 2000)
    pd.set_option('display.float_format', '{:20,.2f}'.format)
    pd.set_option('display.max_colwidth', -1)
    print(x)
    pd.reset_option('display.max_rows')
    pd.reset_option('display.max_columns')
    pd.reset_option('display.width')
    pd.reset_option('display.float_format')
    pd.reset_option('display.max_colwidth')