Challenge #265: Keep In Touch

[TonyAndriani]

Reposting under my new user ID.

[gambiera]

This one took a while. I wrote a macro to sort lists of words  into their alphabetically-sorted equivalents. Then I had to write the geography bit of the routine twice to work out why it was giving strange results. The reason was because there are two words (MAINLINE and  MOONLIKE) that are formed by states that have one state in the middle with three states that don't touch each other coming off of it.  So, depending on how you calculated the contiguous states, you may have missed these two.

I probably could have done this more efficiently, but it was an interesting learning experience.

Spoiler

[fsalmon]

Spoiler

Very difficult. went down a rabbit hole that didn't end up working. Did not think of using tokenise and making it alphabetical until I had a look at the answer.

 

[FrederikE]

Spoiler

w.o. the duplicate ones. 

[mmontgomery]

Challenge #265

[TramNguyenNgoc]

Fun challenge, a lot to take away. I'm sure there are more elegant approaches than mine but learnt a whole lot of stuff about string. I tried all sorts of things from iterative macros to transpose and crosstab up to 98M combinations of word but ended up with this one below, cannot be more concise.

[JennyMartin]

Spoiler

Tricky one, definitely had a sneaky peak at the solution and then still didn't solve optimally getting up to ~165 million rows at one point 😅 still great to learn! Video solution here: https://youtu.be/SGvhBHy5ZHk

[delilah]

Spoiler

[acarter881]

Here's my solution. This was really rough for me. I don't have as much in Alteryx since I spent a lot of time troubleshooting in Python after attempting this in the Python tool.

Spoiler

Here's the Python code I wrote to generate the text file. Wrote the code for attributing each word to its corresponding states, but I was having issues with how long the script was taking to run. I think there's something in my main loop that's duplicating the results and increasing the time spent.

Spoiler

import pandas as pd
from itertools import permutations
from tqdm import tqdm

df = pd.read_excel(io='./notebooks/abbrs.xlsx')

df_2 = pd.read_excel(io='./notebooks/words.xlsx')

state_dict = dict()

comp_list = [word for word in df_2['Word']]

plets = set()

for i in range(len(df['STATE_CD'])):
    state_dict[df['STATE_CD'][i]] = [*df['Concat_Universe_STATE_CD'][i].split(',')]

# Create set of routes of 4 states
for frm, to in state_dict.items():                     
    for target in to:                                   
        for target2 in state_dict[target]:              
            if target2 == frm:
                continue
            for target3 in state_dict[target2]:         
                if target3 == target or target3 == frm:
                    continue
                else:
                    plets.add((frm, target, target2, target3))

plets = sorted(plets)

# Create list of 8 letters
letters = [''.join(abbrs) for abbrs in plets]

big_list = list(zip(plets, letters))

words = list()

for i in tqdm(range(len(big_list))):
    perms = list(permutations(iterable=big_list[i][1], r=len(big_list[i][1])))
    my_words = list(map(''.join, perms))
    for word in my_words:
        words.append(word)

with open(file='output.txt', mode='w') as f:
    for thing in words:
        f.write(thing + '\n')

[Jean-Balteryx]

Here is my workflow !