import pandas as pd
import numpy as np

"""
Explanation: <span style="color: #B40486">BASIC PYTHON FOR RESEARCHERS</span>
by Megat Harun Al Rashid bin Megat Ahmad
last updated: April 14, 2016

<span style="color: #29088A">8. Database and Data Analysis</span>

<span style="color: #0000FF">$Pandas$</span> is an open source library for data analysis in Python. It gives Python similar capabilities to R programming language  and even though it is possible to run R in Jupyter Notebook, it would be more practical to do data analysis with a Python friendly syntax. Similar to other libraries, the first step to use <span style="color: #0000FF">$Pandas$</span> is to import the library and usually together with the <span style="color: #0000FF">$Numpy$</span> library.
End of explanation
"""


# Creating a series (with different type of data)

s1 = pd.Series([34, 'Material', 4*np.pi, 'Reactor', [100,250,500,750], 'kW'])
s1

"""
Explanation: 8.1 Data Structures
Data structures (similar to Sequence in Python) of <span style="color: #0000FF">$Pandas$</span> revolves around the Series and DataFrame structures. Both are fast as they are built on top of <span style="color: #0000FF">$Numpy$</span>.
A Series is a one-dimensional object with a lot of similar properties similar to a list or dictionary in Python's Sequence. Each element or item in a Series will be assigned by default an index label from 0 to N-1 (where N is the length of the Series) and it can contains the various type of Python's data.
End of explanation
"""


# Creating a series with specified index

lt = [34, 'Material', 4*np.pi, 'Reactor', [100,250,500,750], 'kW']

s2 = pd.Series(lt, index = ['b1', 'r1', 'solid angle', 18, 'reactor power', 'unit'])
s2

"""
Explanation: The index of a Series can be specified during its creation and giving it a similar function to a dictionary.
End of explanation
"""


s1[3], s2['solid angle']

"""
Explanation: Data can be extracted by specifying the element position or index (similar to list/dictionary).
End of explanation
"""


pop_cities = {'Kuala Lumpur':1588750, 'Seberang Perai':818197, 'Kajang':795522,
              'Klang':744062, 'Subang Jaya':708296}
cities = pd.Series(pop_cities)
cities

"""
Explanation: Series can also be constructed from a dictionary.
End of explanation
"""


cities.order(ascending=False)

cities

"""
Explanation: The elements can be sort using the <span style="color: #0000FF">$Series.order()$</span> function. This will not change the structure of the original variable.
End of explanation
"""


# Sorting with descending values

cities.sort(ascending=False)
cities

cities

"""
Explanation: Another sorting function is the <span style="color: #0000FF">$sort()$</span> function but this will change the structure of the Series variable.
End of explanation
"""


# cities with population less than 800,000
cities[cities<800000]                

# cities with population between 750,000 and 800,000
cities[cities<800000][cities>750000]

"""
Explanation: Conditions can be applied to the elements.
End of explanation
"""


# Creating a DataFrame by passing a 2-D numpy array of random number 

# Creating first the date-time index using date_range function
# and checking it.
dates = pd.date_range('20140801', periods = 8, freq = 'D')
dates

# Creating the column names as list
Kedai = ['Kedai A', 'Kedai B', 'Kedai C', 'Kedai D', 'Kedai E']

# Creating the DataFrame with specified rows and columns
df = pd.DataFrame(np.random.randn(8,5),index=dates,columns=Kedai)
df

"""
Explanation: A DataFrame is a 2-dimensional data structure with named rows and columns. It is similar to  R's data.frame object and function like a spreadsheet. DataFrame can be considered to be made of series of Series data according to the column names. DataFrame can be created by passing a 2-dimensional array of data and specifying the rows and columns names.
End of explanation
"""


df.head()             # Displaying the first five (default) rows


df.head(3)             # Displaying the first three (specified) rows


df.tail(2)             # Displaying the last two (specified) rows


df.index               # Showing the index of rows


df.columns             # Showing the fields of columns


df.values              # Showing the data only in its original 2-D array


df.describe()          # Simple statistical data for each column


df.T                    # Transposing the DataFrame (index becomes column and vice versa)


df.sort_index(axis=1,ascending=False)     # Sorting with descending column 

df.sort(columns='Kedai D')               # Sorting according to ascending specific column

df['Kedai A']             # Extract specific column (using python list syntax)

df['Kedai A'][2:4]        # Slicing specific column (using python list syntax)


df[2:4]                   # Slicing specific row data  (using python list syntax)

# Slicing specific index range
df['2014-08-03':'2014-08-05']       

# Slicing specific index range for a particular column
df['2014-08-03':'2014-08-05']['Kedai B']       

# Using the loc() function

# Slicing specific index and column ranges
df.loc['2014-08-03':'2014-08-05','Kedai B':'Kedai D']

# Slicing specific index range with specific column names
df.loc['2014-08-03':'2014-08-05',['Kedai B','Kedai D']]

# Possibly not yet to have something like this

df.loc[['2014-08-01','2014-08-03':'2014-08-05'],['Kedai B','Kedai D']]


# Using the iloc() function

df.iloc[3]                 # Specific row location

df.iloc[:,3]               # Specific column location (all rows)

df.iloc[2:4,1:3]           # Python like slicing for range

df.iloc[[2,4],[1,3]]        # Slicing with python like list

# Conditionals on the data

df>0                        # Array values > 0 OR

df[df>0]                    # Directly getting the value

"""
Explanation: Some of the useful functions that can be applied to a DataFrame include:
End of explanation
"""


df[df['Kedai B']<0]           # With reference to specific value in a column (e.g. Kedai B)

df2 = df.copy()             # Made a copy of a database

df2

# Adding column

df2['Tambah'] = ['satu','satu','dua','tiga','empat','tiga','lima','enam'] 

df2

# Adding row using append() function. The previous loc() is possibly deprecated.

# Assign a new name to the new row (with the same format)
new_row_name = pd.date_range('20140809', periods = 1, freq = 'D')

# Appending new row with new data
df2.append(list(np.random.randn(5))+['sembilan'])

# Renaming the new row (here actually is a reassignment)
df2 = df2.rename(index={10: new_row_name[0]})
df2

# Assigning new data to a row
df2.loc['2014-08-05'] = list(np.random.randn(5))+['tujuh']
df2

# Assigning new data to a specific element
df2.loc['2014-08-05','Tambah'] = 'lapan'
df2

# Using the isin() function (returns boolean data frame)
df2.isin(['satu','tiga'])

# Select specific row based on additonal column
df2[df2['Tambah'].isin(['satu','tiga'])]

# Use previous command - select certain column based on selected additional column
df2[df2['Tambah'].isin(['satu','tiga'])].loc[:,'Kedai B':'Kedai D']

# Select > 0 from previous cell...
(df2[df2['Tambah'].isin(['satu','tiga'])].loc[:,'Kedai B':'Kedai D']>0)

"""
Explanation: NaN means empty, missing data or unavailable.
End of explanation
"""


df.mean()                    # Statistical mean (column) - same as df.mean(0), 0 means column

df.mean(1)                   # Statistical mean (row) - 1 means row

df.mean()['Kedai C':'Kedai E']         # Statistical mean (range of columns)

df.max()                               # Statistical max (column)

df.max()['Kedai C']                    # Statistical max (specific column)

df.max(1)['2014-08-04':'2014-08-07']   # Statistical max (specific row)

df.max(1)[dates[3]]          # Statistical max (specific row by variable)

"""
Explanation: 8.2 Data Operations
We have seen few operations previously on Series and DataFrame and here this will be explored further.
End of explanation
"""


df.apply(lambda x: x.max() - x.min())       # Operating array values with function

df.apply(lambda z: np.log(z))               # Operating array values with function

"""
Explanation: Other statistical functions can be checked by typing df.< TAB >.
The data in a DataFrame can be represented by a variable declared using the <span style="color: #0000FF">$lambda$</span> operator.
End of explanation
"""


df3 = df.copy()

df3[r'Kedai A^2/Kedai E'] = df3['Kedai A']**2/df3['Kedai E']
df3

"""
Explanation: Replacing, rearranging and operations of data between columns can be done much like spreadsheet.
End of explanation
"""


df4 = df.copy()
df4

pieces = [df4[6:], df4[3:6], df4[:3]]   # split row 2+3+3
pieces

df5 = pd.concat(pieces)                 # concantenate (rearrange/combine)
df5

df4+df5             # Operation between tables with original index sequence

df0 = df.loc[:,'Kedai A':'Kedai C']     # Slicing and extracting columns
pd.concat([df4, df0], axis = 1)         # Concatenating columns (axis = 1 -> refers to column)

"""
Explanation: Tables can be split, rearranged and combined.
End of explanation
"""


df_add = df.copy()

# Simple auto plotting
%matplotlib inline

df_add.cumsum().plot()

# Reposition the legend

import matplotlib.pyplot as plt

df_add.cumsum().plot()
plt.legend(bbox_to_anchor=[1.3, 1])

"""
Explanation: 8.3 Plotting Functions

Let us look on some of the simple plotting function on <span style="color: #0000FF">$Pandas$</span> (requires <span style="color: #0000FF">$Matplotlib$</span> library).
End of explanation
"""


df_add.cumsum().plot(kind='bar')
plt.legend(bbox_to_anchor=[1.3, 1])

df_add.cumsum().plot(kind='barh', stacked=True)

df_add.cumsum().plot(kind='hist', alpha=0.5)

df_add.cumsum().plot(kind='area', alpha=0.4, stacked=False)
plt.legend(bbox_to_anchor=[1.3, 1])

"""
Explanation: In the above example, repositioning the legend requires the legend function in <span style="color: #0000FF">$Matplotlib$</span> library. Therefore, the <span style="color: #0000FF">$Matplotlib$</span> library must be explicitly imported.
End of explanation
"""


# Plotting a 3D bar plot

from mpl_toolkits.mplot3d import Axes3D
import numpy as np

# Convert the time format into ordinary strings
time_series = pd.Series(df.index.format())

fig = plt.figure(figsize=(8,6))
ax = fig.add_subplot(111, projection='3d')

# Plotting the bar graph column by column
for c, z in zip(['r', 'g', 'b', 'y','m'], np.arange(len(df.columns))):
    xs = df.index
    ys = df.values[:,z]
    ax.bar(xs, ys, zs=z, zdir='y', color=c, alpha=0.5)
    
ax.set_zlabel('Z')
ax.set_xticklabels(time_series, va = 'baseline', ha = 'right', rotation = 15)
ax.set_yticks(np.arange(len(df.columns)))
ax.set_yticklabels(df.columns, va = 'center', ha = 'left', rotation = -42)

ax.view_init(30, -30)

fig.tight_layout()

"""
Explanation: A 3-dimensional plot can be projected on a canvas but requires the <span style="color: #0000FF">$Axes3D$</span> library with slightly complicated settings.
End of explanation
"""


# Export data to a csv file but separated with < TAB > rather than comma
# the default separation is with comma

df.to_csv('Tutorial8/Kedai.txt', sep='\t')

# Export to Excel file

df.to_excel('Tutorial8/Kedai.xlsx', sheet_name = 'Tarikh', index = True)

# Importing data from csv file (without header)

from_file = pd.read_csv('Tutorial8/Malaysian_Town.txt',sep='\t',header=None)
from_file.head()

# Importing data from Excel file (with header (the first row) that became the column names)

from_excel = pd.read_excel('Tutorial8/Malaysian_Town.xlsx','Sheet1')
from_excel.head()

"""
Explanation: 8.4 Reading And Writing Data To File
Data in DataFrame can be exported into csv (comma separated value) and Excel file. The users can also create a DataFrame from data in csv and Excel file, the data can then be processed.
End of explanation
"""


