cea-nus-weather in the temp folder, on windows, %temp%\cea-nus-weather)
In [17]:
import pandas as pd
import datetime
import tempfile
import os
In [18]:
# inputs
YEAR = 2015
# This corresponds to the saved location on the computer in the temp folder
SAVED_LOCATION = os.path.join(tempfile.gettempdir(), 'cea-nus-weather')
In [19]:
today = datetime.datetime(YEAR, 1, 4) # This corresponds to the date of the first excel sheet saved.
# This represents the first date in a year when the weather readings were recorded
all_data = pd.DataFrame()
In [20]:
all_data = pd.DataFrame()
for i in range(52):
current_file = os.path.join(SAVED_LOCATION, 'W_%s.xlsx' % today.strftime('%Y%m%d'))
print current_file
df = pd.read_excel(current_file)
all_data = all_data.append(df[2:], ignore_index=True)
today = today + datetime.timedelta(days=7)
print all_data
all_data.to_csv(os.path.join(SAVED_LOCATION, '%(YEAR)s_weather.csv' % locals()), encoding="utf-8")
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150104.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150111.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150118.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150125.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150201.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150208.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150215.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150222.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150301.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150308.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150315.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150322.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150329.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150405.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150412.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150419.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150426.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150503.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150510.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150517.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150524.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150531.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150607.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150614.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150621.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150628.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150705.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150712.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150719.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150726.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150802.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150809.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150816.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150823.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150830.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150906.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150913.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150920.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20150927.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151004.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151011.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151018.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151025.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151101.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151108.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151115.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151122.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151129.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151206.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151213.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151220.xlsx
c:\users\bhargava\appdata\local\temp\cea-nus-weather\W_20151227.xlsx
Year Date Time Pressure Air Temp \
0 2014.0 2017-12-29 02:00:00 100.3 24.28
1 2014.0 2017-12-29 03:00:00 100.2 24.32
2 2014.0 2017-12-29 04:00:00 100.1 23.98
3 2014.0 2017-12-29 05:00:00 100.2 23.63
4 2014.0 2017-12-29 06:00:00 100.2 23.86
5 2014.0 2017-12-29 07:00:00 100.2 24.08
6 2014.0 2017-12-29 08:00:00 100.3 23.9
7 2014.0 2017-12-29 09:00:00 100.4 23.77
8 2014.0 2017-12-29 10:00:00 100.4 24.44
9 2014.0 2017-12-29 11:00:00 100.4 25.54
10 2014.0 2017-12-29 12:00:00 100.3 26.91
11 2014.0 2017-12-29 13:00:00 100.2 27.41
12 2014.0 2017-12-29 14:00:00 100.1 27.48
13 2014.0 2017-12-29 15:00:00 100 27.44
14 2014.0 2017-12-29 16:00:00 100 27.32
15 2014.0 2017-12-29 17:00:00 100 26.83
16 2014.0 2017-12-29 18:00:00 100 25.9
17 2014.0 2017-12-29 19:00:00 100 25.51
18 2014.0 2017-12-29 20:00:00 100.1 25.21
19 2014.0 2017-12-29 21:00:00 100.1 24.94
20 2014.0 2017-12-29 22:00:00 100.2 24.71
21 2014.0 2017-12-29 23:00:00 100.2 24.5
22 2014.0 2017-12-29 1900-01-01 00:00:00 100.2 24.22
23 2014.0 2017-12-30 01:00:00 100.2 24.55
24 2014.0 2017-12-30 02:00:00 100.2 24.5
25 2014.0 2017-12-30 03:00:00 100.1 24.5
26 2014.0 2017-12-30 04:00:00 100.1 24.31
27 2014.0 2017-12-30 05:00:00 100 24.09
28 2014.0 2017-12-30 06:00:00 100.1 24.13
29 2014.0 2017-12-30 07:00:00 100.1 24.02
... ... ... ... ... ...
8998 2015.0 2017-12-26 19:00:00 100.1 28.72
8999 2015.0 2017-12-26 20:00:00 100.2 27.99
9000 2015.0 2017-12-26 21:00:00 100.3 27.47
9001 2015.0 2017-12-26 22:00:00 100.4 27.02
9002 2015.0 2017-12-26 23:00:00 100.5 26.71
9003 2015.0 2017-12-26 1900-01-01 00:00:00 100.5 26.36
9004 2015.0 2017-12-27 01:00:00 100.5 26.19
9005 2015.0 2017-12-27 02:00:00 100.5 26.03
9006 2015.0 2017-12-27 03:00:00 100.4 25.84
9007 2015.0 2017-12-27 04:00:00 100.4 25.85
9008 2015.0 2017-12-27 05:00:00 100.3 25.81
9009 2015.0 2017-12-27 06:00:00 100.3 25.78
9010 2015.0 2017-12-27 07:00:00 100.4 25.77
9011 2015.0 2017-12-27 08:00:00 100.4 25.59
9012 2015.0 2017-12-27 09:00:00 100.5 26.15
9013 2015.0 2017-12-27 10:00:00 100.5 27.53
9014 2015.0 2017-12-27 11:00:00 100.5 28.82
9015 2015.0 2017-12-27 12:00:00 100.5 29.73
9016 2015.0 2017-12-27 13:00:00 100.5 30.49
9017 2015.0 2017-12-27 14:00:00 100.4 30.86
9018 2015.0 2017-12-27 15:00:00 100.3 30.86
9019 2015.0 2017-12-27 16:00:00 100.3 30.31
9020 2015.0 2017-12-27 17:00:00 100.3 28.46
9021 2015.0 2017-12-27 18:00:00 100.2 29.66
9022 2015.0 2017-12-27 19:00:00 100.3 29.16
9023 2015.0 2017-12-27 20:00:00 100.4 28.34
9024 2015.0 2017-12-27 21:00:00 100.5 27.81
9025 2015.0 2017-12-27 22:00:00 100.6 27.51
9026 2015.0 2017-12-27 23:00:00 100.6 27.29
9027 2015.0 2017-12-27 1900-01-01 00:00:00 100.6 27.03
Relative Humidity Wind Speed Wind Direction Incoming Radiation Rainfall
0 90.4 1.56 331 0.023 0
1 89.6 1.141 334.3 0.032 0
2 91.8 1.426 299.8 0.023 0
3 93.7 2.452 282.1 0.022 0
4 92.5 2.118 289.5 0.021 0
5 92.8 2.207 296.8 0.03 0
6 95.3 3.078 283.4 3.752 5.334
7 99 2.105 308.9 58.71 1.778
8 97.7 1.616 323.4 167.8 0
9 89 1.743 298 336.1 0
10 78.1 2.563 307.7 478.1 0
11 74 3.106 317.1 367.2 0
12 72.9 3.289 320.5 417.6 0
13 71.9 3.712 326.3 381.8 0
14 70.4 3.924 322.8 322.1 0
15 73 3.297 328.3 135.2 0
16 76.2 3.742 343.1 60.04 0
17 77.4 3.209 344.3 15.49 0
18 78.9 3.287 337.6 0.24 0
19 80 3.528 340.1 0.029 0
20 79 2.692 351.4 0.027 0
21 80.1 2.095 8.1 0.031 0
22 80.7 3.215 12.78 0.027 0
23 77.1 3.63 358.7 0.024 0
24 78.6 3.76 0.826 0.024 0
25 78.5 3.343 3.197 0.029 0
26 81.5 2.757 351.8 0.032 0
27 84.6 3.001 337.5 0.022 0
28 83.3 3.422 341.4 0.025 0
29 83.6 2.001 308.8 0.042 0
... ... ... ... ... ...
8998 63.11 4.872 36.71 48.53 0
8999 67.47 4.815 38.21 0.681 0
9000 70.3 4.192 38.36 0.026 0
9001 73.1 3.544 40.2 0.025 0
9002 75.4 3.178 38.7 0.029 0
9003 77.9 3.416 39.66 0.031 0
9004 79.2 2.541 39.32 0.024 0
9005 80.5 2.806 38.46 0.023 0
9006 81.4 2.636 34.15 0.024 0
9007 81 2.392 24.51 0.027 0
9008 82.2 1.832 32.95 0.026 0
9009 82.3 1.448 35.05 0.027 0
9010 82.1 1.614 30.68 0.153 0
9011 83.4 2.445 40.24 41.07 0
9012 82.6 2.591 36.37 199.6 0
9013 76.9 3.331 36.22 358.1 0
9014 70.2 3.746 29.42 563.3 0
9015 64.36 4.084 40.67 412.8 0
9016 62.26 3.275 19.12 218.8 0
9017 60.58 3.287 36.38 347.3 0
9018 59.78 4.036 31.09 406.1 0
9019 61.84 3.048 39.26 215.8 0
9020 69.73 1.833 208 180.8 0
9021 64.33 1.759 49.06 106.7 0
9022 64.73 4.383 37.85 17.9 0
9023 68.68 4.274 42.77 0.81 0
9024 71.5 4.209 39.93 0.022 0
9025 73.4 3.668 35.81 0.024 0
9026 75.3 2.754 32.86 0.023 0
9027 77.3 2.397 34.65 0.024 0
[9028 rows x 10 columns]
Go through the file and see if any details are missing or repeated. Data when obtained from external sources sometimes might have few redundancies or miss few details. As this is not the same in all scenarios and is highly dependent on the data, a manual overlook is advised
For more information about this, go to the following link
Content source: architecture-building-systems/CEAforArcGIS
Similar notebooks: