Pandas Datetime: Create a comparison of the top 10 years in which the UFO was sighted vs the hours of the day
22. Top 10 UFO Years vs. Hours of the Day
Write a Pandas program to create a comparison of the top 10 years in which the UFO was sighted vs the hours of the day.
Sample Solution:
Python Code:
import pandas as pd
#Source: https://bit.ly/1l9yjm9
df = pd.read_csv(r'ufo.csv')
df['Date_time'] = df['Date_time'].astype('datetime64[ns]')
most_sightings_years = df['Date_time'].dt.year.value_counts().head(10)
def is_top_years(year):
if year in most_sightings_years.index:
return year
hour_v_year = df.pivot_table(columns=df['Date_time'].dt.hour,index=df['Date_time'].dt.year.apply(is_top_years),aggfunc='count',values='city')
hour_v_year.columns = hour_v_year.columns.astype(int)
hour_v_year.columns = hour_v_year.columns.astype(str) + ":00"
hour_v_year.index = hour_v_year.index.astype(int)
print("\nComparison of the top 10 years in which the UFO was sighted vs the hours of the day:")
print(hour_v_year.head(10))
Sample Output:
Comparison of the top 10 years in which the UFO was sighted vs the hours of the day:
0:00 1:00 2:00 4:00 ... 20:00 21:00 22:00 23:00
Date_time ...
1993 1.0 1.0 1.0 NaN ... 2.0 NaN NaN 4.0
1994 NaN NaN NaN NaN ... NaN 4.0 2.0 1.0
1995 NaN NaN 1.0 1.0 ... 2.0 1.0 1.0 3.0
1996 NaN 1.0 NaN NaN ... 1.0 NaN 2.0 1.0
1997 NaN 1.0 NaN 1.0 ... NaN 4.0 1.0 2.0
1998 2.0 1.0 NaN NaN ... 2.0 2.0 2.0 NaN
1999 2.0 NaN 1.0 NaN ... NaN 2.0 1.0 2.0
2000 NaN NaN 1.0 NaN ... 4.0 2.0 2.0 1.0
2001 3.0 1.0 NaN 1.0 ... 1.0 5.0 NaN NaN
2002 NaN 1.0 NaN NaN ... 2.0 NaN NaN 3.0
[10 rows x 20 columns]
For more Practice: Solve these Related Problems:
- Write a Pandas program to identify the top 10 years with the highest number of UFO sightings and plot these against the average observation hour.
- Write a Pandas program to group the UFO data by year and hour, then filter the top 10 years and visualize the hourly trends.
- Write a Pandas program to create a pivot table that summarizes the average observation time for the top 10 UFO years and plot the results.
- Write a Pandas program to compare the distribution of observation hours for the top 10 years of UFO sightings using a multi-index pivot table.
Python Code Editor:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a Pandas program to create a comparison of the top 10 years in which the UFO was sighted vs the hours of the day.
Next: Write a Pandas program to create a comparison of the top 10 years in which the UFO was sighted vs each Month.
What is the difficulty level of this exercise?