I have a dataframe like this:
| A | B | C | D | |
|---|---|---|---|---|
| user_id | ||||
| 1 | 1 | 0 | 0 | 1 |
| 2 | 2 | 1 | 0 | 2 |
| 3 | 2 | 3 | 1 | 3 |
| 4 | 3 | 2 | 0 | 4 |
I need to compute the average of all the columns and need the dataframe looks like this:
| A | B | C | D | |
|---|---|---|---|---|
| user_id | ||||
| 1 | 1 | 0 | 0 | 1 |
| 2 | 2 | 1 | 0 | 2 |
| 3 | 2 | 3 | 1 | 3 |
| 4 | 3 | 2 | 0 | 4 |
| Average | 2 | 1.5 | 0.25 | 2.5 |
I'm trying this but it gives me error
df = df.append({'user_id':'Average', df.mean}, ignore_index=True)
Also working:
df = pd.concat([df, df.mean().to_frame('Average').T])
which will create the following result.
A B C D
1 1.0 0.0 0.00 1.0
2 2.0 1.0 0.00 2.0
3 2.0 3.0 1.00 3.0
4 3.0 2.0 0.00 4.0
Average 2.0 1.5 0.25 2.5
If you really want to mix floats and integers, please use
pd.concat([df, df.mean().to_frame('Average', ).T.astype(object)])
This will result in
A B C D
1 1 0 0 1
2 2 1 0 2
3 2 3 1 3
4 3 2 0 4
Average 2.0 1.5 0.25 2.5
I want to quote from the official documenation to dtypes to show the disadvantage of this solution:
Finally, arbitrary objects may be stored using the object dtype, but should be avoided to the extent possible (for performance and interoperability with other libraries and methods).
This is also the reason, why the default data type is float.
do you mean to do:
df.loc["Average", :] = df.mean()
this creates a new row called "Average" in your df where over all columns you store the mean over all columns
This also works:
pd.concat([df, pd.DataFrame(df.describe()).loc[['mean']] ])
A B C D
0 1.0 0.0 0.00 1.0
1 2.0 1.0 0.00 2.0
2 2.0 3.0 1.00 3.0
3 3.0 2.0 0.00 4.0
mean 2.0 1.5 0.25 2.5
You can use pandas.DataFrame.loc to add a line at the bottom :
df.loc['Average'] = df.iloc[:, :].mean()
