import json
import numpy as np
import pandas as pd
import geopandas as gpd
import plotly.express as px
import matplotlib.pyplot as plt

# Cargar los datos previamente extraídos de su web
# https://www.coches.net/segunda-mano/?MinInstalmentPrice=1&hasInstalment=true?pg=1

df = pd.read_csv('scraping_stock_financ_cochesNet_19112025.csv', low_memory=False)
df = df.dropna(subset=['financing_lender'])

print('TOTAL DE VEHÍCULOS en el dataset extraído:', f"{len(df):,.0f}".replace(',', '.'))
print('Columnas del dataset:', list(df.columns))

TOTAL DE VEHÍCULOS en el dataset extraído: 29.615
Columnas del dataset: ['id', 'title', 'manufacturer', 'model', 'version', 'year', 'kms', 'cubic_capacity', 'environmental_label', 'doors', 'power', 'seating_capacity', 'consumption_urban', 'consumption_extra_urban', 'consumption_mixed', 'max_speed', 'acceleration', 'tank_capacity_in_liters', 'trunk_capacity_in_liters', 'weight', 'width', 'height', 'length', 'warranty_months', 'warranty_is_official', 'color', 'cash_price', 'financing_price', 'financing_capital', 'financing_computed_price', 'financing_terms', 'financing_entry', 'financing_min_entry', 'financing_max_entry', 'financing_first_fee', 'financing_fee', 'financing_lender', 'financing_tae', 'financing_tin', 'financing_opening_percentage', 'financing_opening_expenses', 'financing_total_interest', 'financing_amount_to_finance', 'financing_total_loan_amount', 'financing_total_price_terms', 'financing_total_terms_amount', 'financing_total_amount_due', 'financing_coefficient', 'has_taxes', 'creation_date', 'publication_date', 'url', 'status', 'contact_email', 'contact_phone1', 'contact_phone2', 'rating_score_avg', 'rating_comments_number', 'has_reservation', 'has_financing_product', 'client_name', 'client_pageslug', 'client_url', 'client_location_address', 'client_location_province', 'client_location_zipcode', 'client_location_geolocation_latitude', 'client_location_geolocation_longitude', 'statistics_views', 'statistics_shares', 'statistics_favorites', 'statistics_total_calls', 'statistics_total_contacts', 'statistics_total_scheduled_calls', 'statistics_total_attended_calls', 'statistics_messages', 'statistics_total_missed_calls']

financieras = np.unique(df['financing_lender'])
counts = [len(df[df['financing_lender'] == f]) for f in financieras]

percentages = [c / sum(counts) * 100 for c in counts]

MIN_PERCENTAGE = 3
financieras_filtradas = []
counts_filtrados = []
otras_count = 0

for f, c, p in zip(financieras, counts, percentages):
    if p >= MIN_PERCENTAGE:
        financieras_filtradas.append(f.upper())
        counts_filtrados.append(c)
    else:
        otras_count += c

if otras_count > 0:
    financieras_filtradas.append("OTRAS")
    counts_filtrados.append(otras_count)

orden = np.argsort(counts_filtrados)[::-1]
financieras_filtradas = [financieras_filtradas[i] for i in orden]
counts_filtrados = [counts_filtrados[i] for i in orden]

color_map = {'SANTANDER': '#ED0808', 'BBVA': '#398CC9', 'CAIXA': '#FFCE08'}

plt.figure(figsize=(5, 5))
wedges, texts, autotexts = plt.pie(
    counts_filtrados,
    labels=financieras_filtradas,
    autopct='%1.1f%%',
    counterclock=False,
    startangle=90,
    pctdistance=0.8,
    colors=[color_map.get(f, '#7f7f7f') for f in financieras_filtradas]
)

plt.setp(autotexts, size=8, color="black")
plt.setp(texts, size=10, fontweight='bold')
plt.legend(
    wedges,
    [f"{f} ({format(c, ',').replace(',', '.')})" for f, c in zip(financieras_filtradas, counts_filtrados)],
    title="Financieras",
    loc="upper right",
    bbox_to_anchor=(1.3, 1)
)
plt.title('Distribución anuncios de financieras en Coches.net')
plt.show()

financieras = np.unique(df['financing_lender'])
ads_counts = []
avg_prices = []

for financiera in financieras:
    ads = len(df[df['financing_lender'] == financiera])
    avg_price = df[df['financing_lender'] == financiera]['cash_price'].mean()
    ads_counts.append(ads)
    avg_prices.append(avg_price)

fig, ax = plt.subplots(figsize=(10, 6))

color_map = {'SANTANDER': '#ED0808', 'BBVA': '#398CC9', 'CAIXA': '#FFCE08', 'STELLANTIS': '#001137', 'MOBILIZE-NISSAN': '#CED0CD', 'MOBILIZE': '#FE672E', 'LENDROCK': '#EE6B3F', 'BMW-BANK': '#117C87'}
colors = [color_map.get(f.upper(), '#7f7f7f') for f in financieras]
bars = ax.barh(financieras, ads_counts, color=colors)

for bar, count, price in zip(bars, ads_counts, avg_prices):
    ax.text(bar.get_width(), bar.get_y() + bar.get_height()/2, f"{count:,}".replace(',', '.') + " anuncios\n" + f"{price:,.0f} €".replace(',', '.'), fontsize=8)

ax.set_title("Número de anuncios y promedio del precio al contado del ticket medio")
ax.set_xlabel("Número de anuncios")
plt.xticks(list(range(0, 24000, 3000)), fontsize=8)
plt.tight_layout()
plt.show()

print(f"Hay un total de {df['client_name'].nunique():,.0f} concesionarios en el dataset.\n".replace(',', '.'))
print(f'⬇️ 10 concesionarios aleatorios en número de vehículos anunciados y con qué financieras trabajan:')

counts = df.groupby(['client_name', 'financing_lender']).size().unstack(fill_value=0)
counts['TOTAL'] = counts.sum(axis=1)
counts = counts.sort_values('TOTAL', ascending=False)
counts.sample(10)

Hay un total de 1.032 concesionarios en el dataset.

⬇️ 10 concesionarios aleatorios en número de vehículos anunciados y con qué financieras trabajan:

brands = np.unique(df['manufacturer'])
financieras = np.unique(df['financing_lender'])

color_map = {'santander': '#ED0808', 'bbva': '#398CC9', 'caixa': '#FFCE08'}
default_color = '#7f7f7f'

data_matrix = []
for brand in brands:
    row = []
    for f in financieras:
        count = len(df[(df['manufacturer'] == brand) & (df['financing_lender'] == f)])
        row.append(count)
    data_matrix.append(row)

data_matrix = np.array(data_matrix)

avg_prices = [df[df['manufacturer'] == b]['cash_price'].mean() for b in brands]

orden = np.argsort(avg_prices)[::-1]
brands_sorted = [brands[i] for i in orden]
data_matrix_sorted = data_matrix[orden]
avg_prices_sorted = [avg_prices[i] for i in orden]

colors = [color_map.get(f, default_color) for f in financieras]

plt.figure(figsize=(10, 14))
bottom = np.zeros(len(brands_sorted))

for i, f in enumerate(financieras):
    counts = data_matrix_sorted[:, i]
    plt.barh(brands_sorted, counts, left=bottom, color=colors[i], height=0.6, label=f)
    bottom += counts

for i, brand in enumerate(brands_sorted):
    total = np.sum(data_matrix_sorted[i, :])
    avg = avg_prices_sorted[i]
    plt.text(total + 0.5, i, f"{format(total, ',').replace(',', '.')} {'anuncios' if total>1 else 'anuncio'}, Promedio cash: €{format(avg, ',.0f').replace(',', '.')}", va='center', fontsize=7)

plt.xlabel('Número de anuncios')
plt.xticks(list(range(0, 4200, 200)), fontsize=8)
plt.ylabel('Fabricante')
plt.yticks(fontsize=8)
plt.title('Número de anuncios por fabricante y financiera ordenado por promedio del precio en efectivo (desc)')
plt.legend(title='Financieras', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.gca().invert_yaxis()
plt.tight_layout()
plt.show()

gdf = gpd.read_file("provincias_spain.geojson")
count_df = (df.groupby("client_location_province").size().reset_index(name="num_coches"))

merged = gdf.merge(count_df, left_on="Texto", right_on="client_location_province", how="left")
merged["num_coches"] = merged["num_coches"].fillna(0)

geojson_dict = json.loads(merged.to_json())

fig = px.choropleth(
    merged,
    geojson=geojson_dict,
    locations=merged.index,
    color="num_coches",
    hover_name="Texto",
    hover_data={"num_coches": True},
    color_continuous_scale="Viridis"
)

total_coches = f"{len(df):,}".replace(',', '.')
fig.update_geos(fitbounds="locations", visible=False)
fig.update_layout(title=f"Número de coches por provincia. Número de coches en total: {total_coches}")

fig.write_html("mapa_provincias.html", include_plotlyjs='cdn', full_html=True)
fig.show()

terms_values = sorted(df['financing_terms'].dropna().unique())

df['net_profit'] = df['financing_total_amount_due'] - df['financing_amount_to_finance']

n = len(terms_values)
cols = 2
rows = (n + cols - 1) // cols

fig, axes = plt.subplots(rows, cols, figsize=(12, rows * 4), sharey=True)
axes = axes.flatten()

for i, term in enumerate(terms_values):
    ax = axes[i]
    df_term = df[df['financing_terms'] == term]

    avg_profit = df_term.groupby('financing_lender')['net_profit'].mean().sort_values(ascending=False)
    bars = ax.bar(avg_profit.index, avg_profit.values, color='skyblue')

    for bar in bars:
        height = bar.get_height()
        ax.text(bar.get_x() + bar.get_width() / 2, height + 100, f'{int(height):,}', ha='center', va='bottom', fontsize=8)

    ax.set_title(f'Plazo = {int(term)} meses')
    ax.set_xlabel('')
    ax.set_ylabel('€')
    ax.tick_params(axis='x', rotation=45)

plt.suptitle('Beneficio neto promedio por financiera según duración del préstamo', fontsize=16)
plt.tight_layout()
plt.show()

tin_values = [5.99, 6.99, 7.5, 7.99, 8.99, 9.99]

df_120 = df[df['financing_terms'] == 120].copy()
df_120['net_profit'] = df_120['financing_total_amount_due'] - df['financing_amount_to_finance']
df_120 = df_120[df_120['financing_tin'].isin(tin_values)]

n = len(tin_values)
cols = 3
rows = (n + cols - 1) // cols

fig, axes = plt.subplots(rows, cols, figsize=(15, rows * 4), sharey=True)
axes = axes.flatten()

for i, tin in enumerate(tin_values):
    ax = axes[i]
    df_tin = df_120[df_120['financing_tin'] == tin]

    avg_profit = df_tin.groupby('financing_lender')['net_profit'].mean().sort_values(ascending=False)
    bars = ax.bar(avg_profit.index, avg_profit.values, color='skyblue')

    for bar in bars:
        height = bar.get_height()
        ax.text(bar.get_x() + bar.get_width() / 2, height + 100, f'{int(height):,}', ha='center', va='bottom', fontsize=9)

    ax.set_title(f'TIN = {tin:.2f}%')
    ax.set_xlabel('')
    ax.set_ylabel('Beneficio neto promedio (€)')
    ax.tick_params(axis='x', rotation=45)

plt.suptitle('Beneficio neto promedio por financiera según TIN a 120 meses', fontsize=20)
plt.tight_layout(rect=[0, 0, 1, 0.96])
plt.show()

MANUFACTURER = 'AUDI'
MODEL = 'A1'
VERSION = 'Sportback Adrenalin 25 TFSI 70kW 95CV'
YEAR = 2022

df_audis_a1 = df[(df['manufacturer'] == MANUFACTURER) & (df['model'] == MODEL)]

versions_audi_a1 = df_audis_a1['version'].unique()
years_audi_a1 = df_audis_a1['year'].unique()

df_audis_a1_filtered = df_audis_a1[(df_audis_a1['version'] == VERSION) &(df_audis_a1['year'] == YEAR)].reset_index(drop=True)

text_lines = []

for index, car in df_audis_a1_filtered.sort_values('client_location_province', ascending=False).reset_index(drop=True).iterrows():
    text_lines.append(f"OPCIÓN Nº {index+1}")
    text_lines.append(f"    ### INFORMACIÓN DEL VEHÍCULO ###")
    text_lines.append(f"        · Kms: {car['kms']:,.0f}".replace(',', '.'))
    text_lines.append(f"        · Provincia: {car['client_location_province']}")
    text_lines.append(f"        · Concesionario: {car['client_name']}")
    text_lines.append(f"        · Dirección: {car['client_location_address']}")
    text_lines.append(f"        · Teléfono: Por privacidad no se muestra")
    text_lines.append(f"    ### DETALLES DE FINANCIACIÓN ###")
    text_lines.append(f"        · Precio al contado: {car['cash_price']:,.2f} €".replace(',', '.'))
    text_lines.append(f"        · Financiera: {car['financing_lender'].upper()}")
    text_lines.append(f"        · Plazos: {int(car['financing_terms'])} meses")
    text_lines.append(f"        · TAE (Tasa Anual Equivalente): {car['financing_tae']} %".replace('.', ','))
    text_lines.append(f"        · TIN (Tipo de Interés Nominal): {car['financing_tin']} %".replace('.', ','))
    text_lines.append(f"        · Cuota mensual inicial: {car['financing_first_fee']:,.2f} €".replace(',', '.'))
    text_lines.append(f"        · Cuota mensual estándar: {car['financing_fee']:,.2f} €".replace(',', '.'))
    text_lines.append(f"        · Gastos de apertura: {car['financing_opening_expenses']:,.2f} €".replace(',', '.'))
    text_lines.append(f"        · Apertura financiada?: {car['financing_amount_to_finance'] > car['financing_capital']} ({car['financing_opening_percentage']}%)".replace('.', ','))
    text_lines.append(f"        · Intereses totales a pagar: {car['financing_total_interest']:,.2f} €".replace(',', '.'))
    text_lines.append(f"        · Intereses totales a pagar mensualmente: {car['financing_total_interest'] / 120:.2f} €".replace(',', '.'))
    text_lines.append(f"        · Capital financiado: {car['financing_capital']:,.2f} €".replace(',', '.'))
    text_lines.append(f"        · Cantidad total a devolver: {car['financing_total_amount_due']:,.2f} €".replace(',', '.'))
    text_lines.append(f"        · %Incremento al contado vs financiado: +{car['cash_price'] / car['financing_total_amount_due'] * 100:.2f} %".replace('.', ','))

    text_lines.append("")

fig, ax = plt.subplots(figsize=(12, 8))
ax.axis('off')

ax.text(1, 0.95, "AUDI A1 - Sportback Adrenalin 25 TFSI 70kW 95CV - 2022", fontsize=14, va='top', ha='right', bbox=dict(facecolor='yellow', edgecolor='none', pad=2))
ax.text(1, 0.85, "Versión seleccionada: Sportback Adrenalin 25 TFSI 70kW 95CV (2022)", fontsize=10, va='top', ha='right')
ax.text(1, 0.75, f"Financieras disponibles: {', '.join(df_audis_a1_filtered['financing_lender'].unique()).upper()}", fontsize=10, va='top', ha='right')
ax.text(1, 0.65, f"Número de coches encontrados con los filtros: {len(df_audis_a1_filtered)}", fontsize=10, va='top', ha='right')
ax.text(1, 0.55, f"Años de Audi A1 en el dataset: {', '.join(map(str, sorted(years_audi_a1)))}", fontsize=8, va='top', ha='right')
ax.text(1, 0.45, f"Versiones de Audi A1 en el dataset:\n {versions_audi_a1}", fontsize=8, va='top', ha='right')

full_text = "\n".join(text_lines)
ax.text(0, 0.95, full_text, va='top', ha='left', fontsize=10, family='monospace')

plt.tight_layout()
plt.show()

order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

df['publication_date'] = pd.to_datetime(df['publication_date'], errors='coerce', utc=True)
df['day_of_week'] = df['publication_date'].dt.day_name()

day_counts = df['day_of_week'].value_counts().reindex(order)
avg_views = (df.groupby('day_of_week')["statistics_views"].mean().reindex(order))

fig, ax1 = plt.subplots(figsize=(10, 5))

bars = ax1.bar(day_counts.index, day_counts.values, color='gray', label='Número de anuncios')
ax1.set_ylabel('Total número de anuncios', color='gray')
ax1.tick_params(axis='y')

ax2 = ax1.twinx()
ax2.plot(avg_views.index, avg_views.values, marker='o', linewidth=2, label='Promedio de visitas', color='green')
ax2.set_ylabel('Promedio de visitas', color='green')
ax2.tick_params(axis='y')

plt.title('Distribución del número de anuncios publicados y promedio de visitas por día durante la semana')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

coef_grouped = df[df['financing_terms'] == 120]
df_coef = coef_grouped.groupby('financing_lender')['financing_coefficient'].mean().sort_values()

plt.figure(figsize=(8, 5))
bars = df_coef.plot(kind='barh', color='purple', alpha=0.5)

for i, (lender, value) in enumerate(df_coef.items()):
    plt.text(value - value * 0.02, i, f"{value*100:.4f}%", va='center', ha='right', color='white', fontsize=9, fontweight='bold')

plt.title('Coeficiente financiero promedio por financiera a 120 meses')
plt.xlabel('')
plt.ylabel('')

plt.tight_layout()
plt.grid(axis='x', alpha=0.4, color='gray', linestyle='--')
plt.show()

financing_lender	bbva	bmw-bank	caixa	lendrock	mobilize	mobilize-nissan	santander	stellantis	TOTAL
client_name
Cars la Sagra	0	0	0	0	0	0	1	0	1
Flexicar Sabadell Aeropuerto	0	0	0	0	0	0	40	0	40
Total Ocasión: Leganés, Alcalá de Henares, Rivas.	0	0	0	118	0	0	0	0	118
AUTOSAE CENTER	0	0	0	0	0	12	0	0	12
Ocasionplus GETAFE	0	0	0	0	0	0	173	0	173
Seminuevos y Ocasión Lexus Madrid	0	0	0	0	0	0	41	0	41
Autos Juanjo	0	0	0	0	0	0	109	0	109
Autos Auringis	0	0	0	0	0	0	0	13	13
Vázquez de Prada	7	0	0	3	0	0	0	0	10
Rivero Motor	0	0	0	0	0	0	11	0	11

Analásis de coches financiados en Coches.net¶

by Albert Lanza: https://www.linkedin.com/in/albert-lanza-rio/¶

0. Carga de librerias, datos y cuántos anuncios hay en el dataset¶

1. ¿Qué financieras están en Coches.net y qué cuota de mercado tienen en Coches.net?¶

2. ¿Cómo se distribuyen los anuncios por financiera y cuál es su ticket medio al contado?¶

3. ¿Cuántos concesionarios hay en el dataset y, cada uno, con qué financieras trabaja?¶

4. Gráfico de la distribución de los anuncios por financiera y marca de vehículo¶

5. Gráfico de España de cuántos anuncios hay por província¶

6. Promedio del beneficio neto de cada financiera por plazos (84, 96, 108 y 120 meses)¶

7. Promedio del beneficio neto de cada financiera por TIN (5.99, 6.99, 7.50, 7.99, 8.99, 9.99) a 120 meses¶

8. Comparación de vehículo: Audi A1 - Sportback Adrenalin 25 TFSI 70kW 95CV - 2022¶

9. Número de anuncios publicados y promedio de visitas por día durante la semana¶

10. Coeficiente financiero de cada financiera a 120 meses¶