import pandas as pd
import openpyxl 

def preprocess_whisp_data(response): 

    # Retrieve response
    whisp_response = pd.DataFrame(response['properties'])
    whisp_response = whisp_response.melt(var_name="Column", value_name="Value")

    # Load metadata
    whisp_metadata = pd.read_excel('././data/whisp_columns.xlsx')
    
    # Merge datasets
    full_df = pd.DataFrame(whisp_response).merge(whisp_metadata, left_on="Column", right_on="Column name").drop(columns={"Column name"})

    return full_df