return reduce(lambda x, y: pd.merge(x, y, on="category"), partial_dfs)
if __name__ == "__main__":
load_dotenv()
dw_api_token = os.getenv("DATAWRAPPER_API_TOKEN")
dw = Datawrapper(access_token=dw_api_token)
data = pd.read_csv("data/maja_horst_paper_table.csv")
# Gender
chart_info = dw.create_chart(
title="Gender of the interviewed scientists",
chart_type=PIE_CHART,
data=prepare_column(data, "Gender"),
)
dw.update_description(
chart_info["id"],
source_name="Horst, 2013",
source_url="https://doi.org/10.1177/1075547013487513",
)
properties = {
"visualize": {
"custom-colors": {"Male": GREEN, "Female": PURPLE},
"inside_labels": {"enabled": False},
"color_key": {
"stack": False,
"enabled": False,
"position": "top",
class Map:
def __init__(self,
config,
map_folder_id,
access_token=None,
basemapCMD=None,
state_id=None):
self.config = config
self.map_folder_id = map_folder_id
self.dw = Datawrapper(access_token)
self.basemapCMD = basemapCMD
self.state_id = state_id
# __dadosFarol__
self.map_data = self.__dadosFarol__()
def __dadosFarol__(self):
# Puxa os dados do Farol
if self.state_id:
data = (get_cities_farolcovid_main.now(
self.config).query(f"state_id == '{self.state_id}'")[[
"city_id",
"city_name",
"overall_alert",
"deaths",
"subnotification_rate",
]].rename(columns={"city_id": "ID"}))
else:
data = (get_states_farolcovid_main.now(
self.config).sort_values("state_id").reset_index(drop=True)[[
"state_id",
"state_name",
"overall_alert",
"deaths",
"subnotification_rate",
]].rename(columns={"state_id": "ID"}))
data = data.assign(
Value=lambda df: df["overall_alert"].fillna(-1),
overall_alert=lambda df: df["overall_alert"].map(self.config["br"][
"farolcovid"]["categories"]).fillna("-"),
)
return data
def createMap(self):
# Cria o Mapa
stateMap = self.dw.create_chart(
title=" ",
chart_type="d3-maps-choropleth",
data=self.map_data,
folder_id=self.map_folder_id,
)
if self.state_id:
self.dw.add_data(stateMap["publicId"], self.map_data)
mapContour = {
"axes": {
"keys": "ID",
"values": "Value"
},
"publish": {
"embed-width": 600,
"chart-height": 653.3333740234375,
"embed-height": 723,
},
"visualize": {
"basemap": f"{self.basemapCMD}"
},
}
self.dw.update_metadata(stateMap["publicId"], mapContour)
self.dw.update_chart(stateMap["publicId"], theme="datawrapper")
return stateMap["publicId"]
def applyDefaultLayout(self, mapID):
# Colunas com dados a serem mostrados no hover
fields = {
"ID": "ID",
"Value": "Value",
"deaths": "deaths",
"overall_alert": "overall_alert",
"subnotification_rate": "subnotification_rate",
}
if self.state_id:
map_key_attr = "CD_GEOCMU"
title = "{{ city_name }}"
fields["city_name"] = "city_name"
else:
map_key_attr = "postal"
title = "{{ state_name }}"
fields["state_name"] = "state_name"
# Aplica o layout
DEFAULT_LAYOUT = {
"data": {
"transpose": False,
"column-format": {
"ID": {
"type": "text",
"ignore": False,
"number-append": "",
"number-format": "auto",
"number-divisor": 0,
"number-prepend": "",
}
},
},
"visualize": {
"tooltip": {
"body": """<p>Alerta: {{ overall_alert }}</p>
<p>Total de Mortes: {{ deaths }}</p>
<p>Subnotificação: {{ subnotification_rate }}</p>""",
"title": title,
"fields": fields,
},
"map-key-attr": map_key_attr,
"map-key-auto": False,
"map-type-set": "true",
"gradient": {
"stops": [{
"p": 0,
"v": -2
}, {
"p": 1,
"v": 4
}],
"colors": [
{
"c": "#c4c4c4",
"p": 0
},
{
"c": "#c4c4c4",
"p": 1 / 6
}, # v = -1 (null)
{
"c": "#0990A7",
"p": 2 / 6
}, # v = 0 (novo normal)
{
"c": "#F7B502",
"p": 3 / 6
}, # v = 1 (moderado)
{
"c": "#F77800",
"p": 4 / 6
}, # v = 2 (alto)
{
"c": "#F22E3E",
"p": 5 / 6
}, # v = 3 (altissimo)
{
"c": "#F22E3E",
"p": 1
},
],
# "domain": [0, 0.2, 0.4, 0.6, 0.8],
},
},
}
self.dw.update_metadata(mapID, DEFAULT_LAYOUT)
def updateMap(self, mapID):
# Read farol data
self.dw.add_data(mapID, self.map_data)
# Update layout
self.applyDefaultLayout(mapID)
# Update chart on DW
self.dw.update_chart(mapID, title="")
class Activity(commands.Cog):
def __init__(self, bot: commands.Bot):
self.bot = bot
self.dw = Datawrapper(access_token=token) # pylint: disable=invalid-name
async def _comparedw(self, ctx: commands.Context, guild_id: int,
terme1: str, terme2: str):
"""Compare deux tendances"""
if False in (str_input_ok(terme1), str_input_ok(terme2)):
await ctx.send(
"Je ne peux pas faire de tendance avec une expression vide.")
return
# Si les deux mêmes, faire un _trend
if terme1 == terme2:
return await self._trenddw(ctx, guild_id, terme1)
temp_msg: discord.Message = await ctx.send(
f"Je génère les tendances comparées de **{terme1}** et **{terme2}**... 🐝"
)
jour_debut = date.today() - timedelta(days=PERIODE)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(terme1)) /
fn.COUNT(Message.message_id)).alias("terme1"),
(fn.SUM(Message.content.contains(terme2)) /
fn.COUNT(Message.message_id)).alias("terme2"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
msg_par_jour = pandas.read_sql(query_sql, db.connection())
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(terme1)
if custom_emoji_str:
terme1 = custom_emoji_str
custom_emoji_str = emoji_to_str(terme2)
if custom_emoji_str:
terme2 = custom_emoji_str
# Renommage des colonnes
msg_par_jour = msg_par_jour.rename(columns={
"terme1": terme1,
"terme2": terme2
})
# Remplir les dates manquantes
msg_par_jour = msg_par_jour.set_index("date")
msg_par_jour.index = pandas.DatetimeIndex(msg_par_jour.index)
msg_par_jour.reset_index(level=0, inplace=True)
msg_par_jour = msg_par_jour.rename(columns={"index": "date"})
# Rolling average
msg_par_jour[terme1] = msg_par_jour.get(terme1).rolling(
ROLLING_AVERAGE).mean()
msg_par_jour[terme2] = msg_par_jour.get(terme2).rolling(
ROLLING_AVERAGE).mean()
properties = {
"annotate": {
"notes":
f"Moyenne mobile sur les {ROLLING_AVERAGE} derniers jours. Insensible à la casse et aux accents."
},
"visualize": {
"labeling": "top",
"base-color": "#DFC833",
"line-widths": {
terme1: 1,
terme2: 1
},
"custom-colors": {
terme1: "#DFC833",
terme2: 0
},
"y-grid": "off",
},
}
# Send chart
await self.__send_chart(
ctx,
f"'{terme1}' vs '{terme2}'",
f"Tendances dans les messages postés sur {guild_name}",
"d3-lines",
msg_par_jour,
properties,
)
await temp_msg.delete()
async def _trenddw(self, ctx: commands.Context, guild_id: int, terme: str):
"""Trend using Datawrapper"""
if not str_input_ok(terme):
await ctx.send(
"Je ne peux pas faire de tendance avec une expression vide.")
return
temp_msg: discord.Message = await ctx.send(
f"Je génère les tendances de **{terme}**... 🐝")
jour_debut = date.today() - timedelta(days=PERIODE)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(terme)) /
fn.COUNT(Message.message_id)).alias("messages"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
# Exécution requête SQL
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
msg_par_jour = pandas.read_sql(query_sql, db.connection())
# Remplir les dates manquantes
msg_par_jour = msg_par_jour.set_index("date")
msg_par_jour.index = pandas.DatetimeIndex(msg_par_jour.index)
msg_par_jour.reset_index(level=0, inplace=True)
msg_par_jour = msg_par_jour.rename(columns={"index": "date"})
# Rolling average
msg_par_jour["messages"] = msg_par_jour.rolling(ROLLING_AVERAGE).mean()
properties = {
"annotate": {
"notes":
f"Moyenne mobile sur les {ROLLING_AVERAGE} derniers jours. Insensible à la casse et aux accents."
},
"visualize": {
"base-color": "#DFC833",
"fill-below": True,
"labeling": "off",
"line-widths": {
"messages": 1
},
"y-grid": "off",
},
}
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(terme)
if custom_emoji_str:
terme = custom_emoji_str
# Send chart
await self.__send_chart(
ctx,
f"Tendances pour '{terme}'",
f"Dans les messages postés sur {guild_name}",
"d3-lines",
msg_par_jour,
properties,
)
await temp_msg.delete()
async def __send_chart(
self,
ctx,
title: str,
intro: str,
chart_type: str,
data: Any,
properties: Dict[str, Any],
) -> None:
"""Create, send and delete chart"""
chart_id = await self._generate_chart(title, intro, chart_type, data,
properties)
# Envoyer image
filepath = f"/tmp/{chart_id}.png"
self.dw.export_chart(chart_id, filepath=filepath)
await ctx.send(file=discord.File(filepath, "abeille.png"))
# Suppression de DW et du disque
self.dw.delete_chart(chart_id=chart_id)
os.remove(filepath)
async def _generate_chart(
self,
title: str,
intro: str,
chart_type: str,
data: Any,
properties: Dict[str, Any],
) -> str:
new_chart_info = self.dw.create_chart(title=title,
chart_type=chart_type,
data=data)
chart_id = new_chart_info["id"]
# Update
self.dw.update_chart(chart_id, language="fr-FR", theme="pageflow")
self.dw.update_description(
chart_id,
byline="Abeille, plus d'informations sur kutt.it/Abeille",
intro=intro,
)
self.dw.update_metadata(chart_id, properties)
return chart_id
async def _get_trend_img(self, guild_id: int, terme: str,
periode: int) -> Any:
jour_debut = date.today() - timedelta(days=periode)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(terme)) /
fn.COUNT(Message.message_id)).alias("messages"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
# Exécution requête SQL
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
df = pandas.read_sql(query_sql, db.connection())
# Remplir les dates manquantes
df = df.set_index("date")
df.index = pandas.DatetimeIndex(df.index)
df.reset_index(level=0, inplace=True)
df = df.rename(columns={"index": "date"})
# Rolling average
df["messages"] = df.rolling(ROLLING_AVERAGE).mean()
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(terme)
if custom_emoji_str:
terme = custom_emoji_str
title_lines = textwrap.wrap(f"Tendances de <b>'{terme}'</b>")
title_lines.append(f"<i style='font-size: 10px'>Sur {guild_name}.</i>")
title = "<br>".join(title_lines)
fig: go.Figure = px.area(
df,
x="date",
y="messages",
color_discrete_sequence=["yellow"],
# line_shape="spline",
template="plotly_dark",
title=title,
labels={
"date": "",
"messages": ""
},
)
fig.add_layout_image(
dict(
source="https://i.imgur.com/Eqy58rg.png",
xref="paper",
yref="paper",
x=1.1,
y=-0.22,
sizex=0.25,
sizey=0.25,
xanchor="right",
yanchor="bottom",
opacity=0.8,
))
return fig.to_image(format="png", scale=2)
@cog_ext.cog_slash(
name="trend",
description="Dessiner la tendance d'une expression.",
guild_ids=TRACKED_GUILD_IDS,
options=[
create_option(
name="terme",
description="Saisissez un mot ou une phrase.",
option_type=3,
required=True,
),
create_option(
name="periode",
description=
"Période de temps max sur laquelle dessiner la tendance.",
option_type=4,
required=True,
choices=[
create_choice(name="6 mois", value=182),
create_choice(name="1 an", value=365),
create_choice(name="2 ans", value=730),
create_choice(name="3 ans", value=1096),
],
),
],
)
async def trend_slash(self, ctx: SlashContext, terme: str, periode: int):
await ctx.defer()
guild_id = ctx.guild.id
img = await self._get_trend_img(guild_id, terme, periode)
# Envoyer image
await ctx.send(file=discord.File(io.BytesIO(img), "abeille.png"))
@commands.command(name="trendid")
@commands.max_concurrency(1, wait=True)
@commands.guild_only()
@commands.is_owner()
async def trend_id(self, ctx: commands.Context, guild_id: int, *,
terme: str):
temp_msg: discord.Message = await ctx.reply(
f"Je génère les tendances de **{terme}**... 🐝")
async with ctx.typing():
img = await self._get_trend_img(guild_id, terme, PERIODE)
# Envoyer image
await temp_msg.delete()
await ctx.reply(file=discord.File(io.BytesIO(img), "abeille.png"))
@cog_ext.cog_slash(
name="compare",
description="Comparer la tendance de deux expressions.",
guild_ids=TRACKED_GUILD_IDS,
options=[
create_option(
name="expression1",
description="Saisissez un mot ou une phrase.",
option_type=3,
required=True,
),
create_option(
name="expression2",
description="Saisissez un mot ou une phrase.",
option_type=3,
required=True,
),
create_option(
name="periode",
description=
"Période de temps max sur laquelle dessiner la tendance.",
option_type=4,
required=True,
choices=[
create_choice(name="6 mois", value=182),
create_choice(name="1 an", value=365),
create_choice(name="2 ans", value=730),
create_choice(name="3 ans", value=1096),
],
),
],
)
async def compare_slash(self, ctx: SlashContext, expression1: str,
expression2: str, periode: int):
await ctx.defer()
guild_id = ctx.guild.id
img = await self._get_compare_img(guild_id, expression1, expression2,
periode)
# Envoyer image
await ctx.send(file=discord.File(io.BytesIO(img), "abeille.png"))
async def _get_compare_img(self, guild_id: int, expression1: str,
expression2: str, periode: int) -> Any:
jour_debut = date.today() - timedelta(days=periode)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(expression1)) /
fn.COUNT(Message.message_id)).alias("expression1"),
(fn.SUM(Message.content.contains(expression2)) /
fn.COUNT(Message.message_id)).alias("expression2"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
df = pandas.read_sql(query_sql, db.connection())
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(expression1)
if custom_emoji_str:
expression1 = custom_emoji_str
custom_emoji_str = emoji_to_str(expression2)
if custom_emoji_str:
expression2 = custom_emoji_str
# Renommage des colonnes
df = df.rename(columns={
"expression1": expression1,
"expression2": expression2
})
# Remplir les dates manquantes
df = df.set_index("date")
df.index = pandas.DatetimeIndex(df.index)
df.reset_index(level=0, inplace=True)
df = df.rename(columns={"index": "date"})
# Rolling average
df[expression1] = df.get(expression1).rolling(ROLLING_AVERAGE).mean()
df[expression2] = df.get(expression2).rolling(ROLLING_AVERAGE).mean()
title_lines = textwrap.wrap(
f"<b>'{expression1}'</b> vs <b>'{expression2}'</b>")
title_lines.append(f"<i style='font-size: 10px'>Sur {guild_name}.</i>")
title = "<br>".join(title_lines)
fig: go.Figure = px.line(
df,
x="date",
y=[expression1, expression2],
color_discrete_sequence=["yellow", "#4585e6"],
template="plotly_dark",
title=title,
render_mode="svg",
labels={
"date": "",
"variable": ""
},
)
# Hide y-axis
fig.update_yaxes(visible=False, fixedrange=True)
# Legend position
fig.update_layout(legend=dict(
title=None,
orientation="h",
y=1,
yanchor="bottom",
x=0.5,
xanchor="center",
))
fig.add_layout_image(
dict(
source="https://i.imgur.com/Eqy58rg.png",
xref="paper",
yref="paper",
x=1.1,
y=-0.22,
sizex=0.25,
sizey=0.25,
xanchor="right",
yanchor="bottom",
opacity=0.8,
))
return fig.to_image(format="png", scale=2)
@commands.command(name="vsid")
@commands.max_concurrency(1, wait=True)
@commands.guild_only()
@commands.is_owner()
async def compare_id(self, ctx: commands.Context, guild_id: int,
terme1: str, terme2: str):
temp_msg: discord.Message = await ctx.send(
f"Je génère les tendances comparées de **{terme1}** et **{terme2}**... 🐝"
)
async with ctx.typing():
img = await self._get_compare_img(guild_id, terme1, terme2,
PERIODE)
# Envoyer image
await temp_msg.delete()
await ctx.reply(file=discord.File(io.BytesIO(img), "abeille.png"))
async def cog_command_error(self, ctx: commands.Context, error):
if isinstance(error, Maintenance):
await ctx.send(
"Cette fonctionnalité est en maintenance et sera de retour très bientôt ! 🐝"
)
elif isinstance(
error,
(commands.BadArgument, commands.MissingRequiredArgument)):
await ctx.send("Vous avez mal utilisé la commande ! 🐝")
else:
await ctx.send(f"Quelque chose s'est mal passée ({error}). 🐝")
@commands.max_concurrency(1, wait=True)
@cog_ext.cog_slash(
name="rank",
description="Votre classement dans l'utilisation d'une expression.",
guild_ids=TRACKED_GUILD_IDS,
options=[
create_option(
name="expression",
description="Saisissez un mot ou une phrase.",
option_type=3,
required=True,
),
],
)
async def rank_slash(self, ctx: SlashContext, expression: str):
await ctx.defer()
expression = expression.strip()
author = ctx.author
author_id = hashlib.pbkdf2_hmac(hash_name,
str(author.id).encode(), salt,
iterations).hex()
guild_id = ctx.guild.id
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
with db:
with db.bind_ctx([Message]):
rank_query = fn.rank().over(
order_by=[fn.COUNT(Message.message_id).desc()])
subq = (Message.select(
Message.author_id, rank_query.alias("rank")).where(
Message.content.contains(expression)).group_by(
Message.author_id))
# Here we use a plain Select() to create our query.
query = (Select(columns=[subq.c.rank]).from_(subq).where(
subq.c.author_id == author_id).bind(db)
) # We must bind() it to the database.
rank = query.scalar()
if rank is None:
result = f"Vous n'avez jamais employé l'expression *{expression}*."
elif rank == 1:
result = f"🥇 Vous êtes le membre ayant le plus employé l'expression *{expression}*."
elif rank == 2:
result = f"🥈 Vous êtes le 2ème membre à avoir le plus employé l'expression *{expression}*."
elif rank == 3:
result = f"🥉 Vous êtes le 3ème membre à avoir le plus employé l'expression *{expression}*."
else:
result = f"Vous êtes le **{rank}ème** membre à avoir le plus employé l'expression *{expression}*."
await ctx.send(result)