overlay plots over pictures (such as photo, diagram or maps)

Sometime we are need of overlaying the data points or bars over different types of plots. For this we need coordinates of the data points where we want to plot the data points. There are different image analysis software that can do so. Here is use of WebPlotDigitizer online free tool to do so using a leaf anatomy plot.

Examples:

(1) Plot a dummy plot. Here we are setting maximum and minimum co-ordinates to 200 in X and Y.

(2) Insert desired plot in plot area.

(3)  Now we need print and scan or take snip of the plot and save file as jpeg.

(3) Find cordinates to points in the plot  to be plotted using the image analysis software WebPlotDigitizer  :
 First load the file in the web application.

Click the 0, 200 in X-axis and 0,200 in Y axis before proceed.

Now click the point in figure where you want the data to be plotted. Once you are done you can request a csv output. See purple points are selected data points. You can cross check if it is working properly by clicking the known data points (Yellow diamonds in the following figure).

Now copy and paste in file with CVS extension (as suggested in the window) and open then paste in excel sheet.
The co-ordinates of plotting data points can found by simply guessing (for coarse work) in the plot or using some short of tracing act over printed plot. For maps (such as google maps we can find the co-ordinates of the data points)

(4)  Npw we can simply plot different types of plots - connect lines, connected lines with error bars or bubble plots over the figure. To avoid misalignment if you change any features of the data, the range of the data mush be refreshed. We need to remove the data plotted by previous dummy data set and add new series.

See the trick of plotting 4 extra points in right corner (not in anatomy area) are meant for plotting legend scales. Now the following line connected plot with error bars. Off-course we can just plot the error bars corresponding a value we want to plot.

Creating bubble plot: We can create bubble plot in similar way, which may be more useful where the size of bubble is scaled to certain factor.

A transparent version:

We can similarly overlay over map in the similar way:

Here is example with plotting in chromosomes using same trick:

We can combined with other type of plot used as picture, for example here we used add bars to the dendogram.

Just for fun, we can overlay with other type of plot in background (which is not possible in current excel) - just bars in 3D pie - 

Important Note: The scale limit in X and Y axis must be same as used while finding coordinates  otherwise the plot points will misaligned. 

create line plot with error bar plot in excel

XY Error bar plot:

Adding error bars to XY plot is simple task in excel. First we need to plot XY scatter plot with subtype scatter with data points connected by lines without markers.

(2) The format series and find Y error bars to be added (we can also add X error bars in the same way). You can specify different types of errors (fixed such as standard deviation or fixed value or percentage of the value) or user specified in error column (for example SE in above case).

comparision of multiple histograms or density plots non-overlayed in in single plot in Excell

Sometime we might be interested to compare the distributions (using histogram or line plot), which are not overlayed but a series of subplot within a single plot. The following section discuss the tricks:

(1) First of all we calculate frequency for each series of data and store arranged in a table like above. Now we calculate density by dividing Frequency by total number (summation of frequncy in each column). Also calculate maximum density so that w can use this to weigh all density and calculate weighing factor that fits in the plot area. Also we need to provide a number where the Y series will be plotted. Here we have considered 1, 2,3, 4 so that the histograms will be based on 1, 2,3, 4 lines in Y axis.

(4)  Now we need weigh the density by constant factor so that they fit in 1 heigh interval in Y axis.
Here I used the formula =(0.9*(S2/$AF$3)), for example so that maximum value will be 0.9 so that there will a gap of 0.1 between Y1 and Y2, Y2 and Y3 or similarly other consequitive series plot. You can scale to 1 too but the maximum bars will then touch to another series.

 



(5) Now we can add XY scatter plot. Just create pseudo series with any two data series, we will modify it by secificing proper range later.

(6) Now we start editing source data. Here X value for bin range and Y is constant number where the series withing plot need to plotted (i.e. 1, 2,3,4 in our case).

Now just point plot will look like the following:

 (7) Now add the error bar to each point. Add plus error bars with custom series where height is weighted density. You want to modify the error bars without having cap.

(8) Now we can add the line to plot by adding new data series. But we need a trick, now we need to add the Y position (1,2,3,4) to the wieghted density calculated. Then you get points and convert the points to lines type XY plot.

(9) You can remove the bars if you want to by editing error bar setting to no error bars.

(10) If want to overlay one or more series (for example Y1 and Y2 in same pair, Y3 and Y4 in another ), you can change the value of y axis plotting values. For example Y1 and Y2 was set to 1 and Y3 and Y4 was set to 3 in the following plot.



parallel (dot plot) plot in Excel

In this type of plot (in contrast to XY plot) one axis consists of categorical value while another axis consists of quantative value. The points are usually presented or points are sometime connected with line.

Add line plot with points. See the data arrangement trick to creat a gap in three different group coulmn.



You can add the horizontal lines by clicking drop lines under options menu. Also we can add table to show the actual values against the X axis labels.



We can format to remove lines in table if you think it makes the plot crouded.

If there are not alot of points, you can change data point colors, shapes etc so that they represent group they belong to.  You can remove the data points and plot just lines connecting the points or not removing lines in first place (thus there will be both points and lines connecting points). Of course we can add more than one series by simply adding data series to plots.