<jupyter_start><jupyter_text>by <NAME>  Average Price of Books in Colombia The main objective of this project, is to calculate the average price of new printed books and ebooks in Colombia. To do so, it takes as reference one of the best library online in Colombia - Libreria de la U (https://www.libreriadelau.com/) - to scrape thousends of book items.Scraping Date: March 2020<jupyter_code>import requests
import pandas as pd
from scrapy.selector import Selector
from scrapy.http import TextResponse
import re
import unidecode
import json
# List of Edition year

# Star Url
request = requests.get('https://www.libreriadelau.com/'
                       'temas-varios/?O=OrderByReleaseDateDESC') 

response = TextResponse(request.url, body=request.text, encoding='utf-8')
num = response.xpath("//ul[@class='Año de Edición  Atributos LU']/li/a/@title").extract()

num_ediciones = list(dict.fromkeys(num)) # removiendo duplicados
print(num_ediciones)
print('\Resultados:',len(num_ediciones))

# Results for each Edition Year
year = response.xpath("//ul[@class='Año de Edición  Atributos LU']/li/a/text()").re(r'\(([^)]+)')
yearm = list(dict.fromkeys(year)) # Remove duplicates in list

ediciones=[]
for i in yearm:
    ediciones.append(int(i))
print('\nResultados por Edición:','\nTotales:',sum(ediciones), '\nMax:',max(ediciones),'\n')

ediciones.sort(reverse=True)

print(ediciones)
# iteration

# Page results structure
desde = 0
hasta = 19

num_ediciones = num_ediciones[41:] # Slice the list if it's necessary

# Variables 
autor,editorial,edicion,pag,ISBN,tipo,formato,titulo,categoria,precio,file,size,peso,tamano,acabado,link = [],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]

while desde<=max(ediciones) and hasta<=(max(ediciones)+19):
    
    for i in num_ediciones:
        
        # Change book Category in the link when repeating the process
        request = requests.get('https://www.libreriadelau.com/api/catalog_system/pub/products/search/'
            'temas-varios/'+i+'?O=OrderByPriceDESC&map=c,'
           'c,specificationFilter_71&_from='+str(desde)+'&_to='+str(hasta))

        response = TextResponse(request.url, body=request.text, encoding='utf-8')
        
        try:
            data = json.loads(response.text)
        
            if len(data)==0:
                pass

            else:
                for i in data:
                    try: autor.append(i['Autor'][0])
                    except Exception: autor.append(None)

                    try: editorial.append(i['Editorial'][0])
                    except Exception: editorial.append(None)

                    try: edicion.append(i['Año de Edición'][0])
                    except Exception: edicion.append(None)

                    try: pag.append(i['Núm. Páginas'][0])
                    except Exception: pag.append(None)

                    try: ISBN.append(i['ISXN'][0])
                    except Exception: ISBN.append(None)

                    try: tipo.append(i['Tipo'][0])
                    except Exception: tipo.append(None)

                    try: formato.append(i['items'][0]['Formato'][0])
                    except Exception: formato.append(None)

                    try: titulo.append(i['Título'][0])
                    except Exception: titulo.append(None)

                    try: categoria.append(i['categories'][0])
                    except Exception: categoria.append(None)

                    try: precio.append(i['items'][0]['sellers'][0]['commertialOffer']['ListPrice'])
                    except Exception: precio.append(None)

                    try: file.append(i['Formato Electrónico (Virtual)'][0])
                    except Exception:file.append(None)

                    try: size.append(i['Tamaño Archivo (Virtual)'][0])
                    except Exception: size.append(None)

                    try: peso.append(i['Peso (Físico)'][0])
                    except Exception: peso.append(None)

                    try: tamano.append(i['Tamaño (Físico)'][0])
                    except Exception: tamano.append(None)

                    try: acabado.append(i['Acabado (Físico)'][0])
                    except Exception: acabado.append(None)

                    try: link.append(i['link'])
                    except Exception: link.append(None)

        except Exception:
            pass
        
    desde += 20
    hasta += 20

# Load Data in Pandas
f = pd.DataFrame(data=[autor,editorial,edicion,pag,ISBN,tipo,formato,titulo,categoria,precio,file,size,peso,tamano,acabado,link], index=None)
df=f.transpose()
df.columns=['Autor','Editorial','Edicion','Paginas','ISBN','Tipo','Formato','Titulo','Categoria','Precio','File','Size-MB','Peso','Tamaño','Acabado','Link']
# Export/Import
df.to_csv(r'C:\Users\AndyJ\Desktop\App Libros\Libreria\temas-varios3.csv')
# Import CSVs as one

import pandas as pd
import glob

path = r'C:\Users\AndyJ\Desktop\App Libros\Libreria'

all_files = glob.glob(path + "/*.csv")

li = []

for filename in all_files:
    df = pd.read_csv(filename, index_col=None, header=0, dtype={
        'Acabado':'str','Autor':'str','Categoria':'str','Edicion':'str','Editorial':'str','File':'str','Formato':'str',
        'ISBN':'str','Link':'str','Paginas':'float64','Peso':'float64','Precio':'float64','Tamaño':'str','Tipo':'str',
        'Tiutlo':'str'},low_memory=False)
    li.append(df)

df = pd.concat(li, axis=0, ignore_index=True)
df.tail(2)
# Removing Column
df.drop(columns='Unnamed: 0', inplace=True)

# Removing results: price==0
df.drop(df[df.Precio<=10000].index,inplace=True)
df.drop(df[df.Precio>=300000].index,inplace=True)

# Removing duplicates
df.drop_duplicates(keep='first', inplace=True)

# Reset Index
df.reset_index(drop=True, inplace=True)

# Spliting category
df[['Cat0','Cat','Gen','Gen0']] = df.Categoria.str.split("/",expand=True)

# Removing Columns
df.drop(columns=['Cat0','Gen0'], inplace=True)

df.tail(2)
# Ebook's Average Size 
df['Size-MB'].mean()
# Average price by Format
df.groupby('Formato').agg(['mean','count'])['Precio'].sort_values('count', ascending=False)
impreso = (df['Formato']=='Libro Impreso') | (df['Formato']=='Libro Importado')
ebook = (df['Formato']=='eBook')
# Mode Price of Printed Books
df[impreso].Precio.mode()
# Average price by Category
df[impreso].groupby('Cat').agg(['mean','count'])['Precio'].sort_values(by='mean', ascending=False)
# Ebook's Average price by Category
df[ebook].groupby('Cat').agg(['mean','count'])['Precio'].sort_values(by='mean', ascending=False)
# Price Distribution
import matplotlib.ticker as ticker
import matplotlib.pyplot as plt
import numpy as np
import seaborn
%matplotlib inline

bin_values = np.arange(start=1000, stop=300000, step=5000)
df[impreso].Precio.plot.hist(bins=bin_values, figsize=[14,6], facecolor='orangered', alpha=0.75, zorder=2)
df[ebook].Precio.plot.hist(bins=bin_values, figsize=[14,6], facecolor='darkcyan', alpha=0.75,zorder=2)
plt.grid(True, zorder=0)
plt.xlabel('Precio')
plt.title('Distribución de Precios de Libros Impresosy Digitales', fontsize=16);
df[impreso].Precio.describe()
df[ebook].Precio.describe()
#  Average price by Cover
df[impreso].groupby('Acabado').agg(['mean','count'])['Precio'].sort_values(by='count', ascending=False)
# Price comparation: Rustic vs Hard Cover

Rustica = 53147.2
Dura = 67208.5

round(67208.5/53147.2 - 1,3)<jupyter_output><empty_output><jupyter_text>Price comparation: Printed Book vs Ebook<jupyter_code>fisico = df[df['Formato']=='Libro Impreso']
importado = df[df['Formato']=='Libro Importado']
digital = df[df['Formato']=='eBook']
table1 = pd.merge(fisico,digital, on=['Titulo'], how='inner',suffixes=('_fis', '_dig'))
table1['Comparativo'] = table1['Precio_dig']/table1['Precio_fis'] - 1

table2 = pd.merge(importado,digital, on=['Titulo'], how='inner',suffixes=('_fis', '_dig'))
table2['Comparativo'] = table2['Precio_dig']/table2['Precio_fis'] - 1

dfc = pd.concat(objs=[table1,table2], axis=0)
regla = (dfc['Comparativo']<0) & (dfc['Titulo'].notna()) & (dfc['Editorial_fis']==dfc['Editorial_dig']) & (dfc['Edicion_fis']==dfc['Edicion_dig'])

dfc[regla].Comparativo.describe()
# bin_values = np.arange(start=1000, stop=300000, step=5000)
dfc[regla].Comparativo.plot.hist( figsize=[14,6], facecolor='blue', alpha=0.75)
plt.grid(True)
plt.xlabel('Price')
plt.title('Dif. Porcentual de Precio Ebok respecto a Precio de Impreso ', fontsize=16);
xl = df[impreso].groupby('Cat').agg(['mean'])['Precio'].sort_values(by='mean', ascending=False)
xe = df[ebook].groupby('Cat').agg(['mean'])['Precio'].sort_values(by='mean', ascending=False)
xl.plot.bar(figsize=[14,6], facecolor='darkcyan', alpha=0.75, zorder=3, width=.6)
plt.grid(axis='y',zorder=0)
plt.ylabel('Precio')
plt.title('Libros Impresos: Precio promedio por Categoria ', fontsize=16);
xe.plot.bar(figsize=[14,6], facecolor='darkcyan', alpha=0.75, zorder=3,  width=.6)
plt.grid(axis='y',zorder=0)
plt.ylabel('Precio')
plt.title('Ebooks: Precio promedio por Categoria ', fontsize=16);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17385504668741664, 'fraction_numerical': 0.020008892841262782, 'mean_word_length': 4.01505016722408, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 11, 'https://': 3, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '409973', 'license': 'MIT', 'n_tokens_mistral': 3254, 'n_tokens_neox': 3043, 'n_words': 544, 'path': 'Scraping Project - LibreriadelaU.ipynb', 'repo_name': 'andjimbon/Scraping-Project-Price-of-Books', 'size': '9002'}