DataFrame Operations

import pandas as pd
df = pd.read_csv('data/grades.csv', index_col=0)
df

df.insert(0, 'DNI', ['11222333A', '22333222B', '33222333C', '44333222D']) # Insert DNI column at position 0
df.insert(1, 'Edad', [23, 23, 36, 36]) # Insert an Age column at position 1
df

# Or we can simply add a column at the end
df['Otro'] = [1,1,1,1] # If the column doesn't exist, it creates it; if it exists, it overwrites it
df

# Create a new column with normalized PIA grades
df['PIA_norm'] = (df['PIA'] - df['PIA'].min()) / (df['PIA'].max() - df['PIA'].min())
df

df.drop('Otro', axis=1) # Returns a new DataFrame without the 'Otro' column, but doesn't modify the original DataFrame
#df # If we show the original DataFrame, we see that the 'Otro' column is still there

df.drop('Otro', axis=1, inplace=True) # Deletes the 'Otro' column from the original DataFrame
df

df.drop(columns='Edad') # Returns another dataframe without 'Edad'

df.drop(columns=['PIA_norm', 'Edad']) # Returns another dataframe without 'PIA_norm' or 'Edad'

# assign returns a new DataFrame with the new column
df.assign(PIA_doubled=df['PIA'] * 2)

# Using lambda allows referencing columns created in the same assign call
df.assign(
    average=lambda x: (x['PIA'] + x['SAA'] + x['MIA']) / 3,
    passed=lambda x: x['average'] >= 5  # Uses the column just created above
)

# df.at['Alan Turing'] # Would error; at only works to access a specific cell
df.loc['Alan Turing'] # Retrieve a complete row as a Series

DNI         11222333A
Edad               23
PIA               7.0
SAA               9.0
MIA               7.0
SBD              10.0
BDA               9.9
PIA_norm          1.0
Name: Alan Turing, dtype: object

import numpy as np
df.loc['Arthur Samuel'] = ['55444333E', 25, 8, 8.0, 7.0, 9.0, 6.0, np.nan] # Add a new student
# Since the PIA_norm column requires the PIA column first, we cannot add the value of PIA_norm
df

df.drop('Arthur Samuel') # Rows can be deleted like columns (in this case I don't use inplace=True because I don't want to modify the original DataFrame)
#df

print(df.at['Alan Turing','PIA']) # Access a specific cell more efficiently than loc
print(df.loc['Alan Turing','PIA'])
print(df.loc['Alan Turing'].at['PIA'])  # loc returns a series with the row data, so we can use at to access a specific element of that series
print(df['PIA']['Alan Turing']) 
print(df['PIA'].at['Alan Turing'])
print(df.iat[0,2])  # Access by integer position: first row, third column
print(df.iloc[0,2])

7.0
7.0
7.0
7.0
7.0
7.0
7.0

df.at['Alan Turing', 'PIA'] = 10 # Modify a value using the row index and column name
df.loc['Alan Turing', 'PIA'] = 10 # Equivalent to the above
df

df.iloc[1, 2] = 0 # Modify a grade using positions
df

dict_num_keys1 = {1: 'uno', 2: 'dos', 3: 'tres'}
dict_num_keys2 = {1: 'one', 2: 'two', 3: 'three'}
dict_num_keys3 = {1: 'asdasd', 2: 'asdasd', 3: 'asdasd'}

df_ints_as_keys = pd.DataFrame({1:dict_num_keys1, 2: dict_num_keys2, 3: dict_num_keys3})
print(df_ints_as_keys.iloc[1,1])
print(df_ints_as_keys.loc[1,1])

two
uno

# .at is the fastest method for accessing a single scalar value using labels.
# Use it when you need to get or set a single value and performance is critical.
print(df.at['Alan Turing','PIA'])

# .loc is used for label-based selection. It can access a single value, a row, a column, or a slice.
# It is very flexible but slightly slower than .at for single-value access.
print(df.loc['Alan Turing','PIA'])

# This is an example of chained indexing. First, it selects the row as a Series with .loc,
# and then it accesses the value from the Series with .at.
# This is generally not recommended as it can be less efficient and lead to a SettingWithCopyWarning.
print(df.loc['Alan Turing'].at['PIA'])

# This is another form of chained indexing. It first selects the column as a Series,
# and then accesses the value by its index label.
# This is also discouraged in favor of .loc or .at for clarity and performance.
print(df['PIA']['Alan Turing']) 

# Similar to the previous method, this selects the column first and then uses .at to get the value.
# While slightly more explicit, the direct access with df.at[...] or df.loc[...] is preferred.
print(df['PIA'].at['Alan Turing'])

10.0
10.0
10.0
10.0
10.0

df[['DNI']] # Returns a DataFrame with the DNI column

df['DNI'] # Returns a series with the DNI column

Alan Turing       11222333A
Claude Shannon    22333222B
John McCarthy     33222333C
Marvin Minsky     44333222D
Arthur Samuel     55444333E
Name: DNI, dtype: object

df.DNI # Equivalent to the above

Alan Turing       11222333A
Claude Shannon    22333222B
John McCarthy     33222333C
Marvin Minsky     44333222D
Arthur Samuel     55444333E
Name: DNI, dtype: object

df[['DNI','PIA']] # Returns a DataFrame with the DNI and PIA columns

df.loc['Alan Turing', ['PIA','SAA','MIA']] # Access a row and several columns

PIA    10.0
SAA     9.0
MIA     7.0
Name: Alan Turing, dtype: object

df.loc[:, 'PIA':'MIA'] # Returns a DataFrame of all rows with columns PIA, SAA and MIA

df.iloc[:3, 0:3] # Returns a DataFrame of rows before row 3, with columns 0, 1 and 2

df.loc[:'Marvin Minsky'] # Returns a DataFrame with rows up to and including Marvin Minsky

# Students who passed PIA and SAA
df[(df['PIA'] >= 5) & (df['SAA'] >= 5)]

df[(df['PIA'] >= 5) | (df['SAA'] >= 5)] [['PIA','SAA','MIA']] # Students who passed PIA or SAA with their grades in those modules and in MIA

aprobados_pia_saa = df[(df['PIA'] >= 5) & (df['SAA'] >= 5)] # Students who passed PIA and SAA
aprobados_pia_saa.set_index('DNI', inplace=True) # Set DNI as index (the inplace=True argument modifies the original DataFrame instead of returning a new one)
aprobados_pia_saa[['PIA','SAA','MIA']] # Students who passed PIA and SAA, identified by DNI, with their grades in those modules and in MIA

df[(df['PIA'] >= 5) | (df['SAA'] >= 5)].set_index('DNI')[['PIA','SAA','MIA']] # Same as above but in a single line

df.sort_values('PIA', ascending=False) # Sorts the DataFrame by the PIA column in descending order

df.sort_values(by=['PIA', 'SAA', 'MIA'], ascending=False) # Sorts the DataFrame by columns PIA, SAA and MIA (in that order of priority)

df['PIA'].mean() # Mean of the PIA column

np.float64(6.04)

df.sum() # Sum of each column

DNI         11222333A22333222B33222333C44333222D55444333E
Edad                                                  143
PIA                                                  30.2
SAA                                                  41.5
MIA                                                  33.0
SBD                                                  46.0
BDA                                                  40.6
PIA_norm                                         2.230769
dtype: object

df.loc[:, ['PIA','SAA','MIA','BDA','SBD']].mean(axis='columns') # Average grade of each student in the five modules
# axis=1 indicates that the mean is calculated by rows

Alan Turing       9.18
Claude Shannon    5.98
John McCarthy     7.56
Marvin Minsky     7.94
Arthur Samuel     7.60
dtype: float64

df['SBD'].value_counts() # Returns the number of times each value appears in the SBD column
# four students got a 9 in SBD and one got a 10

SBD
9.0     4
10.0    1
Name: count, dtype: int64

df.isnull() # Returns a DataFrame with True in cells that contain NaN and False in those that don't
df.isna()  # Equivalent to the above ("not available")

df.isnull().sum() # Returns the number of null values in each column

DNI         0
Edad        0
PIA         0
SAA         0
MIA         0
SBD         0
BDA         0
PIA_norm    1
dtype: int64