import pandas as pd
import re

# Load data
scores = pd.read_csv('hasil_batch_multi_layer.csv')
ratings = pd.read_csv('ratings_rows Yunan.csv')

# Preprocess scores: extract numeric ID from 'Peserta'
def get_peserta_id(p):
    match = re.search(r'\d+', p)
    return int(match.group()) if match else None

scores['ID_Peserta'] = scores['Peserta'].apply(get_peserta_id)
scores['ID_Frasa'] = scores['File']

# Preprocess ratings
ratings['ID_Peserta'] = ratings['peserta_id']
ratings['ID_Frasa'] = ratings['audio_filename']

# Select relevant columns from scores
score_cols = [f'Score L{i}' for i in range(1, 13)]
scores = scores[['ID_Peserta', 'ID_Frasa'] + score_cols]

# Select relevant columns from ratings
ratings = ratings[['ID_Peserta', 'ID_Frasa', 'rating']]

# Merge
final = pd.merge(scores, ratings, on=['ID_Peserta', 'ID_Frasa'], how='inner')

# Create ID_Pasangan
final['ID_Pasangan'] = final['ID_Peserta'].astype(str) + '_' + final['ID_Frasa']

# Rearrange columns
cols = ['ID_Pasangan', 'ID_Peserta', 'ID_Frasa'] + score_cols + ['rating']
final = final[cols]

# Drop duplicates if any and non-matching rows are already dropped by inner join
final = final.dropna().sort_values(['ID_Peserta', 'ID_Frasa'])

# Save
final.to_csv('dataset_final.csv', index=False)
print('Shape dataset final:', final.shape)