Spaces:
Sleeping
Sleeping
File size: 3,280 Bytes
9826f0b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
import pandas as pd
import sqlite3
# Load the Anime Dataset as a Pandas DataFrame
df = pd.read_csv('data/anime-dataset-2023.csv')
# Separate the DataFrame into separate tables
# Genre Table
# Create a separate DataFrame using Genres column
genres_df = df['Genres'].str.split(', ').explode().str.strip().drop_duplicates().reset_index(drop=True)
genres_df = pd.DataFrame({'genre_id': range(1, len(genres_df) + 1), 'genre_name': genres_df})
# Exclude some Genres
excluded_genres = ['Hentai', 'UNKNOWN', 'Erotica', 'Ecchi']
genres_df = genres_df[~genres_df['genre_name'].isin(excluded_genres)]
# Anime Table
# Filter out animes that contain excluded genres
pattern = '|'.join(excluded_genres)
anime_df = df[~df['Genres'].str.contains(pattern, na=True)]
# Filter out animes without English Names
anime_df = anime_df[anime_df['English name'] != 'UNKNOWN']
# Filter out animes without Scores
anime_df = anime_df[anime_df['Score'] != 'UNKNOWN']
# Filter out animes without Synopsis
anime_df = anime_df[anime_df['Synopsis'] != 'No description available for this anime.']
# Sort by Score and Keep only Top 1000 Animes
anime_df = anime_df.sort_values(by='Score', ascending=False)
anime_df = anime_df.head(1000)
# Rename Columns
anime_df = anime_df.rename(columns={'English name': 'name', 'Score': 'score', 'Synopsis': 'synopsis'})
# AnimeGenre Table
# Create a mapping of genre_name to genre_id
genre_mapping = genres_df.set_index('genre_name')['genre_id'].to_dict()
# Explode genres for filtered animes and map to genre_ids
anime_genre_df = anime_df[['anime_id', 'Genres']].copy()
anime_genre_df = anime_genre_df.assign(genre_name=anime_genre_df['Genres'].str.split(', ')).explode('genre_name')
anime_genre_df['genre_name'] = anime_genre_df['genre_name'].str.strip()
anime_genre_df['genre_id'] = anime_genre_df['genre_name'].map(genre_mapping)
anime_genre_df = anime_genre_df[['anime_id', 'genre_id']].dropna()
anime_genre_df['genre_id'] = anime_genre_df['genre_id'].astype(int)
# Final Clean Up
# Keep only anime_id, Name, Score and Synopsis Columns
anime_df = anime_df[['anime_id', 'name', 'score', 'synopsis']]
anime_df = anime_df.rename(columns={'anime_id': 'id'})
genres_df = genres_df.rename(columns={'genre_id': 'id'})
SCHEMA_SQL = '''
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS Anime (
id INTEGER PRIMARY KEY,
name VARCHAR(50),
score FLOAT,
synopsis TEXT
);
CREATE TABLE IF NOT EXISTS Genre (
id INTEGER PRIMARY KEY,
genre_name VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS AnimeGenre (
anime_id INTEGER NOT NULL,
genre_id INTEGER NOT NULL,
PRIMARY KEY (anime_id, genre_id),
FOREIGN KEY (anime_id) REFERENCES Anime(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (genre_id) REFERENCES Genre(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_anime_id ON AnimeGenre(anime_id);
CREATE INDEX IF NOT EXISTS idx_genre_id ON AnimeGenre(genre_id);
'''
with sqlite3.connect('anime.db') as conn:
conn.executescript(SCHEMA_SQL)
anime_df.to_sql('Anime', conn, if_exists='delete_rows', index=False, method='multi')
genres_df.to_sql('Genre', conn, if_exists='delete_rows', index=False, method='multi')
anime_genre_df.to_sql('AnimeGenre', conn, if_exists='delete_rows', index=False, method='multi')
|