Click here to Skip to main content
15,171,576 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a huge dataset of the temperature of each hour on a day for 50+ cities. I want to get dataframe of max,min, and average tempearture date-wise for each city. For eg Dataset contains

City	Date	Time	Temperature
New York	11/10/2020	10:00	20
New York	11/10/2020	11:00	20
New York	11/10/2020	12:00	21
New York	11/10/2020	13:00	22
New York	11/10/2020	14:00	24
New York	11/10/2020	15:00	25
New York	11/10/2020	16:00	26
New York	11/10/2020	17:00	27
New York	11/10/2020	18:00	26
New York	11/10/2020	19:00	24
New York	11/10/2020	20:00	25

I want dataframe for each city that contains Max, min, and average temperature for every day (like city1_df, city2_df) etc.. I have tried groupby function inside groupby, but does not know how to iterate the process with different variable names. How can I automate this process through various cities?

What I have tried:

cities=df.groupby('City')
city1=cities.get_group('New York')

city1_max=city1.groupby('Date').max()
city1_min=city1.groupby('Date').min()
city1_avg=city1.groupby('Date').mean()

city1_max=city1_max.drop(columns=['City','Time'])
city1_min=city1_min.drop(columns=['City','Time'])

city1_max=city1_max.rename(columns={'Temperature':'Max Temp'})
city1_min=city1_min.rename(columns={'Temperature':'Min Temp'})
city1_avg=city1_avg.rename(columns={'Temperature':'Avg Temp'})

city1_df=pd.concat(['city1_max','city1_min','city1_avg'], axis=1)
Posted
Updated 5-May-21 3:33am
Comments
Richard MacCutchan 5-May-21 3:30am
   
So what is the question?
Vivek R G 5-May-21 8:07am
   
Make plots for each city which include Max temp, Min temp and average temp. per day. So I want these sub data frames
Richard MacCutchan 5-May-21 8:21am
   
OK, you managed to tell us what you want. But we still have no idea what your problem is. If you want help then please stop and think about exactly what problem you are trying to solve. If necessary write it down on paper and read through it to make sure it is clear. When you are satisfied that it is complete, then use the Improve question link below your question, and add the details there.
Maciej Los 5-May-21 3:33am
   
Are you working on dataframes in pandas?
Vivek R G 5-May-21 8:05am
   
yes

1 solution

I'd suggest to follow these two sites:
Comprehensive Guide to Grouping and Aggregating with Pandas - Practical Business Python[^]
Group by: split-apply-combine — pandas 1.2.4 documentation[^]
pandas.core.groupby.DataFrameGroupBy.agg — pandas 0.23.1 documentation[^]

I'm not an python/pandas expert...
As i can see, you can use very simple way to get 3 or more aggregation functions:
Python
agg_func_math = {
    'Temperature':
    ['min', 'max', 'avg']
}
df.groupby(['City']).agg(agg_func_math).round(2)


Good luck!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900