# -*- coding: utf-8 -*-
"""
Created on Wed Dec 11 08:18:24 2024

@author: chuffard
"""

import pandas as pd
import numpy as np


FILTER_CUT_WEIGHTS = pd.read_excel('G:/Deployments/MARS-2024/Trigger trap/Carbon_data/Trigger_trap_filter_log.xlsx', sheet_name='FILTER_CUT_WEIGHTS')
FILTER_CUT_WEIGHTS.dtypes
# Filter                         int64
# Subsample                     object
# Cup #                         object
# Subsample total Weight mg    float64
# CutNumber                     object
# dtype: object
#find the total weight of each filter
Filter_weights = pd.pivot_table(FILTER_CUT_WEIGHTS, values='Subsample total Weight mg', index=['Filter'], aggfunc="sum")
Filter_weights.rename(columns={'Subsample total Weight mg': 'Total_filter_weight_SUM'}, inplace=True)


FILTER_CUT_WEIGHTS = pd.merge(FILTER_CUT_WEIGHTS, Filter_weights, on='Filter')
FILTER_CUT_WEIGHTS['weight_factor'] = FILTER_CUT_WEIGHTS['Total_filter_weight_SUM']/FILTER_CUT_WEIGHTS['Subsample total Weight mg']
#remove columns with nothing in weight factor
FILTER_CUT_WEIGHTS.dropna(subset=['weight_factor'], inplace=True)


Carbon_data = pd.read_excel('G:/Deployments/MARS-2024/Trigger trap/Carbon_data/6861.xlsx', sheet_name='Python')

Filter_Carbon= pd.merge(FILTER_CUT_WEIGHTS, Carbon_data,how='outer')
Filter_Carbon.dtypes
# Filter                         int64
# Subsample                     object
# Cup #                         object
# Subsample total Weight mg    float64
# CutNumber                     object
# Total_filter_weight_SUM      float64
# weight_factor                float64
# Acidified                     object
# C_micrograms                 float64
# N_micrograms                 float64
# C_N                          float64
# Flags                         object
# dtype: object

Filter_Carbon['Carbon_micrograms'] = Filter_Carbon['C_micrograms']*Filter_Carbon['weight_factor']
Filter_Carbon['Nitrogen_micrograms'] = Filter_Carbon['N_micrograms']*Filter_Carbon['weight_factor']

Filter_Carbon_wide = Filter_Carbon.pivot(
    index=["Filter", "Cup #"],
    columns="Acidified",
    values="Carbon_micrograms",
)
Filter_Carbon_wide.rename(columns={'No': 'Carbon_RAW', 'Yes': 'Carbon_Acidified'}, inplace=True)
Filter_Carbon_wide['Carbon_remainder'] = Filter_Carbon_wide['Carbon_RAW']-Filter_Carbon_wide['Carbon_Acidified']
Filter_Carbon_wide=Filter_Carbon_wide.reset_index()
Filter_Carbon_wide['Cup #'] = Filter_Carbon_wide['Cup #'].astype(str)
#filter out the blanks
Filter_Carbon_wide.dropna(subset=['Carbon_RAW'], inplace=True)
BlanksWide = Filter_Carbon_wide[Filter_Carbon_wide['Cup #'].str.contains('B')]
FiltersCarbonOnlyWide = Filter_Carbon_wide[~Filter_Carbon_wide['Cup #'].str.contains('B')]