def print_average_edit_count_per_day(cls):
data = {
'userid': [],
'bot': [],
'edit_count_per_day': [],
'registration': []
}
for item in db.execute(cls.EDIT_COUNT_WITH_REGISTRATION_DATE_QUERY):
data['userid'].append(item[0])
data['bot'].append(item[1])
registration_date = datetime.strptime(item[3], '%Y-%m-%dT%H:%M:%SZ')
current_date = datetime.now()
days_since_registration = (current_date - registration_date).days
edit_count_per_day = int(item[2] / days_since_registration)
data['edit_count_per_day'].append(edit_count_per_day)
data['registration'].append(str(registration_date.date()))
df = pd.DataFrame(data)
df = df.sort_values(by=['edit_count_per_day', 'bot'])
print_df(df, ['userid', 'bot', 'edit_count_per_day', 'registration'], ['12', '35', '20', '20'])
def print_editor_count(cls, mode='none'):
data = {
'url': [],
'editor_count': []
}
for item in db.execute(cls.EDITOR_COUNT_QUERIES[mode]):
data['url'].append(item[0])
data['editor_count'].append(item[1])
df = pd.DataFrame(data)
df = df.sort_values(by=['editor_count', 'url'])
print_df(df, ['url', 'editor_count'], ['90', '15'])
def print_rights_of_bot_in_bot_group(cls):
data = {
'right': []
}
for item in db.execute(cls.RIGHTS_OF_BOTS_IN_GROUP_BOT):
if item[1] is not None:
data['right'] += item[1].split(',')
df = pd.DataFrame(data)
df = df.groupby(['right']).size().reset_index(name='count')
df = df.sort_values(by=['count', 'right'], ascending=[False, True])
print_df(df, ['right', 'count'], ['30', '30'])
def plot_bots_groups_distribution(cls):
data = {
'groups': [],
'count': []
}
for item in db.execute(cls.GROUPED_GROUPS_OF_BOTS_QUERY):
groups = item[0].split(',')
groups.sort()
data['groups'].append(', '.join(groups))
data['count'].append(item[1])
df = pd.DataFrame(data)
df['%'] = round(df['count'] / df['count'].sum() * 100, 2)
print_df(df, ['groups', 'count', '%'], ['60', '15', '15'])
def print_groups_of_bots_without_request(cls):
data = {
'group': list(itertools.chain.from_iterable(
[
item[1].split(',')
for item
in db.execute(cls.GROUPS_OF_BOTS_WITHOUT_REQUEST_QUERY)
if item[1] is not None
]
))
}
df = pd.DataFrame(data)
df = df.groupby(['group']).size().reset_index(name='count')
df = df.sort_values(by=['count', 'group'], ascending=[False, True])
print_df(df, ['group', 'count'], ['30', '30'])
def print_edit_count(cls):
data = {
'userid': [],
'bot': [],
'edit_count': [],
'registration': []
}
for item in db.execute(cls.EDIT_COUNT_WITH_REGISTRATION_DATE_QUERY):
data['userid'].append(item[0])
data['bot'].append(item[1])
data['edit_count'].append(item[2])
data['registration'].append(str(datetime.strptime(item[3], '%Y-%m-%dT%H:%M:%SZ').date()))
df = pd.DataFrame(data)
df = df.sort_values(by=['edit_count', 'bot'])
print_df(df, ['userid', 'bot', 'edit_count', 'registration'], ['12', '35', '12', '20'])
def plot_bots_groups_without_implicit_groups_distribution(cls):
data = {
'groups': []
}
for item in db.execute(cls.GROUPED_GROUPS_OF_BOTS_WITHOUT_IMPLICIT_GROUPS_QUERY):
all_groups = set(item[0].split(','))
implicit_groups = set(item[1].split(','))
explicit_groups = all_groups - implicit_groups
explicit_groups = list(explicit_groups)
explicit_groups.sort()
data['groups'].append(', '.join(explicit_groups))
df = pd.DataFrame(data)
df = df.groupby(['groups']).size().reset_index(name='count')
df = df.sort_values(by=['count', 'groups'], ascending=[False, True])
df['%'] = round(df['count'] / df['count'].sum() * 100, 2)
print_df(df, ['groups', 'count', '%'], ['40', '15', '15'])