All posts by hs

Easy way to perform a lookup in R

In order to make your data meaningful  you have to enrich your data from values supplied by lookup table.

eg. If you have got the POSTCODE in your data and you would like to replace the POSTCODE with the  SUBURB


Here is the easiest code which you can use to perform such lookups.

In this example  we will perform a lookup and then store the new value in a new column for the sake of comparison and testing.

Let us assume that you have a dataframe which has the names and the GenderCodes for each name
You would want to make your data useful by adding the actual gender names from a lookup table

# Create a dataframe containing your data
# You would want to replace the GenderCode in this table with the GenderNames from your lookup table
Name <- c("John", "David", "Angela", "Harry", "Christine")
GenderCode <- c(1,1,2,1,2)

MyData <- data.frame (Name,GenderCode)

# Created your lookup table
# Your lookup table has the codesand the actual value which you would want to use
GCode  <- c(1,2)
GenderName <- c("Male","Female")

MyLookupTable <- data.frame(GCode,GenderName)

# Perform alookup and add a new column in your datatable which will have the values pulled in from your lookup table
MyData$GenderName = MyLookupTable[match(MyData$GenderCode, MyLookupTable$GCode), "GenderName"] 



After you run the code you end with the following data.
Notice that a new column GenderName has been added and it has the actual gender names corresponding to the GenderCodes in your original data.

Using Lookup tables in R








How to count in R

Here is an easy way to count in R

In normal SQL you can easily do the following to get the count of a particular item

eg. Select count(*) from myTable group by FacilityName”
this statement in SQL will give you the count of the records belonging to each FacilityName.

Here is the R equivalent code

I prefer to use a package called plyr
Let us use the built in dataset in r called as mtcars


It will give you the count as shown below.
gear freq
1 3 15
2 4 12
3 5 5

How to create the Bell Curve in EXCEL

To create a Bell Curve represent a normal distribution also known as a Gaussian distribution.

To do that you need four pieces of information

1. Data for which you want to plot the bell curve.
2. Normal Distribution
3. Average
4. Standard Distribution


Create Bell Curve in Excel



Put the  following labels  in column A,B,C and D

Data    Distribution  Average   Standard Deviations in the 4 columns as shown in the screenshot


Use the formula for calculating the Average as shown below. Remember that the average is just one value we simply copy it to all the cells in Column C for convenience.


07-May-16 5-51-02 PM


Step 3

Calculate the standard Deviation in Column D

use the formula for calculating the Standard Deviation as shown below. Remember that the Standard Deviation is just one value, and we simply copy it to all the cells in column D for convenience

07-May-16 10-24-51 PM


Step 4

Now we will calculate the Normal Distribution in column B and we would be using the values in column C(Average) and in column D(Standard Deviation) . Copy the formula to all the cells in B.


07-May-16 10-28-20 PM


Step 5

Now we have all four columns with required information

Column A – has the actual data for which we want to draw the graph, eg. weight of patience, price of a product

Column B – Normal Distribution values for each cell

Column C – has the average value  copied to all the cells in column C

Column D – has the Standard Deviation value copied to all the cells in column D


Draw the graph by selecting all the values in column B ( Normal Distribution values )

07-May-16 10-35-04 PM



We have our bell shaped curve ready .


Multiple IFs in EXCEL

How to use multiple IF conditions in MS EXCEL


A typical IF condition is as below

=if(logical_test, Value_If_True, Value_If_False)


=  IF(A1=1,”label for True condition”,”Label for False Condition”)

eg .IF(A1=1,”YES”,”NO”)  – this will show “YES” if the contents of cell A1 is 1  otherwise it will show NO in the cell where you put this formula.


Now look at the more complicated scenario as shown below.

Consider the following conditions, if the option is 1, 2 or 3 then you would like to display “YES” and if it is not in 1,2, or 3 then you would want to display a “NO”

You can achieve it by using nested or multiple IF conditions.


you can use a formula like  the following


Basically you are replacing the “No” or the FALSE portion of the formula and extending it by giving another IF condition.

So  if B2 =1 then show YES, if B2  <> 1 then try to evaluate if B2 =2 , if B2 is 2 then show a YES, but if B2  is not 2 then try to evaluate if B2 =3 , if B2 is 3 then show YES otherwise show NO.

You can keep on nesting multiple IFs by replacing the “FALSE” portion of the IF command and then putting another IF condition instead.






Populate DataGridView with MongoDB Data using Entity in c#

How to populate a DataGridView in Winforms application using C# and data from MongoDB database

Assuming that you already have a model mapped to a collection in MongoDB.

You would like to populate the data in a DataGridView in c#.

We have a model called QuestionBank


We will fetch all the records from Mongodb collection using the Filter.Empty




Here is the code

   var documents =  db.QuestionBanks.Find(Builders<QuestionBank>.Filter.Empty).ToListAsync();
   this.dataGridView1.DataSource = documents.Result;



There is absolutely no need to do any loops to achieve it.




[C# and VB.Net] How to close a form after confirmation in Winforms

How to close a form in c#  after confirming



You can put the following code in any button


          if (MessageBox.Show("Are you sure you want to exit?", "Close Application", MessageBoxButtons.YesNo) == DialogResult.Yes)


In VB.NET you can use the following code


 If MsgBox("Are you sure you want to close", MsgBoxStyle.YesNo, "Close Application") = MsgBoxResult.Yes Then

        End If

c# – new GUID() returns 0

GUID stands for globally unique identifier.

GUIDs are usually stored as 128-bit values, and are commonly displayed as 32 hexadecimal digits with groups separated by hyphens.

Here is an example {21EC2020-3AEA-4069-A2DD-08002B30309D}.

They may or may not be generated from random (or pseudo-random) numbers.

GUIDs generated from random numbers normally contain 6 fixed bits (these indicate that the GUID is random) and 122 random bits; the total number of unique such GUIDs is 222 (approximately 5.3×1036).

The probability of the same number being generated randomly twice is negligible;

Assuming uniform probability for simplicity, the probability of one duplicate would be about 50% if every person on earth as of 2014 owned 600 million GUIDs.





Do not use this way as it will only return with all zeros as shown below.

Guid  g1 = new Guid();       // g1 will return 00000000-0000-0000-0000-000000000000




Here is the correct way
Guid g1 = Guid.NewGuid();  //   g1 will return a proper GUID like {21EC2020-3AEA-4069-A2DD-08002B30309D}



Could not load file or assembly ‘System.Web.Http.WebHost, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35′ or one of its dependencies. The system cannot find the file specified.

Could not load file or assembly ‘System.Web.Http.WebHost, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35′ or one of its dependencies. The system cannot find the file specified.



I had a Visual Studio 2013 project using MVC4. The application was working fine in my local machine. But when I deployed the application using FileSystem( in this method all the files for the project gets copied to a folder on your local machine, then you can copy all these files to your webhost and your application should start working.)


I got the following error when I deployed the application using this method.

Could not load file or assembly ‘System.Web.Http.WebHost, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35′ or one of its dependencies. The system cannot find the file specified.


How to Fix it?

I searched on the web for  some time to get the answer and in the end I discovered that the System.Web.HTTP.WebHost.DLL file was not being copied to my published folder and hence the error.

1. Go to the solution Explorer in Visual Studio

2. Go to the References

3. Right Mouse Click on the System.Web.HTTP.WebHost entry  to check its prooperties and I found that COPY LOCAL proeprty was set to false and hence the file was not being copied to my published folder.

4. I changed the COPY LOCAL to true and then the file gets copied to  my published folder and gets deployed correctly.