I want to produce a loop to find groupby mean for multiple columns

user_16659264 0
Viewed 53

I'm working with the following dataframe, called 'data':

print (data)

  local_authority  data_2016  data_2017  data_2018
0      Hartlepool          1          4          8
1      Hartlepool          3          6          7
2      Hartlepool          4          8          5
3   Tower Hamlets          3          1          2
4   Tower Hamlets          2          2          3
5   Tower Hamlets          8          0          5
6       Allerdale         27          6          1
7       Allerdale          4          4          1
8       Allerdale          4          3          3
9       Allerdale          6          8          4

I want to find the mean of the observations for each local authority in each year. Three lines of code gives the desired result for any single year:

data_2016 = data[['local_authority','data_2016']]
grouped_2016 = data_2016.groupby('local_authority')
means_2016 = grouped_2016.mean()

Print(means_2016)

                 data_2016
local_authority           
Allerdale        10.250000
Hartlepool        2.666667
Tower Hamlets     4.333333

I want to produce a loop that will run this calculation and produce an output for every year in the dataframe. This is the code I have tried:

from statistics import mean
def getAverage(df,year):
    df = df.copy()
    subset = df[f'data_{year}']
    groupedby = subset.groupby('local_authority')
    average=mean(groupedby)
    return average

average_by_year = pd.DataFrame()
for i in [x.split('_')[-1] for x in data.columns]:
    average_by_year[i] = [getAverage(data, i)]

which brings up the following error term: KeyError: 'data_authority'.

The problem with the above code would appear to be connected to the line

groupedby = subset.groupby('local_authority')

Without this line the code succeeds in calculating the mean of all observations for each individual year. I've added that line in to try and find the mean for each local authority within each year, but I can't get it to work.

numpy python loops
2 Answers
data.groupby('local_authority').mean()

should do it

user_59526150

The solution is to use groupby and mean as suggested by @RafaelNeves:

>>> df.groupby('local_authority').mean()

                 data_2016  data_2017  data_2018
local_authority
Allerdale        10.250000       5.25   2.250000
Hartlepool        2.666667       6.00   6.666667
Tower Hamlets     4.333333       1.00   3.333333

However for your understanding, the problem is i:

for i in [x.split('_')[-1] for x in data.columns]:
    print(i)

authority  # <- Not what you want
2016
2017
2018
user_152399510
Related