# Start writing code here...
SELECT *
FROM 'exams.csv'
idobject
53-98934290.2%
10-10684460.2%
998 others99.6%
genderobject
male51.9%
female48.1%
0
10-5894942
male
1
41-1676468
female
2
64-6396924
male
3
35-2426788
male
4
60-9387304
male
5
67-3666190
female
6
27-7702214
female
7
46-2257650
male
8
40-1499649
male
9
67-7378468
male
Trabajo Práctico Integrador
Análisis exploratorio
#1. Importar las librerias
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
# 2. Cargar los datos del Dataframe
df=pd.read_csv('exams.csv')
df
idobject
53-98934290.2%
10-10684460.2%
998 others99.6%
genderobject
male51.9%
female48.1%
0
10-5894942
male
1
41-1676468
female
2
64-6396924
male
3
35-2426788
male
4
60-9387304
male
5
67-3666190
female
6
27-7702214
female
7
46-2257650
male
8
40-1499649
male
9
67-7378468
male
# Las primeras 5 filas
df.head()
idobject
genderobject
0
10-5894942
male
1
41-1676468
female
2
64-6396924
male
3
35-2426788
male
4
60-9387304
male
#Las ultimas 5 filas
df.tail()
idobject
genderobject
1013
82-7312119
male
1014
45-3445439
male
1015
02-3651562
male
1016
05-5203587
female
1017
13-3347050
male
#Resumen estadistico
df.describe()
math scorefloat64
physics scorefloat64
count
1011.0
1011.0
mean
66.48071216617211
69.06330365974283
std
15.326879704379337
14.694107007851635
min
13.0
27.0
25%
56.0
60.0
50%
67.0
70.0
75%
77.0
79.0
max
100.0
100.0
# 3. Revisar el tipo de datos
df.dtypes
# 4. Eliminar los datos duplicados
print(f'Original: {df.id.count()} filas')
duplicate_rows_df = df[df.duplicated()]
print(f'Cantidad de filas duplicadas: {duplicate_rows_df.id.count()}')
# Eliminar los duplicados
df = df.drop_duplicates()
Original: 1018 filas
Cantidad de filas duplicadas: 18
# Cantidad de filas luego de eliminar los duplicados
print(f'Final: {df.id.count()} filas')
Final: 1000 filas
# 5. Eliminar las columnas irrelevantes
print(df.columns)
df = df.drop(['id'], axis =1)
Index(['id', 'gender', 'race/ethnicity', 'parental level of education',
'lunch', 'employed', 'test preparation course', 'math score',
'physics score', 'chemistry score', 'algebra_score'],
dtype='object')
print(df.columns)
Index(['gender', 'race/ethnicity', 'parental level of education', 'lunch',
'employed', 'test preparation course', 'math score', 'physics score',
'chemistry score', 'algebra_score'],
dtype='object')
# 6. Renombrar las columnas
df = df.rename(columns= {
'gender':'Gender',
'race/ethnicity':'Ethnicity',
'parental level of education':'Parental level of education',
'lunch':'Lunch',
'employed':'Employed',
'test preparation course':'Test preparation course',
'math score':'Math score',
'physics score':'Physics score',
'chemistry score':'Chemistry score',
'algebra_score':'Algebra score'})
df.columns
# Eliminar los valores perdidos o nulos
# Encontrar los valores nulos
print(df.isnull().sum())
Gender 0
Ethnicity 0
Parental level of education 0
Lunch 0
Employed 0
Test preparation course 0
Math score 7
Physics score 7
Chemistry score 7
Algebra score 7
dtype: int64
# Eliminar los valores perdidos
df = df.dropna()
# Despues de eliminar los nulos
print(df.isnull().sum())
Gender 0
Ethnicity 0
Parental level of education 0
Lunch 0
Employed 0
Test preparation course 0
Math score 0
Physics score 0
Chemistry score 0
Algebra score 0
dtype: int64
# 8. Detectar los Outliers
sns.boxplot(x=df['Math score'], color='royalblue')
plt.show()
sns.boxplot(x=df['Physics score'], color='tomato')
plt.show()
sns.boxplot(x=df['Chemistry score'], color='crimson')
plt.show()
sns.boxplot(x=df['Algebra score'], color='dodgerblue')
plt.show()
print(f'Antes: {df.Lunch.count()} filas\n')
Q1 = df.quantile(0.25)
Q3 = df.quantile(0.75)
IQR = Q3 - Q1
print(IQR)
df = df[~((df < (Q1 - 1.5 *IQR)) | (df > (Q3 + 1.5 * IQR))).any(axis=1)]
print(f'\nDespués: {df.Lunch.count()} filas')
Antes: 993 filas
Math score 21.0
Physics score 19.0
Chemistry score 21.0
Algebra score 19.0
dtype: float64
Después: 984 filas
/tmp/ipykernel_248/1586991544.py:6: FutureWarning: Automatic reindexing on DataFrame vs Series comparisons is deprecated and will raise ValueError in a future version. Do `left, right = left.align(right, axis=1, copy=False)` before e.g. `left == right`
df = df[~((df < (Q1 - 1.5 *IQR)) | (df > (Q3 + 1.5 * IQR))).any(axis=1)]
/tmp/ipykernel_248/1586991544.py:6: FutureWarning: Automatic reindexing on DataFrame vs Series comparisons is deprecated and will raise ValueError in a future version. Do `left, right = left.align(right, axis=1, copy=False)` before e.g. `left == right`
df = df[~((df < (Q1 - 1.5 *IQR)) | (df > (Q3 + 1.5 * IQR))).any(axis=1)]
# 9. Encontrar correlaciones y frecuencias
plt.hist(df['Algebra score'], color='dodgerblue', bins=20)
plt.title('Algebra')
plt.ylabel('Student count')
plt.xlabel('Score')
plt.show()
plt.hist(df['Chemistry score'], color='crimson', bins=20)
plt.title('Chemistry')
plt.ylabel('Student count')
plt.xlabel('Score')
plt.show()
plt.hist(df['Math score'], color='royalblue', bins=20)
plt.title('Math')
plt.ylabel('Student count')
plt.xlabel('Score')
plt.show()
plt.hist(df['Physics score'], color='tomato', bins=20)
plt.title('Physics')
plt.ylabel('Student count')
plt.xlabel('Score')
plt.show()
# Correlacion de los datos = Mapas de calor
c= df.corr()
print(c)
Math score Physics score Chemistry score Algebra score
Math score 1.000000 0.812055 0.798312 0.916674
Physics score 0.812055 1.000000 0.951536 0.968358
Chemistry score 0.798312 0.951536 1.000000 0.964652
Algebra score 0.916674 0.968358 0.964652 1.000000
plt.figure(figsize=(20,10))
sns.heatmap(c,cmap="RdBu", annot=True)
plt.show()
# pandas.value_counts() --> devuelve una serie con valores unicos en orden descendente de frecuencia
labels = df['Gender'].value_counts().index
sizes = df['Gender'].value_counts()
colors = ['tomato', 'royalblue']
plt.pie(sizes, labels = labels, colors = colors, autopct='%1.2f%%')
plt.title('Gender')
plt.show()
#Repetimos para Etnia, Empleo y el resto de las variables categóricas
labels = df['Ethnicity'].value_counts().index
sizes = df['Ethnicity'].value_counts()
colors = ['tomato', 'royalblue', 'dodgerblue', 'coral', 'lightsalmon']
plt.pie(sizes, labels = labels,colors = colors, autopct='%1.2f%%')
plt.title('Ethnicity')
plt.show()
labels = df['Employed'].value_counts().index
sizes = df['Employed'].value_counts()
colors = ['tomato', 'royalblue']
plt.pie(sizes, labels = labels, colors = colors, autopct='%1.2f%%')
plt.title('Is the student employed?')
plt.show()
labels = df['Test preparation course'].value_counts().index
sizes = df['Test preparation course'].value_counts()
colors = ['tomato', 'royalblue']
plt.pie(sizes, labels = labels, colors = colors, autopct='%1.2f%%')
plt.title('Test preparation course')
plt.show()
labels = df['Parental level of education'].value_counts().index
sizes = df['Parental level of education'].value_counts()
colors = ['tomato', 'royalblue', 'dodgerblue', 'coral', 'lightsalmon', 'steelblue']
plt.pie(sizes, labels = labels, colors = colors, autopct='%1.2f%%')
plt.title('Parental level of education')
plt.show()
labels = df['Lunch'].value_counts().index
sizes = df['Lunch'].value_counts()
colors = ['tomato', 'royalblue']
plt.pie(sizes, labels = labels, colors = colors, autopct='%1.2f%%')
plt.title('Lunch')
plt.show()
Respondiendo preguntas
Ejemplo: ¿Hay alguna relación entre el promedio de notas obtenidas y el hecho de haber realizado el curso preparatorio?
df['Average score'] = df.mean(axis=1)
# axis=1 hace que aplique la función sobre los valores numéricos de la fila en lugar de las columnas
df
Genderobject
male51.8%
female48.2%
Ethnicityobject
group C32.2%
group D26.3%
3 others41.5%
0
male
group A
1
female
group D
2
male
group E
3
male
group B
4
male
group E
5
female
group D
6
female
group A
7
male
group E
8
male
group D
9
male
group C
si = df[df['Test preparation course'] == 'completed']
no = df[df['Test preparation course'] == 'none']
sns.histplot(si['Average score'], color='blue', alpha=.5, fill=True)
sns.histplot(no['Average score'], color='red', alpha=.5, fill=True)
plt.show()
print('Realizaron el curso: ', si['Test preparation course'].count())
print('No realizaron el curso: ', no['Test preparation course'].count())
Realizaron el curso: 332
No realizaron el curso: 652
Conclusión: Si bien la cantidad de alumnos que no realizo el curso casi duplica a la de quienes lo han completado, esta diferencia no se ve reflejada significativamente en el promedio de las notas.
Se recomienda auditar los contenidos del curso, a fines de lograr una mejora en el rendimiento académico y aumentar el interés del alumnado.