-
Notifications
You must be signed in to change notification settings - Fork 3
Initial data cleanup
Ane edited this page Aug 17, 2021
·
1 revision
- Delete rows at night (current_time before 7:00, and after 23:00)
- Delete rows where minute is not in 00, 15, 30 or 45
- Convert ost and west from the old format to muenchen-ost, muenchen-west to new format
- Convert date format to: YYYY/MM/DD HH:MM (hint: datetime package in python)
- Use the CDS API and obtain the weather data for the past dates
- Save the data
import pandas as pd
boulder_data = pd.read_csv("boulderdata.csv")
boulder_data.head()
gym_name current_time occupancy waiting weather_temp weather_status
frankfurt 2020/10/29 18:31 61.0 0.0 10.95 Rain
muenchen-west 2020/10/29 18:31 27.0 0.0 9.22 Rain
muenchen-ost 2020/10/29 18:31 100.0 14.0 9.22 Rain
dortmund 2020/10/29 18:31 56.0 0.0 10.00 Rain
regensburg 2020/10/29 18:31 68.0 0.0 7.42 Rainboulder_data['current_time'].describe()
>> count 56784
>> unique 10867
>> top 2020/10/29 18:16
>> freq 15
>> Name: current_time, dtype: object
closed_times = ['23:', '00:', '01:', '02:', '03:', '04:', '05:', '06:']
for i in closed_times:
boulder_data = boulder_data[~boulder_data.current_time.str.contains(i)]
boulder_data['current_time'].describe()
>> count 38587
>> unique 7377
>> top 2020/10/29 18:16
>> freq 15
>> Name: current_time, dtype: objectboulder_data = boulder_data.loc[(boulder_data['current_time'].str.contains(":00")) | (boulder_data['current_time'].str.contains(":15")) | (boulder_data['current_time'].str.contains(":30")) | (boulder_data['current_time'].str.contains(":45"))]
boulder_data['current_time'].describe()
>> count 1984
>> unique 404
>> top 2020/10/22 15:00
>> freq 10
>> Name: current_time, dtype: objectboulder_data['gym_name'].describe()
>> count 1984
>> unique 7
>> top dortmund
>> freq 398
>> Name: current_time, dtype: object
boulder_data.gym_name.unique()
>> array(['regensburg', 'muenchen-west', 'frankfurt', 'dortmund',
'muenchen-ost', 'west', 'ost'], dtype=object)
boulder_data['gym_name'] = boulder_data['gym_name'].replace(['ost','west'],['muenchen-ost','muenchen-west'])
boulder_data.gym_name.unique()
>> array(['regensburg', 'muenchen-west', 'frankfurt', 'dortmund',
'muenchen-ost'], dtype=object)from datetime import datetime
boulder_data = boulder_data.reset_index(drop=True)
# from row 97, the format is DD/MM/YYYY HH:MM
# boulder_data.info()
# applied the below function to all rows [10:200] but the other rows were already in the correct format (but some dates coincide with other rows (e.g. 25-29 with 200-205))
# should check for duplicates
boulder_data["current_time"][10:200] = pd.to_datetime(boulder_data['current_time'][10:200], format='%Y-%m-%d %H:%M').dt.strftime('%Y/%m/%d %H:%M')Exploring the data in the below 3 cells:
- figuring out how many cells don't have weather data
- figuring out how detailed the weather status values are
- figuring out which dates & times I should collect data for
In the below cells, I have used this article to download weather data and convert nc format to a pandas dataframe.
import cdsapi
import netCDF4
import numpy
from netCDF4 import num2date
from datetime import datetime, timedelta
def get_weather_data(year, month, day, area, time,
file_location = 'weather_data.nc'
):
"""
Input:
year, month, day, area, time, file_location: Strings
Outputs:
file_location: A string as in input
"""
c = cdsapi.Client()
c.retrieve(
'reanalysis-era5-single-levels',{
'product_type':'reanalysis', # This is the dataset produced by the CDS
'variable':['2m_temperature'],
'year': year,
'month': month,
'day': day,
'area': area,
'time': time,
'format':'netcdf' # The format we choose to use
},
file_location)
return(file_location)
#The function opens the file in format “.nc” and returns a pandas dataframe with the value of temperature and datapoint.
def read_netcdf_file(file_location):
f = netCDF4.Dataset(file_location) # This the python package I used to open the nc file
t2m = f.variables['t2m'][:].flatten()
time = f.variables['time'][:].flatten()
start_time = datetime.strptime("01/01/1900 00:00", "%d/%m/%Y %H:%M")
time_points = []
for x in range(len(time)):
hours_from_start_time = start_time + timedelta(hours=int(time.data[x]))
time_points.append(hours_from_start_time)
values = pd.DataFrame({
"t2m" : [x-273.15 for x in t2m.data if x!= -32767], # As we said the values are in Kelvin, to convert in Celsius we have to subtract 273.15
"time" : time_points
})
return(values)
# Probably not the most efficient way to download data for several cities, but in the next 2 cells, I set the long & lat for the different locations, then downloaded data for that location (for all 6 locations).
# latitude = "50.1109" # frankfurt
# longitude = "8.6821" # frankfurt
# latitude = "51.5136" # dortmund
# longitude = "7.4653" # dortmund
# latitude = "49.0134" # regensburg
# longitude = "12.1016" # regensburg
latitude = "48.1351" # munich
longitude = "11.5820" # munich
file_location = get_weather_data(
year = "2020",
# NB: Single digits days or months must have 0s in front or that will cause an error
day = ['03', '04', '05', '06', '07', '08', '09', '10', '11', '12'],
month = "09",
# The ERA5 accept rectangular shape grid as a searching areas
# but we can use also input a point with this system:ß
area = latitude +'/'+ longitude +'/'+ latitude +'/'+ longitude,
# We can request all 24 hours of the day. The only accepted format are listed here:
time = ['07:00', '09:00', '10:00', '12:00', '13:00', '14:00', '15:00', '16:00', '17:00', '18:00', '19:00', '20:00', '21:00', '22:00'],
file_location = 'munich.nc')
frankfurt_data = read_netcdf_file('frankfurt.nc')
dortmund_data = read_netcdf_file('dortmund.nc')
regensburg_data = read_netcdf_file('regensburg.nc')
muenchen_ost_data = read_netcdf_file('munich.nc')
muenchen_west_data = read_netcdf_file('munich.nc')
# Converting the "time" columns to have the same format as boulder_data (time without seconds)
dortmund_data["time"] = pd.to_datetime(dortmund_data["time"], format='%Y-%m-%d %H:%M:%S').dt.strftime('%Y/%m/%d %H:%M')
frankfurt_data["time"] = pd.to_datetime(frankfurt_data["time"], format='%Y-%m-%d %H:%M:%S').dt.strftime('%Y/%m/%d %H:%M')
regensburg_data["time"] = pd.to_datetime(regensburg_data["time"], format='%Y-%m-%d %H:%M:%S').dt.strftime('%Y/%m/%d %H:%M')
muenchen_ost_data["time"] = pd.to_datetime(muenchen_ost_data["time"], format='%Y-%m-%d %H:%M:%S').dt.strftime('%Y/%m/%d %H:%M')
muenchen_west_data["time"] = pd.to_datetime(muenchen_west_data["time"], format='%Y-%m-%d %H:%M:%S').dt.strftime('%Y/%m/%d %H:%M')
# Converting the "t2m" columns to have the same format as boulder_data (temp to 2 d.p.)
dortmund_data["t2m"] = dortmund_data["t2m"].round(2)
frankfurt_data["t2m"] = frankfurt_data["t2m"].round(2)
regensburg_data["t2m"] = regensburg_data["t2m"].round(2)
muenchen_ost_data["t2m"] = muenchen_ost_data["t2m"].round(2)
muenchen_west_data["t2m"] = muenchen_west_data["t2m"].round(2)
# I then need to add data to the main dataframe for each specific location and time
def gym_check(gym_name):
# I need help understanding how to make this more efficient
"""This function returns the respective CDS df """
if gym_name == "regensburg":
return regensburg_data
if gym_name == "dortmund":
return dortmund_data
if gym_name == "frankfurt":
return frankfurt_data
if gym_name == "muenchen-ost":
return muenchen_ost_data
if gym_name == "muenchen-west":
return muenchen_west_data
for i in range(1661, 1984):
# 1 - return correct cds df
# 2 - check if boulder_data.loc[i]["current_time"] matches any of the cds df["time"]
# (reminder, cds data is hourly)
# 3 - replace weather temp and status
cds = gym_check(boulder_data.loc[i]["gym_name"])
new = cds[cds['time'].astype(str).str[:-3].str.contains(boulder_data.loc[i]["current_time"].split(":")[0])].t2m
boulder_data['weather_temp'][i] = new
boulder_data['weather_status'][i] = "Clouds"
boulder_data.to_csv("boulderdata.csv", index=False)