%matplotlib inline

"""
Explanation: Envinroment preparation
First line if magic enabling matplotlib inline plots
End of explanation
"""


import dask.dataframe as dd
import pandas as pd
import glob
import os

"""
Explanation: Then we have round of inports:
* pandas is our main data storage module
* glob and os are used for filename manipulations
End of explanation
"""


filenames = [ os.path.splitext(wholeFilename)[0] for wholeFilename in 
             [ os.path.basename(wholePath) for wholePath in glob.glob("../input/2*.xlsx") ] ]

dataFiles = pd.DataFrame({"filename": filenames})
dataFiles["year"], dataFiles["pollutant"], dataFiles["resolution"] = dataFiles["filename"].str.split('_', 2).str

dataFiles.head()

dataFiles["year"].value_counts()

dataFiles["pollutant"].value_counts()

dataFiles["resolution"].value_counts()

"""
Explanation: Files index and identification
End of explanation
"""


dataFiles.ix[dataFiles["resolution"] == "(PM2.5)-24g", 'pollutant'] = "Cl_(PM2.5)"
dataFiles.ix[dataFiles["resolution"] == "(PM2.5)-24g", 'resolution'] = "24g"

dataFiles.ix[dataFiles["resolution"] == "(PM2.5)_24g", 'pollutant'] = "SO42_(PM2.5)"
dataFiles.ix[dataFiles["resolution"] == "(PM2.5)_24g", 'resolution'] = "24g"

dataFiles.ix[dataFiles["resolution"] == "w_PM25_24g", 'pollutant'] = "Jony_w_PM25"
dataFiles.ix[dataFiles["resolution"] == "w_PM25_24g", 'resolution'] = "24g"

"""
Explanation: Fixing data files identification
End of explanation
"""


dataFiles["resolution"].value_counts()

"""
Explanation: Now resolution column should be correct:
End of explanation
"""


dataFiles.ix[dataFiles["pollutant"] == "PM25", 'pollutant'] = "PM2.5"

dataFiles.describe()

"""
Explanation: Lets also fix worngly labelled pollutant:
End of explanation
"""


dataFiles[dataFiles["resolution"].isnull()]

"""
Explanation: There is still one empty cell in resolution column. Lets identify it:
End of explanation
"""


dataFiles.drop(dataFiles[dataFiles["filename"] == "2015_depozycja"].index, inplace=True)

dataFiles.describe()

"""
Explanation: After manually examinign 2015_depozycja file I found that it cointains new type of data, which will be useless in planned analysis. I decided to remove it from working memory.
End of explanation
"""


importantPollutants = ["PM10", "PM2.5", "O3", "NO2", "SO2", "C6H6", "CO"]
pollutants2015 = dataFiles[(dataFiles["year"] == "2015") & (dataFiles["resolution"] == "1g") & 
                           (dataFiles["pollutant"].isin(importantPollutants))]

pollutants2015

from tqdm import tqdm

from collections import Counter

#worstStation = {}
#for index, dataRow in tqdm(pollutants2015.iterrows(), total=len(pollutants2015.index)):
#    dataFromFile = pd.read_excel("../input/" + dataRow["filename"] + ".xlsx", skiprows=[1,2])
#    dataFromFile = dataFromFile.rename(columns={"Kod stacji":"Godzina"})
#    dataFromFile = dataFromFile.set_index("Godzina")
#    worstStation[dataRow["pollutant"]] = dataFromFile.max().sort_values(ascending = False).index[0]

"""
Explanation: Looking for worst measuring station for each pollutant in 2015
End of explanation
"""


pollutants = importantPollutants
years = sorted(list(dataFiles["year"].unique()))

pollutantsYears = dataFiles[(dataFiles["year"].isin(years)) & (dataFiles["resolution"] == "1g") & 
                           (dataFiles["pollutant"].isin(pollutants))]

bigDataFrame = pd.DataFrame()
for dataYear in years: 
    print(dataYear)
    yearDataFrame = pd.DataFrame()
    for index, dataRow in tqdm(pollutantsYears[pollutantsYears["year"] == dataYear].iterrows(), total=len(pollutantsYears[pollutantsYears["year"] == dataYear].index)):
        data = pd.read_excel("../input/" + dataRow["filename"] + ".xlsx", skiprows=[1,2])
        data = data.rename(columns={"Kod stacji":"Hour"})

        year = int(dataRow["year"])
        rng = pd.date_range(start = str(year) + '-01-01 01:00:00', end = str(year+1) + '-01-01 00:00:00', freq='H')

        # workaround for 2006_PM2.5_1g, 2012_PM10_1g, 2012_O3_1g
        try:
            data["Hour"] = rng
        except ValueError:
            print("File {} has some mess with timestamps".format(dataRow["filename"]))
            continue

        data = data.set_index("Hour")
        data = data.stack()
        data = pd.DataFrame(data, columns=[dataRow["pollutant"]])
        data.index.set_names(['Hour', 'Station'], inplace=True)

        yearDataFrame = pd.concat([yearDataFrame, data], axis=1)
    
    bigDataFrame = bigDataFrame.append(yearDataFrame)

bigDataFrame.to_pickle("../output/bigDataFrame.pkl")

bigDataFrame

daskBigDataFrame = dd.from_pandas(bigDataFrame)

"""
Explanation: Building one big data frame
End of explanation
"""


