text_html_output_2.png text_html_output_1.png
from pandasql import sqldf

import pandas as pd

import plotly.express as px

df_villagers = pd.read_csv('../input/animal-crossing/villagers.csv', encoding='utf-8')

df_villagers.drop(columns=['id', 'row_n', 'phrase', 'full_id', 'url'])

species = sqldf("SELECT species, COUNT(species) AS size FROM df_villagers GROUP BY species ORDER BY size DESC")
pie = px.pie(species, values='size', names='species', title='Villager Species', color_discrete_sequence=px.colors.qualitative.Dark24,)
pie.show()

barh = px.bar(species, x="size", y="species", orientation='h', color="size",  title='Villager Species')
barh.update_layout(
    
    autosize=False,
    height=800,
)
barh.show()

gender = sqldf("SELECT gender, COUNT(gender) AS count FROM df_villagers GROUP BY gender ORDER BY count DESC")
species_gender = sqldf("SELECT species, gender, COUNT(gender) AS count FROM df_villagers GROUP BY species, gender ORDER BY count ASC")

fig = px.pie(gender, values='count', names='gender', title='Gender Breakdown')
fig.show()

bar = px.bar(species_gender, x="species", y="count", color="gender", title="Gender and Species")
bar.show()

personality = sqldf("SELECT personality, COUNT(personality) AS count FROM df_villagers GROUP by personality ORDER BY count ASC")
species_personality = sqldf("SELECT species, personality, COUNT(personality) AS count FROM df_villagers GROUP BY species, personality")

fig = px.pie(personality, values='count', names='personality', title='Personality Types')
fig.show()

bar = px.bar(species_personality, x="count", y="species", color="personality", orientation='h', title='Personality and Species')
bar.update_layout(
    
        autosize=False,
    width=1000,
    height=1000,
)
bar.show()

df_villagers.fillna({'song': 'Not available'}, inplace = True)
df_villagers.isnull().sum()

song = sqldf("SELECT species, personality, song, COUNT(song) AS count FROM df_villagers GROUP by species, personality, song")

fig = px.scatter(song, x="species", y="personality", size="count", color="song")

fig.show()

sign = sqldf("SELECT sign, COUNT(sign) AS count FROM df_villagers GROUP BY sign")
sign_personality = sqldf("SELECT sign, personality, COUNT(sign) AS count FROM df_villagers GROUP BY sign, personality")
sign_personality_species = sqldf("SELECT species, personality, sign, COUNT(sign) AS count FROM df_villagers GROUP by species, personality, sign")

bar = px.bar(sign_personality, x="count", y="sign", color="personality", orientation='h')
bar.show()

pie = px.pie(sign, values='count', names='sign', title='Horoscope Signs')
pie.show()

fig = px.scatter(sign_personality_species, x="species", y="sign", size="count", color="personality", title="Horoscope, Personality, Species")

fig.update_layout(autosize=False,width=1000, height=1000)
fig.show()

categories = sqldf("SELECT category, COUNT(category) AS count FROM df_items GROUP BY category ORDER BY count DESC")

fig = px.pie(categories, values='count', names='category', title='Item Categories')
fig.update_layout(
    
    autosize=False,
    width=800,
    height=500,
)
fig.show()

barh = px.bar(categories, x="count", y="category", barmode='relative', orientation='h', title='Item Categories')
barh.show()

resale = sqldf('SELECT category,sell_value, buy_value from df_items')
resale = resale.dropna()
resale['resale'] = resale['sell_value'] / resale['buy_value'] * 100
resale_categories = sqldf('SELECT category, AVG(resale) AS avg_resale from resale GROUP BY category ORDER BY avg_resale DESC')
resale_categories

bar = px.bar(resale_categories, x='category', y='avg_resale', color='avg_resale', title='Average Resale Value by Item Category')
bar.update_layout(autosize=False, width=1000, height=1000)
bar.show()