0

I've recently started using pandas pivot table functionality and want to add extra data in the output. I have below dataframe currently

[Dataframe] [1]: https://i.stack.imgur.com/vfNDO.png

I want add one more column next to last column which will give 12 months expense projection i.e. average*12.

[Result dataframe] [2]: https://i.stack.imgur.com/28ZPo.png

Thanking in advance for help from fellow python experts.

Rachit
  • 9
  • 2
    Please add the dataframes in code blocks so that its easier for others to assist you! – Akshay Sehgal Jul 30 '20 at 17:54
  • 1
    Does this answer your question? [Add column to the end of Pandas DataFrame containing average of previous data](https://stackoverflow.com/questions/31698861/add-column-to-the-end-of-pandas-dataframe-containing-average-of-previous-data) – Mobina Jul 30 '20 at 17:56
  • Hi Mobina, Thanks for response. It indeed is a good tip and works, but I'm facing a couple of problems, 1. I could have given a better example of DF but my DF has two section of six months data columns, Expense and Sale. When I'm trying to create projection for sales, the mean is happening for both sections which means instead of taking average of 6 months of sales, it is averaging from sales and expense both (12 columns). 2. In the sales section there are empty cells as well (NAN). While, taking average, it is reducing count by ignoring the empty cell. Need to find solution for this – Rachit Aug 03 '20 at 07:35
  • Hi Akshay, Thanks for the tip. I was struggling with addition of DF. Will remember this in future – Rachit Aug 03 '20 at 08:18

1 Answers1

0

For the given data frame, assuming you have non Nan values, you can do the following.

df['Yearly Projection'] = df.mean(axis=1)*12
# here df is your original datafame
Animesh Mukherkjee
  • 410
  • 2
  • 10
  • Hi Animesh, Thanks for the tip. It indeed helps. However, I still have two problems which I'm struggling with:- 1. I could have given a better example of DF but my DF has two section of six months data columns, Expense and Sale. When I'm trying to create projection for sales, the mean is happening for both sections which means instead of taking average of 6 months of sales, it is averaging from sales and expense both (12 columns). 2. In the sales section there are empty cells as well (NAN). While, taking average, it is reducing count by ignoring the empty cell. – Rachit Aug 03 '20 at 07:28