Tabla de contenidos

  • 1  Preparativos
    • 1.1  Previsualizar los datos
  • 2  Consultas y Análisis
    • 2.1  Encontrar el número de libros publicados después del 1 de enero de 2000
    • 2.2  Encontrar el número de reseñas de usuarios y la calificación promedio para cada libro.
    • 2.3  Identificar la editorial que ha publicado el mayor número de libros
    • 2.4  Identificar al autor que tiene la más alta calificación promedio del libro
    • 2.5  Encuentra el número promedio de reseñas de texto entre los usuarios que calificaron más de 50 libros.
  • 3  Conclusiones

Introducción¶

El coronavirus tomó al mundo entero por sorpresa, cambiando la rutina diaria de todos y todas. Los habitantes de las ciudades ya no pasaban su tiempo libre fuera, yendo a cafés y centros comerciales; sino que más gente se quedaba en casa, leyendo libros. Eso atrajo la atención de las startups (empresas emergentes) que se apresuraron a desarrollar nuevas aplicaciones para los amantes de los libros.

Se ha proporcionado una base de datos de uno de los servicios que compiten en este mercado. Contiene datos sobre libros, editoriales, autores y calificaciones de clientes y reseñas de libros. Esta información se utilizará para generar una propuesta de valor para un nuevo producto.

Descripción de los datos¶

La base de datos proporcionada se compone de cinco sets de datos.

books:

Contiene datos sobre libros:

  • book_id — identificación del libro
  • author_id — identificación del autor o autora
  • title — título
  • num_pages — número de páginas
  • publication_date — fecha de la publicación
  • publisher_id — identificación de la editorial

authors:

Contiene datos sobre autores:

  • author_id — identificación del autor o autora
  • author — el autor o la autora

publishers:

Contiene datos sobre editoriales:

  • publisher_id — identificación de la editorial
  • publisher — la editorial

ratings:

Contiene datos sobre las calificaciones de usuarios:

  • rating_id — identificación de la calificación
  • book_id — identificación del libro
  • username — el nombre del usuario que revisó el libro
  • rating — calificación

reviews:

Contiene datos sobre las reseñas de los y las clientes:

  • review_id — identificación de la reseña
  • book_id — identificación del libro
  • username — el nombre del usuario que revisó el libro
  • text — el texto de la reseña

Se proporciona también el diagrama de datos para una mejor comprensión de las relaciones entre las tablas.

Image.png

Pauta de trabajo¶

Antes de comenzar a hacer consultas a la base de datos se necesitan hacer los preparativos, que constan de.

  • Importar librerías necesarias.
  • Conectarnos a la base de datos.
  • Definir una función para facilitar la realización de las consultas.
  • Previsualizar los datos.

Para poder llevar a cabo exitosamente la tarea de análisis se responderán las siguientes preguntas sobre el contenido de los datos.

  • Encontrar el número de libros publicados después del 1 de enero de 2000.
  • Encontrar el número de reseñas de usuarios y la calificación promedio para cada libro.
  • Identificar la editorial que ha publicado el mayor número de libros con más de 50 páginas (para excluir folletos y publicaciones similares del análisis).
  • Identificar al autor que tiene la más alta calificación promedio del libro: indagar solo en los libros con al menos 50 calificaciones.
  • Encontrar el número promedio de reseñas de texto entre los usuarios que calificaron más de 50 libros.

Preparativos¶

Se da inicio al proyecto importando las librerías necesarias para el desarrollo del proyecto.

In [6]:
import pandas as pd 
from sqlalchemy import create_engine

Una vez importadas las librerías, procedemos a conectar con la base de datos usando las credenciales proporcionadas.

In [20]:
db_config = { 
    'user': 'practicum_student', # username 
    'pwd': 'QnmDH8Sc2TQLvy2G3Vvh7', # password 
    'host': 'yp-trainers-practicum.cluster-czs0gxyx2d8w.us-east-1.rds.amazonaws.com', 
    'port': 5432, # connection port 
    'db': 'data-analyst-final-project-db' # the n ame of the database 
    } 

connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(db_config['user'],
                                                         db_config['pwd'], 
                                                         db_config['host'], 
                                                         db_config['port'], 
                                                         db_config['db']) 

engine = create_engine(connection_string, connect_args={'sslmode' : 'require'})

Una vez conectados exitosamente, construimos una función simple para ejecutar las consultas.

In [88]:
def query (query):
    results = pd.io.sql.read_sql(query, con = engine)
    return results

Además de agilizar la construcción de las consultas, la función también ahorrará líneas de código en el informe.

Previsualizar los datos¶

Siempre es buena práctica previsualizar los datos antes de trabajar con ellos, ya que, nos ayuda a familiarizarnos con la información, comenzamos con la tabla books.

In [89]:
query('SELECT * FROM books LIMIT 5')
Out[89]:
book_id author_id title num_pages publication_date publisher_id
0 1 546 'Salem's Lot 594 2005-11-01 93
1 2 465 1 000 Places to See Before You Die 992 2003-05-22 336
2 3 407 13 Little Blue Envelopes (Little Blue Envelope... 322 2010-12-21 135
3 4 82 1491: New Revelations of the Americas Before C... 541 2006-10-10 309
4 5 125 1776 386 2006-07-04 268

Esta tabla parece tener vínculos a todas las demás tablas. Seguimos con la tabla authors.

In [90]:
query('SELECT * FROM authors LIMIT 5')
Out[90]:
author_id author
0 1 A.S. Byatt
1 2 Aesop/Laura Harris/Laura Gibbs
2 3 Agatha Christie
3 4 Alan Brennert
4 5 Alan Moore/David Lloyd

Seguimos con la tabla publishers.

In [91]:
query('SELECT * FROM publishers LIMIT 5')
Out[91]:
publisher_id publisher
0 1 Ace
1 2 Ace Book
2 3 Ace Books
3 4 Ace Hardcover
4 5 Addison Wesley Publishing Company

Pasamos a la tabla ratings.

In [92]:
query('SELECT * FROM ratings LIMIT 5')
Out[92]:
rating_id book_id username rating
0 1 1 ryanfranco 4
1 2 1 grantpatricia 2
2 3 1 brandtandrea 5
3 4 2 lorichen 3
4 5 2 mariokeller 2

Finalizamos con la tabla reviews.

In [93]:
query('SELECT * FROM reviews LIMIT 5')
Out[93]:
review_id book_id username text
0 1 1 brandtandrea Mention society tell send professor analysis. ...
1 2 1 ryanfranco Foot glass pretty audience hit themselves. Amo...
2 3 2 lorichen Listen treat keep worry. Miss husband tax but ...
3 4 3 johnsonamanda Finally month interesting blue could nature cu...
4 5 3 scotttamara Nation purpose heavy give wait song will. List...

La previsualización finaliza exitosamente.

Consultas y Análisis¶

Ya estan listos los preparativos, así que procedemos a recopilar la información solicitada mediante consultas a la base de datos usando la función query.

Encontrar el número de libros publicados después del 1 de enero de 2000¶

In [94]:
query("SELECT * FROM books WHERE publication_date > '2000-01-01'")
Out[94]:
book_id author_id title num_pages publication_date publisher_id
0 1 546 'Salem's Lot 594 2005-11-01 93
1 2 465 1 000 Places to See Before You Die 992 2003-05-22 336
2 3 407 13 Little Blue Envelopes (Little Blue Envelope... 322 2010-12-21 135
3 4 82 1491: New Revelations of the Americas Before C... 541 2006-10-10 309
4 5 125 1776 386 2006-07-04 268
... ... ... ... ... ... ...
814 994 408 World War Z: An Oral History of the Zombie War 342 2006-09-12 68
815 996 571 Wyrd Sisters (Discworld #6; Witches #2) 265 2001-02-06 147
816 998 201 Year of Wonders 358 2002-04-30 212
817 999 94 You Suck (A Love Story #2) 328 2007-01-16 331
818 1000 509 Zen and the Art of Motorcycle Maintenance: An ... 540 2006-04-25 143

819 rows × 6 columns

El número de libros publicados desde el 1 de enero de 200 es de 819, se han rescatado todos los datos de estos libros.

Encontrar el número de reseñas de usuarios y la calificación promedio para cada libro.¶

In [101]:
query("""
SELECT
    books.book_id AS book_id,
    books.title AS title,
    COUNT (DISTINCT review_id) AS reviews_count,
    AVG (rating) AS average_rating
FROM
    books
    LEFT JOIN reviews ON books.book_id = reviews.book_id
    LEFT JOIN ratings ON books.book_id = ratings.book_id
GROUP BY
    books.book_id,
    books.title
ORDER BY
    average_rating DESC
""")
Out[101]:
book_id title reviews_count average_rating
0 86 Arrows of the Queen (Heralds of Valdemar #1) 2 5.00
1 901 The Walking Dead Book One (The Walking Dead #... 2 5.00
2 390 Light in August 2 5.00
3 972 Wherever You Go There You Are: Mindfulness Me... 2 5.00
4 136 Captivating: Unveiling the Mystery of a Woman'... 2 5.00
... ... ... ... ...
995 915 The World Is Flat: A Brief History of the Twen... 3 2.25
996 316 His Excellency: George Washington 2 2.00
997 202 Drowning Ruth 3 2.00
998 371 Junky 2 2.00
999 303 Harvesting the Heart 2 1.50

1000 rows × 4 columns

Se consigue un dataset con el número de reseñas y calificación promedio para cada libro.

Identificar la editorial que ha publicado el mayor número de libros¶

Para diferenciar libros de otras publicaciones como folletos o revistas se considerará como un libro cualquier publicación que supere las 50 páginas.

In [96]:
query("""
SELECT
    books.publisher_id AS publisher_id,
    publishers.publisher AS publisher,
    COUNT (books.book_id) AS books_count
FROM
    books
    LEFT JOIN publishers ON books.publisher_id = publishers.publisher_id
WHERE
    books.num_pages > 50
GROUP BY
    books.publisher_id,
    publishers.publisher_id
ORDER BY
    books_count DESC
LIMIT 5
""")
Out[96]:
publisher_id publisher books_count
0 212 Penguin Books 42
1 309 Vintage 31
2 116 Grand Central Publishing 25
3 217 Penguin Classics 24
4 35 Bantam 19

Se consigue un top 5 de las editoriales que más han publicado libros de más de 50 páginas, siendo Penguin Books la primera con 42 publicaciones.

Identificar al autor que tiene la más alta calificación promedio del libro¶

Para esta consulta de indagará sólo en publicaciones que tengan más de 50 calificaciones, ya que libros con una sola (o muy pocas) calificación mínima o máxima pueden distorsionar en gran manera los resultados que esperamos obtener.

In [97]:
query("""
SELECT
    authors.author AS author,
    AVG (rating) AS average_rating
FROM
    books
    LEFT JOIN authors ON books.author_id = authors.author_id
    LEFT JOIN ratings ON books.book_id = ratings.book_id
GROUP BY
    authors.author
HAVING
    COUNT(ratings.rating_id) >= 50
ORDER BY
    average_rating DESC
LIMIT 5
""")
Out[97]:
author average_rating
0 Diana Gabaldon 4.300000
1 J.K. Rowling/Mary GrandPré 4.288462
2 Agatha Christie 4.283019
3 Markus Zusak/Cao Xuân Việt Khương 4.264151
4 J.R.R. Tolkien 4.240964

Obtenemos un top 5, donde la clara ganadora es Diana Gabaldon con una calificación promedio de 4.3 estrellas de un total de 5.

Encuentra el número promedio de reseñas de texto entre los usuarios que calificaron más de 50 libros.¶

In [98]:
query("""
SELECT
    AVG (review_count) AS average_rating
FROM (
    SELECT
        username,
        COUNT (review_id) AS review_count
    FROM
         reviews
    WHERE
         username IN (
                    SELECT 
                        username
                    FROM
                        ratings
                    GROUP BY
                        username
                    HAVING 
                        COUNT (DISTINCT book_id) > 50
    )
    GROUP BY
        username
) AS subquery;
""")
Out[98]:
average_rating
0 24.333333

El número promedio de reseñas (reviews) de texto entre los usuarios que calificaron (rating) más de 50 libros es de 24.3, esto demuestra que los usuarios que escriben reseñas generalmente calfican (y por lo tanto leen) varios libros, incentivar a los usuarios a calificar sus lecturas podría ser un buen primer paso para aumentar la cantidad de reseñas escritas.

Conclusiones¶

La fase de preparativos transcurre sin mayor inconveniente, La función query funciona apropiadamente y ahorra muchas líneas al código, lo cual hace que el informe sea más comprensible. Las tablas tienen datos en excelente estado, lo cual es una ayuda extra al desarrollo del proyecto.

A partir de las consultas realizadas a la base de datos proporcionada, se han recopilado las siguientes conclusiones.

  • El número de libros publicados desde el 1 de enero de 200 es de 819.
  • Con 42 publicaciones, Penguin Books es la editorial que más libros (textos de más de 50 páginas) ha lanzado.
  • Diana Gabaldon es la autora mejor calificada con un promedio de 4.3 estrellas de un total de 5.
  • El número promedio de reseñas (reviews) de texto entre los usuarios que calificaron (rating) más de 50 libros es de 24.3

A partir del último punto (y como se menciona en el bloque anterior) se puede presuponer que los usuarios que escriben reseñas generalmente calfican (y por lo tanto leen) varios libros, incentivar a los usuarios a calificar sus lecturas podría ser un buen primer paso para aumentar la cantidad de reseñas escritas, esto debido a que una calificación es menos invasiva y requiere menos esfuerzo que escribir una reseña, y a su vez, una reseña escrita puede resultar más útil para un potencial comprador que una calificación.