Need extract and graph data from an awkwardly structured data set

Clint

New member
Joined
Jan 10, 2013
Messages
6
Reaction score
0
Points
1
I need to extract a subset of data from large data set and graph it as a line graph. I need an automated way to do this. Tried to figure it out on my own and completely failed.

This is kind of complicated (at least for me). The description of the structure of the data set and what I need to do is about a page and a half long. I’m worried that it will be difficult to get a reply (and I need to get this done for a work assignment very quickly) so I plan to post this request on 2 or 3 Excel help sites simultaneously. I hope that’s OK. (I did the same thing a few years ago, and a person on one of the sites got upset that I posted on more than one forum to ask for help.)

The first tab of the attached Excel file has an example of the raw data we get. I added column headers and some lines and colors to the raw data to try to show how the data is organized.



Structure of the data set:

The data comes from an electronic system that is constantly running. We have software that sends a query to the electronic system every half-second to get data on system status. We get a lot of data back from each query. I need to create a graph from a subset of that data.

The horizontal axis of the graph will be time. (In the Excel file, it’s Column A, called “Time offset”.)

The vertical axis will be the value of the data.

I want to graph the data as a line chart. There are 36 variables that need to be plotted (as 36 separate horizontal lines) on the line chart.

A data set will have between 45,000 and 90,000 rows of data, with 12 data points that need to be extracted from each row.

Now, here’s where it gets complicated.

The electronic system consists of three modules, numbered 0, 1 and 2. Each module has 12 “channels”, numbered 1-12. This means that the channel numbers aren’t unique in the raw data. Module 0 has channels 1-12; module 1 has channels 1-12; and module 2 has channels 1-12.

Each time we do a data query, we get several pieces of data about the status of each module and several more pieces of data about the status of each of the channels.

The first graph I need to make will show measurements of impedance on each of the 12 channels on each of the 3 modules. (12 channels x 3 modules = 36 sets of impedance values.) The graph needs to show how impedance changes on each of the channels over time.



Now it gets a bit more complicated…


The software sends each data query to each module simultaneously. But the responses from the modules don’t come back simultaneously. The responses come back at slightly different times, and not always in the same order. Sometimes the modules will respond in the order 0, 1, 2. Other times it will be 2, 0, 1, or 1, 2, 0, etc. You can see this in column E of the Excel file.

Column A is called “Time Offset”. Text color is blue. The values of Time Offset are the time in ms. They are a time stamp of the time that we get the response from each module.

Column E is called “Module Address”. Text color is green. It shows the number of each module. I put in horizontal lines to show data sets that are lumped together as a response to a single data query.

Columns J and Q are purple. All of the values in column J are 1. This indicates that the next seven columns of data are from Channel 1 – but there are three channels with number 1 (from modules 0, 1 and 2). Column Q shows the value of impedance for each of the channel 1’s.

I put in vertical lines to show the division between data for each of the channels with the same number (1, 2, etc.)

So, columns R and Y are in yellow text, with impedance data for each of the channel 2’s.

Columns Z and AG are in red text, with impedance data for each of the channel 3’s. Columns AH and AO are in maroon text, with impedance data for each of the channel 4’s. And columns AP and AW are in magenta text, with impedance data for each of the channel 5’s. I stopped coloring the text after that column because I figured that if you read this far, you would understand the pattern.



The 2nd tab of the Excel file shows my attempt to make it easier to extract and graph the data.

I made the following change in the 2nd tab:

I added a column to the right of each “Channel” column to give every channel a unique number. In the columns I added the channels are numbered as follows:

The channels on module 0 are still numbered 1 to 12

The channels on module 1 are numbered 13 to 24

The channels on module 2 are numbered 25 to 36



So, the graph I want to create from each data set would be as follows:

Horizontal axis is the time offset for each data point

Vertical axis is impedance

There will be 36 lines on the chart, with each line showing the impedance data for one channel (from 1 to 36).



Reminder: The attached Excel file only has 60 rows of data. It was truncated from the original data file that has about 85,000 rows.
 

Attachments

  • TB data sample.xlsx
    73.1 KB · Views: 3

Clint

New member
Joined
Jan 10, 2013
Messages
6
Reaction score
0
Points
1
No replies.... Am I trying to do something that isn't possible in Excel? (BTW, as of now this is the only forum that I've posted this question to.)
 

Clint

New member
Joined
Jan 10, 2013
Messages
6
Reaction score
0
Points
1
Still no replies ;- (
Is there another place to post this question that might be better suited for getting help with this situation?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,170
Reaction score
12
Points
38
Excel Version(s)
365
Is there another place to post this question that might be better suited for getting help with this situation?
This is one of the best places. This forum has recently changed its platform and going through teething problems (it seems no one gets alerts of replies to threads (including me) so have to check manually by visiting this forum).

Still no replies ;- (
Well, as you say in your first post 'The description of the structure of the data set and what I need to do is about a page and a half long' which in itself is a bit of deterrent.

Then you say you're in a hurry:
for a work assignment very quickly
which is now 10 days ago, so anyone coming across this thread now might consider the deadline has passed.

Regarding:
I plan to post this request on 2 or 3 Excel help sites simultaneously. I hope that’s OK.
Yes it's OK provided you supply links here to where you've posted elsewhere as soon as you do.
Have you done so so far?

Now to the nitty gritty:
I need an automated way to do this.
In the attached, not quite an automated way but I think a lot of the donkey work has been eliminated.
In the attached is a Power Query solution to transform the data, then my manual addition of a chart (at cell AM2 of the Sheet3 sheet) and a bit of a macro to label the lines on that chart.
Far from perfect (and I may not have done it in the most effective way).
You should be able to update the table on sheet Raw data (2), then right-click somewhere on the big table on sheet Sheet3 and the data and chart should update (but the labels will be wrong so you'll need to run the macro blah).

This is only the beginnings of a solution, it needs polishing (big time).
 

Attachments

  • ExcelGuru11598TB data sample.xlsm
    80 KB · Views: 1

Clint

New member
Joined
Jan 10, 2013
Messages
6
Reaction score
0
Points
1
Hi p45cal,
Thank you for getting back to me. I appreciate the detailed reply and the approach you took to help me graph the data I need.
As I mentioned, I was in a hurry. I ended up working with the programmer who wrote the code that captures data from the electronic system. He wrote some code (about 60 lines in Python) and then exported it to an EXE file. The EXE file automatically extracts the data and presents it in CSV format that can be imported into Excel, and then graphed with no further data massaging needed. I had to pay him $200 to write the code, but it was worth it. I got the graphs created on time.
Thanks again for your assistance.
 
Top