<jupyter_start><jupyter_text>Pushing RAW data files to Postgres<jupyter_code>import psycopg2
import csv
import pandas as pd
from sqlalchemy import create_engine

#Read data from csv 
insight_HP3_RAD1 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/InSight_HP3_and_RAD1.csv')
insight_HP3_RAD2 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/InSight_HP3_and_RAD2.csv')
mars_weather_2018_1 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2018_1.csv', encoding = "ISO-8859-1")
mars_weather_2018_2 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2018_2.csv', encoding = "ISO-8859-1")
mars_weather_2018_3 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2018_3.csv', encoding = "ISO-8859-1")
mars_weather_2019_1 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2019_1.csv', encoding = "ISO-8859-1")
mars_weather_2019_2 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2019_2.csv', encoding = "ISO-8859-1")
mars_weather_2019_3 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2019_3.csv', encoding = "ISO-8859-1")
mars_weather_2019_4 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2019_4.csv', encoding = "ISO-8859-1")
mars_weather_2019_5 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2019_5.csv', encoding = "ISO-8859-1")
mars_weather_2019_6 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/MarsWeather2019_6.csv', encoding = "ISO-8859-1")
                                  
#Connectivity to DB
engine = create_engine('postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')

# Creating of DataFrame
radiometer1df = pd.DataFrame(insight_HP3_RAD1)
radiometer2df = pd.DataFrame(insight_HP3_RAD2)
marsweather2018_1df = pd.DataFrame(mars_weather_2018_1)
marsweather2018_2df = pd.DataFrame(mars_weather_2018_2)
marsweather2018_3df = pd.DataFrame(mars_weather_2018_3)
marsweather2019_1df = pd.DataFrame(mars_weather_2019_1)
marsweather2019_2df = pd.DataFrame(mars_weather_2019_2)
marsweather2019_3df = pd.DataFrame(mars_weather_2019_3)
marsweather2019_4df = pd.DataFrame(mars_weather_2019_4)
marsweather2019_5df = pd.DataFrame(mars_weather_2019_5)
marsweather2019_6df = pd.DataFrame(mars_weather_2019_6)

#Writing to DB
radiometer1df.to_sql('insight_HP3_RAD1', engine)
radiometer2df.to_sql('insight_HP3_RAD2', engine)
marsweather2018_1df.to_sql('mars_weather_2018_1', engine)
marsweather2018_2df.to_sql('mars_weather_2018_2', engine)
marsweather2018_3df.to_sql('mars_weather_2018_3', engine)
marsweather2019_1df.to_sql('mars_weather_2019_1', engine)
marsweather2019_2df.to_sql('mars_weather_2019_2', engine)
marsweather2019_3df.to_sql('mars_weather_2019_3', engine)
marsweather2019_4df.to_sql('mars_weather_2019_4', engine)
marsweather2019_5df.to_sql('mars_weather_2019_5', engine)
marsweather2019_6df.to_sql('mars_weather_2019_6', engine)<jupyter_output><empty_output><jupyter_text>Pulling RAW data files from Postgres<jupyter_code>import psycopg2
import csv
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')

insight_HP3_RAD1 = pd.read_sql_table('insight_HP3_RAD1', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
insight_HP3_RAD2 = pd.read_sql_table('insight_HP3_RAD2', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2018_1 = pd.read_sql_table('mars_weather_2018_1', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2018_2 = pd.read_sql_table('mars_weather_2018_2', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2018_3 = pd.read_sql_table('mars_weather_2018_3', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2019_1 = pd.read_sql_table('mars_weather_2019_1', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2019_2 = pd.read_sql_table('mars_weather_2019_2', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2019_3 = pd.read_sql_table('mars_weather_2019_3', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2019_4 = pd.read_sql_table('mars_weather_2019_4', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2019_5 = pd.read_sql_table('mars_weather_2019_5', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
mars_weather_2019_6 = pd.read_sql_table('mars_weather_2019_6', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')<jupyter_output><empty_output><jupyter_text>Mars Data Analysis<jupyter_code>import pandas as pd
import csv
import seaborn as sns
import numpy as np
import plotly.express as px
import matplotlib.pyplot as plt
import random as rd
import matplotlib
%matplotlib notebook<jupyter_output><empty_output><jupyter_text>Preprocessing LS Data> 2 datafiles obtained from the observations of Insight rover's HP3 and RAD operation were preprocessed to get the data in required format. From the spacecraft clock time, day count of year on earth was calculated and column of the same was added in both files. Then after both the files were merged.<jupyter_code>rad1df = pd.DataFrame(insight_HP3_RAD1)
rad1df['Spacecraft_Clock_time'].astype(str)
rad1df['Spacecraft_Clock_time'] = pd.to_datetime(rad1df["Spacecraft_Clock_time"], format="%Y-%m-%dT%H:%M:%S.%fZ")
rad1df['dayofyear']=rad1df['Spacecraft_Clock_time'].dt.dayofyear
cols = list(rad1df.columns)
cols = [cols[-1]] + cols[:-1]
rad1df = rad1df[cols]
rad1df = rad1df.drop(rad1df.columns[[1,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]],axis = 1)
rad2df = pd.DataFrame(insight_HP3_RAD2)
rad2df['Spacecraft Clock time'].astype(str)
rad2df['Spacecraft Clock time'] = pd.to_datetime(rad2df["Spacecraft Clock time"], format="%Y-%m-%dT%H:%M:%S.%fZ")
rad2df['dayofyear']=rad2df['Spacecraft Clock time'].dt.dayofyear
cols = list(rad2df.columns)
cols = [cols[-1]] + cols[:-1]
rad2df = rad2df[cols]
rad2df = rad2df.drop(rad2df.columns[[1,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]],axis = 1)
frame = [rad1df, rad2df]
LS_table = pd.concat([rad1df, rad2df], ignore_index=True,sort=False)
LS_table.to_csv('Mars_Longitude.csv', index = False)
LS_table.to_sql('Mars_Longitude.csv', engine)<jupyter_output><empty_output><jupyter_text>Preprocessing Mars weather Data> 9 datafiles obtained from the observations of Insight rover's RISE operation were preprocessed to get the data in required format. In order to match the DOY column with LS table, rows with DOY below 344 for 2018 were dropped and rows wirh DOY above 174 for 2019 were dropped. Thenafter all the 9 files were merged into 1 file.<jupyter_code>marsweather2018_1df = pd.DataFrame(mars_weather_2018_1)
marsweather2018_2df = pd.DataFrame(mars_weather_2018_2)
marsweather2018_3df = pd.DataFrame(mars_weather_2018_3)
marsweather2019_1df = pd.DataFrame(mars_weather_2019_1)
marsweather2019_2df = pd.DataFrame(mars_weather_2019_2)
marsweather2019_3df = pd.DataFrame(mars_weather_2019_3)
marsweather2019_4df = pd.DataFrame(mars_weather_2019_4)
marsweather2019_5df = pd.DataFrame(mars_weather_2019_5)
marsweather2019_6df = pd.DataFrame(mars_weather_2019_6)

marsweather2018_1df = marsweather2018_1df[marsweather2018_1df.DOY > 344]
marsweather2018_2df = marsweather2018_2df[marsweather2018_2df.DOY > 344]
marsweather2018_3df = marsweather2018_3df[marsweather2018_3df.DOY > 344]

marsweather2019_4df = marsweather2019_4df[marsweather2019_4df.DOY <= 174]
marsweather2019_5df = marsweather2019_5df[marsweather2019_5df.DOY <= 174]
marsweather2019_6df = marsweather2019_6df[marsweather2019_6df.DOY <= 174]
mars_table = pd.concat([marsweather2018_1df, marsweather2018_2df, marsweather2018_3df, marsweather2019_1df, marsweather2019_2df, marsweather2019_3df, marsweather2019_4df, marsweather2019_5df, marsweather2019_6df], ignore_index=True, sort= False)
mars_table.to_csv('MarsWeather.csv',index = False)
mars_table.to_sql('MarsWeather.csv',engine)<jupyter_output><empty_output><jupyter_text>Merging all data files> 2 datafiles obtained after initial preprocessing were merged together and converted into dataframe.<jupyter_code>MarsLongitude = pd.read_sql_table('Mars_Longitude.csv', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
MarsWeather = pd.read_sql_table('MarsWeather.csv', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')

MarsWeatherdf = pd.DataFrame(MarsWeather)
MarsLongitudedf = pd.DataFrame(MarsLongitude)

MarsWeatherdf = MarsWeatherdf.merge( MarsLongitudedf, how= 'left', left_on='DOY', right_on='dayofyear', sort = False)
MarsWeatherdf = MarsWeatherdf.drop(MarsWeatherdf.columns[11], axis = 1)
MarsWeatherdf = MarsWeatherdf.drop(MarsWeatherdf.columns[12], axis = 1)
MarsWeatherdf.to_csv('Mars_Statistics.csv', index = False)
MarsWeatherdf.to_sql('Mars_Statistics.csv', engine)<jupyter_output><empty_output><jupyter_text>Preprocessing on final data. > Final data obtained was preprocessed to drop unwanted columns and added Vapour Pressure and Relative Humidity column. Duplicate cases were dropped, dataframe was sorted in ascending order depending upon column values of time and day count. A sample was taken to perform the analyisis.<jupyter_code>M_Stat = pd.read_sql_table('Mars_Statistics.csv', 'postgresql://postgres:Omkar@2908@127.0.0.1:5432/marsdb')
M_Statdf = pd.DataFrame(M_Stat)
M_Statdf['Relative Humidity'] = M_Statdf['Relative Humidity (%)']/100
M_Statdf['Vapour Pressure (mB)'] = M_Statdf['Water Partial Pressure (mB)'] / M_Statdf['Relative Humidity']
M_Statdf.dtypes
M_Statdf.fillna(M_Statdf.mean(), inplace=True)
M_Statdf.drop_duplicates(subset = ["DOY","Mars True Local Solar Time"], keep = 'first' , inplace = True)
M_Statdf = M_Statdf.sort_values(['DOY','Mars True Local Solar Time'], ascending= [True, True])
M_Statdf = M_Statdf.drop(M_Statdf.columns[3], axis= 1)
M_Statdf = M_Statdf.drop(M_Statdf.columns[8], axis= 1)
M_Statdf = M_Statdf[M_Statdf.DOY < 345]
M_Stat2df = M_Statdf.drop_duplicates(subset = "DOY", keep = 'first' , inplace = False) 
M_Stat2df
M_Stat2df.to_csv('Mars_Stat_Processed_1.csv', index= False)
M_Stat2 = pd.read_csv('D:/NCI - Database and Analytical Programming/Project/Mars_Stat_Processed_1.csv')
M_Stat2df = pd.DataFrame(M_Stat2)<jupyter_output><empty_output><jupyter_text>Visualization from the data<jupyter_code>##Correlation between Variables

M_Stat2df.corr(method= 'pearson')
##1st Plot

from mpl_toolkits.mplot3d import Axes3D
from matplotlib import cm
from matplotlib.ticker import LinearLocator as ll
from matplotlib.ticker import FormatStrFormatter as fsf

ax = plt.figure().gca(projection='3d')

#Plotting the graph
surf = ax.plot_trisurf(M_Stat2df['Water Partial Pressure (mB)'], M_Stat2df["Vapour Pressure (mB)"], M_Stat2df["Relative Humidity"], cmap= plt.cm.jet,
                       linewidth= 1)
ax.set_xlabel('Water Partial Pressure (mB)')
ax.set_ylabel('Vapour Pressure (mB)')
ax.set_zlabel('Relative Humidity')

#Customizing Z axis
ax.set_zlim(0,0.5)
ax.zaxis.set_major_locator(ll(10))
ax.zaxis.set_major_formatter(fsf('%.02f'))

##2nd Plot

fig1 = plt.figure()
matplotlib.rcParams['agg.path.chunksize'] = 10000
plt.plot(M_Stat2df['DOY'], M_Stat2df['Surface Air Temp (°C)'], linewidth = 0.8,  marker='o', markerfacecolor='green', markersize= 4)
plt.xlabel('Day of year')
plt.ylabel('Surface Air Temperature (°C)')
plt.title('Variation in Temperature (°C)\n on Mars', multialignment = 'center')
##3rd Plot

fig2, ax3 = plt.subplots()
mu = 19.269540            #Mean of Surface Air Temperature
sigma = 8.448063          #Std.Deviation of Surface Air Temperature
median = 18.20000         #Median of Surface Air Temperature

# the histogram of the data
n, bins, patches = ax3.hist(M_Stat2df['Surface Air Temp (°C)'], 50, density=1)

# add a 'best fit' line
y = ((1 / (np.sqrt(2 * np.pi) * sigma)) *
     np.exp(-0.5 * (1 / sigma * (bins - mu))**2))
ax3.plot(bins, y, '--')
ax3.set_xlabel('Surface Air Temp')
ax3.set_ylabel('Probability density')
ax3.set_title(r'Histogram of Temp: $\mu=19.269540$, $\sigma=8.448063$')

# Tweak spacing to prevent clipping of ylabel
fig2.tight_layout()
plt.show()
M_Stat2df.median()
##4th PLot

from scipy.interpolate import griddata

X_dat = M_Stat2df['Solar_Longitude_LS']
Y_dat = M_Stat2df['DOY']
Z_dat = M_Stat2df['Surface Air Temp (°C)']

X, Y, Z, = np.array([]), np.array([]), np.array([])
for i in range(len(X_dat)):
        X = np.append(X, X_dat[i])
        Y = np.append(Y, Y_dat[i])
        Z = np.append(Z, Z_dat[i])

# create x-y points to be used in heatmap
xi = np.linspace(X.min(), X.max(), 1000)
yi = np.linspace(Y.min(), Y.max(), 1000)

# Z is a matrix of x-y values
zi = griddata((X, Y), Z, (xi[None,:], yi[:,None]), method='cubic')

# I control the range of my colorbar by removing data 
# outside of my range of interest
zmin = 3
zmax = 12
zi[(zi<zmin) | (zi>zmax)] = None

# Create the contour plot
CS = plt.contourf(xi, yi, zi, 15, cmap=plt.cm.rainbow,
                  vmax=zmax, vmin=zmin)
plt.xlabel('Solar_Longitude_LS')
plt.ylabel('DOY')
plt.colorbar()  
plt.show()
##5th PLot

ax1 = plt.figure().gca(projection='3d')

#Plotting the graph
surf1 = ax1.plot_trisurf(M_Stat2df['Dew Point (°C)'], M_Stat2df["Surface Air Temp (°C)"], M_Stat2df["DOY"], cmap= plt.cm.jet,
                       linewidth= 1)
ax1.set_xlabel('Dew Point (°C)')
ax1.set_ylabel('Surface Air Temp (°C)')
ax1.set_zlabel('Day of Year')

#Customizing Z axis
ax1.set_zlim(0,175)
ax1.zaxis.set_major_locator(ll(5))
ax1.zaxis.set_major_formatter(fsf('%.02f'))
##6th Plot

fig3 = plt.figure()
matplotlib.rcParams['agg.path.chunksize'] = 10000
plt.plot(M_Stat2df['DOY'], M_Stat2df['Surface pressure (mB)'], linewidth = 0.8,  marker='o', markerfacecolor='green', markersize= 4)
plt.xlabel('Day of year')
plt.ylabel('Surface pressure (mB)')
plt.title('Variation in Pressure (mB)\n on Mars', multialignment = 'center')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12974252312988596, 'fraction_numerical': 0.08405651581438715, 'mean_word_length': 6.9137343927355275, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 16, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '191624', 'license': 'MIT', 'n_tokens_mistral': 6247, 'n_tokens_neox': 5412, 'n_words': 967, 'path': 'dataset/code/Mars/DAP_Project_4.ipynb', 'repo_name': 'Akshen/DA', 'size': '13943'}