7.4 Rank the structured data

There is a student score table:

1. Expand it to a score table with columns STUDENTID, SUBJECT, SCORE (column-to-row conversion).

2. Perform the inverse operation in 1 (row-to-column conversion).

SPL

In SPL, the group()is grouping function, which can implement the row-to-column conversion operation; the news() is inverse grouping function, which can implement the column-to-row conversion operation. These two functions are inverse operations of each other. SPL also provides pivot()and pivot@r() functions, which can easily implement the conversion operations between rows and columns.

SQL

1. Column-to-row conversion

SELECT * FROM (
    SELECT STUDENTID, English, Math, PE
    FROM scores)
UNPIVOT (
    SCORE FOR SUBJECT IN (English, Math, PE));

2. Row-to-column conversion

WITH inv_scores AS (
    SELECT * FROM (
        SELECT STUDENTID, English, Math, PE
        FROM scores)
    UNPIVOT (
        SCORE FOR SUBJECT IN (English, Math, PE)))
SELECT * FROM (
    SELECT STUDENTID, SUBJECT, SCORE
    FROM inv_scores)
PIVOT (
    AVG(SCORE) FOR SUBJECT IN ('ENGLISH' AS English, 'MATH' AS Math, 'PE' AS PE))
ORDER BY STUDENTID;

Python

score_data=pd.read_csv("../SCORES.csv")
clm=score_data.columns[1:]
subject_score=score_data.melt(id_vars="STUDENTID",
    value_vars=clm,var_name='SUBJECT',value_name="SCORE") #Column-to-row conversion
scores=subject_score.pivot(index='STUDENTID',columns='SUBJECT',values='SCORE') #Row-to-column conversion

Python provides pivot()and melt() functions to implement the conversion operation between rows and columns.

8.1 Foreign key association
Example codes for comparing SPL, SQL, and Python