Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

CSV Data Visualizer Using XGraph.ahk


  • Please log in to reply
4 replies to this topic
carson32
  • Members
  • 2 posts
  • Last active: Jun 17 2016 05:34 PM
  • Joined: 22 Jan 2013

This is a script to plot a subset of csv numeric data into graph form. A slider control lets you precisely navigate the csv file. Graphs were created using XGraph.ahk. http://ahkscript.org...opic.php?t=3492 You need to download XGraph.ahk and place it in the same directory as Data Visualizer.ahk.

 

A sample csv file can be downloaded here https://www.dropbox....eClean.csv?dl=0

 

all%20three%20graphs.PNG?dl=0

 

Image showing all three graphs plotting compass heading raw readings. Scrolling the slider advances the graph viewport through the csv file
 
 
 

menus.PNG?dl=0

 

Image showing the the drop down menu. All the dropdowns have the same list of items. Each row in the dropdown corresponds to a column in the csv file. The text is determined by a header row in the csv file.

#Include XGraph.ahk 
;download XGraph.ahk here http://ahkscript.org/boards/viewtopic.php?t=3492
/*
Why you should use this gui
	- the gui lets you quickly visualize comma separated data in graph form for up to 3 data streams.
	- code could be easily modified to plot additional data streams
	- this script will create a config file to store your last used settings in the same directory as
	  the script
How to use the gui
	- !!!!Make sure your csv is not corrupt. ie blank fields, non-numeric data, extreme values, blank lines = bad mojo!!!!
	- drag and drop csv or any comma separated file onto the gui to analyze it
	- if you have headers on your csv file it will automatically populate the drop down menus
		- the example csv shows how to format the header (note the text controls do not resize so if weird
		  stuff happens try reducing length of variable name or unit
	- the values or percent check box lets you choose to display the actual data point reading (checked)
	  or show the percentage displayed on the graph (unchecked)
	- the same scale for all graphs check box is useful when comparing graphs. When it is is unchecked 
	  each graph gets its own unique scale factor depending on the min and max of the data set 
	  ie the max value will hit the very top and min will hit the very bottom for each graph. This mode
	  is great for viewing data that is unrelated or differing by orders of magnitude. When it is checked 
	  the graphs obey the scale of the data set with the largest range change. ie all data will fit in 
	  all three graphs but will be comparable. I use this when comparing the outputs of a 3 axis compass. 
	  !!!! The graphs have the same scale but do not share the same origin!!!!
	- control r (^r) reloads the script
*/

;load default values

IfNotExist Data_Visualizer.ini
{
	IniWrite  "" , Data_Visualizer.ini , coltwashere ,last_filename
	IniWrite  1, Data_Visualizer.ini , coltwashere ,last_var1
	IniWrite  2, Data_Visualizer.ini , coltwashere ,last_var2
	IniWrite  3, Data_Visualizer.ini , coltwashere ,last_var3
	IniWrite  1 , Data_Visualizer.ini , coltwashere ,values_percent
	IniWrite  0 , Data_Visualizer.ini , coltwashere ,all_graph_same_scale
}

IniRead,last_filename,Data_Visualizer.ini,coltwashere,last_filename
IniRead,last_var1,Data_Visualizer.ini,coltwashere,last_var1
IniRead,last_var2,Data_Visualizer.ini,coltwashere,last_var2
IniRead,last_var3,Data_Visualizer.ini,coltwashere,last_var3
IniRead,values_percent,Data_Visualizer.ini,coltwashere,values_percent
IniRead,all_graph_same_scale,Data_Visualizer.ini,coltwashere,all_graph_same_scale

;build gui
Gui Main:Add, DropDownList, x10 y10 w150 vVGItem1 altsubmit gGDropdown1,turtles||
Gui Main:Add, DropDownList, x+15 yp wp vVGItem2 altsubmit gGDropdown2,are||
Gui Main:Add, DropDownList, x+15 yp wp vVGItem3 altsubmit gGDropdown3,cool||

Gui Main:Add,Text,w150 x0 y+15 vVGVar1Label right ,Var 1:
Gui Main:Add,Text,wp x+5 yp vVGValue1 left,1
Gui Main:Add,Text,wp x0 y+15 vVGVar2Label right,Var 2:
Gui Main:Add,Text,wp x+5 yp vVGValue2 left,1
Gui Main:Add,Text,wp x0 y+15 vVGVar3Label right ,Var 3:
Gui Main:Add,Text,wp x+5 yp vVGValue3 left,1


Gui Main:Add,CheckBox,vVGValPer x330 y45 checked%values_percent% gGValuesOrPercent,Values <---> Percent
Gui Main:Add,CheckBox,vVGScale xp y+10 checked%all_graph_same_scale% gGSameScales,Same scale for all graphs
Gui Main:Add,Text,w75 x315 y+10 right ,Data Points :
Gui Main:Add,Text,w110 x+5 yp vVGDataPoint left,1


Gui Main:Add,Slider, w480 x10 y+30 gGSlider  NoTicks tooltip altsubmit vVGSlider,0

Gui, Main:Add,Text,w440 h100 x30 y+10 hwndhGraph1               
pGraph1 := XGraph(  hGraph1, 0, 3, "", 0x0000ff, 1, True )
Gui, Main:Add,Text,w440 h100 xp y+10 hwndhGraph2               
pGraph2 := XGraph(  hGraph2, 0, 3, "", 0x00ff00, 1, True )
Gui, Main:Add,Text,w440 h100 xp y+10 hwndhGraph3              
pGraph3 := XGraph( hGraph3, 0, 3, "", 0xff0000, 1, True)

;Gui Main: +Alwaysontop 
Gui Main:Show,AutoSize w500 x700 h500,Data Visualizer
sleep 500
GuiControl , Main:,VGItem1,||| 
GuiControl , Main:,VGItem2,|||
GuiControl , Main:,VGItem3,|||
	
GoSub MainGuiDropFiles
return

	
GValuesOrPercent:
GSameScales:
GDropdown1:
GDropdown2:
GDropdown3:
	Gui Main:Submit ,NoHide		
	
	;update gui when user clicks on check box or changes channel in drop down
	
	GuiControl , Main:,VGVar1Label,% csv_header_data[VGItem1-1,1] . ":"
	GuiControl , Main:,VGVar2Label,% csv_header_data[VGItem2-1,1] . ":"
	GuiControl , Main:,VGVar3Label,% csv_header_data[VGItem3-1,1] . ":"
	GoSub GSlider
	
	return
	
GSlider:
	;called when slider is dragged
	Gui Main:Submit ,NoHide		
	
	col_var1 := VGItem1 -1
	col_var2 := VGItem2 -1
	col_var3 := VGItem3 -1	
	val1:= 0
	val2:= 0
	val3:= 0

	i := 0
	;set values in internal array on xgraph control
	Loop 150
	{
		XGraph_Plot(pGraph1,val1,,false) 
		XGraph_Plot(pGraph2,val2,,false) 
		XGraph_Plot(pGraph3,val3,,false) 
		if(VGScale)
		{
			;user wants to use same scale for all graphs. find the minimum factor
			s1 :=  max_min_arr[2,col_var1]
			s2 :=  max_min_arr[2,col_var2]
			s3 :=  max_min_arr[2,col_var3]
		
			if((s1<s2)&&(s1<s3))
			{
				;s1 is smallest
				factor := s1
			}
			else if ((s2<s1)&&(s2<s3))
			{
				;s2 is smallest
				factor := s2
			}
			else
			{
				;s3 is smallest
				factor := s3
			}
			val1 := factor*abs((FileContents[VGSlider+i,col_var1]-max_min_arr[0,col_var1]))
			val2 := factor*abs((FileContents[VGSlider+i,col_var2]-max_min_arr[0,col_var2]))
			val3 := factor*abs((FileContents[VGSlider+i,col_var3]-max_min_arr[0,col_var3]))	
			;msgbox %factor%
		}
		else
		{		
			;user does not want same scale. use unique scale factors
			val1 := max_min_arr[2,col_var1]*abs((FileContents[VGSlider+i,col_var1]-max_min_arr[0,col_var1]))
			val2 := max_min_arr[2,col_var2]*abs((FileContents[VGSlider+i,col_var2]-max_min_arr[0,col_var2]))
			val3 := max_min_arr[2,col_var3]*abs((FileContents[VGSlider+i,col_var3]-max_min_arr[0,col_var3]))	
		}
		i++
	}
	;actually show the resultant array by redrawing graphs
	XGraph_Plot(pGraph1)
	XGraph_Plot(pGraph2)
	XGraph_Plot(pGraph3)
	;info := XGraph_Info(pGraph1)
	;tooltip %info%
	
	;show percentage or actual data point value
	if(VGValPer)
	{
		;show data point
		GuiControl , Main:,VGValue1,% FileContents[VGSlider+i,col_var1] . " " . csv_header_data[col_var1,2] 
		GuiControl , Main:,VGValue2,% FileContents[VGSlider+i,col_var2] . " " . csv_header_data[col_var2,2] 
		GuiControl , Main:,VGValue3,% FileContents[VGSlider+i,col_var3] . " " . csv_header_data[col_var3,2] 
	}
	else
	{
		;show percentage
		GuiControl , Main:,VGValue1,% 100-val1 . " %"
		GuiControl , Main:,VGValue2,% 100-val2 . " %"	
		GuiControl , Main:,VGValue3,% 100-val3 . " %"	
	}
	
return




MainGuiDropFiles:
	;react to file dropped on script or in the case of program loading first time set up default environment
	Gui Main:Submit ,NoHide	
	GuiControl , Main:,VGDataPoint,Parsing File	
	if(A_GuiEvent == "")
	{
		; first load
		filename := last_filename
	}
	else
	{
		; we have a drag and drop
		filename := A_GuiEvent
	}
	row := 0
	column := 0
	FileContents := []
	max_min_arr := []
	csv_header_data := []
	dropdown_l1 := "|"
	dropdown_l2 := "|"
	dropdown_l3 := "|"
	csv_has_header := false
	
	first_column := true
	num_columns := 0
	;read first line to see if there is a header on the csv 
	FileReadLine, first_line, %filename%, 1
	
	;attempt to correct corrupt config file if corrupt csv given
	if((last_var1 == "")||(last_var2 == "" )||(last_var3 == ""))
	{
		last_var1 := 1
		last_var2 := 2
		last_var3 := 3
	}
	
	set_d1_default := false
	set_d2_default := false
	set_d3_default := false
	if first_line contains :
	{
		;the file has a header or a colon in the first row. hopefully it is a header
		csv_has_header := true
		Loop, parse, first_line,`,
		{
			;get header titles and units
			Loop,parse, A_LoopField,:
			{				
				csv_header_data[column,A_Index] :=  A_LoopField
			}
			column++
		}
		Loop % csv_header_data.MaxIndex() + 1 
		{
			;build new dropdowns with the new header info
			if(A_Index == last_var1+1)
			{
				dropdown_l1 := dropdown_l1 . "|"
				set_d1_default := true
			}
			else if(A_Index == last_var2+1)
			{
				dropdown_l2 := dropdown_l2 . "|"
				set_d2_default := true
			}
			else if(A_Index ==last_var3+1)
			{
				dropdown_l3 := dropdown_l3 . "|"
				set_d3_default := true				
			}
			dropdown_l1 := dropdown_l1 . csv_header_data[A_Index-1,1] . "|"
			dropdown_l2 := dropdown_l2 . csv_header_data[A_Index-1,1] . "|"
			dropdown_l3 := dropdown_l3 . csv_header_data[A_Index-1,1] . "|"
		}
		;special code if user last used the last item in drop down
		if(!set_d1_default)
		{
			dropdown_l1 := dropdown_l1 . "|"
			set_d1_default := true
		}
		if(!set_d2_default)
		{
			dropdown_l2 := dropdown_l2 . "|"
			set_d2_default := true
		}
		if(!set_d3_default)
		{
			dropdown_l3 := dropdown_l3 . "|"
			set_d3_default := true
		}
	}
	else
	{
		;the doesnt have a header or a colon in the first row
		csv_has_header := false
		Loop, parse, first_line,`,
		{
			;build new dropdowns with the new header info
			if(A_Index == last_var1+1)
			{
				dropdown_l1 := dropdown_l1 . "|"
				set_d1_default := true
			}
			else if(A_Index == last_var2+1)
			{
				dropdown_l2 := dropdown_l2 . "|"
				set_d2_default := true
			}
			else if(A_Index ==last_var3+1)
			{
				dropdown_l3 := dropdown_l3 . "|"
				set_d3_default := true					
			}
			csv_header_data[A_Index-1,1] := "VAR" . A_Index
			dropdown_l1 := dropdown_l1 . "VAR" . A_Index . "|"
			dropdown_l2 := dropdown_l2 . "VAR" . A_Index . "|"
			dropdown_l3 := dropdown_l3 . "VAR" . A_Index . "|"
			column++
		}
		;special code if user last used the last item in drop down
		if(!set_d1_default)
		{
			dropdown_l1 := dropdown_l1 . "|"
			set_d1_default := true
		}
		if(!set_d2_default)
		{
			dropdown_l2 := dropdown_l2 . "|"
			set_d2_default := true
		}
		if(!set_d3_default)
		{
			dropdown_l3 := dropdown_l3 . "|"
			set_d3_default := true
		}
	}
	
	;read csv file and store in memory
	
	if(csv_has_header)
	{
		row := -1
	}
	else
	{
		row := 0
	}
	column := 0	
	
	Loop, read, %filename%
	{
		Loop, parse, A_LoopReadLine,`,
		{
			if(row != -1)
			{
				;get initial values into min max array from first
				max_min_arr[0,column] := A_LoopField
				max_min_arr[1,column] := A_LoopField
				column++
			}
		}		
		if(row == 0)
		{
			num_columns := column
			;get outta this loop
			break
		}
		row++
		column := 0
	}
	
	if(csv_has_header)
	{
		row := -1
	}
	else
	{
		row := 0
	}
	column := 0
	
	Loop, read, %filename%
	{		
		Loop, parse, A_LoopReadLine,`,
		{	
			if(row != -1)
			{
				FileContents[row,column] := A_LoopField  
				;read file and load into memory. At the same time  check against the min and max array we just initialized and update
				;if we find a value that is more extreme
				
				if((FileContents[row,column] >= max_min_arr[0,column])&&(FileContents[row,column] != "-")&&(FileContents[row,column] != ""))
				{
					;found a max
					max_min_arr[0,column] := FileContents[row,column] 
				}
				if((FileContents[row,column] <= max_min_arr[1,column])&&(FileContents[row,column] != "-")&&(FileContents[row,column] != ""))
				{
					;found a min
					max_min_arr[1,column] := FileContents[row,column] 
				}
				column++
			}
		}
		row++			
		column := 0			
	}
	
	;update the gui to show how many data points we found
	GuiControl , Main:,VGDataPoint,%row% 
	
	Loop %num_columns%   
	{
		;calculate the scale factors required to make all the data points fit in the graphs for each channel
		difference := abs(max_min_arr[0,A_Index -1]-max_min_arr[1,A_Index -1])
		factor := 100/difference		
		max_min_arr[2,A_Index -1] := abs(factor) ;save the factor in the array
		maxa := max_min_arr[1,A_Index -1]
		mina := max_min_arr[0,A_Index -1]
		;msgbox column number %array_index%`n`nmin value %mina%`n`nmax value %maxa%`n`nmultiplication factor %factor%`n`ndifference %difference%
	}
	
	;msgbox max = %maxa%`n`nmin = %mina%
	rowtst:=row-150 ;need this or end data at end of file graphs like poo
	
	;update the range of the slider to match the number of data points we just found 
	GuiControl, Main: +Range0-%rowtst%, VGSlider	
	
	
	if(filename != "")
	{
		;you came back for more. use previous data to update gui 
		GuiControl , Main:,VGItem1,%dropdown_l1% 
		GuiControl , Main:,VGItem2,%dropdown_l2% 
		GuiControl , Main:,VGItem3,%dropdown_l3% 	
		Gui Main:Submit ,NoHide	
		GuiControl , Main:,VGVar1Label,% csv_header_data[VGItem1-1,1] . ":"
		GuiControl , Main:,VGVar2Label,% csv_header_data[VGItem2-1,1] . ":"
		GuiControl , Main:,VGVar3Label,% csv_header_data[VGItem3-1,1] . ":"		
		GoSub GSlider
	}
	else
	{
		;this is your first time. give some directions
		GuiControl , Main:,VGItem1,|Drag And Drop|| 
		GuiControl , Main:,VGItem2,|CSV File On GUI||
		GuiControl , Main:,VGItem3,|To Begin Analysis||
	}
return


MainGuiClose:	
	;update the ini file on close
	Gui Main:Submit ,NoHide	
	
	IniWrite  %filename% , Data_Visualizer.ini , coltwashere ,last_filename
	IniWrite  %VGItem1%, Data_Visualizer.ini , coltwashere ,last_var1
	IniWrite  %VGItem2%, Data_Visualizer.ini , coltwashere ,last_var2
	IniWrite  %VGItem3%, Data_Visualizer.ini , coltwashere ,last_var3
	IniWrite  %VGValPer% , Data_Visualizer.ini , coltwashere ,values_percent
	IniWrite  %VGScale% , Data_Visualizer.ini , coltwashere ,all_graph_same_scale
	exitapp
	return


^r::
	;update ini file on reload of script
	Gui Main:Submit ,NoHide	
	IniWrite  %filename% , Data_Visualizer.ini , coltwashere ,last_filename
	IniWrite  %VGItem1%, Data_Visualizer.ini , coltwashere ,last_var1
	IniWrite  %VGItem2%, Data_Visualizer.ini , coltwashere ,last_var2
	IniWrite  %VGItem3%, Data_Visualizer.ini , coltwashere ,last_var3
	IniWrite  %VGValPer% , Data_Visualizer.ini , coltwashere ,values_percent
	IniWrite  %VGScale% , Data_Visualizer.ini , coltwashere ,all_graph_same_scale	
	reload
	return


SnowFlake_FlowSnake
  • Members
  • 845 posts
  • Last active: Jan 24 2016 05:24 PM
  • Joined: 08 Oct 2012

Ohhhhhhh this program script might come very useful to me thanks for sharing bro!


  • Download link of my scripts on Autohotkey.com 2/10/2015 [DOWNLAND]
  • Contact Info:  https://github.com/floowsnaake //  FloowSnaake(A)gmail.com
  • IF you need Help send me a PM,Email or Post on Github

  • Quote by tank  Posted 29 September 2015 - 06:14 PM

  • "Eventually i will find a way to convert the DB back to PHPBB3. but i dont have the bandwidth right now. No one that has tried has had success. It is the Only way i can keep this open is if i could successfully convert it."

Guest10
  • Members
  • 1216 posts
  • Last active: Oct 30 2015 05:12 PM
  • Joined: 27 Oct 2012

can all of this be done in Excel?



carson32
  • Members
  • 2 posts
  • Last active: Jun 17 2016 05:34 PM
  • Joined: 22 Jan 2013

Ohhhhhhh this program script might come very useful to me thanks for sharing bro!

 

Hopefully it helps you out in some way. I figured it was finally time to give something back to the forums after I have been using them for so many years.

 

can all of this be done in Excel?

 

It probably can. I know you can plot scatter plots of data in excel and the graphs will look like the one this program creates. Where I think this program is useful is when you only care about looking at a small window of a very large data set. Right now this program only looks at 150 data points. With the slider you pick which 150 you want to look at.

 

I am sure you could use the developer tools to create a similar interface within excel, but it would involve some programming to get it to work. I just chose to program with autohotkey instead of VBA. 

 

Also, I am curious, did you try out the program?



Guest10
  • Members
  • 1216 posts
  • Last active: Oct 30 2015 05:12 PM
  • Joined: 27 Oct 2012

Thanks, haven't tried as of yet, but is on my list.