SELECT version()

SELECT qw.usuario, d.deck, s.skill, qw.ndmax, qw.zerotg, qw.zephra, qw.bryan, qw.xenoblur, qw.yamiglen, qw.rax FROM qualifier_wcs_23 qw INNER JOIN decks d ON qw.deck_id = d.deck_id INNER JOIN skills s ON qw.skill_id = s.skill_id;

SELECT 'ZeroTG' AS Servers, SUM(CASE WHEN zerotg THEN 1 ELSE 0 END) AS Duelistas FROM qualifier_wcs_23 UNION ALL SELECT 'ZephraCarl' AS Servers, SUM(CASE WHEN zephra THEN 1 ELSE 0 END) AS Duelistas FROM qualifier_wcs_23 UNION ALL SELECT 'Bryan Norén' AS Servers, SUM(CASE WHEN bryan THEN 1 ELSE 0 END) AS Duelistas FROM qualifier_wcs_23 UNION ALL SELECT 'Xenoblur' AS Servers, SUM(CASE WHEN xenoblur THEN 1 ELSE 0 END) AS Duelistas FROM qualifier_wcs_23 UNION ALL SELECT 'Yami Glen' AS Servers, SUM(CASE WHEN yamiglen THEN 1 ELSE 0 END) AS Duelistas FROM qualifier_wcs_23 UNION ALL SELECT 'Rax Yu-Gi-Oh!' AS Servers, SUM(CASE WHEN rax THEN 1 ELSE 0 END) AS Duelistas FROM qualifier_wcs_23;

servers_qualifiers = servers_qualifiers.sort_values('Duelistas', ascending=0) servers_qualifiers['Relación al Total'] = ( servers_qualifiers.Duelistas / qualifier_wcs_23.shape[0] ).apply('{:.0%}'.format) fig = px.bar(servers_qualifiers, y='Duelistas', x="Servers", title="Registro de decks con su comunidad", text="Relación al Total", color="Servers", template="ggplot2") fig.add_indicator( mode="number", value=qualifier_wcs_23.shape[0], title="Total de Duelistas", number={'font': {'size': 50}}, domain = {'x': [0.75, .95], 'y': [0.75, .95]}) fig.update_layout(showlegend=False) fig.show()

decks = ( qualifier_wcs_23.groupby(['deck'])['deck'].count() .reset_index(name='total') .sort_values('total', ascending=0) ) user_topfive = decks[0:5].total.sum() other_user_decks = decks[5::].total.sum() count_groupby_decks = [int(user_topfive), int(other_user_decks)] labels = "Top 5 mazos más usados", "Resto de mazos" fig = go.Figure(data=[go.Pie(labels=labels, values=count_groupby_decks, hole=0.3)]) fig.update_traces( hoverinfo='label+value', textinfo='percent', textfont_size=20 ) fig.update_layout( showlegend=False, title='Comparativa de mazos más usados', title_x=0.5, title_font=dict(size=25), annotations=[dict(text=f'{sum(count_groupby_decks)}<br>duelistas', x=0.5, y=0.5, font_size=20, showarrow=False)] ) fig.show()

"""Gráfico de los Top 5 """ decks_top_five = decks['deck'][0:5].tolist() resume = ( qualifier_wcs_23.groupby(['deck','skill'])['skill'].size() .reset_index(name='total') .sort_values('total', ascending=0)) topfive = resume[resume['deck'].isin(decks_top_five)].copy() topfive['deck'] = pd.Categorical( topfive['deck'], categories=decks_top_five, ordered=True) topfive['deck'] = topfive['deck'].cat.reorder_categories( decks_top_five, ordered=True) skills = topfive['skill'].unique() colors = [f'#{random.randint(0, 0xFFFFFF):06x}' for _ in range(len(skills))] color_map = {skill: color for skill, color in zip(skills, colors)} fig = px.bar( topfive, y='deck', x="total", color="skill", color_discrete_map=color_map, title="Top Decks NdMAX Qualifiers WCS 2023", text="total", orientation='h') fig.add_indicator( mode="number", value=qualifier_wcs_23['deck'].nunique(), title="Total de<br>mazos distintos", number={'font': {'size': 50}}, domain = {'x': [0.75, .95], 'y': [0.05, 0.25]}) fig.update_layout( showlegend=False, title_x=0.5, xaxis_title="Total de Usuarios", yaxis_title="Mazos", yaxis={'categoryorder': 'array', 'categoryarray': decks_top_five[::-1]} ) fig.show()

''' Gráficos de Venn y de pastel para relaciones entre comunidades más grandes ''' # Obtener los conjuntos de usuarios zero_tg = set(zerotg) zephracarl = set(zephra) bryan_noren = set(bryan) # Calcular los tamaños de los conjuntos size_100 = len(zero_tg - (zephracarl | bryan_noren)) size_010 = len(zephracarl - (zero_tg | bryan_noren)) size_001 = len(bryan_noren - (zero_tg | zephracarl)) size_110 = len((zero_tg & zephracarl) - bryan_noren) size_101 = len((zero_tg & bryan_noren) - zephracarl) size_011 = len((zephracarl & bryan_noren) - zero_tg) size_111 = len(zero_tg & zephracarl & bryan_noren) # Calcular los porcentajes total = sum( [size_100, size_010, size_001, size_110, size_101, size_011, size_111] ) percent_100 = (size_100 / total) * 100 percent_010 = (size_010 / total) * 100 percent_001 = (size_001 / total) * 100 percent_110 = (size_110 / total) * 100 percent_101 = (size_101 / total) * 100 percent_011 = (size_011 / total) * 100 percent_111 = (size_111 / total) * 100 fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(10, 5)) # Crear el gráfico de Venn venn = venn3([zero_tg, zephracarl, bryan_noren], set_labels=('ZeroTG', 'ZephraCarl', 'Bryan Norén'), ax=ax1 ) # Personalizar el gráfico de Venn con los porcentajes venn.get_label_by_id('100').set_text( f"{size_100}\n{percent_100:.2f}%") venn.get_label_by_id('010').set_text( f"{size_010}\n{percent_010:.2f}%") venn.get_label_by_id('001').set_text( f"{size_001}\n{percent_001:.2f}%") venn.get_label_by_id('110').set_text( f"{size_110}\n{percent_110:.2f}%") venn.get_label_by_id('101').set_text( f"{size_101}\n{percent_101:.2f}%") venn.get_label_by_id('011').set_text( f"{size_011}\n{percent_011:.2f}%") venn.get_label_by_id('111').set_text( f"{size_111}\n{percent_111:.2f}%") # Personalizar los colores utilizando Seaborn colors = sns.color_palette('Set3', 7) for patch, color in zip(venn.patches, colors): patch.set_facecolor(color) unique_server = size_100 + size_010 + size_001 two_communities = size_110 + size_101 + size_011 count_duelists = [unique_server, two_communities, size_111] labels = "En una sola", "En dos\ncomunidades", "Están en\nlas 3" pastel_colors = ['#92c6ff', '#ffb7ce', '#b7e3cc'] # Crear el gráfico de pastel con hueco en el centro en ax2 wedges, texts, autotexts = ax2.pie( count_duelists, labels=labels, autopct="%1.2f%%", colors=pastel_colors, wedgeprops={'edgecolor': 'white', 'linewidth': 1, 'linestyle': 'solid'}, pctdistance=0.75 ) # Agregar el círculo blanco en el centro (hueco) centre_circle = plt.Circle((0, 0), 0.4, fc='white') ax2.add_artist(centre_circle) # Calcular el total y mostrarlo en el centro del hueco ax2.text(0, 0, f'{total}\nduelistas', ha='center', va='center', fontsize=12) plt.suptitle('Duelistas en común entre comunidades') ax1.set_title('Gráfico de Venn') ax2.set_title('Relación en cuántas están') plt.show()

session_info.show()