Challenge #3: Running Averages

[Junya_Wang]

Spoiler

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

[devX511]

Spoiler

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

[vishal89]

Hi All,

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

[jimmygatliff]

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]

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

Tabular data

[jiuh_park]

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]

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

Spoiler

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]

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

[iakovidc]

Spoiler

[deviseetharaman]

Spoiler

Took so long to complete.