Normalize column values

Question

I have a dataset which contains different information such as Price, Rating etc. Rating will be always in range of 0-5, however other data like Price and Availability will be different ranges. I am trying to normalize them so that all the attributes look in the range of 0-5 scale.

Example: US has 3 cities with each city contain Price, Rating and Availability information. I am looking to see a summary information for each country for each attribute (Price, Rating and Availability) in the range of 0-5 based on the average of values within the country. This will help to plot them in a single graph to compare easily

Challenge is how can we adjust the pricing and availability information into a scale of 0-5 similar to how the Rating column is.

Base Data

CountryStateNameValueUSCAPrice600USCARating4.2USCAAvailability1USNYPrice800USNYRating3.9USNYAvailability1USPAPrice450USPARating4.5USPAAvailability0PortugalLisbonPrice320PortugalLisbonRating4.1PortugalLisbonAvailability1PortugalPortoPrice250PortugalPortoRating4PortugalPortoAvailability0

Expected Output

CountryNameValueUSPrice3.8USRating4.2USAvailability2.5

RobertOdera · Answer

Hi, @jay_viz

Kindly consider interpolation - keep in mind that you might want to use Non-Zero Averages for all fields except Rating.

I hope you find this helpful - Cheers!

For_jay-viz.yxzp

geraldo · Answer

@jay_viz

Here's a wordflow example

Normalize.yxmd

acarter881 · Answer

Hello, @jay_viz.

I am interpreting the minimum as 0 and the maximum as 5. This gives a different result, but I believe it's one method for solving the problem.

normalize_0_to_5_inclusive.yxmd