Data preparation (or call it pre-processing) is an essential and time-consuming part of any data analytic’s project. To that end, I was working on a data set needed some changes before I could plot it on an effective data visualization. Here’s what I did:
I was working on a data set that looked like this:
|Date||Abu Dhabi, United Arab Emirates||Adalaj, Gujarat, India||Addison, TX|
BUT: I wanted my data to look like
|Abu Dhabi, United Arab Emirates||1||1||1||3||2||2||2||2||2|
|Adalaj, Gujarat, India||4||4||4||3||2||3||3||2||2|
What did my real data looked liked?
it has 380 columns and 500+ Rows and so MANUAL copy pasting was NOT an option!
Excel 2010 Solution:
It’s so simple!
Step 1: Select the data > COPY (Shortcut: ctrl + c)
Step 2: Switch to a new/different excel sheet
step 3: Paste Special > Transpose (T)
So After doing this, This is how the Input & output looks:
In this post, We saw how to swap or reverse the axis of a table data in Excel 2010.
- How to add secondary axis in a chart in Excel 2010? (parasdoshi.com)
- Found something interesting by exploring a “List of companies by revenue” Data Set: (parasdoshi.com)