Pandas Column not found after doing an aggregation function

Question

I have an aggregation function which totals rows in a certain column based on an ID. After being able to correctly aggregate my rows, I wanted to select only the relevant columns, but I keep getting an error saying my ID column isn't found.

Full Code:

import pandas as pd
  
# initialize list of lists
data = [['A29', 112, 10, 0.3], ['A29',112, 15, 0.1], ['A29', 112, 14, 0.22], ['A29', 88, 33, 0.09], ['A29', 88, 29, 0.1], ['A29', 88, 6, 0.2]]
  
# Create the pandas DataFrame
df = pd.DataFrame(data, columns=['Id', 'Cores', 'Provisioning', 'Utilization'])

df['total'] = df['Provisioning'] * df['Utilization']

df=df[['Id', 'Cores','total']]
aggregation_functions = {'Cores': 'first', 'total': 'sum'}
df_new = df.groupby(df['Id']).aggregate(aggregation_functions)

df_new['total1']=df_new['total']/3
print(df_new) #the dataframe contains the Id columns
print(df_new.columns) #doesn't print Id column

df_new=df_new[['Id', 'total1']] #Error: Id column not found

I'm not sure what is happening here. A line above, I print the dataframe and the Id column is present. However, when I try selecting it, it returns an error saying it isn't found. How can I fix this issue?

The groupby makes the ID column into the index, so it is no longer a column. You can access it with df_new.index or use as_index=False — G. Anderson
– G. Anderson, Commented Feb 1, 2023 at 17:18

BrokenBenchmark · Accepted Answer · 2023-02-01 17:09:52Z

3

You should use as_index=False in the call to .groupby(); the Id column is part of the index, which prevents you from selecting it in the desired manner:

df_new = df.groupby(df['Id'], as_index=False).aggregate(aggregation_functions)

answered Feb 1, 2023 at 17:09

BrokenBenchmark

19.4k7 gold badges26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Corralien Over a year ago

df.groupby('Id', as_index=False).aggregate(aggregation_functions)?

BrokenBenchmark Over a year ago

That works, though I wanted to modify as little of the original code as possible.

Collectives™ on Stack Overflow

Pandas Column not found after doing an aggregation function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related