%% Cleaning up the data
% This script takes care of data munging. 
%

%%
% Here we reload the previously saved result for consistency, and covert
% respective fields into separate tables.
clearvars;close all;clc;

load resp.mat
courses = sortrows(struct2table(resp.elements),'id');
categories = sortrows(struct2table(resp.linked.categories),'id');
universities = sortrows(struct2table(resp.linked.universities),'id');

%% Linking tables
% |courses| contains |links| field that stores foreign keys to the other
% tables for looking up associated values. Some courses are missing some of
% those foreign keys. We will drop the affected rows, and create a new
% table |T| with the remaining records.

% extract structure array
S = courses.links;
% get the logical array of fields with data
hasCategories = cellfun(@(x) isfield(x,'categories'),S);
hasUniversities = cellfun(@(x) isfield(x,'universities'),S);
hasSessions = cellfun(@(x) isfield(x,'sessions'),S);
% create a table from arrays with complete data
S = struct2table([S{hasCategories & hasUniversities & hasSessions}]);
T = [courses(hasCategories & hasUniversities & hasSessions,1:end-1), S];

%% STEM Categories
% We need to decide which categories represent STEM subjects, and add a
% variable to indicate STEM or not STEM per course. When there are multiple
% categories assigned to a given course, we treat it as a STEM course as
% long as one of them is included in STEM categories.

% category ids belonging to STEM subjects
stemIds = [1:3 5 9:12 15:17 23:25];
% if category contain any STEM ids, then the course is a STEM course
T.stem = cellfun(@(x) any(ismember(x(1),stemIds)), T.categories);
disp('STEM categories')
disp(categories.name(ismember(categories.id,stemIds)))

%% Sessions
% The same course can be offered repeatedly. You can determine the
% longevity or age of a given course by the count of sessions. 

T.sessions = cellfun(@length,T.sessions);

%% Universities
% Most courses are offered by one university, but there are several
% courses offered jointly by more than one. There are some universities 
% that only appears on joint courses but don't offer its own. We will drop
% them from consideration. 

% it's a joint course if more than 1 univ
isJoint = cellfun(@length,T.universities) > 1;
% list of individual university ids with its own courses
univ_id = cell2mat(T.universities(~isJoint));
% unique university ids
[uniq_uid,~,idx] = unique(univ_id);
% count of univque university ids
courseCount = accumarray(idx,1);
% count of sessions
sessionCount = accumarray(idx,T.sessions(~isJoint));
% courses with more than 1 univ
jointCourses = T.universities(isJoint);
% ignore universities without standalone courses
ignore = setdiff(unique(cell2mat(jointCourses)),uniq_uid);

%%
% After we drop the universities without its own courses, we still have
% several joint courses left. We will pick the university with the most 
% courses as the primary university to represent the course.  

for i = 1:length(jointCourses)
    % drop ids of universities without its own courses
    jointCourses{i}(ismember(jointCourses{i},ignore)) = [];
    % if still more than 1 university
    if length(jointCourses{i}) > 1
        % get the position of the university with max number of courses
        [~,pos] = max(courseCount(ismember(uniq_uid,jointCourses{i})));
        % get the unique university id for that university
        jointCourses{i} = jointCourses{i}(pos);
    end
    % if we have no university left, make it NaN
    if isempty(jointCourses{i})
        jointCourses{i} = NaN;
    end
end
%%
% Now we should have only university per course. Convert the cell array of
% ids to a vector
T.universities(isJoint) = jointCourses;
T.universities = cell2mat(T.universities);

%% 
% Add the count of STEM and non-STEM courses to |universities| and compute
% the ratio. 

% get the count of stem courses by university
stemByUniv = grpstats(T(:,{'universities','stem'}),{'stem','universities'});
stemYes = stemByUniv(stemByUniv.stem == true,:);
stemNo = stemByUniv(stemByUniv.stem == false,:);
% add new variables to hold count of STEM courses
universities.stem = zeros(height(universities),1);
% add new variables to hold count of non-STEM courses
universities.notstem = zeros(height(universities),1);
% get the count of STEM courses by university
universities.stem(ismember(universities.id,stemYes.universities)) =...
    stemYes.GroupCount(ismember(stemYes.universities,universities.id));
% get the count of non-STEM courses by university
universities.notstem(ismember(universities.id,stemNo.universities)) =...
    stemNo.GroupCount(ismember(stemNo.universities,universities.id));
% get the total count of courses
universities.courses = universities.stem + universities.notstem;
% compute ratio of STEM courses
universities.stem_ratio = universities.stem ./ universities.courses;
% replace NaNs with 0's
universities.stem_ratio(isnan(universities.stem_ratio)) = 0;

%%
% Add the session count to |universities|
universities.sessions = zeros(height(universities),1);
universities.sessions(ismember(universities.id,uniq_uid)) =...
    sessionCount(ismember(uniq_uid,universities.id));
