TDM 20100: Project 10 - MongoDB Introduction 1

mkdir -p $SCRATCH/mongodb_data; apptainer run -B $SCRATCH/mongodb_data:/data/db /anvil/projects/tdm/apps/containers/images/mongodb.sif

from pymongo import MongoClient
import pandas as pd
import json

# create a client to connect to the local MongoDB instance
client = MongoClient('mongodb://localhost:27017/')

# Create or connect to a database
db = client['movies_db']

# Create or connect to a collection
movies = db['movies']

# Read the CSV file and convert to MongoDB documents
df = pd.read_csv('/anvil/projects/tdm/data/MovieLens/movies_metadata.csv')

# Convert DataFrame to list of dictionaries
movies_data = df.to_dict('records')

# Insert the data into MongoDB
result = movies.insert_many(movies_data)
print(f"Inserted {len(result.inserted_ids)} movies")

# Find all movies (limit to first 5 for display)
sample_movies = list(movies.find().limit(5))
print(f"Total movies in collection: {movies.count_documents({})}")
print("\nSample movie documents:")
for movie in sample_movies:
    print(f"Title: {movie.get('title', 'N/A')}")
    print(f"Release Date: {movie.get('release_date', 'N/A')}")
    print(f"Vote Average: {movie.get('vote_average', 'N/A')}")
    print("---")

# Get the structure of a movie document
sample_movie = movies.find_one()
print("Movie document structure:")
for key, value in sample_movie.items():
    print(f"{key}: {type(value).__name__}")
    if isinstance(value, str) and len(str(value)) > 100:
        print(f"  Value: {str(value)[:100]}...")
    else:
        print(f"  Value: {value}")
    print()

# Find movies with rating above 8.0
high_rated_movies = list(movies.find({"vote_average": {"$gt": 8.0}}))
print(f"Movies with rating above 8.0: {len(high_rated_movies)}")
for movie in high_rated_movies[:5]:  # Show first 5
    print(f"{movie['title']} - Rating: {movie['vote_average']}")

# Example: Find movies with rating above 7.0 AND released after 2000
recent_good_movies = list(movies.find({
    "$and": [
        {"vote_average": {"$gt": 7.0}},
        {"release_date": {"$regex": "200[0-9]|201[0-9]|202[0-9]"}}  # Regex for years 2000+, as the release year is a string. This is a common pattern for dates in MongoDB.
    ]
}))
print(f"Good movies released after 2000: {len(recent_good_movies)}")
for movie in recent_good_movies[:5]:  # Show first 5
    print(f"{movie['title']} - Rating: {movie['vote_average']}, Year: {movie['release_date'][:4]}")

# YOUR CODE HERE: Find movies with rating above 6.0 AND released in the 1990s
# Hint: Use $and with vote_average > 6.0 and release_date matching "199[0-9]"

# Example: Find action movies using regex pattern matching
action_movies = list(movies.find({
    "genres": {"$regex": "Action", "$options": "i"}  # Case insensitive search
}))
print(f"Action movies: {len(action_movies)}")
for movie in action_movies[:5]:  # Show first 5
    print(f"{movie['title']} - Genres: {movie.get('genres', 'N/A')}")

# YOUR CODE HERE: Find comedy movies using regex pattern matching
# Hint: Use genres field with $regex for "Comedy"

# Find all movies sorted by rating (descending)
movies_by_rating = list(movies.find().sort("vote_average", -1))
print("Movies sorted by rating (highest first):")
for movie in movies_by_rating[:5]:  # Show top 5
    print(f"{movie['title']} - Rating: {movie['vote_average']}")

# Find the highest rated movie
highest_rated = movies.find().sort("vote_average", -1).limit(1)
top_movie = list(highest_rated)[0]
print(f"Highest rated movie: {top_movie['title']} - Rating: {top_movie['vote_average']}")

# YOUR CODE HERE: Find the 3 most popular movies released after 2000
# Hint: Use find() with release_date regex, sort by popularity descending, limit to 3

# Example: Count movies by criteria
total_movies = movies.count_documents({})
high_rated_count = movies.count_documents({"vote_average": {"$gt": 8.0}})
recent_movies_count = movies.count_documents({"release_date": {"$regex": "201[0-9]|202[0-9]"}})

print(f"Total movies: {total_movies}")
print(f"Movies with rating above 8.0: {high_rated_count}")
print(f"Movies released in 2010s or 2020s: {recent_movies_count}")

# YOUR CODE HERE: Count movies with rating above 7.0 and movies released in the 1990s
# Hint: Use count_documents() with appropriate conditions

pipeline = [
    {"$match": {"field": "value"}},  # Filter documents (like WHERE)
    {"$group": {"_id": "$field", "count": {"$sum": 1}}},  # Group and aggregate
    {"$sort": {"count": -1}}  # Sort results
    # ... # other operations can be added here
]

result = collection.aggregate(pipeline)

# Count movies by genre (this is a simplified example - real genre data might be more complex)
pipeline = [
    {"$group": {"_id": "$genres", "count": {"$sum": 1}}},
    {"$sort": {"count": -1}},
    {"$limit": 10}
]

genre_counts = list(movies.aggregate(pipeline))

print("Movies by genre (top 10):")
for genre in genre_counts:
    print(f"{genre['_id']}: {genre['count']} movies")

# Average rating by release year
pipeline = [
    {"$addFields": {
        "year": {"$substr": ["$release_date", 0, 4]}  # Extract year from date
    }},
    {"$match": {"year": {"$ne": ""}}},  # Filter out empty years
    {"$group": {
        "_id": "$year",
        "avg_rating": {"$avg": "$vote_average"},
        "count": {"$sum": 1}
    }},
    {"$sort": {"_id": -1}},  # Sort by year descending
    {"$limit": 10}
]

avg_ratings = list(movies.aggregate(pipeline))
print("Average rating by year (recent years):")
for year in avg_ratings:
    print(f"{year['_id']}: {year['avg_rating']:.2f} rating ({year['count']} movies)")

# Average rating by popularity ranges
pipeline = [
    {"$match": {
        "popularity": {"$exists": True, "$gt": 0},
        "vote_average": {"$gt": 0}
    }},
    {"$addFields": {
        "popularity_range": {
            "$switch": {
                "branches": [
                    {"case": {"$lt": ["$popularity", 10]}, "then": "Low (0-10)"},
                    {"case": {"$lt": ["$popularity", 50]}, "then": "Medium (10-50)"},
                    {"case": {"$lt": ["$popularity", 100]}, "then": "High (50-100)"}
                ],
                "default": "Very High (100+)"
            }
        }
    }},
    {"$group": {
        "_id": "$popularity_range",
        "avg_rating": {"$avg": "$vote_average"},
        "avg_popularity": {"$avg": "$popularity"},
        "count": {"$sum": 1}
    }},
    {"$sort": {"avg_popularity": 1}}
]

popularity_ratings = list(movies.aggregate(pipeline))
print("Average rating by popularity ranges:")
for pop_range in popularity_ratings:
    print(f"{pop_range['_id']}: {pop_range['avg_rating']:.2f} avg rating, {pop_range['avg_popularity']:.1f} avg popularity ({pop_range['count']} movies)")

# Update a single document (find a specific movie by title)
result = movies.update_one(
    {"title": "Toy Story"},
    {"$set": {"our_rating": 9.5}}
)
print(f"Updated {result.modified_count} document")

# Check the update
updated_movie = movies.find_one({"title": "Toy Story"})
print(f"Toy Story's our rating: {updated_movie.get('our_rating', 'No rating')}")

# Update all movies to add a 'watched' field
result = movies.update_many(
    {},
    {"$set": {"watched": False}}
)
print(f"Updated {result.modified_count} documents with watched field")

# Delete movies with very low ratings (be careful with this!)
result = movies.delete_many({"vote_average": {"$lt": 2.0}})
print(f"Deleted {result.deleted_count} movies with rating below 2.0")

# Check how many movies remain
remaining_movies = movies.count_documents({})
print(f"Remaining movies: {remaining_movies}")

# Delete movies from a specific year (example: 1990)
result = movies.delete_many({"release_date": {"$regex": "1990"}})
print(f"Deleted {result.deleted_count} movies from 1990")

# Check remaining movies
remaining_movies = movies.count_documents({})
print(f"Remaining movies: {remaining_movies}")

# Show some sample remaining movies
sample_movies = list(movies.find().limit(3))
print("Sample remaining movies:")
for movie in sample_movies:
    print(f"- {movie['title']} ({movie.get('release_date', 'N/A')})")