Hi everyone!
I am currently trying stata for the first time for my undergraduate dissertation, would really appreciate your help!
I downloaded data on graduate outcomes and their data was only available in csv format and separated into multiple documents. I would need to merge data from 5-6 csv files to one stata file in order to use variables across the 5-6 files. I am facing two main problems:
1. When data is imported, stata counts each column as a frequency rather than accounting for the number in the "number" variable. For example tab domicile England gives 38,190 when it is actually the number of time that England has been mentioned in the columns, rather than the number of England added up according to the "number" column.
2. Each of these csv files contain varying number of variables as they observe different things, when imported to stata the number does not match. Furthermore, each file come with a variable "number" in disregard of variables in the other files, it is okay to paste the data from a new file in the colulmn to the right of the pre-existing ones? (See the last graph)
In general I am quite confused about how to reorganise the data for stata to show the correct number of people who is in "employment and further study", is in "England", and has "IMD".
Not sure if I managed to explain the situation clearly. Any advice and suggestions is appreciated! Thanks!
tab domicile
Domicile | Freq. Percent Cum.
---------------------+-----------------------------------
England | 38,190 10.43 10.43
Non-European Union | 33,420 9.13 19.56
Northern Ireland | 34,830 9.51 29.07
Not known | 9,030 2.47 31.53
Other European Union | 35,580 9.72 41.25
Other UK | 28,620 7.82 49.07
Scotland | 32,460 8.86 57.93
Total | 41,010 11.20 69.13
Total UK | 41,010 11.20 80.33
Total non-UK | 35,940 9.81 90.14
Wales | 36,090 9.86 100.00
---------------------+-----------------------------------
Total | 366,180 100.00
I am currently trying stata for the first time for my undergraduate dissertation, would really appreciate your help!
I downloaded data on graduate outcomes and their data was only available in csv format and separated into multiple documents. I would need to merge data from 5-6 csv files to one stata file in order to use variables across the 5-6 files. I am facing two main problems:
1. When data is imported, stata counts each column as a frequency rather than accounting for the number in the "number" variable. For example tab domicile England gives 38,190 when it is actually the number of time that England has been mentioned in the columns, rather than the number of England added up according to the "number" column.
2. Each of these csv files contain varying number of variables as they observe different things, when imported to stata the number does not match. Furthermore, each file come with a variable "number" in disregard of variables in the other files, it is okay to paste the data from a new file in the colulmn to the right of the pre-existing ones? (See the last graph)
In general I am quite confused about how to reorganise the data for stata to show the correct number of people who is in "employment and further study", is in "England", and has "IMD".
Not sure if I managed to explain the situation clearly. Any advice and suggestions is appreciated! Thanks!
Code:
* Example generated by -dataex-. For more info, type help dataex clear input str53 activity str20 domicile str16 countryofprovider long number "Employment and further study" "England" "All" 23890 "Employment and further study" "Non-European Union" "All" 4545 "Employment and further study" "Northern Ireland" "All" 1305 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 2415 "Employment and further study" "Other UK" "All" 75 "Employment and further study" "Scotland" "All" 2185 "Employment and further study" "Total" "All" 35745 "Employment and further study" "Total UK" "All" 28780 "Employment and further study" "Total non-UK" "All" 6960 "Employment and further study" "Wales" "All" 1330 "Employment and further study" "England" "All" 25505 "Employment and further study" "Non-European Union" "All" 4930 "Employment and further study" "Northern Ireland" "All" 1380 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 2680 "Employment and further study" "Other UK" "All" 85 "Employment and further study" "Scotland" "All" 2355 "Employment and further study" "Total" "All" 38355 "Employment and further study" "Total UK" "All" 30745 "Employment and further study" "Total non-UK" "All" 7610 "Employment and further study" "Wales" "All" 1420 "Employment and further study" "England" "All" 18725 "Employment and further study" "Non-European Union" "All" 4220 "Employment and further study" "Northern Ireland" "All" 910 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 2230 "Employment and further study" "Other UK" "All" 65 "Employment and further study" "Scotland" "All" 1745 "Employment and further study" "Total" "All" 28895 "Employment and further study" "Total UK" "All" 22440 "Employment and further study" "Total non-UK" "All" 6455 "Employment and further study" "Wales" "All" 995 "Employment and further study" "England" "All" 20180 "Employment and further study" "Non-European Union" "All" 4580 "Employment and further study" "Northern Ireland" "All" 985 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 2490 "Employment and further study" "Other UK" "All" 70 "Employment and further study" "Scotland" "All" 1900 "Employment and further study" "Total" "All" 31280 "Employment and further study" "Total UK" "All" 24210 "Employment and further study" "Total non-UK" "All" 7070 "Employment and further study" "Wales" "All" 1075 "Employment and further study" "England" "All" 5160 "Employment and further study" "Non-European Union" "All" 325 "Employment and further study" "Northern Ireland" "All" 390 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 185 "Employment and further study" "Other UK" "All" 10 "Employment and further study" "Scotland" "All" 440 "Employment and further study" "Total" "All" 6850 "Employment and further study" "Total UK" "All" 6340 "Employment and further study" "Total non-UK" "All" 510 "Employment and further study" "Wales" "All" 335 "Employment and further study" "England" "All" 5325 "Employment and further study" "Non-European Union" "All" 350 "Employment and further study" "Northern Ireland" "All" 400 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 190 "Employment and further study" "Other UK" "All" 10 "Employment and further study" "Scotland" "All" 455 "Employment and further study" "Total" "All" 7075 "Employment and further study" "Total UK" "All" 6535 "Employment and further study" "Total non-UK" "All" 540 "Employment and further study" "Wales" "All" 345 "Employment and further study" "England" "All" 5525 "Employment and further study" "Non-European Union" "All" 2720 "Employment and further study" "Northern Ireland" "All" 190 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 1075 "Employment and further study" "Other UK" "All" 25 "Employment and further study" "Scotland" "All" 555 "Employment and further study" "Total" "All" 10400 "Employment and further study" "Total UK" "All" 6600 "Employment and further study" "Total non-UK" "All" 3795 "Employment and further study" "Wales" "All" 310 "Employment and further study" "England" "All" 5800 "Employment and further study" "Non-European Union" "All" 2910 "Employment and further study" "Northern Ireland" "All" 200 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 1180 "Employment and further study" "Other UK" "All" 25 "Employment and further study" "Scotland" "All" 585 "Employment and further study" "Total" "All" 11020 "Employment and further study" "Total UK" "All" 6930 "Employment and further study" "Total non-UK" "All" 4090 "Employment and further study" "Wales" "All" 325 "Employment and further study" "England" "All" 3010 "Employment and further study" "Non-European Union" "All" 2445 "Employment and further study" "Northern Ireland" "All" 100 "Employment and further study" "Not known" "All" 0 "Employment and further study" "Other European Union" "All" 940 "Employment and further study" "Other UK" "All" 15 "Employment and further study" "Scotland" "All" 350 "Employment and further study" "Total" "All" 7015 "Employment and further study" "Total UK" "All" 3630 "Employment and further study" "Total non-UK" "All" 3385 "Employment and further study" "Wales" "All" 155 "Employment and further study" "England" "All" 3215 end
tab domicile
Domicile | Freq. Percent Cum.
---------------------+-----------------------------------
England | 38,190 10.43 10.43
Non-European Union | 33,420 9.13 19.56
Northern Ireland | 34,830 9.51 29.07
Not known | 9,030 2.47 31.53
Other European Union | 35,580 9.72 41.25
Other UK | 28,620 7.82 49.07
Scotland | 32,460 8.86 57.93
Total | 41,010 11.20 69.13
Total UK | 41,010 11.20 80.33
Total non-UK | 35,940 9.81 90.14
Wales | 36,090 9.86 100.00
---------------------+-----------------------------------
Total | 366,180 100.00
Code:
* Example generated by -dataex-. For more info, type help dataex clear input str20 domicile str16 countryofprovider str46 levelofqualificationobtained long number str29 wideningparticipationcharacteris long var21 "England" "All" "All" 23890 "IMD" 2775 "Non-European Union" "All" "All" 4545 "IMD" 2360 "Northern Ireland" "All" "All" 1305 "IMD" 415 "Not known" "All" "All" 0 "IMD" 12895 "Other European Union" "All" "All" 2415 "IMD" 11665 "Other UK" "All" "All" 75 "IMD" 1230 "Scotland" "All" "All" 2185 "IMD" 1945 "Total" "All" "All" 35745 "IMD" 1640 "Total UK" "All" "All" 28780 "IMD" 300 "Total non-UK" "All" "All" 6960 "IMD" 25490 "Wales" "All" "All" 1330 "IMD" 19980 "England" "All" "All" 25505 "IMD" 5510 "Non-European Union" "All" "All" 4930 "IMD" 1500 "Northern Ireland" "All" "All" 1380 "IMD" 1135 "Not known" "All" "All" 0 "IMD" 370 "Other European Union" "All" "All" 2680 "IMD" 3795 "Other UK" "All" "All" 85 "IMD" 3110 "Scotland" "All" "All" 2355 "IMD" 690 "Total" "All" "All" 38355 "IMD" 165 "Total UK" "All" "All" 30745 "IMD" 135 "Total non-UK" "All" "All" 7610 "IMD" 30 "Wales" "All" "All" 1420 "IMD" 51130 "England" "All" "All" 18725 "IMD" 42140 "Non-European Union" "All" "All" 4220 "IMD" 8990 "Northern Ireland" "All" "All" 910 "IMD" 25640 "Not known" "All" "All" 0 "IMD" 22155 "Other European Union" "All" "All" 2230 "IMD" 3480 "Other UK" "All" "All" 65 "IMD" 1540 "Scotland" "All" "All" 1745 "IMD" 1255 "Total" "All" "All" 28895 "IMD" 285 "Total UK" "All" "All" 22440 "IMD" 135 "Total non-UK" "All" "All" 6455 "IMD" 100 "Wales" "All" "All" 995 "IMD" 35 "England" "All" "All" 20180 "IMD" 365 "Non-European Union" "All" "All" 4580 "IMD" 330 "Northern Ireland" "All" "All" 985 "IMD" 35 "Not known" "All" "All" 0 "IMD" 205 "Other European Union" "All" "All" 2490 "IMD" 165 "Other UK" "All" "All" 70 "IMD" 40 "Scotland" "All" "All" 1900 "IMD" 20 "Total" "All" "All" 31280 "IMD" 15 "Total UK" "All" "All" 24210 "IMD" 5 "Total non-UK" "All" "All" 7070 "IMD" 300 "Wales" "All" "All" 1075 "IMD" 255 "England" "All" "All" 5160 "IMD" 45 "Non-European Union" "All" "All" 325 "IMD" 2995 "Northern Ireland" "All" "All" 390 "IMD" 2530 "Not known" "All" "All" 0 "IMD" 465 "Other European Union" "All" "All" 185 "IMD" 14240 "Other UK" "All" "All" 10 "IMD" 12815 "Scotland" "All" "All" 440 "IMD" 1425 "Total" "All" "All" 6850 "IMD" 2200 "Total UK" "All" "All" 6340 "IMD" 1830 "Total non-UK" "All" "All" 510 "IMD" 370 "Wales" "All" "All" 335 "IMD" 25490 "England" "All" "All" 5325 "IMD" 19980 "Non-European Union" "All" "All" 350 "IMD" 5510 "Northern Ireland" "All" "All" 400 "IMD" 1740 "Not known" "All" "All" 0 "IMD" 1290 "Other European Union" "All" "All" 190 "IMD" 455 "Other UK" "All" "All" 10 "IMD" 4275 "Scotland" "All" "All" 455 "IMD" 3470 "Total" "All" "All" 7075 "IMD" 805 "Total UK" "All" "All" 6535 "IMD" 180 "Total non-UK" "All" "All" 540 "IMD" 145 "Wales" "All" "All" 345 "IMD" 30 "England" "All" "All postgraduate" 5525 "IMD" 54130 "Non-European Union" "All" "All postgraduate" 2720 "IMD" 44550 "Northern Ireland" "All" "All postgraduate" 190 "IMD" 9580 "Not known" "All" "All postgraduate" 0 "IMD" 28640 "Other European Union" "All" "All postgraduate" 1075 "IMD" 24565 "Other UK" "All" "All postgraduate" 25 "IMD" 4070 "Scotland" "All" "All postgraduate" 555 "IMD" 1860 "Total" "All" "All postgraduate" 10400 "IMD" 1525 "Total UK" "All" "All postgraduate" 6600 "IMD" 340 "Total non-UK" "All" "All postgraduate" 3795 "IMD" 140 "Wales" "All" "All postgraduate" 310 "IMD" 105 "England" "All" "All postgraduate" 5800 "IMD" 35 "Non-European Union" "All" "All postgraduate" 2910 "IMD" 425 "Northern Ireland" "All" "All postgraduate" 200 "IMD" 385 "Not known" "All" "All postgraduate" 0 "IMD" 40 "Other European Union" "All" "All postgraduate" 1180 "IMD" 205 "Other UK" "All" "All postgraduate" 25 "IMD" 165 "Scotland" "All" "All postgraduate" 585 "IMD" 40 "Total" "All" "All postgraduate" 11020 "IMD" 20 "Total UK" "All" "All postgraduate" 6930 "IMD" 15 "Total non-UK" "All" "All postgraduate" 4090 "IMD" 5 "Wales" "All" "All postgraduate" 325 "IMD" 355 "England" "All" "All postgraduate" 3010 "IMD" 295 "Non-European Union" "All" "All postgraduate" 2445 "IMD" 60 "Northern Ireland" "All" "All postgraduate" 100 "IMD" 245 "Not known" "All" "All postgraduate" 0 "IMD" 80 "Other European Union" "All" "All postgraduate" 940 "IMD" 165 "Other UK" "All" "All postgraduate" 15 "IMD" 765 "Scotland" "All" "All postgraduate" 350 "IMD" 325 "Total" "All" "All postgraduate" 7015 "IMD" 435 "Total UK" "All" "All postgraduate" 3630 "IMD" 160 "Total non-UK" "All" "All postgraduate" 3385 "IMD" 35 "Wales" "All" "All postgraduate" 155 "IMD" 125 "England" "All" "All postgraduate" 3215 "IMD" 3800 end
