<jupyter_start><jupyter_text>Sheet Data ![image.png](attachment:28ae176e-c7ab-4588-90de-d9bf8aec478b.png)<jupyter_code>from typing import TypedDict, List, NamedTuple, Union, Literal
import pandas as pd
from openpyxl.worksheet.worksheet import Worksheet
import openpyxl
from tqdm import tqdm
import re

class SheetInfo(TypedDict):
    """get title and coordinates of table data from business unit workbook sheet"""
    title: str
    subtitle: str
    title_rows: List[int]
    index_cols: List[int]

class SearchException(Exception):
    pass

class Cell(TypedDict):
    row: int
    col: int

Direction = Union[Literal["across"], Literal["down"]]
             
def traverse_to_value(ws: Worksheet, start_cell: Cell, direction: Direction, stop_search: int = 10000) -> Cell:
    """
    move from a starting cell across or down in a worksheet until a non-empty cell is found and its coordinates returned
    exception thrown if iteration passes `stop_search` count
    """
    cell = start_cell.copy()
    for i in range(stop_search):
        if direction == 'across':
            cell['col'] += 1
        else:
            cell['row'] += 1
        if ws.cell(row=cell['row'], column=cell['col']).value is not None:
            return cell
    raise SearchException

def row_empty(row: List) -> bool:
    """
    detect if a row of cells is empty
    """
    return all(c.value is None for c in row)

def get_sheet_info(ws: Worksheet) -> SheetInfo:
    # get cell containing header
    header_cell = traverse_to_value(ws, Cell(row=1, col=1), "across")
    # get first cell containing column titles, below header and subheader
    titles_cell = traverse_to_value(ws, Cell(row=header_cell["row"] + 2, col=header_cell["col"]), "down")
    # get first cell with index values
    indexes_cell = traverse_to_value(ws, Cell(row=1, col=1), "down")
    title_rows = [r for r in range(titles_cell["row"], indexes_cell["row"]) if not row_empty(ws[r])]
    
    return {
        'title': ws.cell(
            header_cell["row"], 
            header_cell["col"]
        ).value,
        'subtitle': ws.cell(
            header_cell["row"] + 1, 
            header_cell["col"]
        ).value,
        'title_rows': title_rows,
        'index_cols': [i for i in range(1, header_cell["col"])],
    }
wb = openpyxl.load_workbook('excel-files/ukbusinessworkbook2021.xlsx')
sh8=get_sheet_info(wb["Table 8"])
sh8
def get_df(shnm, info):
    df = pd.read_excel(
        'excel-files/ukbusinessworkbook2021.xlsx', 
        sheet_name=shnm,
        header=[row-1 for row in info['title_rows']],
        index_col=[col-1 for col in info['index_cols']]
    )
    
    total_cols = df.filter(like='Total', axis=1).columns
    df = df.drop(total_cols, axis=1)
    
    total_rows = df.filter(like='Total', axis=0).index
    df = df.drop(total_rows, axis=0)
    
    df = df.apply(pd.to_numeric, errors='coerce')
    df = df.dropna()
    
    return df
df1 = get_df('Table 1', get_sheet_info(wb['Table 1']))
df2 = df1.copy()
dfflat = pd.melt(df2, ignore_index=False)
dfflat['variable_x'] = dfflat.index.get_level_values(level=0)
dfflat
import re
m=re.match('^Table (\d+)', 'Table 132')
m
m.group(1)
dftest = pd.DataFrame({'a':[1,2], 'b':['pls', 4]})
dftest.apply(pd.to_numeric, errors='coerce')
import re
from tqdm import tqdm
dfs = []
tables = {}
for shnm in tqdm(wb.sheetnames):
    match = re.match('^Table (\d+)', shnm) 
    if match:
        table_index = int(match.group(1))
        sh_info = get_sheet_info(wb[shnm])
        df = get_df(shnm, sh_info)
        df = pd.melt(df, ignore_index=False, var_name=[f'variable_{x}' for x in range(df.columns.nlevels)])
        df['variable_x'] = df.index.get_level_values(level=0)
        df['table'] = table_index
        dfs.append(df)
        tables[table_index] = sh_info
wbdfin = pd.concat(dfs)
wbdf = wbdfin.reset_index(drop=True)
dftables = pd.DataFrame.from_dict(tables, orient='index')
dftables = dftables.drop(['title_rows', 'index_cols'], axis=1)
dftables
wbdf['value'] = wbdf['value'].astype(int)
wbdf<jupyter_output><empty_output><jupyter_text>Database try writing dataframe to a test sqlite database<jupyter_code>import pandas as pd
df = pd.DataFrame({'a': ['a', 'b'], 'idx': ['x', 'y']})
import sqlite3
with sqlite3.connect('test.db') as con:
    df.to_sql('users', con=con, if_exists='replace', dtype={'idx': 'TEXT PRIMARY KEY'})<jupyter_output><empty_output><jupyter_text>now write for real<jupyter_code>with sqlite3.connect('businessdatabase2.db') as con:
    wbdf.to_sql('tabledata', con=con, if_exists='replace', dtype={'index_label': 'INTEGER PRIMARY KEY'})
    dftables.to_sql('tableinfo', con=con, if_exists='replace', dtype={'index_label': 'TEXT PRIMARY KEY'})<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1434305027460921, 'fraction_numerical': 0.014575411913814956, 'mean_word_length': 3.8022312373225153, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 16, 'https://': 0, '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}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '609955', 'license': 'MIT', 'n_tokens_mistral': 1712, 'n_tokens_neox': 1661, 'n_words': 388, 'path': 'data/datasheets.ipynb', 'repo_name': 'joeledwardson/business-activity-visualiser', 'size': '4734'}