<jupyter_start><jupyter_text>Genders<jupyter_code>face_genders = get_face_genders()
face_genders = face_genders.where(
    face_genders.labeler_id == Labeler.objects.get(name='knn-gender').id)
face_genders = face_genders.join(
    black_faces, black_faces.face_id == face_genders.face_id, 'left_outer'
).select(
    *[c if c != 'face_id' else 'face_genders.face_id' for c in face_genders.columns], 
    black_faces.is_black
)
face_genders = face_genders.na.fill({'is_black': False})
face_genders = face_genders.withColumn('is_host2', face_genders.host_probability > 0.5)
face_genders = face_genders.withColumn('height_gte20', face_genders.height >= 0.2)
face_genders = face_genders.withColumn(
    'hour_in_video', func.floor(face_genders.min_frame / face_genders.fps / 3600))
gender_group_keys = [
    'video_id', 
    'channel_id', 
    'canonical_show_id', 
    'hour_in_video',
    'in_commercial', 
    'is_host2', 
    'height_gte20', 
    'haircolor_id', 
#     'clothing_id', 
#     'hairlength_id',
    'is_black'
]

print('Summing over male faces')
male_st = sum_over_column(
    face_genders, 'duration', gender_group_keys,
    probability_column='male_probability')

print('Summing over female faces')
female_st = sum_over_column(
    face_genders, 'duration', gender_group_keys,
    probability_column='female_probability')
print('Done!')
gender_outfile = '/app/data/gender_10y.csv'

with open(gender_outfile, 'w') as f:
    f.write(','.join([
        'gender', 
        'video_id',
        'channel',
        'canonical_show',
        'date',
        'hour',
        'video_hour',
        'isoweekday', 
        'in_commercial', 
        'is_host',
        'height_gte20',
        'hair_color',
#         'hair_length',
#         'clothing',
        'is_black',
        'screentime_seconds',
        'variance_seconds_sq'
    ]))
    f.write('\r\n')
    
    def write_row(g, k, v):
        (
            video_id, 
            channel_id, 
            cshow_id,
            hour_in_video,
            in_comm, 
            is_host, 
            bbox_height, 
            color_id,
#             clothing_id, 
#             length_id, 
            is_black
        ) = k
        video_path = video_id_to_path[video_id]
        date, video_hour = get_date_hour_from_path(video_path)
        week_day = get_isoweekday(date)
        row = [
            g,
            str(video_id),
            channel_id_to_name[channel_id], 
            cshow_id_to_name[cshow_id],
            date,
            # NOTE: this can wrap into the next day
            str((video_hour + hour_in_video) % 24),
            str(video_hour),
            str(week_day), 
            str(in_comm), 
            str(is_host),
            str(bbox_height),
            color_id_to_name.get(color_id, ''),
#             length_id_to_name.get(length_id, ''),
#             clothing_id_to_name.get(clothing_id, ''),
            str(is_black),
            str(v[0]),
            str(v[1])
        ]
        f.write(','.join(row))
        f.write('\r\n')

    row_count = 0
    total_rows = len(female_st) + len(male_st)
    for k in male_st:
        write_row('m', k, male_st[k])
        row_count += 1
        if row_count % 10000 == 0:
            print('  {} / {}'.format(row_count, total_rows))

    for k in female_st:
        write_row('f', k, female_st[k])        
        row_count += 1
        if row_count % 10000 == 0:
            print('  {} / {}'.format(row_count, total_rows))
    
    print('Wrote {} rows'.format(row_count))<jupyter_output><empty_output><jupyter_text>Identities<jupyter_code>face_idents = get_face_identities(include_name=True)
face_genders_basic = spark.load('query_facegender')
face_genders_basic = face_genders_basic.where(
    face_genders_basic.labeler_id == Labeler.objects.get(name='knn-gender').id)
face_idents = face_idents.join(
    face_genders_basic.select('face_id', 'gender_id'), 
    face_idents.face_id == face_genders_basic.face_id, 'left_outer'
).select(
    *[c if c != 'face_id' else 'face_identities.face_id' for c in face_idents.columns], 
    face_genders_basic.gender_id
)
face_idents = face_idents.join(
    black_faces, black_faces.face_id == face_idents.face_id, 'left_outer'
).select(
    *[c if c != 'face_id' else 'face_identities.face_id' for c in face_idents.columns], 
    black_faces.is_black
)
face_idents = face_idents.na.fill({'is_black': False})
face_idents = face_idents.withColumn('height_gte20', face_idents.height >= 0.2)
face_idents = face_idents.withColumn(
    'hour_in_video', func.floor(face_idents.min_frame / face_idents.fps / 3600))

ident_group_keys = [
    'name', 
    'video_id', 
    'channel_id', 
    'canonical_show_id',
    'hour_in_video',
    'in_commercial', 
    'height_gte20',
    'haircolor_id', 
#     'hairlength_id', 
#     'clothing_id', 
    'gender_id',
    'is_black'
]

print('Summing over identities')
ident_st = sum_over_column(
    face_idents, 'duration', ident_group_keys,
    probability_column='probability')
print('Done!')
print('Computing true genders')
def ident_to_true_gender(ident_st):
    ident_mf_diff = {}
    for k in ident_st:
        name = k[0]
        gender = gender_id_to_name.get(k[-2], '')
        if name not in ident_mf_diff:
            ident_mf_diff[name] = 0.
        if gender == 'M':
            ident_mf_diff[name] += ident_st[k][0]
        elif gender == 'F':
            ident_mf_diff[name] -= ident_st[k][0]
    return {k: 'M' if v >= 0 else 'F' for k, v in ident_mf_diff.items()}
name_to_true_gender = ident_to_true_gender(ident_st)

print('Computing true is_black')
def ident_to_true_is_black(ident_st):
    ident_is_black_diff = {}
    for k in ident_st:
        name = k[0]
        is_black = k[-1]
        if name not in ident_is_black_diff:
            ident_is_black_diff[name] = 0.
        if is_black:
            ident_is_black_diff[name] += ident_st[k][0]
        else:
            ident_is_black_diff[name] -= ident_st[k][0]
    return {k: v >= 0 for k, v in ident_is_black_diff.items()}
name_to_true_is_black = ident_to_true_is_black(ident_st)
print('Done')
ident_outfile = '/app/data/identity_10y.csv'

with open(ident_outfile, 'w') as f:
    f.write(','.join([
        'name',
        'video_id',
#         'video_path',
        'channel', 
        'canonical_show',
        'date', 
        'hour',
        'video_hour',
        'isoweekday', 
        'in_commercial',
        'height_gte20',
        'hair_color',
#         'hair_length',
#         'clothing',
        'pred_gender',
        'true_gender',
        'pred_is_black',
        'true_is_black',
        'is_host',
        'screentime_seconds', 
        'variance_seconds_sq'
    ]))
    f.write('\r\n')
    
    def write_row(k, v):
        (
            name, 
            video_id,
            channel_id, 
            cshow_id,
            hour_in_video,
            in_comm, 
            bbox_height, 
            color_id, 
#             length_id, 
#             clothing_id, 
            gender_id,
            is_black,
        ) = k
        video_path = video_id_to_path[video_id]
        date, video_hour = get_date_hour_from_path(video_path)
        week_day = get_isoweekday(date)
        row = [
            name,
            str(video_id),
            channel_id_to_name[channel_id], 
            cshow_id_to_name[cshow_id],
            date, 
            str((video_hour + hour_in_video) % 24),
            str(video_hour),
            str(week_day),
            str(in_comm),
            str(bbox_height),
            color_id_to_name.get(color_id, ''),
#             length_id_to_name.get(length_id, ''),
#             clothing_id_to_name.get(clothing_id, ''),
            gender_id_to_name.get(gender_id, ''),
            name_to_true_gender.get(name, ''),
            str(is_black),
            str(name_to_true_is_black.get(name, '')),
            str(get_identity_is_host(channel_id, name)),
            str(v[0]),
            str(v[1])
        ]
        f.write(','.join(row))
        f.write('\r\n')
    
    row_count = 0
    for k in ident_st:
        write_row(k, ident_st[k])
        row_count += 1
        if row_count % 10000 == 0:
            print('  {} / {}'.format(row_count, len(ident_st)))
    
    print('Wrote {} rows'.format(row_count))<jupyter_output><empty_output><jupyter_text>Videos<jupyter_code>print('Computing video id to commercials')
VIDEO_ID_TO_COMMERCIALS = defaultdict(list)
for c in get_commercials().select('video_id', 'min_frame', 'max_frame').collect():
    VIDEO_ID_TO_COMMERCIALS[c.video_id].append(
        (c.min_frame, c.max_frame)
    )
    
def _annotate_frame_in_commercial(df):
    assert ('video_id' in df.columns)
    assert ('number' in df.columns)
    def in_commercial_helper(video_id, frame_no):
        if video_id in VIDEO_ID_TO_COMMERCIALS:
            for c_min, c_max in VIDEO_ID_TO_COMMERCIALS[video_id]:
                if frame_no >= c_min and frame_no <= c_max:
                    return True
        return False
    my_udf = func.udf(in_commercial_helper, BooleanType())
    df = df.withColumn(
        'in_commercial', my_udf('video_id', 'number')
    )
    return df

print('Computing frames with host')
if True:
    faces = get_faces()
    frames_w_host_df = faces.where(
        faces.host_probability > 0.5
    ).select('frame_id').distinct()
    spark.save('frames_w_host', frames_w_host_df)

print('Computing frames to face count')
if True:
    frames_face_count_df = faces.groupBy(
        'frame_id'
    ).agg(
        func.count(func.lit(1)).alias('face_count')
    )
    spark.save('frames_face_count', frames_face_count_df)

print('Done')
videos = get_videos()
videos = videos.where((videos.duplicate == False) & (videos.corrupted == False))
frames = get_frames()
frames_with_host = spark.load('frames_w_host').withColumn('has_host', func.lit(True))
frames_face_count = spark.load('frames_face_count')
frames = frames.join(
    videos, frames.video_id == videos.id
).join(
    frames_with_host, frames.id == frames_with_host.frame_id, 'left_outer'
).join(
    frames_face_count, frames.id == frames_face_count.frame_id, 'left_outer'
).select(
    'frames.*',
    videos.fps,
    videos.channel_id,
    videos.canonical_show_id,
    frames_with_host.has_host,
    frames_face_count.face_count
).where(
    ((videos.threeyears_dataset == True) & (frames.number % func.floor(videos.fps * 3) == 0)) | \
    ((videos.threeyears_dataset == False) & (frames.number % func.ceil(videos.fps * 3) == 0))
)
frames = _annotate_frame_in_commercial(frames)
frames = frames.withColumn(
    'hour_in_video', func.floor(frames.number / frames.fps / 3600))
video_outfile = '/app/data/video_10y.csv'

frame_group_by_keys = [
    'channel_id', 
    'canonical_show_id', 
    'video_id', 
    'hour_in_video', 
    'in_commercial', 
    'has_host', 
    'face_count'
]

print('Summing over frames')
with open(video_outfile, 'w') as f:
    f.write(','.join([
        'video_id',
#         'video_path',
        'channel', 
        'canonical_show', 
        'date', 
        'hour',
        'video_hour',
        'isoweekday', 
        'in_commercial', 
        'host_onscreen',
        'num_faces_onscreen',
        'screentime_seconds'
    ]))
    f.write('\r\n')
    
    row_count = 0
    for row in frames.groupBy(
        frame_group_by_keys
    ).agg(
        func.count(func.lit(1)).alias('frame_count')
    ).sort(
        frame_group_by_keys
    ).collect():
        video_id = row.video_id
        channel_id = row.channel_id
        cshow_id = row.canonical_show_id
        in_comm = row.in_commercial
        has_host = row.has_host == True
        face_count = row.face_count
        hour_in_video = row.hour_in_video
        if face_count is None:
            face_count = 0
        video_path = video_id_to_path[video_id]
        date, video_hour = get_date_hour_from_path(video_path)
        week_day = get_isoweekday(date)
        f.write(','.join([
            str(video_id),
            channel_id_to_name[channel_id], 
            cshow_id_to_name[cshow_id],
            date,
            str((video_hour + hour_in_video) % 24),
            str(video_hour),
            str(week_day),
            str(in_comm), 
            str(has_host),
            str(face_count),
            str(row.frame_count * 3)
        ]))
        f.write('\r\n')
        row_count += 1
        if row_count % 10000 == 0:
            print(row_count)
    print('Wrote {} rows'.format(row_count))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1255702280912365, 'fraction_numerical': 0.008083233293317327, 'mean_word_length': 2.4586216440631055, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '187632', 'license': 'Apache-2.0', 'n_tokens_mistral': 4552, 'n_tokens_neox': 4389, 'n_words': 697, 'path': 'app/notebooks/tableau_csv.ipynb', 'repo_name': 'scanner-research/esper-tv', 'size': '12495'}