library(reticulate)
## Warning: pakiet 'reticulate' został zbudowany w wersji R 4.3.3
# Specify the Python environment
Sys.setenv(RETICULATE_PYTHON = "C:\\Users\\mgodl\\AppData\\Local\\Programs\\Python\\Python312\\python.exe")
library(tidyr)
# Data
student_data2 <- data.frame(
student_id = 1:50,
name_age = c("John_21", "Alice_20", "Bob_22", "Emily_23", "Michael_22"),
exam_scores = c("midterm1_80,midterm2_85,final_75", "midterm1_75,midterm2_78,final_80", "midterm1_82,midterm2_80,final_85", "midterm1_88,midterm2_90,final_92", "midterm1_85,midterm2_86,final_88")
)
head(student_data2)
## student_id name_age exam_scores
## 1 1 John_21 midterm1_80,midterm2_85,final_75
## 2 2 Alice_20 midterm1_75,midterm2_78,final_80
## 3 3 Bob_22 midterm1_82,midterm2_80,final_85
## 4 4 Emily_23 midterm1_88,midterm2_90,final_92
## 5 5 Michael_22 midterm1_85,midterm2_86,final_88
## 6 6 John_21 midterm1_80,midterm2_85,final_75
TASK 1
# Seperating (Name & Age)
student_data2 <- separate(student_data2,
col = name_age,
into = c("Name","Age"),
sep = "_")
head(student_data2)
## student_id Name Age exam_scores
## 1 1 John 21 midterm1_80,midterm2_85,final_75
## 2 2 Alice 20 midterm1_75,midterm2_78,final_80
## 3 3 Bob 22 midterm1_82,midterm2_80,final_85
## 4 4 Emily 23 midterm1_88,midterm2_90,final_92
## 5 5 Michael 22 midterm1_85,midterm2_86,final_88
## 6 6 John 21 midterm1_80,midterm2_85,final_75
TASK 2
# Seperating (Exam Scores)
student_data2 <- separate(student_data2,
col = exam_scores,
into = c("Midterm1","Midterm2","Midterm3"),
sep = ",")
head(student_data2)
## student_id Name Age Midterm1 Midterm2 Midterm3
## 1 1 John 21 midterm1_80 midterm2_85 final_75
## 2 2 Alice 20 midterm1_75 midterm2_78 final_80
## 3 3 Bob 22 midterm1_82 midterm2_80 final_85
## 4 4 Emily 23 midterm1_88 midterm2_90 final_92
## 5 5 Michael 22 midterm1_85 midterm2_86 final_88
## 6 6 John 21 midterm1_80 midterm2_85 final_75
TASK 3
# Seperating (Filtering/Cleaning the Data to see more truely)
student_data2$Midterm1 <- as.numeric(gsub(".*_(\\d+).*", "\\1", student_data2$Midterm1))
student_data2$Midterm2 <- as.numeric(gsub(".*_(\\d+).*", "\\1", student_data2$Midterm2))
student_data2$Midterm3 <- as.numeric(gsub(".*_(\\d+).*", "\\1", student_data2$Midterm3))
head(student_data2)
## student_id Name Age Midterm1 Midterm2 Midterm3
## 1 1 John 21 80 85 75
## 2 2 Alice 20 75 78 80
## 3 3 Bob 22 82 80 85
## 4 4 Emily 23 88 90 92
## 5 5 Michael 22 85 86 88
## 6 6 John 21 80 85 75
TASK 4
#Data to Long Data
student_data2_long <- pivot_longer(student_data2,
cols = starts_with("Midterm"),
names_to = "Exam",
values_to = "Score")
#Output of Data Long
head(student_data2_long)
## # A tibble: 6 × 5
## student_id Name Age Exam Score
## <int> <chr> <chr> <chr> <dbl>
## 1 1 John 21 Midterm1 80
## 2 1 John 21 Midterm2 85
## 3 1 John 21 Midterm3 75
## 4 2 Alice 20 Midterm1 75
## 5 2 Alice 20 Midterm2 78
## 6 2 Alice 20 Midterm3 80
TASK 5
#Complete Missing Combinations
student_data2_long <- complete(student_data2_long,
student_id,
Name,
Age,
Exam,
fill = list(Score = NA))
#Output of Data2_Long with filled.
head(student_data2_long)
## # A tibble: 6 × 5
## student_id Name Age Exam Score
## <int> <chr> <chr> <chr> <dbl>
## 1 1 Alice 20 Midterm1 NA
## 2 1 Alice 20 Midterm2 NA
## 3 1 Alice 20 Midterm3 NA
## 4 1 Alice 21 Midterm1 NA
## 5 1 Alice 21 Midterm2 NA
## 6 1 Alice 21 Midterm3 NA
TASK 6
# Data
student_data_4 <- data.frame(
student_id = 1:50,
name_age = c("John_21", "Alice_20", "Bob_22", "Emily_23", "Michael_22"),
exam_scores = c("midterm1_80,midterm2_85,final_75", "midterm1_75,midterm2_78,final_80", "midterm1_82,midterm2_80,final_85", "midterm1_88,midterm2_90,final_92", "midterm1_85,midterm2_86,final_88")
)
# Transform data to wide format
# Split variables exam_scores to separate columns for each exam type
student_data_4 <- separate(student_data_4, exam_scores, into = c("midterm1", "midterm2", "final"), sep = ",")
student_data_4 <- separate(student_data_4, midterm1, into = c("midterm_n1", "midterm1"), sep = "_")
student_data_4 <- separate(student_data_4, midterm2, into = c("midterm_n2", "midterm2"), sep = "_")
student_data_4 <- separate(student_data_4, final, into = c("final_n", "final"), sep = "_")
student_data_4 <- subset(student_data_4, select = -c(midterm_n1, midterm_n2,final_n))
# Transform student_data4 to long format
student_data_4 <- student_data_4 %>%
pivot_longer(cols = -c(student_id, name_age),
names_to = "exam_type",
values_to = "score")
student_data_4 <- pivot_wider(student_data_4,
names_from = exam_type,
values_from = score)
head(student_data_4)
## # A tibble: 6 × 5
## student_id name_age midterm1 midterm2 final
## <int> <chr> <chr> <chr> <chr>
## 1 1 John_21 80 85 75
## 2 2 Alice_20 75 78 80
## 3 3 Bob_22 82 80 85
## 4 4 Emily_23 88 90 92
## 5 5 Michael_22 85 86 88
## 6 6 John_21 80 85 75
TASK 7
# Data
student_data_5 <- data.frame(
student_id = 1:50,
name_age = c("John_21", "Alice_20", "Bob_22", "Emily_23", "Michael_22"),
exam_scores = c("midterm1_80,midterm2_85,final_75", "midterm1_75,midterm2_78,final_80", "midterm1_82,midterm2_80,final_85", "midterm1_88,midterm2_90,final_92", "midterm1_85,midterm2_86,final_88")
)
# Split variables name_age to 2 separate columns: name and age
student_data_5 <- separate(student_data_5, name_age, into = c("name", "age"), sep = "_")
# Merge variables name and age to one column called name_age
student_data_5 <- unite(student_data_5, name_age, name, age, sep = "_")
head(student_data_5)
## student_id name_age exam_scores
## 1 1 John_21 midterm1_80,midterm2_85,final_75
## 2 2 Alice_20 midterm1_75,midterm2_78,final_80
## 3 3 Bob_22 midterm1_82,midterm2_80,final_85
## 4 4 Emily_23 midterm1_88,midterm2_90,final_92
## 5 5 Michael_22 midterm1_85,midterm2_86,final_88
## 6 6 John_21 midterm1_80,midterm2_85,final_75
TASK 1 in python
import pandas as pd
import random
random.seed(42)
df = pd.DataFrame({
'student_id': range(1, 51),
'name': [f"Student{x}" for x in range(1,51)],
'midterm1': [random.randint(60, 100) for _ in range(1, 51)],
'midterm2': [random.randint(60, 100) for _ in range(1, 51)],
'final': [random.randint(60, 100) for _ in range(1, 51)]
})
print(df[:10])
## student_id name midterm1 midterm2 final
## 0 1 Student1 100 95 85
## 1 2 Student2 67 78 89
## 2 3 Student3 61 100 69
## 3 4 Student4 77 99 76
## 4 5 Student5 75 83 68
## 5 6 Student6 74 96 75
## 6 7 Student7 68 72 95
## 7 8 Student8 66 64 94
## 8 9 Student9 94 62 76
## 9 10 Student10 65 74 97
# From wide to long format
# 1. with melt
long_df = df.melt(id_vars=['name'], value_vars=['midterm1', 'midterm2', 'final'])
print(long_df[:10])
## name variable value
## 0 Student1 midterm1 100
## 1 Student2 midterm1 67
## 2 Student3 midterm1 61
## 3 Student4 midterm1 77
## 4 Student5 midterm1 75
## 5 Student6 midterm1 74
## 6 Student7 midterm1 68
## 7 Student8 midterm1 66
## 8 Student9 midterm1 94
## 9 Student10 midterm1 65
TASK 2 in python
# Dataset
data_2 = pd.DataFrame({
'student_id': range(1,51),
'name_age': ["John_21", "Alice_20", "Bob_22", "Emily_23", "Michael_22"] * 10,
'exam_scores': ["midterm1_80,midterm2_85,final_75", "midterm1_75,midterm2_78,final_80", "midterm1_82,midterm2_80,final_85", "midterm1_88,midterm2_90,final_92", "midterm1_85,midterm2_86,final_88"] * 10
})
student_data2 = data_2.__deepcopy__()
# Split variables name_age to 2 separate columns: name and age
student_data2[['name', 'age']] = student_data2['name_age'].str.split('_', expand=True)
student_data2.drop(columns=['name_age'], inplace=True)
columns = ['midterm1', 'midterm2', 'final']
# Split variables exam_scores to separate columns for each exam type
student_data2[columns] = student_data2['exam_scores'].str.split(',', expand=True)
student_data2.drop(columns=['exam_scores'], inplace=True)
for name in columns:
student_data2[name] = student_data2[name].apply(lambda x: x.split('_')[1])
# Transform student_data2 to long format
student_data2 = student_data2.melt(id_vars=['student_id', 'name', 'age'], value_vars=columns)
print(student_data2[:10])
## student_id name age variable value
## 0 1 John 21 midterm1 80
## 1 2 Alice 20 midterm1 75
## 2 3 Bob 22 midterm1 82
## 3 4 Emily 23 midterm1 88
## 4 5 Michael 22 midterm1 85
## 5 6 John 21 midterm1 80
## 6 7 Alice 20 midterm1 75
## 7 8 Bob 22 midterm1 82
## 8 9 Emily 23 midterm1 88
## 9 10 Michael 22 midterm1 85
TASK 3 in python
from itertools import product
student_data3 = data_2.__deepcopy__()
# Complete missing combinations for students
student_data3[['name', 'age']] = student_data3['name_age'].str.split('_', expand=True)
student_data3.drop(columns=['student_id'], inplace=True)
permutations = set(product(student_data3['name'], student_data3['age']))
new_df = pd.DataFrame({
'name': ["_".join(perm) for perm in permutations],
})
student_data3.drop_duplicates(subset=['name_age'], inplace=True)
new_df['exam_results'] = student_data3.loc[(new_df['name'].isin(student_data3['name_age'])), 'exam_scores']
print(student_data3[:10])
## name_age exam_scores name age
## 0 John_21 midterm1_80,midterm2_85,final_75 John 21
## 1 Alice_20 midterm1_75,midterm2_78,final_80 Alice 20
## 2 Bob_22 midterm1_82,midterm2_80,final_85 Bob 22
## 3 Emily_23 midterm1_88,midterm2_90,final_92 Emily 23
## 4 Michael_22 midterm1_85,midterm2_86,final_88 Michael 22
TASK 4 in python
# Transform data to wide format
student_data4 = student_data2.__deepcopy__()
student_data5 = student_data2.__deepcopy__()
# 1.
# student_data4 = student_data4.pivot(index=['student_id', 'name'], columns='variable', values='value')
# student_data4.head(10)
# 2.
student_data5 = student_data5.set_index(['student_id', 'name', 'age', 'variable'])['value'].unstack()
print(student_data5[:10])
## variable final midterm1 midterm2
## student_id name age
## 1 John 21 75 80 85
## 2 Alice 20 80 75 78
## 3 Bob 22 85 82 80
## 4 Emily 23 92 88 90
## 5 Michael 22 88 85 86
## 6 John 21 75 80 85
## 7 Alice 20 80 75 78
## 8 Bob 22 85 82 80
## 9 Emily 23 92 88 90
## 10 Michael 22 88 85 86
# Merge variables name and age to one column called name_age
student_data6 = student_data5.__deepcopy__()
# 1.
student_data6.reset_index(inplace=True)
student_data6['name_age'] = student_data6['name'].astype(str) + '-' + student_data6['age']
student_data6.drop(columns=['name', 'age'], inplace=True)
print(student_data6[:10])
## variable student_id final midterm1 midterm2 name_age
## 0 1 75 80 85 John-21
## 1 2 80 75 78 Alice-20
## 2 3 85 82 80 Bob-22
## 3 4 92 88 90 Emily-23
## 4 5 88 85 86 Michael-22
## 5 6 75 80 85 John-21
## 6 7 80 75 78 Alice-20
## 7 8 85 82 80 Bob-22
## 8 9 92 88 90 Emily-23
## 9 10 88 85 86 Michael-22
