def inner_join(dataframe1, dataframe2, on=None, left_on=None, right_on=None):
# join by common columns if nothing is specified
if on == left_on == right_on is None:
print('No columns specified')
common_cols = list(
set(dataframe1.columns).intersection(dataframe2.columns))
if len(common_cols) == 0:
raise ValueError('No common columns exist')
print('inner joining on: ', common_cols)
on = common_cols
# check if on was specified and create left_on/right_on
if on is not None:
left_on = right_on = on
left_on = make_list_if_not_list(left_on)
right_on = make_list_if_not_list(right_on)
if not all([col in dataframe1.columns for col in left_on]):
raise ValueError('All columns not present in dataframe1')
if not all([col in dataframe2.columns for col in right_on]):
raise ValueError('All columns not present in dataframe2')
return dataframe1.merge(dataframe2,
left_on=left_on,
right_on=right_on,
how='inner')
def unmelt(df, values, columns, index=None, reset_index=True, remove_multiindex=True):
"""
This function unmelts a dataframe
:param df:
:param values:
:param columns:
:param index:
:param reset_index:
:return:
"""
# if no index is provided use all other columns in DataFrame
if index is None:
index = set(df.columns)
_cols = make_list_if_not_list(columns)
_vals = make_list_if_not_list(values)
index = index - set(_cols + _vals)
# pivot table to unmelt the dataframe using first item found for each group
dfpiv = df.pivot_table(values=values, columns=columns, index=index, aggfunc='first')
if reset_index:
dfpiv.reset_index(inplace=True)
if remove_multiindex:
dfpiv = flatten_hier_column_names(dfpiv)
# remove column index name in case one was created
dfpiv.columns.name = ''
return dfpiv
def anti_join(dataframe1,
dataframe2,
on=None,
left_on=None,
right_on=None,
reset_index=True):
# join by common columns if nothing is specified
if on == left_on == right_on is None:
print('No columns specified')
common_cols = list(
set(dataframe1.columns).intersection(dataframe2.columns))
if len(common_cols) == 0:
raise ValueError('No common columns exist')
print('anti joining on: ', common_cols)
on = common_cols
# check if on was specified and create left_on/right_on
if on is not None:
left_on = right_on = on
left_on = make_list_if_not_list(left_on)
right_on = make_list_if_not_list(right_on)
if not all([col in dataframe1.columns for col in left_on]):
raise ValueError('All columns not present in dataframe1')
if not all([col in dataframe2.columns for col in right_on]):
raise ValueError('All columns not present in dataframe2')
# pdb.set_trace()
dataframe2[
'_jointag_'] = 'tag' # add a tag in case of joining on all columns (if all cols no NA to drop)
df2_nodups = dataframe2.drop_duplicates(
right_on) # drop duplicates so as not to add any rows during merge
df2_nodups = df2_nodups[right_on + [
'_jointag_'
]] # subset just the columns of interest in case df is very wide
dataframe1.reset_index(drop=True, inplace=True)
all_df1_ids = list(dataframe1.index)
df1_ids_in_df2 = list(
dataframe1.merge(df2_nodups,
left_on=left_on,
right_on=right_on,
how='left').dropna(subset=['_jointag_']).index)
ids_not_in_df2 = set(all_df1_ids).difference(df1_ids_in_df2)
dataframe2.drop('_jointag_', axis=1, inplace=True)
if len(ids_not_in_df2) == 0:
print('Warning: anti join is returning and empty dataframe')
if reset_index:
return dataframe1.iloc[list(ids_not_in_df2)].reset_index(drop=True)
else:
return dataframe1.iloc[list(ids_not_in_df2)]
def left_interlace(dataframe1,
dataframe2,
interlace_cols,
on=None,
left_on=None,
right_on=None,
drop_right_on=False,
**kwargs):
interlace_cols = make_list_if_not_list(interlace_cols)
if on == left_on == right_on is None:
print('No columns specified')
d1cols = dataframe1.columns
d2cols = dataframe2.columns
d2cols = [c for c in d2cols if c not in interlace_cols]
common_cols = list(set(d2cols).intersection(d1cols))
if len(common_cols) == 0:
raise ValueError('No common columns exist')
print('left joining on: ', common_cols)
on = common_cols
# check if on was specified and create left_on/right_on
if on is not None:
left_on = right_on = on
if any([lo in interlace_cols for lo in left_on]):
raise ValueError('interlace columns found in left joining columns')
if any([ro in interlace_cols for ro in right_on]):
raise ValueError('interlace columns found in right joining columns')
left_on = make_list_if_not_list(left_on)
right_on = make_list_if_not_list(right_on)
if not all([col in dataframe1.columns for col in left_on]):
raise ValueError('All columns not present in dataframe1')
if not all([col in dataframe2.columns for col in right_on]):
raise ValueError('All columns not present in dataframe2')
dataframe1 = dataframe1.merge(dataframe2,
left_on=left_on,
right_on=right_on,
how='left',
**kwargs)
for col in interlace_cols:
dataframe1[col] = np.where(dataframe1[col + '_x'].notnull(),
dataframe1[col + '_x'],
dataframe1[col + '_y'])
dataframe1.drop([col + '_x', col + '_y'], axis=1, inplace=True)
return dataframe1
def left_join(dataframe1,
dataframe2,
on=None,
left_on=None,
right_on=None,
drop_right_on=False,
verbosity=1,
**kwargs):
# join by common columns if nothing is specified
if on == left_on == right_on is None:
if verbosity == 1:
print('No columns specified')
common_cols = list(
set(dataframe1.columns).intersection(dataframe2.columns))
if len(common_cols) == 0:
raise ValueError('No common columns exist')
if verbosity == 1:
print('left joining on: ', common_cols)
on = common_cols
# check if on was specified and create left_on/right_on
if on is not None:
left_on = right_on = on
left_on = make_list_if_not_list(left_on)
right_on = make_list_if_not_list(right_on)
if not all([col in dataframe1.columns for col in left_on]):
raise ValueError('All columns not present in dataframe1')
if not all([col in dataframe2.columns for col in right_on]):
raise ValueError('All columns not present in dataframe2')
dataframe1 = dataframe1.merge(dataframe2,
left_on=left_on,
right_on=right_on,
how='left',
**kwargs)
if drop_right_on:
if on is not None:
warnings.warn(
'Can not drop right joining columns if they are all in both DataFrames'
)
else:
drops = set(right_on).difference(left_on)
dataframe1.drop(drops, axis=1, inplace=True)
return dataframe1
def encode_datetime(df,
datetime_col_name,
features=['hour', 'day'],
suffix='',
make_categorical=False,
drop_datetime=True,
inplace=False):
features = make_list_if_not_list(features)
if not isinstance(df[datetime_col_name].iloc[0],
(pd._libs.tslib.Timestamp)):
print('Converting to pandas._libs.tslib.Timestamp')
df[datetime_col_name] = pd.to_datetime(df[datetime_col_name])
if not inplace:
df = df.copy(deep=True)
# pandas dt.dayofweek monday = 0 sunday = 6 for some reason
column_names = [f + suffix for f in features]
feature_dict = dict(zip(features, column_names))
datecol = df[datetime_col_name]
for key in feature_dict.keys():
df[feature_dict[key]] = datecol.dt.__getattribute__(key)
if drop_datetime:
df.drop(datetime_col_name, axis=1, inplace=True)
if make_categorical:
df = to_categorical(df, features)
if not inplace:
return df
def semi_join(dataframe1, dataframe2, on=None, left_on=None, right_on=None):
# TODO: seems like a simple .isin() is faster? Test this out and change if neccessary.
# join by common columns if nothing is specified
if on == left_on == right_on is None:
print('No columns specified')
common_cols = list(
set(dataframe1.columns).intersection(dataframe2.columns))
if len(common_cols) == 0:
raise ValueError('No common columns exist')
print('semi joining on: ', common_cols)
on = common_cols
# check if on was specified and create left_on/right_on
if on is not None:
left_on = right_on = on
left_on = make_list_if_not_list(left_on)
right_on = make_list_if_not_list(right_on)
if not all([col in dataframe1.columns for col in left_on]):
raise ValueError('All columns not present in dataframe1')
if not all([col in dataframe2.columns for col in right_on]):
raise ValueError('All columns not present in dataframe2')
dataframe2[
'_jointag_'] = 'tag' # add a tag in case of joining on all columns (if all cols no NA to drop)
df2_nodups = dataframe2.drop_duplicates(
right_on) # drop duplicates so as not to add any rows during merge
df2_nodups = df2_nodups[right_on + [
'_jointag_'
]] # subset just the columns of interest in case df is very wide
# merge resets index
merged = dataframe1.merge(df2_nodups,
left_on=left_on,
right_on=right_on,
how='inner')
if len(merged) == 0:
print('Warning: semi join is returning and empty dataframe')
return merged[dataframe1.columns]
def append_to_column_name(df, cols, string_to_append, before=True):
cols = make_list_if_not_list(cols)
if all(isinstance(i, str) for i in cols):
old_names = df.columns[df.columns.isin(cols)]
elif all(isinstance(i, int) for i in cols):
old_names = df.columns[cols]
else:
raise ValueError('cols should either be a list of indices or a list of column names')
if before:
new_names = [string_to_append + o for o in old_names]
else:
new_names = [o + string_to_append for o in old_names]
rename_dict = dict(zip(old_names, new_names))
df.rename(columns=rename_dict, inplace=True)