<commit_before>import os
import time

import openpyxl
from openpyxl.cell.cell import get_column_letter
from odm2api.ODM2.models import *

from yodatools.converter.Abstract import iInputs
import xlrd
import pandas


class ExcelInput(iInputs):
    # https://automatetheboringstuff.com/chapter12/
    def __init__(self, input_file, output_file=None):
        super(ExcelInput, self).__init__()
        self.input_file = input_file

        # self.workbook.get_named_range('Organizations_Table').attr_text

        if output_file is None:
            output_file = "export.csv"

        self.output_file = output_file
        self.workbook = None
        self.sheets = []
        self.name_ranges = None
        self.tables = {}

    def get_table_name_ranges(self):
        """
        Returns a list of the name range that have a table.
        The name range should contain the cells locations of the data.
        :rtype: list
        """
        CONST_NAME = "_Table"
        table_name_range = {}
        for name_range in self.name_ranges:
            if CONST_NAME in name_range.name:
                sheet = name_range.attr_text.split('!')[0]
                sheet = sheet.replace('\'', '')

                if sheet in table_name_range:
                    table_name_range[sheet].append(name_range)
                else:
                    table_name_range[sheet] = [name_range]

        return table_name_range

    def parse(self, file_path=None):
        if not self.verify(file_path):
            print "Something is wrong with the file but what?"
            return

        self.tables = self.get_table_name_ranges()
        methods = self.parse_methods()
        variables = self.parse_variables()
        specimens = self.parse_specimens()
        units = self.parse_units()
        processing_levels = self.parse_processing_level()
        # sampling_feature = self.parse_sampling_feature()
        data_values = self.read_data_values()

    def parse_units(self):

        CONST_UNITS = 'Units'

        if CONST_UNITS not in self.tables:
            return []

        sheet = self.workbook.get_sheet_by_name(CONST_UNITS)
        tables = self.tables[CONST_UNITS]

        units = []
        for table in tables:
            cells = sheet[table.attr_text.split('!')[1].replace('$', '')]
            cells = cells[1:]  # Remove the column names

            for row in cells:
                unit = Units()
                unit.UnitsTypeCV = row[0].value
                unit.UnitsAbbreviation = row[1].value
                unit.UnitsName = row[2].value
                unit.UnitsLink = row[3].value
                units.append(unit)

        return units

    def read_data_values(self):
        dataframes = pandas.read_excel(io=self.input_file, sheetname='Data Values')
        print dataframes

    def parse_processing_level(self):
        CONST_PROC_LEVEL = 'Processing Levels'

        if CONST_PROC_LEVEL not in self.tables:
            return []

        sheet = self.workbook.get_sheet_by_name(CONST_PROC_LEVEL)
        tables = self.tables[CONST_PROC_LEVEL]

        processing_levels = []
        for table in tables:
            cells = sheet[table.attr_text.split('!')[1].replace('$', '')]
            cells = cells[1:]  # Remove the column names

            for row in cells:
                proc_lvl = ProcessingLevels()
                proc_lvl.ProcessingLevelCode = row[0].value
                proc_lvl.Definition = row[1].value
                proc_lvl.Explanation = row[2].value
                processing_levels.append(proc_lvl)<commit_msg>commiting progress on transposing<commit_after>import os
import time

import openpyxl
from openpyxl.cell.cell import get_column_letter
from odm2api.ODM2.models import *

from yodatools.converter.Abstract import iInputs
import xlrd
import pandas


class ExcelInput(iInputs):
    # https://automatetheboringstuff.com/chapter12/
    def __init__(self, input_file, output_file=None):
        super(ExcelInput, self).__init__()
        self.input_file = input_file

        # self.workbook.get_named_range('Organizations_Table').attr_text

        if output_file is None:
            output_file = "export.csv"

        self.output_file = output_file
        self.workbook = None
        self.sheets = []
        self.name_ranges = None
        self.tables = {}

    def get_table_name_ranges(self):
        """
        Returns a list of the name range that have a table.
        The name range should contain the cells locations of the data.
        :rtype: list
        """
        CONST_NAME = "_Table"
        table_name_range = {}
        for name_range in self.name_ranges:
            if CONST_NAME in name_range.name:
                sheet = name_range.attr_text.split('!')[0]
                sheet = sheet.replace('\'', '')

                if sheet in table_name_range:
                    table_name_range[sheet].append(name_range)
                else:
                    table_name_range[sheet] = [name_range]

        return table_name_range

    def parse(self, file_path=None):
        if not self.verify(file_path):
            print "Something is wrong with the file but what?"
            return

        self.tables = self.get_table_name_ranges()
        methods = self.parse_methods()
        variables = self.parse_variables()
        specimens = self.parse_specimens()
        units = self.parse_units()
        processing_levels = self.parse_processing_level()
        # sampling_feature = self.parse_sampling_feature()
        data_values = self.read_data_values()

    def parse_units(self):

        CONST_UNITS = 'Units'

        if CONST_UNITS not in self.tables:
            return []

        sheet = self.workbook.get_sheet_by_name(CONST_UNITS)
        tables = self.tables[CONST_UNITS]

        units = []
        for table in tables:
            cells = sheet[table.attr_text.split('!')[1].replace('$', '')]
            cells = cells[1:]  # Remove the column names

            for row in cells:
                unit = Units()
                unit.UnitsTypeCV = row[0].value
                unit.UnitsAbbreviation = row[1].value
                unit.UnitsName = row[2].value
                unit.UnitsLink = row[3].value
                units.append(unit)

        return units

    def read_data_values(self):
        dataframes = pandas.read_excel(io=self.input_file, sheetname='Data Values')
        # dataframes.transpose()
        dataframes = dataframes.set_index(['LocalDateTime'])
        unstacked_dataframes = dataframes.unstack()
        avg = unstacked_dataframes['AirTemp_Avg']
        avg.values
        print dataframes

    def parse_processing_level(self):
        CONST_PROC_LEVEL = 'Processing Levels'

        if CONST_PROC_LEVEL not in self.tables:
            return []

        sheet = self.workbook.get_sheet_by_name(CONST_PROC_LEVEL)
        tables = self.tables[CONST_PROC_LEVEL]

        processing_levels = []
        for table in tables:
            cells = sheet[table.attr_text.split('!')[1].replace('$', '')]
            cells = cells[1:]  # Remove the column names

            for row in cells:
                proc_lvl = ProcessingLevels()
                proc_lvl.ProcessingLevelCode = row[0].value
                proc_lvl.Definition = row[1].value
                proc_lvl.Explanation = row[2].value
                processing_levels.append(proc_lvl)
{'fraction_non_alphanumeric': 0.09025807323334685, 'fraction_numerical': 0.004323740035130388, 'mean_word_length': 2.0841666666666665, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4726960', 'n_tokens_mistral': 2205, 'n_tokens_neox': 2089, 'n_words': 456}