Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 19 of 21

Python Pandas — II — Question 8

Back to all questions
8
Question

Question 8

Given a DataFrame that stores the details of past 25 years' monthly sales. Some old data is however missing. Write a script to calculate average :

  • Monthly sales across years

  • Yearly sales

Make sure that missing values do not hamper the overall result.

Solution
import pandas as pd
data = {
    'Year': [],
    'Month': [],
    'Sales': []
}

n = int(input("Enter the number of rows in the DataFrame: "))
for i in range(n):
    year = int(input("Enter the year: "))
    month = int(input("Enter the month (1-12): "))
    sales = float(input("Enter the sales amount: "))
    data['Year'].append(year)
    data['Month'].append(month)
    data['Sales'].append(sales)

df = pd.DataFrame(data)

df.fillna(0, inplace=True)

avg_monthly_sales = df.groupby('Month')['Sales'].mean()

yearly_sales = df.groupby('Year')['Sales'].mean()

print("Average Monthly Sales Across Years:")
print(avg_monthly_sales)
print("\nYearly Sales:")
print(yearly_sales)
Output
Average Monthly Sales Across Years:
Month
1     543.25
2     644.25
3     486.50
4     583.50
5     376.50
6     644.75
7     847.00
8     497.75
9     564.50
10    701.00
11    524.75
12    522.25
Name: Sales, dtype: float64

Yearly Sales:
Year
2000    781.082333
2001    480.030233
2002    390.530000
2003    682.917767
2004    421.250050
2005    408.333333
2006    509.757500
2007    634.333667
2008    907.250000
2009    876.222222
2010    940.757578
2011    690.900067
2012    623.250043
2013    890.088888
2014    491.500000
2015    601.919997
2016    623.250055
2017    489.086633
2018    523.750000
2019    789.910067
2020    456.250000
2021    481.083333
2022    590.750000
2023    618.916667
2024    621.250000
Name: Sales, dtype: float64
Answer

import
pandas
as
pd
data
=
{
'Year'
: [],
'Month'
: [],
'Sales'
: []
}
n
=
int
(
input
(
"Enter the number of rows in the DataFrame: "
))
for
i
in
range
(
n
):
year
=
int
(
input
(
"Enter the year: "
))
month
=
int
(
input
(
"Enter the month (1-12): "
))
sales
=
float
(
input
(
"Enter the sales amount: "
))
data
[
'Year'
].
append
(
year
)
data
[
'Month'
].
append
(
month
)
data
[
'Sales'
].
append
(
sales
)
df
=
pd
.
DataFrame
(
data
)
df
.
fillna
(
0
,
inplace
=
True
)
avg_monthly_sales
=
df
.
groupby
(
'Month'
)[
'Sales'
].
mean
()
yearly_sales
=
df
.
groupby
(
'Year'
)[
'Sales'
].
mean
()
print
(
"Average Monthly Sales Across Years:"
)
print
(
avg_monthly_sales
)
print
(
"
\n
Yearly Sales:"
)
print
(
yearly_sales
)
Output
Average Monthly Sales Across Years:
Month
1 543.25
2 644.25
3 486.50
4 583.50
5 376.50
6 644.75
7 847.00
8 497.75
9 564.50
10 701.00
11 524.75
12 522.25
Name: Sales, dtype: float64

Yearly Sales:
Year
2000 781.082333
2001 480.030233
2002 390.530000
2003 682.917767
2004 421.250050
2005 408.333333
2006 509.757500
2007 634.333667
2008 907.250000
2009 876.222222
2010 940.757578
2011 690.900067
2012 623.250043
2013 890.088888
2014 491.500000
2015 601.919997
2016 623.250055
2017 489.086633
2018 523.750000
2019 789.910067
2020 456.250000
2021 481.083333
2022 590.750000
2023 618.916667
2024 621.250000
Name: Sales, dtype: float64