Excel, ChatGPT, and Querri - How to standardize units of measurement in your data

When working with data from multiple sources, data can be in different units. One example can be found in the Kaggle FIFA 21 Raw Dataset. This dataset has a lot of opportunities for cleaning up the data, but here we’re just going to look at the Height and Weight columns. We’ll look at 3 methods to clean up these columns and we’ll take each step by step.

The data in the height column is a mix of numbers in the Metric system (cm) as well as in the English system (ft and in), while the data in the weight column is in kgs. The goal here is to convert data in the Height column to Feet with one decimal place and Weight from kgs to lbs.

Method 1: Basic Excel

  1. Load File (Using Open Office)
  2. Add 2 Columns after Column N
  3. In Cell O2 Enter this formula: =RIGHT(M2,2)="cm"
  4. Copy this formula to all of column O.
  5. Sort the Worksheet by Column O.
  6. Find the Split.
  7. In Cell O2 Enter: =VALUE(LEFT(M2,1))+VALUE(MID(M2,3,LEN(M2)-3))/12
  8. In Cell P2 Enter: =VALUE(LEFT(N2,3))
  9. Copy the formulas in columns O and P down to the first TRUE in column O.
  10. Look up conversion rates: 1cm=0.0328084 feet and 1kg = 2.200462 lbs.
  11. In Cell O42 Enter: =ROUND(VALUE(LEFT(M42,3))*0.0328084,2)
  12. In Cell P42 Enter: =ROUND(VALUE(LEFT(N42,2))*2.200462,0)
  13. Copy these formulas down to the end.
  14. Select Columns O:P and Copy/Paste Values.
  15. Delete Columns M:N. All done.

Method 2: Advanced Excel (with or without leveraging ChatGPT)

  1. Add Columns O:P
  2. In Cell O2: Enter =IF(RIGHT(M2,2)="cm",ROUND(VALUE(LEFT(M2,3))*0.0328084,2),ROUND(VALUE(LEFT(M2,1))+VALUE(MID(M2,3,LEN(M2)-3))/12,2))
  3. In Cell P3: Enter =IF(RIGHT(N2,2)="kg",ROUND(VALUE(LEFT(N2,2))*2.200462,0),VALUE(LEFT(N2,3)))
  4. Copy these formulas down.
  5. Select Columns O:P and Copy/Paste Values.
  6. Delete Columns M:N.

Method 3: Using Querri

  1. Load the Data into a Project in Querri.
  2. Tell it to convert the data in plain English.

All the 3 tools were able to accomplish the task, the difference being that Excel needed some advanced knowledge to craft a set of formulae, ChatGPT made it easy but required prompting skill, while Querri simplified the task to plain English commands.

Tags

#AI
#ChatGPT
#Data Accessibility
#Data literacy
#Data management
#Excel
#How-To
#Large Language Models
#Messy Data
#cleandata
#data cleanup
#data organization