Cleaning dirty string variables

Cleaning dirty string variables

by Juno -

Hi!

I have a very dirty data set I'm trying to clean and I'm a bit stuck. The variable for height "ht" is entered in many different ways both as inches and centimeters, with units listed in different ways.

For example:

156c

157 c

158.2 cm

62i

63.5 in

I'm having trouble figuring out a systematic way to clean this. As I see it I need to a) remove the internal spaces between the numeric values and the unit descriptions so that all values are uniform. Then I need to split the units from the numeric values. Then convert all the values into a standard a standard units i.e. inches --> cm. Aside from knowing how NOT to design data entry for the future, I'm stuck on how to manipulate this data in stata. I tried splitting, but because the spaces between the units are variable (either 0, 1, 2) the split was not useful. I tried permutations of the "itrim" command as listed on various websites, but state didn't recognize it...so a bit stuck. I also tried thinking about coming from the end of the expression using the regexs /regexm commands to list $cm but got nowhere. 

Thanks for any guidance!

Juno

 

Re: Cleaning dirty string variables

by Maria Chao -

Hi Juno, 

You’re definitely on the right track: first separate the alpha and the numeric in your string variable. Then convert all values into the same metric.

For step 1, Stata is ‘smart’ in the sense that you can tell it to look for numbers or for characters without worrying about spaces in your string variable. To extract the number from your string variable, you can create a numeric variable based on your string variable by using the destring command with the generate and ignore options. It would look something like:

destring var, generate (newvar1) ignore (“chars”)

“var” is the name of the original string variable; “newvar1” is the name you want to give your new numeric variable. In the parentheses after ignore, you want to specify any characters you want Stata to ignore. So in your case ignore (c cm i in)

You can then create a variable that specifies the unit of measurement. You can do this by telling Stata to generate a new variable and assign it a specific value, say 1, whenever “c” is in your original string variable [Note that specifying “c” will also include “cm”]. Then replacing your new variable with another value, say 2, anytime your original string variable has an “i” [Note that specifying “i” will also include “in”]. The "regexm" tells Stata to match for what's specified in the parentheses.

generate newvar2 =1 if(regexm(var, "c"))

replace newvar2 =2 if(regexm(var, "i"))

You can then label your values and variables and move on to converting. 

Hope that helps!

Maria