Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #3: Running Averages

Junya_Wang
8 - Asteroid
Spoiler
Junya_Wang_0-1588372826872.png

 

It takes longer to understand the question than to build the workflow.

devX511
7 - Meteor
Spoiler
d.JPG

Bit Complex to understand the Problem Statement with a weird output.

vishal89
5 - Atom

Hi All,

 

I did not understand the use of Dynamic rename toll in the workflow. Could you please help me out? 

jimmygatliff
6 - Meteoroid
Spoiler
My output is a little different -- keeps the values for each RM category, year, and month, but adds two columns for each original column adding the 3mo and 6mo averages.
MRod
8 - Asteroid

My output looks different. I put it back in a 2-dimensional table. I had 8,208 rows (1,368 original rows x 6 columns) when I transposed the data (solution has 25k)

 

Spoiler

 

  • Used the Multi-row formula to update missing values and then to create the averages
    • Had to sort after transposing to ensure I updated from the appropriate category
    • For the averages, made sure for values that don't exist we should have as Null for I can create a formula
    • Renamed the fields on the Join
Spoiler


Workflow
markrod30_0-1589160603287.png

 

 

 

Tabular data
markrod30_1-1589160755756.png

 

jiuh_park
6 - Meteoroid

I think there is no need to use multi-join tool since using 2 multi-formula tools in a row will solve the problem by appending both 3m and 6m fields. Also, I see no need for using cross tab, dynamic rename tool.

jarrod
ACE Emeritus
ACE Emeritus

Finally figured out a way to clean up the python code for this one:

Spoiler
jarrod_0-1589900310033.png
from ayx import Alteryx
df = Alteryx.read('#1')

# first part of [] is the row selection, so we are selecting all rows
# followed by the fields 3-8 (zero indexed)
# for each field in that selection, run 3Mo 
# (and prefix the new field with "3Mo_")
# then run 6Mo average and prefix with "6Mo_"

for i in df.iloc[:,3:8]:
    df['3Mo_'+i] = df.groupby('RM Category')[i].transform(lambda x: x.rolling(3,1).mean())
    df['6Mo_'+i] = df.groupby('RM Category')[i].transform(lambda x: x.rolling(6,1).mean())

Alteryx.write(df,1)

# The longer form of the above code is:
df['3Mo_C.LK98'] = df.groupby('RM Category')['C.LK98'].transform(lambda x: x.rolling(3,1).mean())
df['3Mo_P.LK98'] = df.groupby('RM Category')['P.LK98'].transform(lambda x: x.rolling(3,1).mean())
df['3Mo_C.1K'] = df.groupby('RM Category')['C.1K'].transform(lambda x: x.rolling(3,1).mean())
df['3Mo_D.1K'] = df.groupby('RM Category')['D.1K'].transform(lambda x: x.rolling(3,1).mean())
df['3Mo_C.NLP3'] = df.groupby('RM Category')['C.NLP3'].transform(lambda x: x.rolling(3,1).mean())
df['3Mo_P.NLP3'] = df.groupby('RM Category')['P.NLP3'].transform(lambda x: x.rolling(3,1).mean())
df['6Mo_C.LK98'] = df.groupby('RM Category')['C.LK98'].transform(lambda x: x.rolling(6,1).mean())
df['6Mo_P.LK98'] = df.groupby('RM Category')['P.LK98'].transform(lambda x: x.rolling(6,1).mean())
df['6Mo_C.1K'] = df.groupby('RM Category')['C.1K'].transform(lambda x: x.rolling(6,1).mean())
df['6Mo_D.1K'] = df.groupby('RM Category')['D.1K'].transform(lambda x: x.rolling(6,1).mean())
df['6Mo_C.NLP3'] = df.groupby('RM Category')['C.NLP3'].transform(lambda x: x.rolling(6,1).mean())
df['6Mo_P.NLP3'] = df.groupby('RM Category')['P.NLP3'].transform(lambda x: x.rolling(6,1).mean())
Alteryx.write(df,2)

 

 #SnakingMyWayThruChallenges

AcevedoYo
8 - Asteroid

100% did not understand this challenge and looking at the solution didn't clear up the goal, but hey this is what I came up with.

Spoiler
YomaraA_0-1589988904961.png

 

iakovidc
7 - Meteor
Spoiler
Capture.PNG
deviseetharaman
11 - Bolide
Spoiler
Took so long to complete.