Halloween Data Visualization Competition - 13 Question and 13 Charts: UFO Sightings Analysis

1.     In which country were reported more UFO sightings?

2.     Next question is which cities are the most visited by UFOs.

3.     It seems that in both cases the US is a leader. So, next question is in which part of the US more UFO sightings were reported. The most visited by UFOs part of the US is the East Coast.

5.     What is about California?  It seems that Southern California is a leader.

6.     OK, the U.S. is a leader, what percent of total observations compared to other countries does the U.S. have?

7.     What if we compare the number and the total duration of observations. The U.S. is also a leader.

8.     We know all about UFO locations, what is about UFO shapes. What kind of shape the most popular in different countries. The answer is – a  light shape.

9.     What kind of shape has the longest observational period in total. The sphere shape is a winner here, it was observed 32 200 hours in total.

10.  Next question about observation duration. In which countries UFO were appeared for the longest time. Great Britain has the longest average observation time.

11.  All the charts above show the information in total. Let’s see how many UFO sightings and average duration were reported by year. It seems in the recent years the number of UFO observations is much larger than it was before, but average duration stays the same. The average duration in 1983 was enormously large of 332 hours.

12.  A general pattern of the reported over time data.

13.  In which month UFO appearance is more reported. In which month UFO appearance lasted for the longest time.

14.  The same questions but regarding time of day. It seems that people more often observe UFO object at 9 p.m., but at 5 p.m. they observe them longer.

 

select city, latitude, longitude

from [kaggle_ufo_data_cleaned]


select latitude, longitude

from [kaggle_ufo_data_cleaned]

where country = 'us'


select city, latitude, longitude, count (1) as observations

from [kaggle_ufo_data_cleaned]

group by 1,2, 3

having observations > 10


select city, latitude, longitude,

count (1) as observations

from [kaggle_ufo_data_cleaned]

where state = 'ca'

group by 1, 2, 3


select country, count(*) as observations, round((sum (duration_seconds)/3600), 2) as total_time

from [kaggle_ufo_data_cleaned]

where country != ''

group by 1

order by country


select SUM(CASE WHEN country = 'us' THEN 1 ELSE 0 END) as us, (count(*)-us) as other_countries

from [kaggle_ufo_data_cleaned]


select country, shape, count (shape)

from [kaggle_ufo_data_cleaned]

group by 1,2

having country !='' and shape !=''

order by country


select shape, sum (duration_seconds)/3600

from [kaggle_ufo_data_cleaned]

group by 1

having kaggle_ufo_data_cleaned.shape!= ''


select country, avg(duration_seconds)/3600

from [kaggle_ufo_data_cleaned]

where country !=''

group by 1


select extract(year from date_observed) as year, count(*) as observations, round((avg(duration_seconds)/3600), 2) as avg_duration

from [kaggle_ufo_data_cleaned]

group by year


select extract(year from date_observed) as year, count(*) as observations

from [kaggle_ufo_data_cleaned]

group by year


select extract(month from date_observed) as month, count(*) as observations, round((avg(duration_seconds)/3600),2) as avg_duration

from [kaggle_ufo_data_cleaned]

group by month


select extract (hour from date_observed) as time, count(*) as observations, round((avg(duration_seconds)/3600),2) as avg_duration

from [kaggle_ufo_data_cleaned]

group by time
Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like3 Follow
  • 3 Likes
  • 2 mths agoLast active
  • 213Views
  • 1 Following