DEV Community: Nirmal

How i used a simple python script to compare 2 huge csv file using Pandas

Nirmal — Sun, 07 Mar 2021 10:56:56 +0000

Recently i came across a requirement to compare a column data in a csv file with another csv file. These files contains 13 columns with 65 million of rows.

Initially i thought it's simple one and used basic scripting with bash to process line by lines. Then, i realized it took me hours to get that processed. Then i moved to nodejs and then to python. No matter what language i used the result is same. It took more time and i felt it's not worth to process using the regular approach. Then i turned towards pandas library and i tried. To my surprise it processed within 10 seconds.

The requirement is basically to match the emailId in the file1.csv against the md5 email hash of the file2.csv.

Here the snippet i used to compare the matched dataset.

import pandas as pd

import hashlib 

def md5hash(mailId):
    print(mailId)
    result = hashlib.md5(mailId)
    return result.digest()

df=pd.read_csv("file1.csv",dtype={ "emailId": "string"},low_memory=False)

# First get the has of the emailId and append to the existing row
df['md5_hashed'] = [hashlib.md5(val.encode()).hexdigest() for val in df['emailId']]
df.to_csv("hashed_records.csv",index=False)


#Store the data in a dataframe
df1=pd.read_csv("file1_with_hashed_email.csv",low_memory=False)
df2=pd.read_csv("file2.csv")
df2_hash=df2["md5_emailId"]

matched=df.loc[df1['md5_hashed'].isin(df2_hash)]
matched.to_csv("non-matched.csv",index=False)

#Just add a ~ to filter the unmatched
unmatched=df.loc[~df['md5_hashed'].isin(df2_hash)]
unmatched.to_csv("non-matched.csv",index=False)

I worked with Pandas earlier. But i realized the power of pandas which has saved my hours instead of parsing with nodejs/db based solutions.

Hope this helps someone who faced similar scenarios.

Detective Series - Website Down

Nirmal — Fri, 05 Jul 2019 16:35:16 +0000

Detective Angadh

Let's see how Angadh, our Detective Master (Imaginary Character) resolved a Case in which the website was down for more than a week in a small company ABC.

Case Details :

ABC Agency a small company which runs campaigns and advertisements business. They built a in-house web application and it was running smooth for more than 2 years. Suddenly the site is in-accessible/down for more than 1 week. Initially they had a developer who were taking care of all the development efforts and deployment this application. Unfortunately, the Developer is no more with the ABC Company and the end users has NO knowledge about the Application Hosting or Development or SourceCode related details. Now they are facing heat and their business continuity is completely halted.

Current State :

ABC have no clues on how to resolve and bring the Website up. Even though they found some subject experts they couldn't help as ABC doesn't have any information about the application code, database or hosting details.

Finally, ABC reached our Detective Master Angadh to get this resolved with the available information they have. The available information is the domain name of the in-house app they developed.

Domain Name : digital-agency.abc.com

Investigation :

After reviewing the case study Angadh prepared the following list to figure out whats going on behind with a Possible cause list and Troubleshooting checklist

Possible Causes for the Website Down : ( a Typical website with Less users)

DNS Resolving Issues
DDOS Attacks
Server Down
Web Server may be stopped
Background Process may have crashed
May be Licensing related Issues based on Date
Server IP Address Changes

Checklist :

Angadh felt the only way to identify the issue is to get the details of the server and access them to troubleshoot further.

Checklist : Identify Server, Hosting and Source Code

[x] Identify the IP Address of the Website
[x] Identify where the Application is hosted. (i.e) Where the Server is Local Premise/Cloud
[x] Identify the Application Platform (PHP, NodeJS etc)
[x] Identify whether they used Apache, PHP-FPM or NGIX
[x] Identify whether they had source code repository

Angadh used the following tools for clearing the above checklist.

A) Where the Server & Location ?

Ping the website domain Name from terminal. ping
Ping.eu to trace the DNS route
IPLocation using iplocation.net

With these Tools, Angadh Identified that the application is hosted on one of the Cloud Platform (Google Cloud) along with the IP Address details from the DNS

B) How to get into the Server ?

Angadh inquired the System Admin of ABC whether they have any account in Google Cloud Platform and he got a read only access to the GCP Platform. He was seeing couple of servers running there and using the VM Instance IP (Public IP Address gathered from Checklist A ), he SSH'ed to the server.

He knows well by default, GCP will allow you to ssh to the Linux instance by injecting the on-the-fly keys to the server for the respective user who logged in and have access to the GCP Project account.

He felt he was very close to the solution and trying to explore the files and directories on the server.

C) What is the App Platform & Where is the SourceCode Repository ?

Reviewing the source code can give some hints about the platform the application was built. Angadh inquired the same SysAdmin whether they have any accounts with GitHub,TFS, BitBucket etc. The SysAdmin mentioned he has an account with BitBucket and provided an access. Angadh explored the repository and identified the right project and realised that the platform is built on NodeJS

D) How this NodeJS Web App served in the 80 port ?

/var/www/html was one of the popular directory path used by majority of platforms. Angadh tried to change the directory to that folder and he couldn't find anything there. The directory is empty.

Its going to nightmare to figure out where these files are located and how this has been served. As per the code it was defaulted to port 8080. But someway they would have overridden in the Production server through 80 port or they would have did a reverse proxy using NGINX or Apache Proxy. He thought either these services are stopped or crashed for some unexpected reasons.

Angadh couldn't find any apache or nginx server running in the server process. So, the app was served in 80 port through a different mechanism.

He even tried using CURL command to figure out whether the website is working inside the server.

$ curl http://digital-agency.abc.com

Could not resolve host: digital-agency.abc.com

 $ curl http://localhost:80 
Failed to connect to localhost port 80: Connection refused

$ curl http://localhost:8080
<html>
.....
.....
</html>

Hurray, Angadh happy to see that he is getting some response when he used the curl command. Now what reverse proxy running behind in the server at 80 port.

E) Time Travel - Terminal Command History

Looking at the state of information gathered so far, Angadh realised the only way to resolve the issue this to figure out the recent commands executed in the server by the old developer.

$ /etc/passwd

The above command listed some of the users. Using su , he tried to switch to that user and executed the history command

$ history > command-history.txt

This one command exported all the commands to a text file and Angadh patiently reviewed each of the one and he is surprised to see the last command used in that user shell.

sudo iptables -t nat -I PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 8080

When he looked at this one command he realised that the app is not served through a traditional webserver like Apache or Nginx, but it uses directly the EXPRESS NodeJS Server and re-routed using iptables (feature in Linux Distros to route IP requests using rules)

Now Angadh executed this command again and tried accessing the site and he was able to access the site without issues.

F) The Final Part - Why the Website is up now ?

After executing this command, the request to this server with 80 Port are redirected to the NodeJS 8080 Port and the only possible reason for the previous failure is they missed to save this changes in the IPTable.

The command to save the IPTable Rules permanent

# https://unix.stackexchange.com/questions/52376/why-do-iptables-rules-disappear-when-restarting-my-debian-system
sh -c "iptables-save > /etc/iptables.rules"

Angadh suspected that this issue have arised due to some server restarts or patches applied in the VM Instance running in the GCP. He went to the VM Instance and checked the last boot time and it exactly 9 days since the website down was reported.

    $ last reboot

Finally, Angadh Concluded the root cause of the Website down and tried restarting again as he is very sure that the IPTables are saved as per the previous command. After a restart the WebApp worked well and he provided a detailed report to ABC and the company was very happy that he cracked all these i short duration and get their business running again.

Angadh, knows well that the solution implemented using IPTables are not robust way. But his focus is to investigate and resolve the issues in the current environment which was working well for more than a year.

Hope you enjoyed this detective series with Angadh.

Protect & UnProtect Sheets Excel Sheets using VBA Macros (Excel Online)

Nirmal — Sat, 29 Jun 2019 05:44:29 +0000

Excel Online

With the advent of Modern Browsers and Web Standards, users have switched to online version of Spreadsheets and Word processors which are easily accessible from multiple devices and platforms. But still we can't forget the power of Macros written using VBA Scripting mostly targeted for Excel & Word.

We have been using Excel Spreadsheets as a Database for one of our Sharepoint Application. This Application generates multiple spreadsheets and provides access to users through Excel Online. We want to protect some of the files based on roles and user designation. Looks like currently Sheet protection/UnProtection is not supported in Excel online. This feature is also requested by 183 people in the Excel UserVoice. In the post i am sharing the workaround for the same.

Macro Solution for Protect/UnProtect

The workaround we followed is to Enable the OneDrive Sync in the Sharepoint Application and then create a Macro to Protect/UnProtect all the 100+ of files. This will be immediately synced with OneDrive sync utility to online cloud. When you open the Excel Online and try editing the file, you will get a message "The sheet is protected."

Here the Macro to protect/protect all the files in a given folder in the OneDrive folder.

Function ApplyProtection(sourceFolder as String,sheetName as string, isUnProtect As Boolean)


    Dim sheetPassword As String
    sheetPassword="YOUR SHEET PASSWORD"

    Dim actualFile As String

    Dim selFile As String
    actualFile = Dir(sourceFolder & "\*.xlsx")

    Do While Len(actualFile) > 0

        Application.ScreenUpdating = False
        Set wbResults = Workbooks.Open(filename:=sourceFolder & "/" & actualFile, UpdateLinks:=0, ReadOnly:=False)

        Dim reqSheet As Worksheet

        Set reqSheet = wbResults.Sheets(sheetName)
        Dim sheetState As Boolean

        sheetState = SheetProtected(reqSheet)

        If sheetState = False And isUnProtect = False Then
                reqSheet.Protect Password:=sheetPassword
        End If

        If sheetState = True And isUnProtect = True Then
                reqSheet.Unprotect Password:=sheetPassword
        End If

        actualFile = Dir
    Loop

End Function

    
Private Function SheetProtected(TargetSheet As Worksheet) As Boolean
     'Function purpose:  To evaluate if a worksheet is protected

    If TargetSheet.ProtectContents = True Then
        SheetProtected = True
    Else
        SheetProtected = False
    End If

End Function

'Function Usage. Create Macro "ProtectFiles" and bind this ProtectAllFiles Module

Sub ProtectAllFiles()

Dim sourceFolder As String
sourceFolder="PUT YOUR SOURCE FOLDER PATH"
Call ApplyProtection(sourceFolder,False)

End Sub


'Function Usage. Create Macro "UnProtectFiles" and bind this UnProtectAllFiles Module
Sub UnProtectAllFiles()

Dim sourceFolder As String
sourceFolder="PUT YOUR SOURCE FOLDER PATH"
Call ApplyProtection(sourceFolder,True)

End Sub

Hope this is helpful for someone who wants to protect sheets for the Online version of Excel.

Happy Scripting.

Proposal for Standardising User Digital Presence Profile using .yml

Nirmal — Sun, 16 Jun 2019 12:02:55 +0000

Being in a developer community, we come across many People who have been inspiring to us through their great works, blog posts, videos, course tutorials, podcasts and much more. What if we have a standard way to easily share those inspiring digital presence profile using a standard convention.

Hereby sharing my thoughts on this to the community and would like to hear from you all. What you think and let me know and also share your comments if there any service which is already addressed these challenges.

Background

Traditional way of Sharing Profiles in Phones uses VCARD File
(VCard, JCARD) - Wiki . But i don't find one to share a user profile about his digital presence on the Web.

Initial Proposal to Standardise Public Profile using a simple .yml file to easily share and consume by applications.

Summary

In this RFC, I am proposing a standardised way to create public profiles and share them easily to other applications. This standardised proposal will not includes and personal details like Phone Number, Address etc. This focusses mostly on the user digital presence and the corresponding links to their works, blogs and any other public account which they want to share with anyone.

Challenges

With the increasing number of applications used by users, each of these application is using a Custom Registration form, OAuth based/SSO based Integration to collect the profile information. Once registered, the profile details are not updated on regular basis. Either some of the information are obsolete (assume the user blog site is moved to a different site)
Even the Author doesn't have a common way to manage and update their profiles and keep the version history.

How if everyone has a common profile as a yaml file :

Asssume a user ABC, maintains his profile using a source control version like GitHub.com. They can create multiple profiles and share wherever needed with just a Url/file.

(e.g)
http://github.com//my-profiles/default-profile.yml
http://github.com//my-profiles/work-profile.yml

Applications who want to get the user profile information, they can simply read the yaml file and update them on regular intervals.

Sample default-profile.yml

website: gyanmoti.in
country: india
name: Nirmal
profile_image_url: "https://media.licdn.com/dms/image/C4D03AQFgMmks5SVa_w/profile-displayphoto-shrink_200_200/0?e=1566432000&v=beta&t=XehIjAJiGztgNbN42I5fY6RWvY8dpeN4J_czWn0IRY0"
social_accounts: 
  - facebook: NA
  - twitter: nirmal_kumar
  - youtube: NA
  - linked_in: nirmalweb
creations:
  - courses: http://usercourses.com
  - publications: http://userpublications
  - podcasts: http://userpodcasts

Hereby requesting your comments for this public profile and thus making the web a better place to share and manage one's own profile.

Feel free to send a PR if you wish to add anything to this proposal here in Github.

Kubernetes - Refresher Notes

Nirmal — Mon, 20 May 2019 01:51:20 +0000

This post is for anyone to quickly refresh on the phrases in Kubernetes. If you are starting to explore Kubernetes then this can be used as a checklist on the areas you should be aware of. I have also included some basic commands to work with kubernetes.

Concepts :

KUBECONFIG
Cluster - Master & Worker
Node
Node Port
ClusterIP
Statefulset vs Statetless
Deployment
Service
Pod
Namespace
Context
PersistentVolume
PersistentVolumeClaim
HostPath (Local)
Ingress
LoadBalancer
ConfigMap
Secrets
DNS (..svc.cluster.local)
KubeAdmin
RBAC
ServiceAccount
Rollout
Replica Scale

Scripts

Annotations,Labels & Selectors
Kustomize (-k vs -f)
Apply/Create/Patch

Local Kubernetes Setup :

Docker for Desktop Mac
MiniKube

Tools

Kubernetes Dashboard - web UI for Kubernetes clusters
Kubectl and Proxy
Kubernetic Desktop - View Cluster Resources, Similar to Kitematic for Docker.
Sekret - Encryption Tool for Kubernetes Secrets YAML
Kamus - Encryption and Decryption solution for Kubernetes applications
Kubectx - Switch faster between clusters and namespaces in kubectl
kube-ps1 - Kubernetes prompt info for bash and zsh to see the current cluster context with namespace
Kubefwd - Bulk port forwarding Kubernetes services for local development.

Basic Commands

Contexts


# Get all contexts
kubectl config get-contexts 

# Get Current Context
kubectl config current-context


# Set the default context
kubectl config use-context nk-dev-cluster  

kubectl config set-context nk-dev --namespace=rafa-dev --cluster=docker-for-desktop-cluster --user=docker-for-desktop

Describe K8 Resources

# kubectl describe <resource> <resourceName>
kubectl describe pods pod1

# Get the resource definition in yaml
kubectl get pod my-pod -o yaml

Expose a Service

#expose a app named web-app as a service 

kubectl expose deployment web-app --name=web-app --type=NodePort --target-port=8080


# For Testing Locally
kubectl port-forward servicename 8080:8080

Connect to the running Pod Container


kubectl exec web-app-fcc6b5ddf-k2txq -it -- /bin/sh

Troubleshooting


#Simple Container to quickly run some curl commands to the pods
kubectl run curl-test --image=radial/busyboxplus:curl -i --tty --rm

Thanks for reading this refresher.

Retrieve entire data from paginated API recursively.

Nirmal — Sun, 17 Feb 2019 08:52:16 +0000

In this post i am going to show to retrieve data from a paginated API using recursive method.

To explain the functionality, I am going to use a dummy REST API which i created in the mockapi.io. Currently the dataset has 91 records. By default it returns 91 records. In realtime, the dataset may be huge of more than 10000's data items. It's not recommended to query the entire dataset in a single api as it burdens the server performance and also place a heavy load on the client side as well. The recommended approach is to paginate the results when you are showing in the client side. That's the reason most of the API's provides some additional filters in the api to return only the records which the user is interested instead of returning the entire datastore.

Here some of the common filters used by the API's.

1.offset, limit : "limit" determines how many records to retrieve starting from the "offset"
2.page, limit : "page" determines which data the server needs to return based on number of "limit" -> records per page .

Please refer the respective API developer manual for more details on how to retrieve paginated results.

Sample API End Points :

Without Pagination :
https://5b5cb0546a725000148a67ab.mockapi.io/api/v1/users

With Pagination :
https://5b5cb0546a725000148a67ab.mockapi.io/api/v1/users?page=4&limit=2

The above API endpoints are good for a normal use case. But they are requirements in which you need to retrieve all the data from the API and the API is designed to return only 50 records per call. In this scenario, we can create a recursive paginated API call and capture the entire dataset.

Remember,If you are not handling this properly it may end up with infinite looping and your browser may hang. In this example i have limited 20 items per page.

const limitPerPage=20;
const apiUrl="https://5b5cb0546a725000148a67ab.mockapi.io/api/v1/users";

const getUsers = async function(pageNo = 1) {

let actualUrl=apiUrl + `?page=${pageNo}&limit=${limitPerPage}`;
var apiResults=await fetch(actualUrl)
.then(resp=>{
return resp.json();
});

return apiResults;

}

const getEntireUserList = async function(pageNo = 1) {
  const results = await getUsers(pageNo);
  console.log("Retreiving data from API for page : " + pageNo);
  if (results.length>0) {
    return results.concat(await getEntireUserList(pageNo+1));
  } else {
    return results;
  }
};


(async ()=>{

    const entireList=await getEntireUserList();
    console.log(entireList);

})();

I have used the browser developer console to show the output. Please see this gif http://recordit.co/p3FkQS7QpJ

Thanks for reading & happy coding..