Sample

Python
R
SQL
MySQL
PostgreSQL
SQL Server
SQLite
Tableau
PowerBI
Excel
Author

Sumendar

Published

March 9, 2023

Excel:

Excel Chart Emeding from Google Drive’s Sheet:

Only Chart Direct copied from Google Embed Code without File:

R Programming:

Show the code 
library(dplyr)
library(ggplot2)
iris1 <- iris %>% filter(Sepal.Length <= 5.4)
head(iris1, 4L)
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
Show the code 
ggplot(mtcars, aes(factor(cyl), mpg)) + geom_point()

Show the code 
# install.packages("tidyverse")
# install.packages("palmerpenguins")
library(tidyverse)
Warning: package 'tidyverse' was built under R version 4.2.3
Warning: package 'tidyr' was built under R version 4.2.3
Warning: package 'readr' was built under R version 4.2.3
Warning: package 'forcats' was built under R version 4.2.3
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ lubridate 1.9.2     ✔ tibble    3.1.8
✔ purrr     1.0.1     ✔ tidyr     1.3.0
✔ readr     2.1.4     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
Show the code 
library(palmerpenguins)
Warning: package 'palmerpenguins' was built under R version 4.2.3
Show the code 
glimpse(penguins)
Rows: 344
Columns: 8
$ species           <fct> Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adel…
$ island            <fct> Torgersen, Torgersen, Torgersen, Torgersen, Torgerse…
$ bill_length_mm    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39.2, 34.1, …
$ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19.6, 18.1, …
$ flipper_length_mm <int> 181, 186, 195, NA, 193, 190, 181, 195, 193, 190, 186…
$ body_mass_g       <int> 3750, 3800, 3250, NA, 3450, 3650, 3625, 4675, 3475, …
$ sex               <fct> male, female, female, NA, female, male, female, male…
$ year              <int> 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007…
Show the code 
penguins %>% 
  group_by(species) %>% 
  summarize(across(where(is.numeric), mean, na.rm = TRUE))
# A tibble: 3 × 6
  species   bill_length_mm bill_depth_mm flipper_length_mm body_mass_g  year
  <fct>              <dbl>         <dbl>             <dbl>       <dbl> <dbl>
1 Adelie              38.8          18.3              190.       3701. 2008.
2 Chinstrap           48.8          18.4              196.       3733. 2008.
3 Gentoo              47.5          15.0              217.       5076. 2008.
Show the code 
ggplot(penguins, aes(x = body_mass_g, y = flipper_length_mm)) +
    geom_point()

Inline code in R Programming

The mtcars dataset has 32 rows

The mtcars dataset has an average miles/per gallon of 20.090625

Interactive Graph in R Programming:

Show the code 
#install.packages("plotly")
#install.packages("gapminder")
#install.packages("ggplot2")
library(ggplot2)
library(plotly)
library(gapminder)

p <- gapminder %>%
  filter(year==1977) %>%
  ggplot( aes(gdpPercap, lifeExp, size = pop, color=continent)) +
  geom_point() +
  theme_bw()

ggplotly(p)

SQL within R (MySQL):

Show the code 
#install.packages("RMySQL")

library(RMySQL)
Warning: package 'RMySQL' was built under R version 4.2.3
Loading required package: DBI
Show the code 
mysqlconnection = dbConnect(RMySQL::MySQL(),
                            dbname='world',
                            host='localhost',
                            port=3306,
                            user='root',
                            password='sumendar')
result = dbSendQuery(mysqlconnection, "select * from city") # write query to acces the records from a particular table.
data.frame = fetch(result, n = 5)
print(data.frame)
  ID           Name CountryCode      District Population
1  1          Kabul         AFG         Kabol    1780000
2  2       Qandahar         AFG      Qandahar     237500
3  3          Herat         AFG         Herat     186800
4  4 Mazar-e-Sharif         AFG         Balkh     127800
5  5      Amsterdam         NLD Noord-Holland     731200

SQL within R (PostgreSQL):

Show the code 
library(DBI)
library(RPostgres)
con <- DBI::dbConnect(
  RPostgres::Postgres(),
  dbname = 'tutorialdb',
  host = 'localhost',
  port = 5432,
  user = 'postgres',
  password = 'sumendar' # insert your password here
)
DBI::dbGetQuery(con, 'SELECT customer_id, name, location, email FROM customers LIMIT 3')
  customer_id    name  location                      email
1           1 Orlando Australia                           
2           2   Keith     India keith0@adventure-works.com
3           3   Donna   Germany donna0@adventure-works.com

SQL within R (SQL Server):

How to connect R to SQL Server to Query Your Database:
https://medium.com/@sebastiansm7/how-to-connect-r-to-sql-server-to-query-your-databse-de986a640a0d

Show the code 
#install.packages("DBI")
#install.packages("ODBC")
library(DBI)
library(odbc)
Warning: package 'odbc' was built under R version 4.2.3
Show the code 
con <- dbConnect(odbc::odbc(), ".")
Show the code 
data1 <- dbGetQuery(con,'select * from Training.dbo.City')
head(data1)
  CityID CityCode   CityName PopulationDensity
1      1      ADI  Ahmedabad              High
2      2      BLR  Bangalore              High
3      3      CHD Chandigarh               Low
4      4      CHN    Chennai              High
5      5      DDN   Dehradun            Medium
6      6      DEL      Delhi              High

SQL within R (SQLite):

Show the code 
library(DBI)
# Create an ephemeral in-memory RSQLite database
con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbListTables(con)
character(0)
Show the code 
library(DBI)
# Create existed RSQLite database file connections
con <- dbConnect(RSQLite::SQLite(), "C:\\sqlite\\data\\chinook_database\\chinook.db")
dbListTables(con)
 [1] "albums"          "artists"         "customers"       "employees"      
 [5] "genres"          "invoice_items"   "invoices"        "media_types"    
 [9] "playlist_track"  "playlists"       "sqlite_sequence" "sqlite_stat1"   
[13] "tracks"
Show the code 
dbListFields(con, "employees")
 [1] "EmployeeId" "LastName"   "FirstName"  "Title"      "ReportsTo" 
 [6] "BirthDate"  "HireDate"   "Address"    "City"       "State"     
[11] "Country"    "PostalCode" "Phone"      "Fax"        "Email"
Show the code 
dbReadTable(con, "employees")
  EmployeeId LastName FirstName               Title ReportsTo
1          1    Adams    Andrew     General Manager        NA
2          2  Edwards     Nancy       Sales Manager         1
3          3  Peacock      Jane Sales Support Agent         2
4          4     Park  Margaret Sales Support Agent         2
5          5  Johnson     Steve Sales Support Agent         2
6          6 Mitchell   Michael          IT Manager         1
7          7     King    Robert            IT Staff         6
8          8 Callahan     Laura            IT Staff         6
            BirthDate            HireDate                     Address
1 1962-02-18 00:00:00 2002-08-14 00:00:00         11120 Jasper Ave NW
2 1958-12-08 00:00:00 2002-05-01 00:00:00                825 8 Ave SW
3 1973-08-29 00:00:00 2002-04-01 00:00:00               1111 6 Ave SW
4 1947-09-19 00:00:00 2003-05-03 00:00:00            683 10 Street SW
5 1965-03-03 00:00:00 2003-10-17 00:00:00                7727B 41 Ave
6 1973-07-01 00:00:00 2003-10-17 00:00:00        5827 Bowness Road NW
7 1970-05-29 00:00:00 2004-01-02 00:00:00 590 Columbia Boulevard West
8 1968-01-09 00:00:00 2004-03-04 00:00:00                 923 7 ST NW
        City State Country PostalCode             Phone               Fax
1   Edmonton    AB  Canada    T5K 2N1 +1 (780) 428-9482 +1 (780) 428-3457
2    Calgary    AB  Canada    T2P 2T3 +1 (403) 262-3443 +1 (403) 262-3322
3    Calgary    AB  Canada    T2P 5M5 +1 (403) 262-3443 +1 (403) 262-6712
4    Calgary    AB  Canada    T2P 5G3 +1 (403) 263-4423 +1 (403) 263-4289
5    Calgary    AB  Canada    T3B 1Y7  1 (780) 836-9987  1 (780) 836-9543
6    Calgary    AB  Canada    T3B 0C5 +1 (403) 246-9887 +1 (403) 246-9899
7 Lethbridge    AB  Canada    T1K 5N8 +1 (403) 456-9986 +1 (403) 456-8485
8 Lethbridge    AB  Canada    T1H 1Y8 +1 (403) 467-3351 +1 (403) 467-8772
                     Email
1   andrew@chinookcorp.com
2    nancy@chinookcorp.com
3     jane@chinookcorp.com
4 margaret@chinookcorp.com
5    steve@chinookcorp.com
6  michael@chinookcorp.com
7   robert@chinookcorp.com
8    laura@chinookcorp.com
Show the code 
# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM employees WHERE Title = 'IT Staff'")
dbFetch(res)
  EmployeeId LastName FirstName    Title ReportsTo           BirthDate
1          7     King    Robert IT Staff         6 1970-05-29 00:00:00
2          8 Callahan     Laura IT Staff         6 1968-01-09 00:00:00
             HireDate                     Address       City State Country
1 2004-01-02 00:00:00 590 Columbia Boulevard West Lethbridge    AB  Canada
2 2004-03-04 00:00:00                 923 7 ST NW Lethbridge    AB  Canada
  PostalCode             Phone               Fax                  Email
1    T1K 5N8 +1 (403) 456-9986 +1 (403) 456-8485 robert@chinookcorp.com
2    T1H 1Y8 +1 (403) 467-3351 +1 (403) 467-8772  laura@chinookcorp.com

Python Programming:

Markdown echo:false cell:

<IPython.core.display.Markdown object>
Show the code 
import matplotlib.pyplot as plt

fig, ax = plt.subplots()

fruits = ['apple', 'blueberry', 'cherry', 'orange']
counts = [40, 100, 30, 55]
bar_labels = ['red', 'blue', '_red', 'orange']
bar_colors = ['tab:red', 'tab:blue', 'tab:red', 'tab:orange']

ax.bar(fruits, counts, label=bar_labels, color=bar_colors)
<BarContainer object of 4 artists>
Show the code 
ax.set_ylabel('fruit supply')
ax.set_title('Fruit supply by kind and color')
ax.legend(title='Fruit color')

plt.show()

Interactive Graph in Python Programming:

Show the code 
import plotly.express as px
import plotly.io as pio
df = px.data.iris()
fig = px.scatter(df, x="sepal_width", y="sepal_length", 
                 color="species", 
                 marginal_y="violin", marginal_x="box", 
                 trendline="ols", template="simple_white")
fig.show()

SQL within Python (SQLite with SQLite3 module)

Show the code 
import sqlite3 # this package is used to communicate with sqlite3
import pandas as pd

# Connect to the database
con = sqlite3.connect('chinook.db')
# Run SQL          
sql_query = pd.read_sql('SELECT * FROM invoice_items', con)
# Convert SQL to DataFrame
df = pd.DataFrame(sql_query)
print(df)
      InvoiceLineId  InvoiceId  TrackId  UnitPrice  Quantity
0                 1          1        2       0.99         1
1                 2          1        4       0.99         1
2                 3          2        6       0.99         1
3                 4          2        8       0.99         1
4                 5          2       10       0.99         1
...             ...        ...      ...        ...       ...
2235           2236        411     3136       0.99         1
2236           2237        411     3145       0.99         1
2237           2238        411     3154       0.99         1
2238           2239        411     3163       0.99         1
2239           2240        412     3177       1.99         1

[2240 rows x 5 columns]

SQL within Python (SQLite with SQLAlchemy)

Show the code 
#!pip install sqlalchemy or conda install sqlalchemy 
import sqlalchemy as db
# datasource downloaded from https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources
# datasource downloaded from local drive path C:/sqlite/data/chinook_database/
# engine = db.create_engine("sqlite:///Chinook_Sqlite.sqlite")
engine1 = db.create_engine("sqlite:///C:/sqlite/data/chinook_database/chinook.db")
# read it as a dataframe
import pandas as pd
df = pd.read_sql_table('albums', engine1)
print(df.head())
   AlbumId                                  Title  ArtistId
0        1  For Those About To Rock We Salute You         1
1        2                      Balls to the Wall         2
2        3                      Restless and Wild         2
3        4                      Let There Be Rock         1
4        5                               Big Ones         3
Show the code 
connection = engine1.connect()
metadata = db.MetaData()
customers = db.Table('customers', metadata, autoload=True, autoload_with=engine1)
print(type(customers))
<class 'sqlalchemy.sql.schema.Table'>
Show the code 
print(customers.columns.keys())
['CustomerId', 'FirstName', 'LastName', 'Company', 'Address', 'City', 'State', 'Country', 'PostalCode', 'Phone', 'Fax', 'Email', 'SupportRepId']

SQL within Python (MySQL Server with SQLAlchemy)

Show the code 
# IMPORT THE SQALCHEMY LIBRARY's CREATE_ENGINE METHOD
from sqlalchemy import create_engine

# DEFINE THE DATABASE CREDENTIALS
user = 'root'
password = 'sumendar'   # you can change your pswrd whatever u gave while installing MySQL
host = '127.0.0.1'
port = 3306
database = 'world'

# PYTHON FUNCTION TO CONNECT TO THE MYSQL DATABASE AND
# RETURN THE SQLACHEMY ENGINE OBJECT
def get_connection():
    return create_engine(
        url="mysql+pymysql://{0}:{1}@{2}:{3}/{4}".format(
            user, password, host, port, database
        )
    )


if __name__ == '__main__':

    try:
        
        # GET THE CONNECTION OBJECT (ENGINE) FOR THE DATABASE
        engine2 = get_connection()
        print(
            f"Connection to the {host} for user {user} created successfully.")
    except Exception as ex:
        print("Connection could not be made due to the following error: \n", ex)
Connection to the 127.0.0.1 for user root created successfully.
Show the code 
connection = engine2.connect()
metadata = db.MetaData()
city = db.Table('city', metadata, autoload=True, autoload_with=engine2)
print(city.columns.keys())
['ID', 'Name', 'CountryCode', 'District', 'Population']
Show the code 
print(type(city))
<class 'sqlalchemy.sql.schema.Table'>
Show the code 
city1  = pd.read_sql_table(city, connection)
print(city1)    
        ID            Name CountryCode       District  Population
0        1           Kabul         AFG          Kabol     1780000
1        2        Qandahar         AFG       Qandahar      237500
2        3           Herat         AFG          Herat      186800
3        4  Mazar-e-Sharif         AFG          Balkh      127800
4        5       Amsterdam         NLD  Noord-Holland      731200
...    ...             ...         ...            ...         ...
4074  4075      Khan Yunis         PSE     Khan Yunis      123175
4075  4076          Hebron         PSE         Hebron      119401
4076  4077        Jabaliya         PSE     North Gaza      113901
4077  4078          Nablus         PSE         Nablus      100231
4078  4079           Rafah         PSE          Rafah       92020

[4079 rows x 5 columns]

SQL within Python (PostgreSQL with SQLAlchemy)

Show the code 
# IMPORT THE SQALCHEMY LIBRARY's CREATE_ENGINE METHOD
from sqlalchemy import create_engine

# DEFINE THE DATABASE CREDENTIALS
user = 'postgres'
password = 'sumendar'
host = '127.0.0.1'
port = 5432
database = 'tutorialdb'

# PYTHON FUNCTION TO CONNECT TO THE POSTGRESQL DATABASE AND
# RETURN THE SQLACHEMY ENGINE OBJECT
def get_connection():
    return create_engine(
        url="postgresql://{0}:{1}@{2}:{3}/{4}".format(
            user, password, host, port, database
        )
    )


if __name__ == '__main__':

    try:
        # GET THE CONNECTION OBJECT (ENGINE) FOR THE DATABASE
        engine3 = get_connection()
        print(
            f"Connection to the {host} for user {user} created successfully.")
    except Exception as ex:
        print("Connection could not be made due to the following error: \n", ex)
Connection to the 127.0.0.1 for user postgres created successfully.
Show the code 
connection = engine3.connect()
metadata = db.MetaData()
customers = db.Table('customers', metadata, autoload=True, autoload_with=engine3)
print(type(customers))
<class 'sqlalchemy.sql.schema.Table'>
Show the code 
print(customers.columns.keys())
['customer_id', 'name', 'location', 'email']
Show the code 
df = pd.read_sql_query('select * from "customers"',con=engine3)
print(df.head())
   customer_id     name       location                       email
0            1  Orlando      Australia                            
1            2    Keith          India  keith0@adventure-works.com
2            3    Donna        Germany  donna0@adventure-works.com
3            4    Janet  United States  janet1@adventure-works.com

SQL within Python (SQLServer with SQLAlchemy)

Show the code 
#import pyodbc
import sqlalchemy as sal
from sqlalchemy import create_engine
import pandas as pd
Server = 'INHYDLP8LM08R1'
Database = 'Training'
Driver = 'ODBC Driver 17 for SQL Server'
Database_Con = f'mssql://@{Server}/{Database}?driver={Driver}' 
engine4 = create_engine(Database_Con)
con =engine4.connect()
df = pd.read_sql_query("select * from [dbo].[City]", con)
df
    CityID CityCode    CityName PopulationDensity
0        1      ADI   Ahmedabad              High
1        2      BLR   Bangalore              High
2        3      CHD  Chandigarh               Low
3        4      CHN     Chennai              High
4        5      DDN    Dehradun            Medium
5        6      DEL       Delhi              High
6        7      JPR      Jaipur            Medium
7        8      KOL     Kolkata              High
8        9      MUM      Mumbai              High
9       10      MYS      Mysore               Low
10      11      PDY  Puducherry               Low
11      12      RNC      Ranchi            Medium

SQL within Python (MySQL Server with pymysql package)

Show the code 
import pymysql
import pandas as pd

conn=pymysql.connect(host='localhost',port=int(3306),user='root',passwd='sumendar',db='imdb')

df=pd.read_sql_query("SELECT * FROM imdb.movie",conn)
C:\Users\ADMINI~1\ANACON~1\lib\site-packages\pandas\io\sql.py:762: UserWarning:

pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
Show the code 
print(type(df))
<class 'pandas.core.frame.DataFrame'>
Show the code 
df.head()    
          id  ...        production_company
0  tt0012494  ...          Decla-Bioscop AG
1  tt0038733  ...               The Archers
2  tt0060908  ...                 Fildebroc
3  tt0069049  ...  Royal Road Entertainment
4  tt0071145  ...    Blaze Film Enterprises

[5 rows x 9 columns]

SQL within Python (MySQL Server with mysql.connector package)

Show the code 
# try later for this

Azure Data Studio(md/ipynb) for SQL

MySQL, SQL Server and PostgreSQL Coding (Direct copied from Azure Data Studio After executing output got there then pasted here)

Show the code 
# try later for this

Tableau Chart Embed:

Embedding Tableau Public Views in iFrame source:

Power BI Chart Embed:

Embedding Power BI Report in iFrame source:

Various Media Contents Embed