Data Cleaning & Data Exploration

use usprojects;
select * from ushouseholdincome;
select ID, count(ID)
from ushouseholdincome
group by ID
having count(ID)>1;
select * from ushouseholdincome_statistics;
# Change column name
ALTER TABLE ushouseholdincome_statistics rename column `id` to `ID`;
select row_id, id,
row_number() over(partition by id order by id) row_num
from ushouseholdincome;
select *
from (
select row_id, id,
row_number() over(partition by id order by id) row_num
from ushouseholdincome) duplicates
where row_num >1;
delete from ushouseholdincome
where row_id in (
	select row_id
	from (
	select row_id, id,
	row_number() over(partition by id order by id) row_num
	from ushouseholdincome) duplicates
	where row_num >1);
# Check whether there are duplicates in Table: ushouseholdincome_statistics
select ID, count(ID)
from ushouseholdincome_statistics
group by ID
having count(ID)>1;
# found duplicate state_name "Georgie" and lower case 'a' in 'Alabama'
# Change state_name
update ushouseholdincome
set State_Name = 'Georgia'
where State_Name = 'georia';
update ushouseholdincome
set State_Name = 'Alabama'
where State_Name = 'alabama';
update ushouseholdincome
set Place = 'Autaugaville'
where County='Autauga County' and City='Vinemont';
select Type, Count(Type)
from ushouseholdincome
group by Type
order by 1;
update ushouseholdincome
set Type = 'Borough'
where Type = 'Boroughs';
select Aland, AWater
from ushouseholdincome
where ALand=0 or ALand='' or ALand is null;
select *
from ushouseholdincome;

Use usprojects;
select *
from ushouseholdincome var
inner join ushouseholdincome_statistics inc
on var.id=inc.id
where Mean <> 0;

select var.State_name, round(avg(Mean),1), round(avg(Median),1), avg(ALand), avg(AWater)
from ushouseholdincome var
inner join ushouseholdincome_statistics inc
on var.id=inc.id
where Mean <> 0
group by var.State_Name
order by 3 desc
limit 10;

select var.Type, Count(var.Type), round(avg(Mean),1), round(avg(Median),1), avg(ALand), avg(AWater)
from ushouseholdincome var
inner join ushouseholdincome_statistics inc
on var.id=inc.id
where Mean <> 0
group by var.Type
order by 4 desc
limit 10;

select var.Type, Count(var.Type), round(avg(Mean),1), round(avg(Median),1), avg(ALand), avg(AWater)
from ushouseholdincome var
inner join ushouseholdincome_statistics inc
on var.id=inc.id
where Mean <> 0
group by var.Type
order by 4 desc
limit 10;

select *
from ushouseholdincome
where Type = 'County';

select *
from ushouseholdincome
where Type = 'Urban';

select var.Type, Count(var.Type), round(avg(Mean),1), round(avg(Median),1), avg(ALand), avg(AWater)
from ushouseholdincome var
inner join ushouseholdincome_statistics inc
on var.id=inc.id
where Mean <> 0
group by var.Type
having Count(var.Type)>100
order by 4 desc
limit 10;


select var.State_Name, City, round(avg(Mean),1), round(avg(Median),1)
from ushouseholdincome var
inner join ushouseholdincome_statistics inc
on var.id=inc.id
group by var.State_Name, City
order by round(avg(Mean),1) desc
limit 10;