CODEWITHSUNDEEP
pythonpandas
R_Queery
R_Queery

Reputation: 537

Pandas Create New Columns Using Rolling Sum with decrementing window

A slightly different version of this question. The answers mostly work, the challenge is that I think I need the range to update after each loop, and I'm not entirely sure how to accomplish this.

Alternatively, it seems like this is similar to a rolling sum type of question but the window is dynamic and I'm creating new columns....(not sure)

At any rate here is the code I wound up writing out but I know it's awful, still a n00b.

What's the more elegant way to do this

sample data

df = pd.DataFrame({'level1': np.random.randint(1, 10, 10),
               'level2': np.random.randint(1, 10, 10),
               'level3': np.random.randint(1, 10, 10),
               'level4': np.random.randint(1, 10, 10),
               'level5': np.random.randint(1, 10, 10),
               'level6': np.random.randint(1, 10, 10),
               'level7': np.random.randint(1, 10, 10),
               'level8': np.random.randint(1, 10, 10),
               'level9': np.random.randint(1, 10, 10),
               'level10': np.random.randint(1, 10, 10),
               'level11': np.random.randint(1, 10, 10),
               'level12': np.random.randint(1, 10, 10),
               'level13': np.random.randint(1, 10, 10),
               'level14': np.random.randint(1, 10, 10),
               'level15': np.random.randint(1, 10, 10)})

My current "working" solution"

orgcols['layers2'] = orgcols.Level2 + orgcols.Level3 + orgcols.Level4 + orgcols.Level5 + orgcols.Level6 + orgcols.Level7 + orgcols.Level8 +orgcols.Level9 + orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15

orgcols['layers3'] = orgcols.Level3 + orgcols.Level4 + orgcols.Level5 + orgcols.Level6 + orgcols.Level7 + orgcols.Level8 + orgcols.Level9 + orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15

orgcols['layers4'] = orgcols.Level4 + orgcols.Level5 + orgcols.Level6 + orgcols.Level7 + orgcols.Level8 + orgcols.Level9 + orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers5'] = orgcols.Level5 + orgcols.Level6 + orgcols.Level7 + orgcols.Level8 + orgcols.Level9 + orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers6'] = orgcols.Level6 + orgcols.Level7 + orgcols.Level8 + orgcols.Level9 + orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers7'] = orgcols.Level7 + orgcols.Level8 + orgcols.Level9 + orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers8'] = orgcols.Level8 + orgcols.Level9 + orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers9'] = orgcols.Level9 + orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers10'] = orgcols.Level10 + orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers11'] = orgcols.Level11 + orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers12'] = orgcols.Level12 + orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers13'] = orgcols.Level13 + orgcols.Level14 + orgcols.Level15 

orgcols['layers14'] = orgcols.Level14 + orgcols.Level15 

orgcols['layers15'] = orgcols.Level15

Upvotes: 1

Views: 131

Answers (2)

R_Queery
R_Queery

Reputation: 537

Updated, here is the final code. The issue was that I need to create a the new columns with 1 loop and then write a loop to sum the columns. The prior code: 

for i in range(2, 15, 1):
    orgcols['layers_'+str(i)] = orgcols.loc[:,'Org_Chart_Level_2_Name_int':'Org_Chart_Level_15_Name_int'].sum(axis=1)

Created the new columns and summed across, but since I needed to skip columns in a decreasing manner, I needed a second loop, like this: 

for i in range(2,16):
    t='layers'+str(i)
    orgcols[t]=0
    for j in range(i,16):
        orgcols[t]=orgcols[t]+orgcols['Level'+str(j)]

Upvotes: 0

Ankur Jyoti Phukan
Ankur Jyoti Phukan

Reputation: 806

You have to use two loops one for layers and internal loop to do the summation part.

for i in range(2,15):
  t='layers'+i
  orgcols[t]=0
  for j in range(i,15):
    orgcols[t]=orgcols[t]+orgcols['Level'+j]

i Just try to reduce the code in few lines, and have not try to run this due to no data provided so, if any error occur please comment.

Upvotes: 1

Related Questions