0

I have a dataset which a snippet can be seen below for different temperatures recorded in different countries, what I want to do is create a new pandas table which shows the average temperature for each country in Celcius and Fahrenheit using python.

          Country  Temperature_Celsius Symbol_C  Temperature_Fahrenheit Symbol_F
0   United States                 23.4     °C                   74.12       °F
1   United States                 25.6     °C                   78.08       °F
2   United States                 25.3     °C                   77.54       °F
3   United States                 24.1     °C                   75.38       °F
4   United States                 25.0     °C                   77.00       °F
5           India                 36.6     °C                   97.88       °F
6           India                 38.2     °C                  100.76       °F
7           India                 39.3     °C                  102.74       °F
8           India                 39.1     °C                  102.38       °F
9           India                 42.2     °C                  107.96       °F
10   South Africa                 23.4     °C                   74.12       °F
11   South Africa                 23.1     °C                   73.58       °F
12   South Africa                 24.0     °C                   75.20       °F
13   South Africa                 21.2     °C                   70.16       °F
14   South Africa                 21.6     °C                   70.88       °F

I have been able to do it seperately for celcius and fahrenheit using df.groupby('Country')['Temperature_Celsius'].agg(['count','mean']).reset_index() but it is not in dataframe and also does not have a heading for average. I have tried creating a list but I am implementing it wrong and have not found similar examples to follow and finding it very hard to figure out how to do it.

I would ideally like the end result to look like the below as a dataframe.

enter image description here

Any guidance would be appreciated!

ouroboros1
  • 9,113
  • 3
  • 7
  • 26
Marie
  • 11
  • 2
  • Hi, welcome to SO! Please add the data samples as text, not as a picture. E.g. if you're working with a `pd.DataFrame`, try `df.head().to_dict(orient='list')` and post in a block between triple backticks (```). Show both input *and* expected output. Also, show us what you have tried so far, and why your attempt isn't giving you the result that you expect. See: [Research Effort](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users). – ouroboros1 Aug 20 '22 at 22:52
  • Apologies. You do show research effort. But this is a duplicate of [Apply multiple functions to multiple groupby columns](https://stackoverflow.com/questions/14529838/apply-multiple-functions-to-multiple-groupby-columns). I think you are looking for: `df.groupby('Country').agg(Average_Celcius=('Temperature_Celsius','mean'),Average_Fahrenheit=('Temperature_Fahrenheit','mean')).reset_index(drop=False)`. – ouroboros1 Aug 20 '22 at 23:04
  • Add `sort=False` to preserve order. So, this should get you desired output: `df.groupby('Country', sort=False).agg(Average_Celcius=('Temperature_Celsius','mean'),Average_Fahrenheit=('Temperature_Fahrenheit','mean')).reset_index(drop=False)` – ouroboros1 Aug 20 '22 at 23:12
  • This is what I have been looking for, works perfectly! Thank you so much – Marie Aug 20 '22 at 23:14

0 Answers0