ZikaVirus Analysis and Out-Break Using PIG
Project Introduction:
Domain - Health Care
Technology Use - PIG Latin on hadoop cluster
DataSet - cdc_zika.csv (DataSet Link: https://www.kaggle.com/cdc/zika-virus-epidemic)
An outbreak of the Zika virus, an infection transmitted mostly by the Aedes species mosquito (Ae. aegypti and Ae. albopictus), has been sweeping across the Americas and the Pacific since mid-2015. This dataset shares publicly available data related to the ongoing Zika epidemic.
With help of this dataset,we are going to generate few reports which will help us understanding that how many countries are impacted with zika virus, is virus increasing/deceasing in affected countries and affecting more to what age groups and many more.. )DATASET DESCRIPTION:
- report_date ::The report date is the date that the report was published.
- location ::A location is specified for each observation following the specific names specified in the country place name database
- location_type:: A location code is included indicating: city, district, municipality, county, state, province, or country.
- data_field::The data field is a short description of what data is represented in the row and is related to a specific definition defined by the report from which it comes.
- data_field_code::This code is defined in the country data guide. It includes a two letter country code (ISO-3166 alpha-2, list), followed by a 4-digit number corresponding to a specific report type and data type.
- time_period::Optional
- time_period_type::Required only if 'time_period' is specified.
- value::The observation indicated for the specific 'report_date', 'location', 'data_field'
- unit :The unit of measurement for the 'data_field'
Q1::Most affected country in terms of Zika confirmed and Zika suspected cases, as well as the count of total number of Zika confirmed and Zika suspected cases in all the available countries in the dataset.
// Load data file with below schema
raw = LOAD 'cdc_zika.csv' USING org.apache.pig.piggybank.storage.CSVExcelStorage() AS (report_date:chararray,location:chararray,location_type:chararray,data_field:chararray,data_field_code:chararray,time_period:chararray,time_period_type:chararray,value:int,unit:chararray);
// Filter loaded raw data having 'zika confirmed' & 'zika suspected' cases
filter_reportData = FILTER raw BY (location_type == 'country' and ((data_field matches '.*(zika).*' and data_field matches '.*(confirmed).*') or (data_field matches '.*(zika).*' and data_field matches '.*(suspected).*')));
// Get only field required for report
reqReportField = FOREACH filter_reportData GENERATE location,value;
//Group by location field to get location wise total zika cases
grp_reportData = GROUP reqReportField BY location;
//Get county and Total zika cases found against that county
grp_reportData_count = FOREACH grp_reportData Generate group as Country,SUM(reqReportField.value) as TotalZikaCases;
//Sort results by total zika cases found for each county
Zika_AffectedCases = ORDER grp_reportData_count BY TotalZikaCases DESC;
//Store the results to hadoop cluster
STORE Zika_AffectedCases INTO 'Zika_AffectedCases';
//Below are the results from this report showing country name and total 'zika confirmed' & 'zika suspected' cases
//Limit the result set to 1 which will show most affected county from 'zika confirmed' & 'zika suspected' cases
most_Zika_AffectedCountry = LIMIT Zika_AffectedCases 1;
//Store the results to hadoop cluster
STORE most_Zika_AffectedCountry INTO 'most_Zika_AffectedCountry';
//Below is the results from this report showing most affected county name with total 'zika confirmed' & 'zika suspected' cases
(Dominican_Republic,48025)
Here is Data visualisation using tableau tool:.
Q2::As the analysis in the dataset is weekly or twice a week, reported date wise analysis of most number of Zika confirmed and Zika discarded cases, which will be helpful in understanding in which month or season a country is most affected to the disease, which in turn helps in understanding the temperature or the climate condition that leading the source of this disease.
// Load data file with below schema
raw = LOAD 'cdc_zika_test.csv' USING org.apache.pig.piggybank.storage.CSVExcelStorage() AS (report_date:chararray, location:chararray,location_type:chararray,data_field:chararray, data_field_code:chararray, time_period:chararray, time_period_type:chararray,value:int, unit:chararray);
// Filter loaded raw data having 'zika confirmed' & 'zika discarded' cases and location_type as 'county'
filter_reportData = FILTER raw BY (location_type == 'country' and ((data_field matches '.*(zika).*' and data_field matches '.*(confirmed).*') or (data_field matches '.*(zika).*' and data_field matches '.*(discarded).*')));
// Get only field required for report
getReportField = FOREACH filter_reportData GENERATE report_date,location,value;
//Group by report_date ,location field to get ReportedDate & Country wise total zika cases
grpBY_reportData = GROUP getReportField BY (report_date,location);
//Get ReportedDate ,county and Total zika cases found against that ReportedDate ,county
totalCase_By_ReportedDate_n_Country = FOREACH grpBY_reportData Generate group.report_date,group.location, SUM(getReportField.value) as TotalZikaCases;
//Group By report_date to group record by report date.
grpByCases_By_reportedDate = GROUP totalCase_By_ReportedDate_n_Country BY report_date;
// Get most Affected country against each reported date.
mostAfectedCounty_OnReporteDates = FOREACH grpByCases_By_reportedDate {
inner_sorted = ORDER totalCase_By_ReportedDate_n_Country BY TotalZikaCases DESC; mostAfectedcountry_Record = LIMIT inner_sorted 1;
GENERATE BagToString(mostAfectedcountry_Record) AS MostAfectedcountryOnReportedDate ;
}
//Store the results into Hadoop Cluster.
STORE mostAfectedCounty_OnReporteDates INTO mostAfectedCounty_OnReporteDates';
//Below is result from above relation(2015-12-09_Guatemala_29) (2016-03-16_Nicaragua_140) (2016-03-19_Dominican_Republic_18) (2016-03-26_Dominican_Republic_40) (2016-03-28_Nicaragua_148) (2016-03-30_Ecuador_184) (2016-04-02_Dominican_Republic_70) (2016-04-04_Nicaragua_147) (2016-04-06_Ecuador_188) (2016-04-09_Dominican_Republic_73) (2016-04-13_Ecuador_191) (2016-04-16_Dominican_Republic_104) (2016-04-18_Nicaragua_158) (2016-04-20_Ecuador_197) (2016-04-23_Dominican_Republic_104) (2016-04-25_Nicaragua_178) (2016-04-27_Ecuador_202) (2016-04-30_Dominican_Republic_104) (2016-05-03_Nicaragua_196) (2016-05-04_Nicaragua_226) (2016-05-05_Nicaragua_217) (2016-05-07_Dominican_Republic_104) (2016-05-09_Nicaragua_217) (2016-05-11_Nicaragua_218) (2016-05-14_Dominican_Republic_104) (2016-05-16_Nicaragua_196) (2016-05-18_Ecuador_276) (2016-05-19_Nicaragua_234) (2016-05-21_Dominican_Republic_104) (2016-05-23_Nicaragua_246) (2016-05-25_Ecuador_338) (2016-05-26_Nicaragua_9) (2016-05-27_Nicaragua_254) (2016-05-28_Dominican_Republic_159) (2016-06-01_Ecuador_426) (2016-06-03_Nicaragua_47) (2016-06-04_Dominican_Republic_161) (2016-06-06_Nicaragua_232) (2016-06-07_Nicaragua_12) (2016-06-13_Nicaragua_328) (2016-06-15_Ecuador_550) (2016-06-22_Ecuador_1174) (2016-06-29_Ecuador_1316)
Here is Data visualisation using tableau tool:
This below graph shows Reported Date wise, total Zika Confirmed & Discarded cases for each county.
Q3::Individual country’s reported date data to analyze the number of Zika confirmed and suspected cases increasing or decreasing day by day, which helps in identifying the countries where it’s growing adversely as well as about the countries taking precaution against the disease where the numbers are controlled or decreasing
// Load data file with below schema
raw = LOAD 'cdc_zika.csv' USING org.apache.pig.piggybank.storage.CSVExcelStorage() AS (report_date:chararray,location:chararray, location_type:chararray,data_field:chararray,data_field_code:chararray,time_period:chararray,time_period_type:chararray, value:int,unit:chararray);
//Filter loaded raw data having 'zika confirmed' & 'zika suspected' cases and location_type as 'county'
filter_reportData = FILTER raw BY (location_type == 'country' and ((data_field matches '.*(zika).*' and data_field matches '.*(confirmed).*') or (data_field matches '.*(zika).*' and data_field matches '.*(suspected).*')));
//Get only field required for report
getReportField = FOREACH filter_reportData GENERATE location,report_date,value;
//Group By Country and ReportedDate
grpBY_reportData = GROUP getReportField BY (location,report_date);
// Get country's total Zika case against each reported date.
totalCase_By_Country_n_ReportedDate = FOREACH grpBY_reportData{
sort = ORDER getReportField BY location ASC, report_date ASC;
latest = LIMIT sort 1;
GENERATE FLATTEN(latest);
};
//Store result in Hadoop Cluster
STORE totalCase_By_Country_n_ReportedDate INTO TotalZikaCaseBy_Country_n_ReportedDate';
//Below is sample of the results
Data visualisation using tableau tool:
This below graph shows Individual Country's reported date Zika confirmed and suspected cases which helps understanding if cases are increasing or decreasing day by day in that country.
Q4 -Age group wise analysis of the number of Zika confirmed and suspected cases found, which helps in understanding which age group has the risk of the disease as outcome varies across the age groups.
// Load data file with below schema
raw = LOAD 'cdc_zika.csv' USING org.apache.pig.piggybank.storage.CSVExcelStorage() AS(report_date:chararray, location:chararray, location_type:chararray,data_field:chararray,data_field_code:chararray,time_period:chararray, time_period_type:chararray,value:int,unit:chararray);
// Filter loaded raw data having 'zika confirmed' & 'zika suspected' cases and location_type as 'county'
filter_reportData = FILTER raw BY ( ((data_field matches '.*(zika).*' and data_field matches '.*(confirmed).*') or (data_field matches '.*(zika).*' and data_field matches '.*(suspected).*')) and (data_field matches '.*(ages).*'));
// Get only field required for report
getReportField = FOREACH filter_reportData GENERATE SUBSTRING(data_field,INDEXOF(data_field,'ages_',0)+5,(int)SIZE(data_field)-2) as AgeGrp, value ;
//Group By Age Group
grp_reportData = GROUP getReportField BY AgeGrp;
//Get AgeGroup,Totak Zika Cases against that age group.
totalCase_By_AgeGroup = FOREACH grp_reportData Generate group As AgeGrp,SUM(getReportField.value) as TotalZikaCases;
//Store the results into Hadoop Cluster.
STORE totalCase_By_AgeGroup INTO 'TotalZikaCase_By_AgeGroup';
//Below is result from this report.
Data visualisation using tableau tool:
This graphs show AgeGroup wise Zika case report.
Q5- Predicting the outbreak of Zika virus based on the existing data
Q1 report shows that Dominican Republic is most affected county.
Q2 report shows that May month is the month where countries are most affected to this Zika virus.
Q3 report shows that Zika virus is increasing day by day.
Q4 reports shows that 20-49yrs Age group is most affected by this Zika virus.
Comments
Post a Comment