CODEWITHSUNDEEP
pythonpandas
ricksterrocket
ricksterrocket

Reputation: 79

python pandas averaging columns to produce new ones

I have a Pandas DataFrame with the following data, displaying the hours worked per week for employees at a company:

name       week 1     week 2      week 3     week 4...

joey       20         15           35         10
thomas     20         10           25         15
mark       30         20           25         10
sal        25         25           15         20
amy        25         30           20         10

Assume the data carries on in the same for 100+ weeks.

What I want to produce is a biweekly average of hours for each employee, so the average hours worked over two weeks. Shown in the following DataFrame:

name       weeks 1-2      weeks 2-4...      

joey         17.5           22.5           
thomas       15             20           
mark         25             17.5         
sal          25             17.5           
amy          27.5           15

How could I make this work? Trying out iterating right now but I'm stuck.

Upvotes: 1

Views: 46

Answers (1)

Gabriela Melo
Gabriela Melo

Reputation: 619

You can achieve that with the following:

for i in range(0, len(df.columns), 2): 
    df[f'weeks {i+1}-{i+2}'] = df.iloc[:, i:i+1].mean(axis=1)

This code basically iterates through the amount of columns, taking a step of size 2. Then it selects the column indicated by the current iteration (variable i) and the following column (i+1), averages these two, and stores in a new column.

It assumes columns are properly ordered.

Upvotes: 1

Related Questions