March 30, 2019

fuzzylookup in python

This post will help you to get output using fuzzy lookup in python.


In this example I have used 2 datasets, input dataset and master dataset.



import pandas as pd
import fuzzywuzzy as fuzz
from fuzzywuzzy import fuzz
from fuzzywuzzy import process
import difflib

dfinput = pd.read_excel("E:/006 Projects/005 Dataset/fuzzygroup.xlsx", sheet="input")

dfinput.describe()
dfinput.head()

dfiCorrect = pd.read_excel("E:/006 Projects/005 Dataset/fuzzygroupcorrect.xlsx", 
sheet="Correct")

dfiCorrect.describe()
dfiCorrect.head()
dfiCorrect.columns
dfinput.columns

compare = pd.MultiIndex.from_product([dfinput['name'], dfiCorrect['TextMat']]).to_series()

def metrics(tup):
    return pd.Series([fuzz.ratio(*tup),                      
    fuzz.token_sort_ratio(*tup)],                     
    ['ratio', 'token'])

result = compare.apply(metrics).unstack().idxmax().unstack(0)
result.to_csv("E:/006 Projects/005 Dataset/output.csv")



using fuzzywuzzy python library we can generate the correct output with few lines of code.


Another way to write the same code which provide % of match which is additional information.


# second option
wrong_names=dfinput['name'].dropna().values
#Correct country names dataset
correct_names=dfiCorrect['TextMat'].values

names_array=[]
ratio_array=[]
def match_names(wrong_names,correct_names):
    for row in wrong_names:
        x=process.extractOne(row, correct_names)
        names_array.append(x[0])
        ratio_array.append(x[1])
    return names_array,ratio_array

name_match,ratio_match=match_names(wrong_names,correct_names)

dfinput['correct_country_name']=pd.Series(name_match)
dfinput['country_names_ratio']=pd.Series(ratio_match)
dfinput.columns

dfinput.to_csv("E:/006 Projects/005 Dataset/string_matched_country_names.csv")
result.to_csv("E:/006 Projects/005 Dataset/output.csv")

print(dfinput[['name','correct_country_name','country_names_ratio']].head(10))


Refer this screenshot




December 30, 2017

Difference between Datetime and Datetime2

Difference between Datetime and Datetime2

Datetime
Datetime2
range min
1753-01-01 00:00:00
0001-01-01 00:00:00
Range Max
9999-12-31 00:00:00
9999-12-31 00:00:00
Accuracy
0.00333 Seconds
100 Nanoseconds

December 29, 2017

find employees with birth date in the month of May and birth date lies in 7 to 21st of the month

using the Adventure-works database 

Get the list of employees whose birth date lies in 7th to 21st in the month of May
Data:





Desired query:

-- Get the list of Employees where birthdate lies in 7 to 21 in the month of May
Select NationalIDNumber, BirthDate
from HumanResources.Employee
where MONTH(birthdate) = 5
and day(birthdate) >= 7 and DAY(birthdate) <= 21
order by day(BirthDate) asc




December 25, 2017

hol file Microsoft Office Outlook

Happy New year 2018


So block your leaves in Microsoft office outlook by using .hol file

Hol stands for Holiday file

This file is used to add holidays in your outlook calendar.

This is very easy to create and install into the outlook.

One of the major advantages of this task is that whenever other colleague or stakeholder want to block your time then he would easily know / aware about the holiday or leave you have planned already in the outlook calendar.


Lets start to create one simple Entry

Step 1
            Open notepad

Step 2
            Add your holiday in below format
            [Maharashtra Day / Labor Day] , “2017/05/01”


Step 3
            Save this file as Holidaylist.hol
Remember I have not saved this file as txt file, I have saved this file as hol file.
Hol stands for Holiday file

Step 4
            Send it to your outlook email.

From inbox open this file / attachment.

This will prompt you to save the file / modify the outlook.

Click on ok and you are done.

For more reference please refer below link




howto create Holiday file in Microsoft office outlook

December 10, 2017

Downgrade Tableau workbook from latest version to older version of tableau

Example: from version 10.0.1 to tableau 9.3

From version 10.0.1 to tableau 9.1

Step 1: 
We need to create twb file, if it is already a twb file the no need to make any change. If you have twbx then Unpackage the workbook by right click on Workbook and Select Unpackaged if workbook is packaged.

Step 2
               Next step is to open twb file in Notepad.
               Select the file and right click on the twb file and select open with notepad

Step 3
               In notepad file Find the latest version i.e. 10.0.0 and replace with the version you want to be in
Example if you have tableau 10.0.0 and you want to convert into tableau 9.3 then find 10.0 in the notepad and replace with 9.3
There should be at least 3 instances where you have to replace the version in notepad.

Step 4
               Save the notepad file as it is.

Step 5
               You are done, now you can again right click on the twb file and select package workbook

June 17, 2017

file extensions available in Tableau



Different file extensions available in Tableau


  • Tableau Workbook (.twb).
  •  Tableau Packaged Workbook (.twbx).
  • Tableau Datasource (.tds).
  • Tableau Packaged Datasource (.tdsx).
  • Tableau Data extract (.tde).
  • Tableau Bookmark (.tdm).
  • Tableau Map Source (.tms).
  • Tableau Preferences (.tps)

January 19, 2017

Get list of tables used in Stored procedure


SELECT DISTINCT        so.ID, so.name AS 'Procedure Name' , oo.name AS 'Table Name'
FROM sysdepends d, sysobjects so, sysobjects oo
WHERE    so.id=d.id
      AND so.name= 'Stored_Proc_Name'   -- Enter Your stored Procedure Name here
      AND oo.id=d.depid
ORDER BY so.NAME,oo.NAME