This is a just a bit of addition to a previous post, by formatting the Excel output further using the Python XlsxWriter package. The additions are : Colour formatting has been added to the Total Cost column The “Total Cost” has been given a money formatting The above uses row : column numeric values instead…
Untitled This is a quick script on how to save Python pivot_table in an excel file. Credit to pbpython In [1]:
|
1 2 3 4 |
import sqlalchemy import pyodbc from pandas import DataFrame import pandas as pd |
In [2]:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
df = DataFrame ## connect to SqlServer Database and get information. try: import urllib params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};SERVER=.\MSSQLSERVER_ENT;DATABASE=Antrak;Trusted_Connection=yes;") engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect=%s"' % params) resoverall = engine.execute("SELECT * FROM FlightBookingsMain") df = df(resoverall.fetchall()) df.columns = resoverall.keys() except (RuntimeError, TypeError, NameError): print('Error in Conneccting') print(RuntimeError, TypeError, NameError) finally: print("connected") |
|
1 |
connected |
In [8]:
|
1 |
df.head() |
Out[8]: First Name Last Name Sex Phone Fax Email Address Booking Date Departure Date Arrival Date Address1 … Number of Passengers Total Cost Currency Numeric Code…
pivot table for year on year This is a quick example of how to use pivot_table, to calculate year on year percentage sales . In [162]:
|
1 2 3 4 5 6 |
import sqlalchemy import pyodbc import numpy as np from pandas import DataFrame from bokeh.plotting import figure, output_file, show import pandas as pd |
In [163]:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
df = DataFrame ## connect to SqlServer Database and get information. try: import urllib params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};SERVER=.\MSSQLSERVER_ENT;DATABASE=Antrak;Trusted_Connection=yes;") engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect=%s"' % params) resoverall = engine.execute("SELECT * FROM FlightBookingsMain") df = df(resoverall.fetchall()) df.columns = resoverall.keys() except (RuntimeError, TypeError, NameError): print('Error in Conneccting') print(RuntimeError, TypeError, NameError) finally: print("connected") |
|
1 |
connected |
In [164]:
|
1 2 |
#lets check the head df.head() |
Out[164]: First Name Last Name Sex Phone Fax Email Address Booking Date Departure Date Arrival Date Address1 … Number of Passengers…
This is a quick walk through Bokeh data exploration and visualization and also python pivot_tables (credit to pbpython on the pivot_tables).Visualization Dashboard In [49]:
|
1 2 3 4 5 |
import sqlalchemy import pyodbc from pandas import DataFrame from bokeh.plotting import figure, output_file, show import pandas as pd |
In [50]:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
df = DataFrame ## connect to SqlServer Database and get information. try: import urllib params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};SERVER=.\MSSQLSERVER_ENT;DATABASE=Antrak;Trusted_Connection=yes;") engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect=%s"' % params) resoverall = engine.execute("SELECT top 10 * FROM FlightBookingsMain") df = df(resoverall.fetchall()) df.columns = resoverall.keys() except (RuntimeError, TypeError, NameError): print('Error in Conneccting') print(RuntimeError, TypeError, NameError) finally: print("connected") |
|
1 |
connected |
In [51]:
|
1 2 |
#lets look at the head of the dataframe df.head() |
Out[51]: First Name Last Name Sex Phone Fax Email Address Booking Date Departure Date Arrival Date Address1 … Number of Passengers Total Cost Currency…
Group By FunctionThis is a quick look at Python groupby function. Very powerful and useful function. We will take a simple look at it here. Credits to Data School , creator of Python course materials. lets import sample dataset In [18]:
|
1 |
import pandas as pd |
In [19]:
|
1 |
drinks = pd.read_csv('http://bit.ly/drinksbycountry') |
In [20]:
|
1 2 |
#lets check the head drinks.head() |
Out[20]: country beer_servings spirit_servings wine_servings total_litres_of_pure_alcohol continent…
Pandas iloc, loc, and ix functions are very powerful ways to quickly select data from your dataframe. Today , we take a quick look at these 3 functions. Credits to Data School, you can check him out in Youtube In [1]:
|
1 |
import pandas as pd |
In [2]:
|
1 2 |
#lets get this public dataset and play with it ufo = pd.read_csv('http://bit.ly/uforeports') |
In [3]:
|
1 2 |
#lets examine the head ufo.head() |
Out[3]: City Colors Reported Shape Reported State…
In this quick notes, we will go through aggregation in Python. This is part of lectures on Learning Python for Data Analysis and Visualization by Jose Portilla on Udemy.
|
1 2 3 |
import numpy as np import pandas as pd from pandas import Series, DataFrame |
url = “http://archive.ics.uci.edu/ml/machine-learning-databases/wine-quality/” In [2]:
|
1 |
dframe_wine = pd.read_csv('winequality-red.csv', sep=';') |
In [4]:
|
1 |
dframe_wine.head() |
Out[4]: fixed acidity volatile acidity citric acid residual sugar chlorides free sulfur dioxide total sulfur dioxide density…
In this quick notes, we will have a look at Rank and Sort in Series and DataFrames in Python In [1]:
|
1 2 3 |
import numpy as np import pandas as pd from pandas import Series, DataFrame |
In [3]:
|
1 2 3 |
#make a series ser1 = Series(range(3), index=['C','A','B']) ser1 |
Out[3]:
|
1 2 3 4 |
C 0 A 1 B 2 dtype: int32 |
In [4]:
|
1 2 |
#sort index of series ser1.sort_index() |
Out[4]:
|
1 2 3 4 |
A 1 B 2 C 0 dtype: int32 |
In [5]:
|
1 2 |
#order or sort by values ser1.order() |
|
1 2 |
C:\Users\adaba\Anaconda3\lib\site-packages\ipykernel\__main__.py:2: FutureWarning: order is deprecated, use sort_values(...) from ipykernel import kernelapp as app |
Out[5]:
|
1 2 3 4 |
C 0 A 1 B 2 dtype: int32 |
In [6]:
|
1 |
ser1.sort_values() |
Out[6]:
|
1 2 3 4 |
C 0 A 1 B 2 dtype: int32 |
In [7]:
|
1 |
from numpy.random import randn |
In [8]:
|
1 2 3 |
#make a series by passing in 10 random numbers ser2 = Series(randn(10)) ser2 |
Out[8]:
|
1 2 3 4 5 6 7 8 9 10 11 |
0 -0.201751 1 1.534109 2 0.542825 3 1.421174 4 -0.173771 5 0.002573 6 -0.490208 7 -0.765340 8 -2.546040 9 -0.456699 dtype: float64 |
In [10]:
|
1 2 |
#sort the values ser2.sort_values() |
…
This week we have a look at Data Alignment in Python In [1]:
|
1 2 3 |
import numpy as np import pandas as pd from pandas import Series, DataFrame |
In [2]:
|
1 2 3 |
#create series ser1 = Series([0,1,3], index=['A','B','C']) ser1 |
Out[2]:
|
1 2 3 4 |
A 0 B 1 C 3 dtype: int64 |
In [3]:
|
1 2 |
ser2 = Series([3,4,5,6], index=['A','B','C','D']) ser2 |
Out[3]:
|
1 2 3 4 5 |
A 3 B 4 C 5 D 6 dtype: int64 |
In [4]:
|
1 2 |
#adding two series which have different lenghts ser1 + ser2 |
Out[4]:
|
1 2 3 4 5 |
A 3.0 B 5.0 C 8.0 D NaN dtype: float64 |
In [6]:
|
1 2 3 4 |
#lets try and add dataframes #create dataframe dframe = DataFrame(np.arange(4).reshape((2,2)), columns=list('AB'), index=['LA','GA']) dframe |
Out[6]: A B LA 0 1 GA 2 3 In [10]:
|
1 2 3 |
#create a second dataframe dframe2 = DataFrame(np.arange(9).reshape((3,3)), columns=list('ABC'), index=['LA','NYC','GA']) dframe2 |
Out[10]: A B C LA 0 1 2 NYC 3 4…
An experienced DevOps and Cloud Training Company to meet your DevOps and Cloud needs
You can see how this popup was set up in our step-by-step guide: https://wppopupmaker.com/guides/auto-opening-announcement-popups/
