DataFrames - Average Columns

user_4463825 0
Viewed 143

I have the following dataframe in pandas

Column 1   Column 2  Column3   Column 4
   2           2         2         4
   1           2         2         3

I am looking to create a dataframe which contains averages of columns 1& 2, Columns 3 &4, and so on.

  ColumnAvg(12)      ColumnAvg(34)
      2                   3
      1.5                 1.5

I was using this, but it is averaging everything.

df.mean(axis=1)

Is there a way, that I can add the column headers, when averaging each row. If not, another way would be to create two arrays, average them and then create a new dataframe.

pandas dataframe numpy python
3 Answers

You an do groupby with axis and pass the list

out = df.groupby([1,1,2,2],axis=1).mean()
     1    2
0  2.0  3.0
1  1.5  2.5
user_79645270

you can use groupby and group it with `//2' th index, which will group it by exactly 2 columns

  df.groupby((np.arange(len(df.columns)) // 2) + 1, axis=1).mean().add_prefix('ColumnAVg')

to start with index 2, as you mentioned in comment

 dfgrp=  df.iloc[:,2:].groupby((np.arange(len(df.iloc[:,2:].columns)) // 2) + 1, axis=1).mean().add_prefix('ColumnAVg')
 dfnew = pd.concat([df.iloc[:,2:],dfgrp])
user_36808310

Your idea of moving the computation into numpy is good, as it allows for multiple column pairs :

arr = df.to_numpy()
# split into pairs
split = np.split(arr, arr.shape[-1] // 2, axis=1)
# get mean and create dataframe
pd.DataFrame(np.mean(split, axis=0))
    0   1
0   2.0 3.0
1   1.5 2.5
user_71757130
Related