· 8 years ago · Feb 15, 2018, 01:18 PM
1"""
2NOTE: If you've downloaded the repository from Github, then you already
3 have the necessary directory hierarchy and can skips for setting them up.
4This script is used for gap-filling sensor data that comes off the Em50g data loggers
5made by Decagon (now Meter Group). It does this:
6 1. Iterates through a directory that has multiple logger data files.
7 2. Extracts Logger ID and stores in each table (upper lefthand corner).
8 3. Trims sensor data based on a given Start and End date/time.
9 4. If needed, it gapfills any missing time-points (with No-Data)
10 5. Updates record/observation count (in upper lefthand corner).
11 6. Outputs each logger data file (as csv or excel file: change in script if needed)
12Gap-filling data to specific start and end times for all logger data coming off
13of a project will make it much easier to join them for comparison later.
14This is needed b/c sensor data can have the following:
15 1. Data gaps due to sensor failure or data logger failure.
16 2. Different start dates/times due to when they were installed/removed.
17 3. And a number of other reasons that may have caused time-gaps.
18
19BEFORE RUNNING THIS SCRIPT:
20 This script requires the following setup:
21 1. A directory to be setup called: 'sensor-data'
22 2. Insided 'sensor-data' create a directory called: 'raw-sensor-data'
23 3. Download/import '.xls' logger data files to: 'raw-sensor-data'
24 4. Your file names for each logger data file should:
25 a. Start with your Logger ID: D3Y as example (D3Y-12Feb2018-0914.xls).
26
27 Inside the script itself do the following:
28 1. Change the string for 'path_to_raw_data' to the path on your local
29 computer where you've put your 'raw-sensor-data' directory.
30 2. Based on your particular study and when/how data was collected, change the
31 following variable strings to meet your needs (in the script below):
32 start = '8/21/2017 15:00:00' (time-point when good data came in)
33 end = '11/21/2017 10:00:00' (time-point when good data stopped coming in)
34 frequency = 'H' (H for hourly, D for daily, etc.)
35
36Note:
37 1. The data coming off of the em50g is in a particular format ('wide table') that
38 has 3 headers above the data. This complicates processing and this script
39 deals with that issue.
40 2. The output from this script is still in a wide formatted table identical
41 to the output from the logger. The difference is that it will be
42 trimmed and gapfilled to when the loggers started and stopped
43 recording data in the field.
44 3. You will still need to clean and process the data for analysis.
45 4. Because of the akward headings you will need to do the
46 following after running the script (if running output as excel files):
47 a. Open each excel file.
48 b. Change data-types to numbers instead of strings. """
49
50# import packages
51import numpy as np
52import pandas as pd
53import os, shutil, glob
54
55############ UPDATE PATH TO WHERE YOU HAVE YOUR: raw-sensor-data ##############
56
57############# See Instructions Above for Setting up your Directories ##########
58
59# path to where your raw em50g logger data resides ('raw-sensor-data')
60path_to_raw_data = r'C:UsersjdextDesktopem50g-data-logger-gap-fill-toolsensor-dataraw-sensor-data'
61
62# change working directory to: 'path_to_raw_data'
63os.chdir(path_to_raw_data)
64
65# move 1 step back in your directories to create new directories for output.
66os.chdir('..')
67
68# create directory for gap-filled data (i.e., final data outputs)
69gap_filled_directory = 'gap-filled-sensor-data' # variable for output directory
70
71if not os.path.exists(gap_filled_directory): # this creates directory if it does not exist
72 os.makedirs(gap_filled_directory)
73else: # this removes and recreates directory if it exists
74 shutil.rmtree(gap_filled_directory)
75 os.makedirs(gap_filled_directory)
76
77# create directory for intermediate data output (i.e., garbage)
78intermediate_directory = 'intermediate-data' # variable for output directory
79
80if not os.path.exists(intermediate_directory): # this creates directory if it does not exist
81 os.makedirs(intermediate_directory)
82else: # this removes and recreates directory if it exists
83 shutil.rmtree(intermediate_directory)
84 os.makedirs(intermediate_directory)
85
86# change working directory back to: 'path_to_raw_data'
87os.chdir(path_to_raw_data)
88
89'''Create a date_time_range that compliments the study trial period for when sensors were
90collecting data. This date_time_range dataframe will be used to join, trim, and gap-fill
91logger data. Date-time range determined by install date/time and
92removal date/time of sensors and data loggers.'''
93
94######## UPDATE START, END, FREQUENCY BASED ON YOUR NEEDS ############
95
96# start and end date/time based on the beginning/end of your trial study period
97start = '12/14/2017 17:00:00' # the date/time when your sensors starting recording data
98end = '2/11/2018 18:00:00'# the date/time when your sensors stopped recording data
99frequency = 'H' # this is the frequency that your data was collected (e.g., 'H', 'D', 'W')
100
101# function for creating a 1 column dataframe with a time-series of when loggers collected data
102'''this is used for joining to the raw data for the purpose of gap-filling missing rows.'''
103
104def create_date_time_range(start, end, frequency='H'): # default is hourly: 'H'
105 time_series = pd.date_range(start, end, freq=frequency)
106 date_range_series = pd.Series(time_series)
107 date_time_range = pd.DataFrame(date_range_series)
108 date_time_range.columns = ['date_time']
109 date_time_range = date_time_range.set_index('date_time')
110
111 return date_time_range # returns time-series as a dataframe
112
113# run function and save output as dataframe in 'date_time_range'
114date_time_range = create_date_time_range(start, end, frequency)
115
116# create list of file pathways for loop to iterate over during processing
117''' uses pathname matching with '.xls' to get a list of pathways for each raw data logger file
118This stores all logger data file pathways to a list so that it can be iterated over'''
119logger_files = glob.glob(path_to_raw_data + '*.xls')
120
121#%%
122
123# for-loop that takes a file and joins it to timeDate_df for gap-filling
124for file in logger_files:
125
126 '''This loop does the followign:
127 1. Iterates through a directory that has multiple logger data files.
128 2. Extracts Logger ID and stores in each table (upper lefthand corner).
129 3. Trims sensor data based on a given Start and End date/time.
130 4. If needed, it gapfills any missing time-points (with No-Data)
131 5. Updates record/observation count (in upper lefthand corner)'''
132
133 # read in file as dataframes, one with headers and one without
134 without_headers = pd.read_excel(file, header=None) # no headers
135 with_headers = pd.read_excel(file, header=2, mangle_dupe_cols=True) # with 3rd row as header
136
137 # obtain logger ID from 'without_headers' by pulling from upper lefthand corner of dataframe
138 logger_id = without_headers.iloc[0,0] # extract file name from upper lefthand corner
139 logger_id = logger_id.split('.')[0] # remove things after '.' (xls)
140 logger_id = logger_id.split('-')[0] # extract logger ID by keeping everything before 1st hyphen
141
142 # replace file in upper lefthand corner with Logger ID.
143 without_headers.iloc[0,0] = logger_id
144
145 # replace old count of records with new count from created time series
146 without_headers.iloc[1,0] = str(len(date_time_range)) + ' records'
147
148 # subset first three rows from 'without_headers' (these will be inserted as headers later)
149 headers_for_insert = without_headers.iloc[0:3,:]
150
151 # set index as date_time column for joining purposes
152 with_headers = with_headers.set_index('Measurement Time')
153
154 # Left hand join using created time-series as series to join on.
155 join_for_gap_filling = date_time_range.join(with_headers)
156
157 # remove '***' and replace with 'nan' (Not a Number(nan))
158 # the '***' are from the sensor data and are added by data logger software
159 join_for_gap_filling.replace('***', np.nan, inplace=True)
160
161 # reset index to prepare table for final processing steps
162 join_for_gap_filling = join_for_gap_filling.reset_index()
163
164 # change working directory to: 'intermediate-outputs'
165 os.chdir('../' + intermediate_directory)
166
167 # write 'headers_for_insert' to csv as a way to start building table (this is the 1st 2 headers)
168 intermediate = logger_id + '_intermediate.csv'
169 headers_for_insert.to_csv(intermediate,
170 index=False, header=False, encoding='utf-8')
171
172 # Open file with 3 headers for each file and add in 3rd header and subsequent data
173 with open(intermediate, 'a', encoding='utf-8') as f:
174 join_for_gap_filling.to_csv(f, index=False, header=True, encoding='utf-8')
175
176 # Read in csv for gap-filled logger
177 gap_filled = pd.read_csv(intermediate, header=None, encoding='utf-8')
178 gap_filled_final = gap_filled.drop(gap_filled.index[3])
179
180 # change working directory to: 'gap-filled-sensor-data'
181 os.chdir('../' + gap_filled_directory)
182
183 # Write final gap-filled output to individual excel files (as xlsx)
184 ## gap_filled_logger_data = logger_id + '_gap-filled-sensor-data.xlsx'
185 ## gap_filled_final.to_excel(gap_filled_logger_data, sheet_name=logger_id, index=False, header=False)
186
187 # Write final gap-filled output to individual excel files (as xlsx)
188 gap_filled_logger_data = logger_id + '_gap-filled-sensor-data.csv'
189 gap_filled_final.to_csv(gap_filled_logger_data, index=False, header=False, encoding='utf-8')
190
191# change working directory to: 'path_to_raw_data'
192os.chdir(path_to_raw_data)