ncalm

={"C","L","Q","C","I","Y","O","G","K","P","P","I";"R","H","O","L","L","Y","A","P","H","T","V","F";"Q","P","R","F","U","L","K","P","E","V","J","E";"F","R","Q","I","E","S","X","R","E","M","Q","A";"G","V","X","O","S","J","L","K","T","A","Y","R";"F","T","N","L","H","T","C","O","O","D","C","S";"J","O","L","L","Y","A","M","J","H","C","M","E";"E","N","O","E","R","G","S","A","N","T","A","F";"R","L","A","N","D","I","T","N","S","D","P","Q";"T","P","V","U","K","F","D","G","N","D","H","T";"C","H","M","E","O","T","B","E","L","L","S","P";"W","Q","D","R","S","S","F","L","P","Y","M","X"}

ncalm

df = xl("A2:G31").astype(str)
# set the column names from the first group
df.columns = df.loc[1,:]

# create initial region and quarter columns
fill = lambda s, d: s.where(s.str.contains(d), np.nan).ffill()
df['Region'] = fill(df['Category'], ':')
df['Quarter'] = np.where(fill(df['Jan-Sales'], '-')=='Jan-Sales','Q1','Q2')

# remove unwanted rows

ncalm

# https://owenprice.pyscriptapps.com/excel-challenginator/latest/

import pandas as pd
df['Date'] = pd.to_datetime(df.Data, errors='coerce').ffill()
display(df.head(3), 'After creating Date column (first 3 rows)')

df = df.loc[df.Data!=df.Date]
display(df.head(3), 'After removing date-only rows (first 3 rows)')

df = pd.concat([

ncalm

You are a data cleaning assistant. I will give you a list of messy addresses. Standardize them into a clean table with the following columns: street address, city, state, postal code, country

Rules:
- Use appropriate capitalization.
- Always spell out the full name of a state or province. Do not use abbreviations. 
- Remove non-city locality descriptors (e.g., downtown, midtown, metro area, greater, borough of, city of). Do not place them in the city or street address.
- If a city token includes directional prefixes/suffixes (SE, NW, North, South), discard those and return only the clean city name; do not attach the markers to the street address.
- If the city is missing and a postal code is available, infer the city from the postal code.
- If the state/province is missing but the street address and city are available, infer the state/province.
- If a state/province is present but the country is missing, infer the country from it (e.g., Ontario → Canada; TX/CO/AL → United States).

ncalm

// Simple frequency table using COUNTA
FREQTBL = LAMBDA(dimension, GROUPBY(dimension, dimension, COUNTA,,0));

// Alternate version using EXPAND
FREQTBL_alt = LAMBDA(dimension, GROUPBY(dimension,EXPAND(1,ROWS(dimension),,1),COUNT,,0));

DESCRIBE = LAMBDA(data,
  LET(arepl, REPLICATE(VSTACK,ROWS(data)),
    LAMBDA(statistic, 
      GROUPBY(arepl(statistic),

ncalm

Here's an example of how this pyscript app works in the browser:

https://owenprice.pyscriptapps.com/pq-regex/latest/?txt=abc123def456&pattern=%5Cd%2B

To clone the project and modify, create an account on pyscript.com and clone this project:
https://pyscript.com/@owenprice/pq-regex/

ncalm

def flatten_geography(rich_value):
    props = rich_value.data.get("properties", {})
    flat = {}

    def extract(val):
        if not isinstance(val, dict):
            return str(val) if val is not None else None

        typ = val.get("type")
        if typ in ("String", "FormattedNumber"):

ncalm

def portfolio_analysis(data: UDF.Range, has_dates: bool = True, price_type: str = "Close",
                       annual_factor: int = 252, custom_weights: list = None, risk_free_rate: float = 0.0) -> UDF.Range:
    """
    Calculates key portfolio metrics from stock price data.
    
    Args:
        data: Daily stock prices. If has_dates=True, the first column should be dates.
        has_dates: Whether the first column contains dates (True) or is price data (False).
        price_type: String indicating price type used ("Close" or "Adj Close").
        annual_factor: Number of trading days per year, defaulting to 252 for daily data.

ncalm

import inspect

begin_print = False
module_print = True
function_print = True

for key, value in list(globals().items()):
    if begin_print and not key.endswith("_print"):
        # print imported module names if requested
        if module_print and inspect.ismodule(value):

ncalm

DATE.EXP = LAMBDA(y, m, d,
   LET(
      mdJoin, CROSSJOIN(m, d),
      ymdJoin, CROSSJOIN(y, mdJoin),
      yearArray, CHOOSECOLS(ymdJoin, 1),
      monthArray, CHOOSECOLS(ymdJoin, 2),
      dayArray, CHOOSECOLS(ymdJoin, -1),
      SORT(MAP(yearArray, monthArray, dayArray, DATE))
   )
);