COLA | COLA: Clean Objectoriented & Layered Architecture | Architecture library

function onEdit(event) {
  const sourceSheet = "sheet1";
  const eventRange = event.range;
  const eventSheet = eventRange.getSheet();
  if (eventSheet.getSheetName() == sourceSheet && eventRange.columnStart == 1 && eventRange.isChecked()) {
    const ui = SpreadsheetApp.getUi();
    const result = ui.alert('Are you sure you this student is ready to export? This will delete this row', ui.ButtonSet.YES_NO);
    if (result == ui.Button.YES) {
      const [name, ...value] = eventRange.offset(0, 1, 1, 4).getValues()[0];
      const targetSheet = event.source.getSheetByName(name);
      if (targetSheet) {
        targetSheet.appendRow(value);
        eventSheet.deleteRow(eventRange.rowStart);
      } else {
        throw new Error("No sheet.");
      }
    } else {
      eventRange.uncheck();
    }
  }
}

# take the smallest value if A->B mapping is not unique
df_AtoB = df1.groupby("colA", as_index=False).agg(colB_=("colB", "min"))

df = df2.merge(df_AtoB, on="colA", how="left")
df.colB = df.colB.fillna(df.colB_)
df = df.drop(columns="colB_") 

# take the smallest value if A->B mapping is not unique
df_AtoB = df1.groupby("colA", as_index=False).agg(colB_=("colB", "min"))

df = df2.merge(df_AtoB, on="colA", how="left")
df.colB = df.colB.fillna(df.colB_)
df = df.drop(columns="colB_") 

def find_char(list_of_df , char):
    result = []
    for df in list_of_df:
        for c in df.columns:
            if char in c:
                result.append(c)
    return result

res = find_char([df,df2,df3] , 'A')
print(res)
['colA', 'colA', 'colA']

def find_char(list_of_df , char):
    result = []
    for df in list_of_df:
        for c in df.columns:
            if char in c:
                result.append(c)
    return result

res = find_char([df,df2,df3] , 'A')
print(res)
['colA', 'colA', 'colA']

l = [','.join(i.columns[i.columns.str.contains('A')]) for i in [df,df2,df3]]

WHERE 'Coca Cola' LIKE column2

library(dplyr)
library(tidyr)
mydf %>% 
   pivot_longer(cols = everything(), names_to = 'carac')

# A tibble: 4 x 2
  carac value
  <chr> <chr>
1 colA  a    
2 colB  1    
3 colC  X    
4 colD  2    

stack(mydf)[2:1]

library(dplyr)
library(tidyr)
mydf %>% 
   pivot_longer(cols = everything(), names_to = 'carac')

# A tibble: 4 x 2
  carac value
  <chr> <chr>
1 colA  a    
2 colB  1    
3 colC  X    
4 colD  2    

stack(mydf)[2:1]

library(dplyr)
library(tidyr)
mydf %>% 
   pivot_longer(cols = everything(), names_to = 'carac')

# A tibble: 4 x 2
  carac value
  <chr> <chr>
1 colA  a    
2 colB  1    
3 colC  X    
4 colD  2    

stack(mydf)[2:1]

out = pd.wide_to_long(df,['x','y'],i=['idx','colA','n'],j='cnt').dropna().reset_index()
Out[8]: 
   idx  colA  n  cnt     x    y
0   1    10  3     1   0.0  4.0
1   1    10  3     2   3.0  5.0
2   1    10  3     3   4.0  3.0
3   2     5  2     1   1.0  0.0
4   2     5  2     2   2.0  1.0
5   3    12  4     1   7.0  4.0
6   3    12  4     2   8.0  5.0
7   3    12  4     3  10.0  3.0
8   3    12  4     4  11.0  3.0

# pip install pyjanitor
import janitor
import pandas as pd
df.pivot_longer(index = ['idx', 'colA', 'n'], 
                names_to = (".value", "counter"), 
                names_pattern=r"(.)(.)", 
                sort_by_appearance = True).dropna()
 
    idx  colA  n counter     x    y
0     1    10  3       1   0.0  4.0
1     1    10  3       2   3.0  5.0
2     1    10  3       3   4.0  3.0
4     2     5  2       1   1.0  0.0
5     2     5  2       2   2.0  1.0
8     3    12  4       1   7.0  4.0
9     3    12  4       2   8.0  5.0
10    3    12  4       3  10.0  3.0
11    3    12  4       4  11.0  3.0

=arrayformula({"Email","Members";
query(
array_constrain(
{
flatten(split(rept("|"&Input!A2:A,len(regexreplace(Input!B2:B&" | "&Input!C2:C,"[^\|]",))+1),"|")),
trim(flatten(split(Input!B2:B&"|"&Input!C2:C,"|")))
},
max(if(Input!B2:B<>"",len(regexreplace(Input!B2:B&" | "&Input!C2:C,"[^\|]",))+1,))*counta(Input!B2:B),2),
"where Col1 is not null",0)
})

=arrayformula({"Email","Manager";
query(
array_constrain(
{
flatten(split(rept("|"&Input!A2:A,len(regexreplace(Input!B2:B,"[^\|]",))+1),"|")),
trim(flatten(split(Input!B2:B,"|")))
},
max(if(Input!B2:B<>"",len(regexreplace(Input!B2:B,"[^\|]",))+1,))*counta(Input!B2:B),2),
"where Col1 is not null",0)
})

=arrayformula({"Email","Members";
query(
array_constrain(
{
flatten(split(rept("|"&Input!A2:A,len(regexreplace(Input!B2:B&" | "&Input!C2:C,"[^\|]",))+1),"|")),
trim(flatten(split(Input!B2:B&"|"&Input!C2:C,"|")))
},
max(if(Input!B2:B<>"",len(regexreplace(Input!B2:B&" | "&Input!C2:C,"[^\|]",))+1,))*counta(Input!B2:B),2),
"where Col1 is not null",0)
})

=arrayformula({"Email","Manager";
query(
array_constrain(
{
flatten(split(rept("|"&Input!A2:A,len(regexreplace(Input!B2:B,"[^\|]",))+1),"|")),
trim(flatten(split(Input!B2:B,"|")))
},
max(if(Input!B2:B<>"",len(regexreplace(Input!B2:B,"[^\|]",))+1,))*counta(Input!B2:B),2),
"where Col1 is not null",0)
})

<form method="get" action="browse-store-1.php">
 <label for="letter-start">Store names start with letter </label>
 <select id="letter-start" name="letter-start">
  <option id="all">All</option>
  <option id="#" value="#">#</option>
  <option id="a" value="A">A</option>
  <option id="b" value="B">B</option>
  <option id="c" value="C">C</option>
...
  <option id="z" value="Z">Z</option>
 </select>
 <input type="submit" name="submit">
</form>

<?php
if(isset($_GET['submit'])) {
    echo "<div class=\"flex-container\">";
    $selected = $_GET['letter-start'];
    foreach ($stores as $s) {
        if ($selected === $s['name'][0]) {
            $id = $s['id'];
            $name = $s['name'];
            //display the stores
            echo "<div class=\"item\"><a href=\"store-home.php?id=$id\"><div class=\"image\"><img src=\"images/store.png\" alt=\"a store\"></div><h3 class=\"name\">$name</h3></a></div>";
        }
    }
}

<form method="get" action="browse-store-1.php">
 <label for="letter-start">Store names start with letter </label>
 <select id="letter-start" name="letter-start">
  <option id="all">All</option>
  <option id="#" value="#">#</option>
  <option id="a" value="A">A</option>
  <option id="b" value="B">B</option>
  <option id="c" value="C">C</option>
...
  <option id="z" value="Z">Z</option>
 </select>
 <input type="submit" name="submit">
</form>

<?php
if(isset($_GET['submit'])) {
    echo "<div class=\"flex-container\">";
    $selected = $_GET['letter-start'];
    foreach ($stores as $s) {
        if ($selected === $s['name'][0]) {
            $id = $s['id'];
            $name = $s['name'];
            //display the stores
            echo "<div class=\"item\"><a href=\"store-home.php?id=$id\"><div class=\"image\"><img src=\"images/store.png\" alt=\"a store\"></div><h3 class=\"name\">$name</h3></a></div>";
        }
    }
}

library(dplyr)  
library(tidyr)

df %>%
  extract(ColA, c('A1', 'A2'), '(.*)/(.*)', remove = FALSE) %>%
  mutate(across(A1:A2, ~replace(., ColB != 'sep', NA)))

#         ColA      A1   A2 ColB
#1     abc/def     abc  def  sep
#2     bcd/efg    <NA> <NA>   no
#3 def/ghj/yes def/ghj  yes  sep
#4 fet/hjk/yes    <NA> <NA>   no

df[paste0('A', 1:2)] <- read.csv(text = sub("/(\\w+)$",
          ",\\1", df$ColA), header = FALSE)
df[paste0("A", 1:2)] <- replace(df[paste0("A", 1:2)], df$Col1B != 'sep', NA)

library(dplyr)
library(tidyr)
df %>% 
     separate(ColA, into = c("A1", "A2"), remove = FALSE, sep="/(?=[^/]+$)") %>%
     mutate(across(A1:A2, ~ case_when(ColB == 'sep' ~ .)))
#         ColA      A1   A2 ColB
#1     abc/def     abc  def  sep
#2     bcd/efg    <NA> <NA>   no
#3 def/ghj/yes def/ghj  yes  sep
#4 fet/hjk/yes    <NA> <NA>   no

df %>% 
 mutate(new = str_split(case_when(ColB == 'sep' ~ ColA), "/(?=[^/]+$)")) %>% 
 unnest_wider(c(new))

df[paste0('A', 1:2)] <- read.csv(text = sub("/(\\w+)$",
          ",\\1", df$ColA), header = FALSE)
df[paste0("A", 1:2)] <- replace(df[paste0("A", 1:2)], df$Col1B != 'sep', NA)

library(dplyr)
library(tidyr)
df %>% 
     separate(ColA, into = c("A1", "A2"), remove = FALSE, sep="/(?=[^/]+$)") %>%
     mutate(across(A1:A2, ~ case_when(ColB == 'sep' ~ .)))
#         ColA      A1   A2 ColB
#1     abc/def     abc  def  sep
#2     bcd/efg    <NA> <NA>   no
#3 def/ghj/yes def/ghj  yes  sep
#4 fet/hjk/yes    <NA> <NA>   no

df %>% 
 mutate(new = str_split(case_when(ColB == 'sep' ~ ColA), "/(?=[^/]+$)")) %>% 
 unnest_wider(c(new))

df[paste0('A', 1:2)] <- read.csv(text = sub("/(\\w+)$",
          ",\\1", df$ColA), header = FALSE)
df[paste0("A", 1:2)] <- replace(df[paste0("A", 1:2)], df$Col1B != 'sep', NA)

library(dplyr)
library(tidyr)
df %>% 
     separate(ColA, into = c("A1", "A2"), remove = FALSE, sep="/(?=[^/]+$)") %>%
     mutate(across(A1:A2, ~ case_when(ColB == 'sep' ~ .)))
#         ColA      A1   A2 ColB
#1     abc/def     abc  def  sep
#2     bcd/efg    <NA> <NA>   no
#3 def/ghj/yes def/ghj  yes  sep
#4 fet/hjk/yes    <NA> <NA>   no

df %>% 
 mutate(new = str_split(case_when(ColB == 'sep' ~ ColA), "/(?=[^/]+$)")) %>% 
 unnest_wider(c(new))

 SELECT C.costumer,REF.NAME,REF2.NAME
 FROM OTHER_TABLE AS C
 JOIN TABLE_SOMETHING_LIKE_THIS AS REF ON C.BUY1=REF.ID
 JOIN TABLE_SOMETHING_LIKE_THIS AS REF2 ON C.BUY2=REF2.ID

select costumer,
  (select name from tbl1 where id=buy1) buy1,
  (select name from tbl1 where id=buy2) buy2
from tbl2;