I need to create the following file structure. The information originally came from an excel pivot table. I have managed to create a csv file with labels in the first column and counts in the second. The desired output is shown below.
SLA | Country | Gender | Visa Class | Age | Ethnicity | Persons |
Acacia Ridge | AFGHANISTAN | Female | 866 | 18-64 | Hazaragi | 3 |
| | Male | 866 | 18-64 | Dari | 1 |
| | | | | Hazaragi | 6 |
SLA | Country | Gender | Visa Class | Age | Ethnicity | Persons |
Acacia Ridge | AFGHANISTAN | Female | 866 | 18-64 | Hazaragi | 3 |
Acacia Ridge | AFGHANISTAN | Male | 866 | 18-64 | Dari | 1 |
Acacia Ridge | AFGHANISTAN | Male | 866 | 18-64 | Hazaragi | 6 |
The source data is as below
Acacia Ridge | 131 |
AFGHANISTAN | 10 |
Female | 3 |
866 | 3 |
18-64 | 3 |
Hazaragi | 3 |
Male | 7 |
866 | 7 |
18-64 | 7 |
Dari | 1 |
Hazaragi | 6 |
AUSTRALIA | 5 |
Female | 2 |
187 | 1 |
00-17 | 1 |
Not stated | 1 |
485 | 1 |
00-17 | 1 |
Not stated | 1 |
Male | 3 |
186 | 1 |
00-17 | 1 |
Not stated | 1 |
820 | 1 |
00-17 | 1 |
Not stated | 1 |
885 | 1 |
00-17 | 1 |
Not stated | 1 |
I need to create a loop statement which uses the locality as a counter, the number opposite is the total for the locality, the number against the country is the total for the country and the same applied for sex, age and visa class. Where the number is opposite a language such as Dari it is that number that need to be in the persons column. I have 13 years of data with each data set having approximately 400,000 cases.
Is anybody able to assist.
Brian cooper
Locked
