The dataset I am using has number of columns which hold criminal offence codes (eg, 90, 120, 10) for prisoners. The columns are sparsely populated because of the complex survey routing logic used to capture the data. The data needs to be one hot encoded to feed into a machine learning model. Having (number of columns where offenses are held) x (number of offense codes) does one-hot encode the data, but it creates a dataset that is far too sparse.
I therefore want to create one column for each offense code and, for each row in the dataset, populate it with the count of that code across all columns that hold offenses.
I can imagine a way to do this by converting the dataframe to a dictionary, but this seems very slow and bad practice for pandas.
#dataset is a dataframe
#offense_columns is a list of strings corresponding to column names in the dataset
#create a list of all the codes that appear across all offense columns
all_possible_offense_codes=[]
for colname in all_possible_offense_codes.values():
for value in dataset[colname].values():
if value not in all_possible_offense_codes:
all_possible_offense_codes.append(value)
#create a copy subset of the dataframe with just the offense columns
offense_cols_subset=dataset[offense_columns]
#convert to dictionary- quicker to loop through than df
offense_cols_dict=offense_cols_subset.to_dict(orient='index')
#create an empty dictionary to hold the counts and append back onto the main dataframe
all_offense_counts={}
#look at each row in the dataframe (converted into a dict) one by one
for row,variables in offense_cols_dict.items():
#create a dict with all offense code as key and value as 0 (starting count)
#considered using get(code,0) rather than prepopulating keys and vals...
#but think different vals across dicts would create alignment issues...
#when appending back onto dataset df
this_row_offense_counts={code:0 for code in all_possible_offense_codes}
#then go through each offense column
for column in offense_columns:
#find the code stored in this column for this row
code=offense_cols_dict[row,column]
#increment count by 1
this_row_offense_counts[code]=this_row_offense_offense_counts[code]+1
#once all columns have been counted, store counts in dictionary
all_offense_counts[row]=this_row_offense_counts
#once all rows have been counted, turn into a dataframe
offense_counts_cols=pf.DataFrame.from_dict(all_offense_counts,orient=index)
#join to the original dataframe
dataset.join(offense_counts)
#drop the sparsely populated offense_columns
dataset.drop(offense_columns,axis=1)