Stata cheat sheet: data transformation
3 likes515 views
Stata desk reference cheat sheet on data transformations and munging operations. Co-authored with Laura Hughes. Udpated 2016/03/10.
![Tim Essam (tessam@usaid.gov) • Laura Hughes (lhughes@usaid.gov) inspired by RStudio’s awesome Cheat Sheets (rstudio.com/resources/cheatsheets) geocenter.github.io/StataTraining updated March 2016
Disclaimer: we are not affiliated with Stata. But we like it. CC BY NC
Data Transformation
with Stata 14.1 Cheat Sheet
For more info see Stata’s reference manual (stata.com)
export delimited "myData.csv", delimiter(",") replace
export data as a comma-delimited file (.csv)
export excel "myData.xls", /*
*/ firstrow(variables) replace
export data as an Excel file (.xls) with the
variable names as the first row
Save & Export Data
save "myData.dta", replace
saveold "myData.dta", replace version(12)
save data in Stata format, replacing the data if
a file with same name exists
Stata 12-compatible file
Manipulate Strings
display trim(" leading / trailing spaces ")
remove extra spaces before and after a string
display regexr("My string", "My", "Your")
replace string1 ("My") with string2 ("Your")
display stritrim(" Too much Space")
replace consecutive spaces with a single space
display strtoname("1Var name")
convert string to Stata-compatible variable name
TRANSFORM STRINGS
display strlower("STATA should not be ALL-CAPS")
change string case; see also strupper, strproper
display strmatch("123.89", "1??.?9")
return true (1) or false (0) if string matches pattern
list make if regexm(make, "[0-9]")
list observations where make matches the regular
expression (here, records that contain a number)
FIND MATCHING STRINGS
GET STRING PROPERTIES
list if regexm(make, "(Cad.|Chev.|Datsun)")
return all observations where make contains
"Cad.", "Chev." or "Datsun"
list if inlist(word(make, 1), "Cad.", "Chev.", "Datsun")
return all observations where the first word of the
make variable contains the listed words
compare the given list against the first word in make
charlist make
display the set of unique characters within a string
* user-defined package
replace make = subinstr(make, "Cad.", "Cadillac", 1)
replace first occurrence of "Cad." with Cadillac
in the make variable
display length("This string has 29 characters")
return the length of the string
display substr("Stata", 3, 5)
return the string located between characters 3-5
display strpos("Stata", "a")
return the position in Stata where a is first found
display real("100")
convert string to a numeric or missing value
_merge code
row only
in ind2
row only
in hh2
row in
both
1
(master)
2
(using)
3
(match)
Combine Data
ADDING (APPENDING) NEW DATA
MERGING TWO DATASETS TOGETHER
FUZZY MATCHING: COMBINING TWO DATASETS WITHOUT A COMMON ID
merge 1:1 id using "ind_age.dta"
one-to-one merge of "ind_age.dta"
into the loaded dataset and create
variable "_merge" to track the origin
webuse ind_age.dta, clear
save ind_age.dta, replace
webuse ind_ag.dta, clear
merge m:1 hid using "hh2.dta"
many-to-one merge of "hh2.dta"
into the loaded dataset and create
variable "_merge" to track the origin
webuse hh2.dta, clear
save hh2.dta, replace
webuse ind2.dta, clear
append using "coffeeMaize2.dta", gen(filenum)
add observations from "coffeeMaize2.dta" to
current data and create variable "filenum" to
track the origin of each observation
webuse coffeeMaize2.dta, clear
save coffeeMaize2.dta, replace
webuse coffeeMaize.dta, clear
load demo dataid blue pink
+
id blue pink
id blue pink
should
contain
the same
variables
(columns)
MANY-TO-ONE
id blue pink id brown blue pink brown _merge
3
3
1
3
2
1
3
. .
.
.
id
+ =
ONE-TO-ONE
id blue pink id brown blue pink brownid _merge
3
3
3
+ =
must contain a
common variable
(id)
match records from different data sets using probabilistic matchingreclink
create distance measure for similarity between two strings
ssc install reclink
ssc install jarowinklerjarowinkler
Reshape Data
webuse set https://github.com/GeoCenter/StataTraining/raw/master/Day2/Data
webuse "coffeeMaize.dta" load demo dataset
xpose, clear varname
transpose rows and columns of data, clearing the data and saving
old column names as a new variable called "_varname"
MELT DATA (WIDE → LONG)
reshape long coffee@ maize@, i(country) j(year)
convert a wide dataset to long
reshape variables starting
with coffee and maize
unique id
variable (key)
create new variable which captures
the info in the column names
CAST DATA (LONG → WIDE)
reshape wide coffee maize, i(country) j(year)
convert a long dataset to wide
create new variables named
coffee2011, maize2012...
what will be
unique id
variable (key)
create new variables
with the year added
to the column name
When datasets are
tidy, they have a
c o n s i s t e n t ,
standard format
that is easier to
manipulate and
analyze.
country
coffee
2011
coffee
2012
maize
2011
maize
2012
Malawi
Rwanda
Uganda cast
melt
Rwanda
Uganda
Malawi
Malawi
Rwanda
Uganda 2012
2011
2011
2012
2011
2012
year coffee maizecountry
WIDE LONG (TIDY) TIDY DATASETS have
each observation
in its own row and
each variable in its
own column.
new variable
Label Data
label list
list all labels within the dataset
label define myLabel 0 "US" 1 "Not US"
label values foreign myLabel
define a label and apply it the values in foreign
Value labels map string descriptions to numers. They allow the
underlying data to be numeric (making logical tests simpler)
while also connecting the values to human-understandable text.
Replace Parts of Data
rename (rep78 foreign) (repairRecord carType)
rename one or multiple variables
CHANGE COLUMN NAMES
recode price (0 / 5000 = 5000)
change all prices less than 5000 to be $5,000
recode foreign (0 = 2 "US")(1 = 1 "Not US"), gen(foreign2)
change the values and value labels then store in a new
variable, foreign2
CHANGE ROW VALUES
useful for exporting datamvencode _all, mv(9999)
replace missing values with the number 9999 for all variables
mvdecode _all, mv(9999)
replace the number 9999 with missing value in all variables
useful for cleaning survey datasets
REPLACE MISSING VALUES
replace price = 5000 if price < 5000
replace all values of price that are less than $5,000 with 5000
Select Parts of Data (Subsetting)
FILTER SPECIFIC ROWS
drop in 1/4drop if mpg < 20
drop observations based on a condition (left)
or rows 1-4 (right)
keep in 1/30
opposite of drop; keep only rows 1-30
keep if inlist(make, "Honda Accord", "Honda Civic", "Subaru")
keep the specified values of make
keep if inrange(price, 5000, 10000)
keep values of price between $5,000 – $10,000 (inclusive)
sample 25
sample 25% of the observations in the dataset
(use set seed # command for reproducible sampling)
SELECT SPECIFIC COLUMNS
drop make
remove the 'make' variable
keep make price
opposite of drop; keep only columns 'make' and 'price'](https://image.slidesharecdn.com/statacheatsheets-datatransformation-160310175618/85/Stata-cheat-sheet-data-transformation-1-320.jpg)
More Related Content
What's hot (19)
Similar to Stata cheat sheet: data transformation (20)
Recently uploaded (20)
Stata cheat sheet: data transformation
- 1. Tim Essam (tessam@usaid.gov) • Laura Hughes (lhughes@usaid.gov) inspired by RStudio’s awesome Cheat Sheets (rstudio.com/resources/cheatsheets) geocenter.github.io/StataTraining updated March 2016 Disclaimer: we are not affiliated with Stata. But we like it. CC BY NC Data Transformation with Stata 14.1 Cheat Sheet For more info see Stata’s reference manual (stata.com) export delimited "myData.csv", delimiter(",") replace export data as a comma-delimited file (.csv) export excel "myData.xls", /* */ firstrow(variables) replace export data as an Excel file (.xls) with the variable names as the first row Save & Export Data save "myData.dta", replace saveold "myData.dta", replace version(12) save data in Stata format, replacing the data if a file with same name exists Stata 12-compatible file Manipulate Strings display trim(" leading / trailing spaces ") remove extra spaces before and after a string display regexr("My string", "My", "Your") replace string1 ("My") with string2 ("Your") display stritrim(" Too much Space") replace consecutive spaces with a single space display strtoname("1Var name") convert string to Stata-compatible variable name TRANSFORM STRINGS display strlower("STATA should not be ALL-CAPS") change string case; see also strupper, strproper display strmatch("123.89", "1??.?9") return true (1) or false (0) if string matches pattern list make if regexm(make, "[0-9]") list observations where make matches the regular expression (here, records that contain a number) FIND MATCHING STRINGS GET STRING PROPERTIES list if regexm(make, "(Cad.|Chev.|Datsun)") return all observations where make contains "Cad.", "Chev." or "Datsun" list if inlist(word(make, 1), "Cad.", "Chev.", "Datsun") return all observations where the first word of the make variable contains the listed words compare the given list against the first word in make charlist make display the set of unique characters within a string * user-defined package replace make = subinstr(make, "Cad.", "Cadillac", 1) replace first occurrence of "Cad." with Cadillac in the make variable display length("This string has 29 characters") return the length of the string display substr("Stata", 3, 5) return the string located between characters 3-5 display strpos("Stata", "a") return the position in Stata where a is first found display real("100") convert string to a numeric or missing value _merge code row only in ind2 row only in hh2 row in both 1 (master) 2 (using) 3 (match) Combine Data ADDING (APPENDING) NEW DATA MERGING TWO DATASETS TOGETHER FUZZY MATCHING: COMBINING TWO DATASETS WITHOUT A COMMON ID merge 1:1 id using "ind_age.dta" one-to-one merge of "ind_age.dta" into the loaded dataset and create variable "_merge" to track the origin webuse ind_age.dta, clear save ind_age.dta, replace webuse ind_ag.dta, clear merge m:1 hid using "hh2.dta" many-to-one merge of "hh2.dta" into the loaded dataset and create variable "_merge" to track the origin webuse hh2.dta, clear save hh2.dta, replace webuse ind2.dta, clear append using "coffeeMaize2.dta", gen(filenum) add observations from "coffeeMaize2.dta" to current data and create variable "filenum" to track the origin of each observation webuse coffeeMaize2.dta, clear save coffeeMaize2.dta, replace webuse coffeeMaize.dta, clear load demo dataid blue pink + id blue pink id blue pink should contain the same variables (columns) MANY-TO-ONE id blue pink id brown blue pink brown _merge 3 3 1 3 2 1 3 . . . . id + = ONE-TO-ONE id blue pink id brown blue pink brownid _merge 3 3 3 + = must contain a common variable (id) match records from different data sets using probabilistic matchingreclink create distance measure for similarity between two strings ssc install reclink ssc install jarowinklerjarowinkler Reshape Data webuse set https://github.com/GeoCenter/StataTraining/raw/master/Day2/Data webuse "coffeeMaize.dta" load demo dataset xpose, clear varname transpose rows and columns of data, clearing the data and saving old column names as a new variable called "_varname" MELT DATA (WIDE → LONG) reshape long coffee@ maize@, i(country) j(year) convert a wide dataset to long reshape variables starting with coffee and maize unique id variable (key) create new variable which captures the info in the column names CAST DATA (LONG → WIDE) reshape wide coffee maize, i(country) j(year) convert a long dataset to wide create new variables named coffee2011, maize2012... what will be unique id variable (key) create new variables with the year added to the column name When datasets are tidy, they have a c o n s i s t e n t , standard format that is easier to manipulate and analyze. country coffee 2011 coffee 2012 maize 2011 maize 2012 Malawi Rwanda Uganda cast melt Rwanda Uganda Malawi Malawi Rwanda Uganda 2012 2011 2011 2012 2011 2012 year coffee maizecountry WIDE LONG (TIDY) TIDY DATASETS have each observation in its own row and each variable in its own column. new variable Label Data label list list all labels within the dataset label define myLabel 0 "US" 1 "Not US" label values foreign myLabel define a label and apply it the values in foreign Value labels map string descriptions to numers. They allow the underlying data to be numeric (making logical tests simpler) while also connecting the values to human-understandable text. Replace Parts of Data rename (rep78 foreign) (repairRecord carType) rename one or multiple variables CHANGE COLUMN NAMES recode price (0 / 5000 = 5000) change all prices less than 5000 to be $5,000 recode foreign (0 = 2 "US")(1 = 1 "Not US"), gen(foreign2) change the values and value labels then store in a new variable, foreign2 CHANGE ROW VALUES useful for exporting datamvencode _all, mv(9999) replace missing values with the number 9999 for all variables mvdecode _all, mv(9999) replace the number 9999 with missing value in all variables useful for cleaning survey datasets REPLACE MISSING VALUES replace price = 5000 if price < 5000 replace all values of price that are less than $5,000 with 5000 Select Parts of Data (Subsetting) FILTER SPECIFIC ROWS drop in 1/4drop if mpg < 20 drop observations based on a condition (left) or rows 1-4 (right) keep in 1/30 opposite of drop; keep only rows 1-30 keep if inlist(make, "Honda Accord", "Honda Civic", "Subaru") keep the specified values of make keep if inrange(price, 5000, 10000) keep values of price between $5,000 – $10,000 (inclusive) sample 25 sample 25% of the observations in the dataset (use set seed # command for reproducible sampling) SELECT SPECIFIC COLUMNS drop make remove the 'make' variable keep make price opposite of drop; keep only columns 'make' and 'price'