• The Dataset (bmw.csv)

Download Sample Dataset: bmw.csv

1. Load the Dataset

You start by importing Pandas and loading the BMW CSV file. The read_csv() function reads the file and creates a DataFrame. Calling head() shows the first five rows, which lets you confirm whether the data loaded correctly.

import pandas as pd 
df = pd.read_csv('bmw.csv') 
df.head()

Output:

2. Understanding value_counts()

The value_counts() function counts how many times each unique value appears in a column. It sorts the results in descending order, returns a Series, and uses the unique values as the index. This makes it useful when you want to understand category frequency.

However, many beginners skip value_counts even though it saves time.

2.1 Count Transmission Types

df['transmission'].value_counts()

Output:

This shows that semi-automatic transmissions appear the most in the dataset.

In addition, both functions work well when you analyse multiple columns.

2.2 Get Proportions Using normalize=True

df['transmission'].value_counts(normalize=True)

This means about 43 percent of all BMW cars in the dataset are Semi-Auto.

2.3 Convert to Percentage

df['transmission'].value_counts(normalize=True) * 100

Multiplying by 100 changes the proportions into readable percentages.

Output

2.4 Disable Sorting Using sort=False

df['transmission'].value_counts(sort=False)

The sort=False option tells Pandas to return counts in the order values appear in the column instead of sorting them.

Output

2.5 Sort by Index

df['transmission'].value_counts().sort_index()

Here, Pandas counts the values and then sorts them alphabetically by their index. That helps when you want an organized output rather than descending order.

Output

2.6 Reset the Index

df['transmission'].value_counts().reset_index()

The reset_index() function converts the Series into a DataFrame. The old index becomes a normal column, which makes the output easier to join with other tables or export.

Output

3. Count Multiple Columns Using value_counts()

You can count combinations by passing multiple columns. Pandas treats each unique pair as a separate category and counts how often it appears.

df[['year','transmission']].value_counts(normalize=True)

Output (truncated)

This reveals that 2019 had the highest number of semi-automatic cars.

3.1 Get Proportion for Multiple Columns

df[['year','transmission']].value_counts(normalize=True)

Pandas now divides each pair count by the total number of rows and returns proportions instead of raw frequencies.

Output:

3.2 Percentage Format

df[['year','transmission']].value_counts(normalize=True) * 100

This returns percentage values, making it easier to read and compare the categories.

Output:

3.3 Sort Index on Both Columns

mdf = df[['year','transmission']].value_counts(normalize=True) * 100 
mdf.sort_index()

sort_index() sorts based on the tuple index consisting of year and transmission. This gives neatly organized output ordered by both columns.

Output:

3.4 Reset Index and Sort

mdf = mdf.reset_index() 
mdf.sort_values(['year','transmission'], ascending=[True, False])

After resetting the index, the columns become normal DataFrame columns. Sorting them gives better control over the display order, such as sorting by year ascending and transmission descending.

Output:

4. Understanding groupby()

The groupby() function groups rows based on a column and then performs calculations on each group. You can compute mean, median, sum, max, min, count, and more. Pandas separates the data into groups internally and then applies your chosen operation to each group independently.

4.1 Average Mileage per Transmission

mdf = df.groupby('transmission').agg(avg_mileage=('mileage','mean')) 
mdf

All rows are grouped by transmission type in this code, which then chooses the mileage column within each group, computes the mean, and outputs the results in a new DataFrame.

Output:

4.2 Reset Index

mdf.reset_index()

Resetting the index converts the group labels back into normal DataFrame columns.

4.3 Sort by Mileage

mdf.sort_values('avg_mileage', ascending=False)

Sorting the result arranges the transmission types based on average mileage. This makes comparisons easier.

Manual cars show the highest average mileage among the three.

Output:

Insight:
Manual cars have the highest mileage.

5. Groupby With Multiple Aggregations

A dictionary-style structure within agg() allows you to compute multiple statistics simultaneously. Pandas adds the outcomes to new output columns after applying each operation to the designated column.

For example, groupby helps you split data before applying calculations.

mdf = df.groupby('transmission').agg( 
      avg_mileage=('mileage','mean'),
      median_mileage=('mileage','median'),
      car_count=('model','count') 
)

Here, Pandas groups by transmission type and calculates three different values. It takes the mean and median of mileage and counts the number of models in each group.

Output:

6. Price and Engine Size Analysis

You can combine many aggregations across multiple columns. Pandas handles each operation one by one inside each transmission group.

mdf = df.groupby('transmission').agg( 
    model_count=('model','count'), 
    total_price=('price','sum'), 
    avg_price=('price','mean'), 
    median_price=('price','median'), 
    max_price=('price','max'), 
    min_price=('price','min'), 
    avg_engine_size=('engineSize','mean') 
)

Pandas divides the data by transmission first. The total number of cars, the average price, the median price, the highest and lowest prices, the sum of prices, and the average engine size for each transmission group are then determined.

Output:

7. Best Fuel Type for Mileage

You can group by fuel type to examine mileage performance across different fuel categories.

mdf = df.groupby('fuelType').agg( 
    avg_mileage=('mileage','mean'), 
    median_mileage=('mileage','median')
 ).sort_values('avg_mileage', ascending=False)

Fuel types are grouped by Pandas, which then calculates mileage statistics and sorts the results so that the fuel type with the best mileage shows up first.

Output:

8. Multi-Column Groupby

You can group by more than one column at the same time. Pandas creates a separate group for every transmission and fuel type combination.

mdf = df.groupby(['transmission','fuelType']).agg( 
    avg_price=('price','mean')
)

Pandas reads each unique pair of transmission and fuelType, groups rows accordingly, calculates the average price inside each group, and returns the results in a multi-index DataFrame.

Output:

9. Multi Aggregation on Price and Mileage

Here, you calculate the average price, total price, and average mileage for each transmission and fuel type combination.

As a result, your summaries become cleaner and easier to compare.

mdf = df.groupby(['transmission','fuelType']).agg( 
    avg_price=('price','mean'), 
    total_price=('price','sum'), 
    avg_mileage=('mileage','mean'), 
)

Pandas handles each calculation separately but keeps the same group structure. The result gives deep insights into how transmission and fuel type together affect price and mileage.

Finally, you understand how to combine value_counts and groupby for deeper analysis.

Output:

Final Thought

You looked at how value_counts() facilitates working with multiple columns, formatting results, calculating proportions, and counting values. In addition to applying computations like mean, median, sum, count, and more, you learned how to group rows using the groupby() function. You also saw how multi-aggregation and multi-column grouping produce effective summaries. These features provide you with powerful tools for efficiently and rapidly analyzing complicated datasets.

Internal link:

Aggregate Functions in Pandas: Beginner’s Guide with Examples

Filtering in Pandas: Learn loc, iloc, isin(), and between()

Load Data in Pandas – A Complete Beginner’s Guide to Data Import

Pandas official documentation:

https://pandas.pydata.org/docs/ag

Conclusion:

Data science is transforming finance, from predictive analytics to personalized services. It’s not just a technology upgrade – it’s changing how the industry operates. Those who leverage data science will stay ahead in this data-driven era.

Want to know what else can be done by Data Science?

If you wish to learn more about data science or want to advance your career in the data science field, feel free to join our free workshop on Master’s in Data Science with Power BI, where you will get to know how exactly the data science field works and why companies are ready to pay handsome salaries in this field.

In this workshop, you will get to know each tool and technology from scratch, which will make you skillfully eligible for any data science profile.

To join this workshop, register yourself on ConsoleFlare, and we will call you back.

Thinking, Why Console Flare?

Recently, ConsoleFlare has been recognized as one of the Top 10 Most Promising Data Science Training Institutes of 2023.

Console Flare offers the opportunity to learn Data Science in Hindi, just like how you speak daily.

Console Flare believes in the idea of “What to learn and what not to learn,” and this can be seen in their curriculum structure. They have designed their program based on what you need to learn for data science and nothing else.

Want more reasons?

Register yourself on ConsoleFlare, and we will call you back.
Log in or sign up to view
See posts, photos, and more on Facebook.

value_counts and groupby in Pandas