How to use groupby inside groupby through iteration

Question

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 4-May-21 21:02pm

Vivek R G

Updated 5-May-21 2:33am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2021-05-05T02:33:00

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!