%load_ext watermark
%watermark -a 'Sebastian Raschka' -v -d -p pandas
[More information](http://nbviewer.ipython.org/github/rasbt/python_reference/blob/master/ipython_magic/watermark.ipynb) about the `watermark` magic command extension.
This is just a small but growing collection of pandas snippets that I find occasionally and particularly useful -- consider it as my personal notebook. Suggestions, tips, and contributions are very, very welcome!
I am heavily into sports prediction (via a machine learning approach) these days. So, let us use a (very) small subset of the soccer data that I am just working with.
import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/rasbt/python_reference/master/Data/some_soccer_data.csv')
df
# Converting column names to lowercase
df.columns = [c.lower() for c in df.columns]
# or
# df.rename(columns=lambda x : x.lower())
df.tail(3)
df = df.rename(columns={'p': 'points',
'gp': 'games',
'sot': 'shots_on_target',
'g': 'goals',
'ppg': 'points_per_game',
'a': 'assists',})
df.tail(3)
# Processing `salary` column
df['salary'] = df['salary'].apply(lambda x: x.strip('$m'))
df.tail()
df['team'] = pd.Series('', index=df.index)
# or
df.insert(loc=8, column='position', value='')
df.tail(3)
# Processing `player` column
def process_player_col(text):
name, rest = text.split('\n')
position, team = [x.strip() for x in rest.split(' — ')]
return pd.Series([name, team, position])
df[['player', 'team', 'position']] = df.player.apply(process_player_col)
# modified after tip from reddit.com/user/hharison
#
# Alternative (inferior) approach:
#
#for idx,row in df.iterrows():
# name, position, team = process_player_col(row['player'])
# df.ix[idx, 'player'], df.ix[idx, 'position'], df.ix[idx, 'team'] = name, position, team
df.tail(3)
cols = ['player', 'position', 'team']
df[cols] = df[cols].applymap(lambda x: x.lower())
df.head()
nans = df.shape[0] - df.dropna().shape[0]
print('%d rows have missing values' % nans)
# Selecting all rows that have NaNs in the `assists` column
df[df['assists'].isnull()]
df[df['assists'].notnull()]
# Filling NaN cells with default value 0
df.fillna(value=0, inplace=True)
df
# Adding an "empty" row to the DataFrame
import numpy as np
df = df.append(pd.Series(
[np.nan]*len(df.columns), # Fill cells with NaNs
index=df.columns),
ignore_index=True)
df.tail(3)
# Filling cells with data
df.loc[df.index[-1], 'player'] = 'new player'
df.loc[df.index[-1], 'salary'] = 12.3
df.tail(3)
# Sorting the DataFrame by a certain column (from highest to lowest)
df.sort('goals', ascending=False, inplace=True)
df.head()
# Optional reindexing of the DataFrame after sorting
df.index = range(1,len(df.index)+1)
df.head()
# Creating a dummy DataFrame with changes in the `salary` column
df_2 = df.copy()
df_2.loc[0:2, 'salary'] = [20.0, 15.0]
df_2.head(3)
# Temporarily use the `player` columns as indices to
# apply the update functions
df.set_index('player', inplace=True)
df_2.set_index('player', inplace=True)
df.head(3)
# Update the `salary` column
df.update(other=df_2['salary'], overwrite=True)
df.head(3)
# Reset the indices
df.reset_index(inplace=True)
df.head(3)
# Selecting only those players that either playing for Arsenal or Chelsea
df[ (df['team'] == 'arsenal') | (df['team'] == 'chelsea') ]
# Selecting forwards from Arsenal only
df[ (df['team'] == 'arsenal') & (df['position'] == 'forward') ]
types = df.columns.to_series().groupby(df.dtypes).groups
types
# select string columns
df.loc[:, (df.dtypes == np.dtype('O')).values].head()
df['salary'] = df['salary'].astype(float)
types = df.columns.to_series().groupby(df.dtypes).groups
types
I was recently asked how to do an if-test in pandas, that is, how to create an array of 1s and 0s depending on a condition, e.g., if val less than 0.5 -> 0, else -> 1. Using the boolean mask, that's pretty simple since True and False are integers after all.
int(True)
import pandas as pd
a = [[2., .3, 4., 5.], [.8, .03, 0.02, 5.]]
df = pd.DataFrame(a)
df
df = df <= 0.05
df
df.astype(int)