CODEWITHSUNDEEP
pythonpandaspandas-groupby
Hendry Lim
Hendry Lim

Reputation: 1974

Group into Years and Type using Pandas

I have a dataset (Data):

(Data)

Quarter Type  Value
1989-Q1 A      3.8
1989-Q1 B      3.9
1989-Q2 A      3.7
1989-Q2 B      3.2
1989-Q3 A      4.2
1989-Q3 B      4.2
1989-Q4 A      4.1
1989-Q4 B      4.5
1990-Q1 A      4.8
1990-Q1 B      4.9
1990-Q2 A      4.7
1990-Q2 B      4.2
1990-Q3 A      4.6
1990-Q3 B      4.7
1990-Q4 A      4.8

I would like to use pandas to group the quarters into year for each of the type (Desired)

(Desired)

Quarter
1989 A 3.9  # (Average of the 4 Quarters)
1990 A 3.7
1989 B 3.7  # (Average of the 4 Quarters)
1990 B 3.6

Please advise how this can be achieved. Tq

Upvotes: 0

Views: 33

Answers (2)

Josh Friedlander
Josh Friedlander

Reputation: 11657

Of course by the time I'd finished this others had gotten there first, but I did add a sort at the end:

df['Year'], df['Quarter'] = df['Quarter'].str.split('-', 1).str
df = df[['Year', 'Type', 'Value']]
grp = pd.DataFrame(df.groupby(['Year', 'Type'])['Value'].mean())
grp.sort_values(by='Type')

Upvotes: 1

user3471881
user3471881

Reputation: 2724

You want to calculate averages of Value per Year and Type. Since your data doesn't have a column with data of years you first need to get this from the Quarter column.

One way of doing it is to str.split().

df[["Year", "Quarter"]] = df['Quarter'].str.split('-', expand=True)

Now we can calculate averages by using DataFrame.groupby():

df.groupby(['Year', 'Type'])['Value'].mean()

Upvotes: 1

Related Questions