[Python] Dynamically adding columns to panda dataframes [duplicate]

Trying to use pandas to create a data frame that contains the total of time spent on each task over a period of time, then add a column for each week in the data, dynamically identifying what weeks to add.

It feels like the kind of task that pandas should excel at, but (more likely due to my lack of expertise) I end up doing it in a very ugly way, so just asking if there is a better way.

So let's start with the setup, I generated a data frame that has four weeks, and five tasks, and a random number of hours added per tasks

import pandas as pd
import numpy as np


ramdomizer=np.random.default_rng(2025)
hours_array = ramdomizer.integers(2, 8, 15)
df = pd.DataFrame({
"Week": [ 1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4],
"Task": [ "Task 1", "Task 2", "Task 3", "Task 4", "Task 5", "Task 1", "Task 2", "Task 4", "Task 5", "Task 3", "Task 1", "Task 3", "Task 5", "Task 1", "Task 4"],
"Hours": hours_array
})
df


Give me a nice dataframe that looks like

Week​

Task​

Hours
0​

1​

Task 1​

4
1​

1​

Task 2​

7
2​

1​

Task 3​

7
3​

1​

Task 4​

4
4​

1​

Task 5​

7
5​

2​

Task 1​

6
6​

2​

Task 2​

5
7​

2​

Task 4​

7
8​

3​

Task 5​

6
9​

3​

Task 3​

7
10​

3​

Task 1​

4
11​

3​

Task 3​

2
12​

4​

Task 5​

4
13​

4​

Task 1​

3
14​

4​

Task 4​

4​

And getting the total is easy enough

summary_df = df.groupby("Task")["Hours"].sum()
summary_df

Task​

Hours
Task 1​

17
Task 2​

12
Task 3​

16
Task 4​

15
Task 5​

17​

Still nice and forward, but now I want to add the next couple of columns, and preferably I felt the right way would be to do something like

for week, week_data in df.groupby("Week"):
print(f"Processing week {week}")
summary_df[f"Week {week}"] = (week_data.loc[
week_data['Task'].eq[summary_df['Task']],
['Hours']].sum())


But I just could not get this approach to work, so ended up building a dict with all the data, making it into a data frame:

week_columns={}
print(summary_df.index)
for week, week_data in df.groupby("Week"):
current_week = []
print("---------------------------------------------------")
print(f"Processing week {week}")
for task in summary_df.index:
mask = week_data['Task'].eq(task)
task_hours = week_data.loc[mask, ["Hours"]].sum().iloc[0]
print(f"Found {mask.sum()} entries for {task} in week {week} totaling {task_hours} ")
current_week.append(task_hours)

week_columns[f"Week {week}"] = current_week
print(week_columns)
new_df = pd.DataFrame(
index= summary_df.index,
data= week_columns
)

new_df


It is VERY verbose, but it does do the trick,

Task​

Week 1
Week 2
Week 3
Week 4
Task 1​

4​

6​

4​

3
Task 2​

7​

5​

0​

0
Task 3​

7​

0​

9​

0
Task 4​

4​

7​

0​

4
Task 5​

7​

0​

6​

4​

And I can then concat the two dataframes,

concat_df = pd.concat([summary_df, new_df], axis=1)
concat_df

Task​

Hours
Week 1
Week 2
Week 3
Week 4
Task 1​

17​

4​

6​

4​

3
Task 2​

12​

7​

5​

0​

0
Task 3​

16​

7​

0​

9​

0
Task 4​

15​

4​

7​

0​

4
Task 5​

17​

7​

0​

6​

4​

but it seems convoluted, is there a simpler more "pandas mastery" way? Any suggestions or suggested reading are welcome

Continue reading...