CODEWITHSUNDEEP
pythonpandas
bgilliam
bgilliam

Reputation: 95

python: creating a new dataframe from the averages of a filtered dataframe

i would like to filter a dataframe, calculate averages across rows and then create a new dataframe with those averages. In the below example I get the averages for types 'blue' and 'red', calculate averages by month and then create a dataframe with blue and red as columns and the monthly averages as rows.

My question is: is there a way to do this with a loop? So, for example, if i had a list (in this case ['blue', 'red']), could i loop through that list to get the get the averages, create the blue and red dataframes and then concatenate them?

import pandas as pd
#CREATE GENERIC DATAFRAME
df = {'account': ['Alpha', 'Beta', 'Gamma', 'Epsilon'],
      'Type': ['red','blue','red','blue'],
         'Jan': [150, 200, 50, 40],
         'Feb': [200, 210, 90, 30],
         'Mar': [140, 215, 95, 60],
         'Apr': [150, 190, 75, 20]}
df = pd.DataFrame.from_dict(df)

#filter for blue types only
df_blue = df[df['Type'] == 'blue']

#get averages for blue types
blue_series = df_blue.mean()

#create a dataframe for blue types
blue_df = blue_series.to_frame()
blue_df = blue_df.rename(columns = {0:'blue'})

#repeat above for red types
df_red = df[df['Type'] == 'red']
red_series = df_red.mean()
red_df = red_series.to_frame()
red_df = red_df.rename(columns = {0:'red'})

#CONCATENATE BLUE AND RED DATAFRAMES
final_df = pd.concat([blue_df, red_df],axis=1)

desired result:

    blue    red
Apr  105.0  112.5
Feb  120.0  145.0
Jan  120.0  100.0
Mar  137.5  117.5

Upvotes: 0

Views: 2092

Answers (1)

Jorge
Jorge

Reputation: 2239

You can try this:

means_df = pd.DataFrame()
for color in ['red', 'blue']:
    dff = df[df['Type']==color]
    means = dff.mean(0).to_frame().rename(columns={0:color})
    means_df = pd.concat([means_df, means], 1)
print(means_df)

or a less complicated:

df.groupby('Type').mean().T

both generate this dataframe:

Type    blue    red
Jan 120.0   100.0
Feb 120.0   145.0
Mar 137.5   117.5
Apr 105.0   112.5

Upvotes: 2

Related Questions