#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
FCDB S4H Unification Script

Supplemental material 2.

In this script we present the operations we made to clean our data and calculate 
the statiscal measures needed for our work. 

Python: python --version
3.8.10

Requeriments:
 - pandas: 1.1.4    
 - numpy: 1.19.4 

"""

# IMport libraries
import pandas as pd # dataframe work -v 1.1.4
import numpy as np # numerical work -v 1.19.4

# Auxiliary definitions

def change_values(x):
    """Auxiliary function definition to apply to a full dataframe. 
        Data in our dataframe usually have quite low numerical values
        that we need to get rid of. 

    Args:
        x (str): The str obtained from uploading and reading the 
                      excel file.

    Returns:
        str: Empty string

    """
    if float(x) < 0.000000001:
        x = ''   
    return x

def std(x):
    """Auxiliary function definition to apply to a full dataframe in order to
        make standard deviations. 

    Args:
        x (str): The str obtained from uploading and reading the 
                      excel file.

    Returns:
        str: Empty string

    """
    return np.std(x)


# Import the dataframe 
df = pd.read_excel('FCDB S4H(S).xlsx')

# Cleaning data: remove id with no ID and extremley low values.
df = df[df['ID'].notnull()]
          
for i in df.columns[8:]:
   df[i] = df[i].apply(change_values)     

# Export the dataframe into new file. 

df.to_excel("Cleaned_FCDB_S4H(S).xlsx",index=False)

#%% For this part df is asumed to be cleaned as previously stated.

# Transform data from the dataframe to numerical value. 
# Invalid parsing will be set as NaN

for i in df.columns[8:]:
    df[i] = pd.to_numeric(df[i],errors='coerce')

# Statistical Mean calculation by same ID
x = df.groupby(df['ID'])[df.columns[8:]].mean()
x['IDU'] = x.index
x['type'] = 'mean' 

# STD Calculation by same ID
y = df.groupby(df['ID'])[df.columns[8:]].agg(std)
y['IDU'] = y.index
y['type'] = 'std'

# Median calculation by same ID
z = df.groupby(df['ID'])[df.columns[8:]].median()
z['IDU'] = z.index
z['type'] = 'median'

# As previous operation create 3 different dataframes, we join them by concat
# and sort by ID 
result = pd.concat([x,y,z])
result_sorted = result.sort_values(by=['IDU'])

# Export the dataframe to new file.

result_sorted.to_excel("FCDB S4H(U).xlsx")