Data Science - Pandas- Series and Dataframe

Thsi Post have the complete syntax and code wise details of Pandas two Major concept Series and Data Frame.

Now Lets Start-

Thsi Post have the complete syntax and code wise details of Pandas two Major concept Series and Data Frame.
Now Lets Start-
# Pandas Series
#Series is a one-dimensional labeled array capable of
#holding data of any type (integer, string, float, python objects, etc.).
# The axis labels are collectively called index
In [1]:
1 import pandas as pd
2 import numpy as np
In [2]:
# A pandas Series can be created using the following constructor −
# pandas.Series( data, index, dtype, copy)
3 df = pd.Series()
4 df
Out[2]:
Series([], dtype: float64)
In [3]:
# A series can be created using various inputs like −
# Array
# Dict
# Scalar value or constant
In [4]:
# Create a Series from ndarray
    data = np.array(['D','I','Z','S','W','E','B'])
    s = pd.Series(data)
    s
Out[4]:
0 D 1 I 2 Z 3 S 4 W 5 E 6 B dtype: object
In [6]:
# Create a Series from ndarray
    data = np.array(['D','I','Z','S','W','E','B'])
    s = pd.Series(data, index =['001','002','003','004','005','006','007'])
    s
Out[6]:
    001 D 002 I 003 Z 004 S 005 W 006 E 007 B dtype: object
In [7]:
# Create a Series from dict
    data = {'D':1,'I':2,'Z':3,'S':4,'W':5.0,'E':6,'B':7.0}
    s = pd.Series(data)
    s
Out[7]:
D 1.0 I 2.0 Z 3.0 S 4.0 W 5.0 E 6.0 B 7.0 dtype: float64
In [8]:
# Create a Series from dict
    data = {'D':1,'I':2,'Z':3,'S':4,'W':5.0,'E':6,'B':7.0}
    s = pd.Series(data, index =['001','002','003','004','005','006','007'])
    s
# output will be NaN becuase in case of dictionary
# if the values not match with index it gices NaN
Out[8]:
001 NaN 002 NaN 003 NaN 004 NaN 005 NaN 006 NaN 007 NaN dtype: float64
In [9]:
# Create a Series from dict
    data = {'D':1,'I':2,'Z':3,'S':4,'W':5.0,'E':6,'B':7.0}
    s = pd.Series(data, index =['D','002','Z','004','W','006','B'])
    s
Out[9]:
    D 1.0 002 NaN Z 3.0 004 NaN W 5.0 006 NaN B 7.0 dtype: float64
In [12]:
# Create a Series from Scalar
    s = pd.Series(3, index = ['1','2','3','4','5','6'])
    s
Out[12]:
1 3 2 3 3 3 4 3 5 3 6 3 dtype: int64
In [13]:
# Create a Series from Scalar without index
    s = pd.Series(3)
    s
Out[13]:
0 3 dtype: int64
In [14]:
# Accessing Data from Series with Position
    s = pd.Series([1,2,3,4,5,6,7],index = ['D','I','Z','S','W','E','B'])
#retrieve the first three element
    print s[:3]
File "<ipython-input-14-fbdfbded2fb7>", line 2 s = pd.Series([1,2,3,4,5,,6,7],index = ['D','I','Z','S','W','E','B']) ^ SyntaxError: invalid syntax
In [16]:
# Accessing Data from Series with Position
    s = pd.Series([1,2,3,4,5,6,7],index = ['D','I','Z','S','W','E','B'])
# Retrieve the first three element
    print(s[:3])
    D 1 I 2 Z 3 dtype: int64
In [17]:
# Retrieve the last three elements.
    print(s[-3:])
    W 5 E 6 B 7 dtype: int64
In [18]:
# Retrieve Data Using Label (Index)
    print(s['D'])
In [19]:
# Retrieve multiple elements using a list of index label values.
    print(s[['D','Z','S','W']])
    D 1 Z 3 S 4 W 5 dtype: int64
In [20]:
# If a label is not contained, an exception is raised.
    print(s[['D','Z','S','W','Chauhan']])
    D 1.0 Z 3.0 S 4.0 W 5.0 Chauhan NaN dtype: float64
    c:\users\hp\appdata\local\programs\python\python37-32\lib\site-packages\pandas\core\series.py:1152: FutureWarning: Passing list-likes to .loc or [] with any missing label will raise KeyError in the future, you can use .reindex() as an alternative. See the documentation here: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike return self.loc[key]
In [ ]:
# DataFrame
# Defination-A Data frame is a two-dimensional data structure,
# i.e., data is aligned in a tabular fashion in rows and columns ​
# Features of DataFrame
# Potentially columns are of different types
# Size – Mutable
# Labeled axes (rows and columns)
# Can Perform Arithmetic operations on rows and columns ​
# A pandas DataFrame can be created using the following constructor −
# pandas.DataFrame( data, index, columns, dtype, copy)

In [21]:
# How to create the Pandas DataFrame?
In [22]:
 ​
# Their are following methods to create the Pandas DataFrame.
# Lists
# dict
# Series
# Numpy ndarrays
# Another DataFrame
# Now lets us start the learning this with

In [23]:
    import pandas as pd
    import numpy as np​

# define the dataframe object
 ​    df = pd.DataFrame()​
# lets print this
    df
Out[23]:
In [24]:
    print(df)
Empty DataFrame Columns: [] Index: []
In [27]:
1 import pandas as pd
2 import numpy as np
3 ​
4 # define the dataframe object
5 data = [1,2,3,4,5,6,7]
6 df = pd.DataFrame(data)
7 ​
8 # lets print this
9 print(df)
0 0 1 1 2 2 3 3 4 4 5 5 6 6 7
In [28]:
1 df
Out[28]:
0
0 1
1 2
2 3
3 4
4 5
5 6
6 7
In [29]:
1 import pandas as pd
2 import numpy as np
3 ​
4 # define the dataframe object
5 data = [['Sawan',26],['Sandeep',25],['Shiv',24]]
6 df = pd.DataFrame(data,columns=['Name','Age'])
7 ​
8 # lets print this
9 print(df)
Name Age 0 Sawan 26 1 Sandeep 25 2 Shiv 24
In [31]:
1 import pandas as pd
2 import numpy as np
3 ​
4 # define the dataframe object
5 data = [['Sawan',26],['Sandeep',25],['Shiv',24]]
6 df = pd.DataFrame(data,columns=['Name','Age'], dtype=int)
7 ​
8 # lets print this
9 print(df)
Name Age 0 Sawan 26 1 Sandeep 25 2 Shiv 24
In [36]:
1 # Create a DataFrame from Dict of ndarrays / Lists
2 import pandas as pd
3 import numpy as np
4 ​
5 # define the dataframe object
6 data = {'Name':['Dizsweb','Sawan','Chauhan'] ,'Age' :[ 123, 26, 35]}
7 df = pd.DataFrame(data)
8 ​
9 # lets print this
10 print(df)
Name Age 0 Dizsweb 123 1 Sawan 26 2 Chauhan 35
In [37]:
1 # Create a DataFrame from Dict of ndarrays / Lists
2 import pandas as pd
3 import numpy as np
4 ​
5 # define the dataframe object
6 data = {'Name':['Dizsweb','Sawan','Chauhan'] ,'Age' :[ 123, 26, 35]}
7 df = pd.DataFrame(data, index =['R1','R2','R3'])
8 ​
9 # lets print this
10 print(df)
Name Age R1 Dizsweb 123 R2 Sawan 26 R3 Chauhan 35
In [39]:
1 # Create a DataFrame from List of Dicts
2 import pandas as pd
3 import numpy as np
4 data = [{'a':3,'b':4,'c':8},{'a':5,'d':6,'f':9},{'b':3,'c':8,'d':9}]
5 df = pd.DataFrame(data)
6 print(df)
a b c d f 0 3.0 4.0 8.0 NaN NaN 1 5.0 NaN NaN 6.0 9.0 2 NaN 3.0 8.0 9.0 NaN
In [44]:
1 # Creation of two Dataframe from the same data
2 import pandas as pd
3 data = [{'a': 1, 'b': 2},{'a': 5, 'b': 10, 'c': 20}]
4 ​
5 #With two column indices, values same as dictionary keys
6 df1 = pd.DataFrame(data, index=['first', 'second'], columns=['a', 'b'])
7 ​
8 #With two column indices with one index with other name
9 df2 = pd.DataFrame(data, index=['first', 'second'], columns=['a', 'b1'])
10 print(df1)
11 ​
12 print(df2)
a b first 1 2 second 5 10 a b1 first 1 NaN second 5 NaN
In [47]:
1 # Create a DataFrame from Dict of Series
2 d = {'one' : pd.Series([1, 2, 3], index=['a', 'b', 'c']),
3 'two' : pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd'])}
4 ​
5 df = pd.DataFrame(d)
6 print(df)
7 ​
one two a 1.0 1 b 2.0 2 c 3.0 3 d NaN 4
In [48]:
1 # perticular column Selection
2 print(df['one'])
a 1.0 b 2.0 c 3.0 d NaN Name: one, dtype: float64
In [50]:
1 # Column Addition
2 import pandas as pd
3 ​
4 d = {'one' : pd.Series([1, 2, 3], index=['a', 'b', 'c']),
5 'two' : pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd'])}
6 ​
7 df = pd.DataFrame(d)
8 ​
9 # Adding a new column to an existing DataFrame object with column label by passing new series
10 ​
11 print ("Adding a new column by passing as Series:")
12 df['three']=pd.Series([10,20,30],index=['a','b','c'])
13 print(df)
14 ​
15 print("Adding a new column using the existing columns in DataFrame:")
16 df['four']=df['one']+df['three']
17 ​
18 print(df)
Adding a new column by passing as Series: one two three a 1.0 1 10.0 b 2.0 2 20.0 c 3.0 3 30.0 d NaN 4 NaN Adding a new column using the existing columns in DataFrame: one two three four a 1.0 1 10.0 11.0 b 2.0 2 20.0 22.0 c 3.0 3 30.0 33.0 d NaN 4 NaN NaN
In [51]:
1 # Column Deletion
2 # Using the previous DataFrame, we will delete a column
3 # using del function
4 import pandas as pd
5 ​
6 d = {'one' : pd.Series([1, 2, 3], index=['a', 'b', 'c']),
7 'two' : pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd']),
8 'three' : pd.Series([10,20,30], index=['a','b','c'])}
9 ​
10 df = pd.DataFrame(d)
11 print("Our dataframe is:")
12 print(df)
13 ​
14 # using del function
15 print("Deleting the first column using DEL function:")
16 del(df['one'])
17 print(df)
18 ​
19 # using pop function
20 print("Deleting another column using POP function:")
21 df.pop('two')
22 print(df)
Our dataframe is: one two three a 1.0 1 10.0 b 2.0 2 20.0 c 3.0 3 30.0 d NaN 4 NaN Deleting the first column using DEL function: two three a 1 10.0 b 2 20.0 c 3 30.0 d 4 NaN Deleting another column using POP function: three a 10.0 b 20.0 c 30.0 d NaN
In [55]:
1 # Row Selection, Addition, and Deletion with loc()
2 d = {'one' : pd.Series([1, 2, 3], index=['a', 'b', 'c']),
3 'two' : pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd'])}
4 ​
5 df = pd.DataFrame(d, dtype=int)
6 print(df.loc['b'])
one 2.0 two 2.0 Name: b, dtype: float64
In [57]:
1 # Row Selection, Addition, and Deletion with iloc()
2 d = {'one' : pd.Series([1, 2, 3], index=['a', 'b', 'c']),
3 'two' : pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd'])}
4 ​
5 df = pd.DataFrame(d, dtype=int)
6 print(df.iloc[2])
one 3.0 two 3.0 Name: c, dtype: float64
In [58]:
1 # Slice Rows
2 import pandas as pd
3 ​
4 d = {'one' : pd.Series([1, 2, 3], index=['a', 'b', 'c']),
5 'two' : pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd'])}
6 ​
7 df = pd.DataFrame(d)
8 print(df[2:4])
one two c 3.0 3 d NaN 4
In [63]:
1 # Addition of Rows using append funtion
2 ​
3 import pandas as pd
4 ​
5 df = pd.DataFrame([[1, 2], [3, 4]], columns = ['a','b'])
6 df2 = pd.DataFrame([[5, 6], [7, 8]], columns = ['a','b'])
7 ​
8 df = df.append(df2)
9 print(df)
a b 0 1 2 1 3 4 0 5 6 1 7 8
In [64]:
1 # Deletion of Rows
2 ​
3 import pandas as pd
4 ​
5 df = pd.DataFrame([[1, 2], [3, 4]], columns = ['a','b'])
6 df2 = pd.DataFrame([[5, 6], [7, 8]], columns = ['a','b'])
7 ​
8 df = df.append(df2)
9 ​
10 # Drop rows with label 0
11 df = df.drop(0)
12 ​
13 print(df)
a b 1 3 4 1 7 8
In [65]:
1 # Python Pandas - Panel
2 # A panel is a 3D container of data.
3 # The term Panel data is derived from econometrics and
4 # is partially responsible for the name pandas − pan(el)-da(ta)-s.
5 ​
6 # The names for the 3 axes are intended to give
7 # some semantic meaning to describing operations
8 # involving panel data. They are −
9 ​
10 # items − axis 0, each item corresponds to a DataFrame contained inside.
11 # major_axis − axis 1, it is the index (rows) of each
of the DataFrames.
12
#     minor_axis − axis 2, it is the columns of each of the DataFrames.
13
​
14
# pandas.Panel()
15
# A Panel can be created using the following constructor −
16
# pandas.Panel(data, items, major_axis, minor_axis, dtype, copy)
17
# This class is removed from pandas now

Sql Server DBA Interview Question more than 1000

Question 1. What Is The Difference Between Lock, Block And Deadlock?

Solution -

Lock: DB engine locks the rows / page / table to access 

the data which is worked upon according to the query.

Block: When one process blocks the resources of another

process then the situation occured is called blocking. Blocking can be identified by using the following query

SELECT * FROM sys.dm_exec_requests 

where blocked <> 0
SELECT * FROM master..sysprocesses 

where blocked <> 0

Deadlock: When something happens as follows:

 Error 1205 is reported by SQL Server for deadlock.

Question 2. what is the sequence of SQL statement are processed in SQL Server?

Solution - The clauses of the select are processed in 

the following sequence

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
7. TOP clause

Question 3. on which TCP/IP port does SQL Server run?

Solution - By default SQL Server runs on port 1433. 

But according to requirement is can be changed such as we have more than one instance of SQL Server, 

Then  differenet port can be assigned to different Instances

SQL Server constraint

CHECK constraint

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

After this if we used to insert the following query

insert into employees values(0,'Sawan','Chauan',24567)

Then following Output Message will be diplayed.

Second Example of Query

When we try to insert the following -

Then we will receive the above error

TRY----CATCH constructs

To handles exceptions by writing scripts inside the TRY block and error handling in the CATCH block

Indexes in Python

Indexing is a standard feature of most programming languages. Java, Python, C#, C++, and C all have syntax like E[i] whereby an item with index i can be reference in E (which might be a character string, an array of numbers etc). 

Python goes further beyond, it is possible to refer to chunks  of a sequence instead of indexing items one at a time. This feature in python is called slicing a sequence. Other techniques, like split and join, make it easy to take apart strings and assemble lists of strings into a new strings . In the same way that hand tools allow people to cut, shape, reshape, and put together wood into furniture, slicing, split, and join operations form the “carpentry” of Python’s text processing tasks. Even more powerful methods, notabe regular expressions, could be likened to the power tools of text and string processing. This post only covers the basic techniques, leaving regular expressions and other more advance libraries of software as later topics to explore. Working with slices particularly in Python’s syntax for slices, takes some getting used to. With regular practice, it becomes easy to write expressions that take apart sequences in creative ways and use operators to assemble slices into a new sequences. For strings slicing is only the beginning. String methods provide ways to search, replace, trim and form new strings from sequences of strings.

Following three methods are used to get the data in python via indexing
  1    loc() Only for lables
  2    iloc()  only for integer
  3    ix()  for both label and and integer.

Linear Programming Problem (LPP) Learnig Example with more than 2 varibles For Data Science Biggner in Python and Jupyter

In this Post we will learn lot of Linear Programming Problem Example. I will try to provide the coding part of problem not the lengthy documentation.

Now lets Start with our Examples which is generally asked in various unveristy exams also.

Example 1:-

A Manufacturing compamy is engaged in producing three types of product L, M and  N. The production department produces, each day, components sufficent to make 50 units of L, 25 Units of M and 30 units of N.
The management is confronted with the problem of optimizing the daily production of the prodccts in the assembly department, where only 100 man hours are availble daily for assembly the products.

Following addtional information is available-

Type of Product	Profit Contribution per Unit of Products (RS.)	Assemble Time per product (hrs)
L	12	0.8
M	20	1.7
N	45	2.5

The Company has a daily order commitment for 20 units of product L  and a total of 15 units of M and N.

Now we have to Formulate the  Linear programming model as maximize the total profit.

Now LP Model formulation of the is problem is -

Constraints	Product Type			Total
	L	M	N
Production Capacity	50	25	30
Man Hours per Unit	0.8	1.7	2.5	100
Order commitment	20	15( both of M and N)
Profit Contribution	12	20	45

Maximize Or Objective Function above Problem is -  

     Z=12 X1 + 20 X2 + 45 X3 or 

    Z=12x+20y+24z

Comstraints-

   (A) 0.8X1 + 1.7X2 +2.5 X3 <=100

   (B) X1 <= 50

   (C) X2 <= 25

   (D) X3 <= 30

ORDER COMMITMENT

  (A) X1 >= 20

  (B) X2 + X3 >=15

  (C) X1, X2, X3 >=0

This is the Complete description of our Problem, Now lets start the coding in python to get the final output of problem.

Solution -
As per our profit contribution we should have minimum production of product L, then M and maximum of N.
Now we have to find the coordinates.
1- X1=(0,20) and (0,50)
2- X2= (0,15)
3- X3= (0,15)

LPP Problem With Python and Jupyter

Linear Programmming (LP) is a widely used mathematical modeling technique developed to help the decisions makers in planning and decision - making according to resource allocation is concerned.

Structure of LP Model are

1- Decision Variables (Activities)

    We need to evalute various alternative for getting the optimum solution of desired Objective Function.

    The values of certain varibles is not under decision-makers. Then such varibles is called Controllable.

   The values of certain varibles is under decision-makers. Then such varibles is called uncontrollable.

2- Objective function

   This is the funtion which have to either maximized or minimized.

   Otimize ( Minimize or Maximize) 

      Z = C1X1 + C2X2 + C3X3 + ...... + CnXn

     where C1, C2, C3, ..., Cn are constant 

     X1, X2, X3,  .... Xn are varibles.

3- Constraints

There are always certain limitations on the use of resourses, example like lobour, machinery, raw material, space, money , etc . the limit the degree to which objective can be achived. Such constraints must be expressed as linear equalities i terms of decision vatriables.  

Assumptions of an LP Model-

1- Certainty

In all LPP models is assumed that all model parameters contribute to make unit decision.

2- Additivity

   The value of the objective function and the totaln amount of each resourses used muct be eqaul to the sum of the respective indivdual and consumtpion of resourses by a unit of decision varible.

3- Linearity or proportionality

   The amount of each resourse used and ists contibution to the profit in objective function must be proportional to the values of each decision varibles

4- Divisiblity or Continuity

   The solution of each resourses values of decision variables  are allowed to assume continuous values.

Advantages of LPP-

1- Optimum uses of productive Resources.

2- Improve the Quality of decesion making.

3- Fesible and possible Solution. 

4- Identifing the bottlenecks.

5- Re- evalution of decesion by changeing the varibles.

Limitation of LPP-

1- All relationship are considered as Linear.

2- No gaurentee to have integer Solution.

3- No consideration of time and uncertainity.

4- Large - Scale problem solution is very complicated.

5- Parameteres is assumed as constant.

6- Deals with only single objective.

Application Ares of Linear Programming Problem(LPP)

1- Agriculture

2- Military Applications

3- Production Management

    1- Mix Product    

   2- Production Planning    

   3- Assembly Line Balancing    

   4- Trim Loss

4- Financial Management

    1- Portfolio Selection    

    2- Profit Management

5- Marketing Management

    1- Media Selection    

    2- Travellling Salesman Problem    

    3- Physical Distribution

6- Personal Management.

   1- Staffing Problem   

   2- Determination of Equitable Salaries   

   3- Job Evaluation and Selection

A linear programming problem may be defined as the problem for maximizing or minimizing a linear function subject to the linear constraints. The constraints could be in equalities or inequalities.

Here is a simple example: find numbers x1 and x2 that maximizes the sum x1 + x2 subject to the constraints(conditions)
(i)    X1 ≥ 0
(ii)   X2 ≥ 0
(ii)   X1 + 2X2 ≤ 40
(iii)  4X1 + 2X2 ≤ 120
(iv)  X1 + X2 ≤ 1

The first two constraints,X1 ≥ 0 and X2 ≥ 0 are called nonnegativity constraints  and will always be postive in case of maximum and mininum problems becauase in real world if something exist then it could calculted and formulated.

The other constraints shown above are called the main constraints. The function that have to be maximized (or minimized) is called the objective function. Here, the objective function is X1 + X2.

We usually denotes this function with Z.
    Z =  X1 +  X2

Lets understand the complete concept with Real Life Example

We want to find the maximum solution of this linear polymer to:

Z=5x+4y

Z= objective function and x, y are variables.

 Constraints are following:

   i       x ≥ 0,   

   ii      y ≥ 3,     

   iii     2y ≤ 20 – x,     

   iv     4y ≥ 2x – 8,
   V       y ≤ 2x − 10

# LPP Problem Sample Example 1

# LPP Problem Sample Example 1

import numpy as np

import matplotlib.pyplot as plt

% matplotlib inline

# Construct lines

# x > 0

x = np.linspace(0, 20, 2000)

# y >= 3

y1 = (x*0) + 3

# 2y <= 50 - x

y2 = (20-x)/2.0

# 4y >= 2x - 8 

y3 = (2*x-8)/4.0

# y <= 2x - 10 

y4 = 2 * x -10

# Make plot

plt.plot(x, y1, label=r'$y\geq3$')

plt.plot(x, y2, label=r'$2y\leq20-x$')

plt.plot(x, y3, label=r'$4y\geq 2x - 8$')

plt.plot(x, y4, label=r'$y\leq 2x-10$')

plt.xlim((0, 16))

plt.ylim((0, 11))

plt.xlabel(r'$x$')

plt.ylabel(r'$y$')

# Fill feasible region

y5 = np.minimum(y2, y4)

y6 = np.maximum(y1, y3)

plt.fill_between(x, y5, y6, where=y5>y6, color='grey', alpha=0.5)

plt.legend(bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)

Our solution lies somewhere in the grey feasible region in the graph below.

It has been proven that the minima and maxima of linear programming problems are at the vertices of the feasible region. In this, there are 4 corners to our feasible region, So we can find the solutions for putting the value of this corner into the Objective Function each corner to find our maximum.

Four Courner X Y Cordinates Are:

Maximun 

Funtion             X           Y             Value

Z= 5x+ 4y                 8                  6                  64

 

Z= 5x+ 4y                 6.25             3                  43.25  

Z= 5x+ 4y                 10                3                  62                                  

Z= 5x+ 4y                 12                4                  76

So Last Cordinate ( 12 , 4) is giveing us the highest value.

So these are the  best for maximum. 

So Second Cordinate ( 6.25 , 3) is giveing us the lost value.

So these are the  best for maximum.


Linear Programming Problem Three and two varibles examples complete with python and jupyter note book

Arduino Uno board project for School , Enginweering, University Techfest demo and code example

   iv     4y≥2x–8
   v      y≤2x−5