Python: Keeping track of which data comes from which file

If you have dataframe consist of data from multiple file, and you want to keep a column to preserve the information of file from where the data is coming along with the content of the file then we can make use of DataFrame assign function to create the column while reading and creating dataframe.

We are first taking an empty dataframe with columns in it.

To read multiple file in a directory we are using glob module

Then while reading the content of file in dataframe, we will use assign function to create new column to store filename along with data.

We have two file emp1.csv and emp2.csv in our python directory, lets try to read the file name through glob module

import pandas as pd

import matplotlib.pyplot as plt

import pandas as pd

import numpy as np

import glob

read_file = glob.glob('emp*.csv')

read_file

Output: ['emp1.csv', 'emp2.csv']

type(read_file)

Output: list

        

Here you see the read_file object of glob consist of all the files from the python parent directory.

Now we need to read the content of all file in the directory and keep all data in a single dataframe.

First we create an empty dataframe with column name and an additional column to store the filename from where data is getting extracted and stored in dataframe.

Then we loop through the filenames list (read_file) and pass the file name to pd.read_csv function

While reading file and storing data in dataframe we use assign function to create one more filed with the information of filename

df_file = pd.read_csv(files).assign(filename=files)

There after we concate the data frame to make a single dataframe. In the output you can see data are segregated under filename

df = pd.DataFrame(columns=['emp_no','emp_name','emp_sal','filename'])

for files in read_file:

    df_file = pd.read_csv(files).assign(filename=files)

    df= pd.concat([df,df_file],axis=0)

   

df

Output

emp_no   emp_name emp_sal  filename

0        E1001    Aayansh  1000     emp1.csv

1        E1002    Prayansh 2000     emp1.csv

2        E1003    Rishika  1500     emp1.csv

3        E1004    Mishty   900      emp1.csv

0        E2001    Sidhika  1000     emp2.csv

1        E2002    Kavita   2000     emp2.csv

2        E2003    Happy    1500     emp2.csv

3        E2004    Sandeep  900      emp2.csv

df.groupby(['filename']).count()

         emp_no   emp_name emp_sal

filename                 

emp1.csv 4        4        4

emp2.csv 4        4        4

Data Science with…Python J

Post Reference: Vikram Aristocratic Elfin Share

Reading multiple file dynamically and storing data in single Pandas dataframe

Here we will make use of glob module which gives us all file in a directory in List format.

We have two file emp1.csv and emp2.csv in our python directory, lets try to read the file name through glob module

import pandas as pd

import matplotlib.pyplot as plt

import pandas as pd

import numpy as np

import glob

read_file = glob.glob('emp*.csv')

read_file

Output: ['emp1.csv', 'emp2.csv']

type(read_file)

Output: list

        

Here you see the read_file object of glob consist of all the files from the python parent directory.

Now we need to read the content of all file in the directory and keep all data in a single dataframe.

First we create an empty dataframe with column name, there after we use concat function of pandas to concatenate the previous read content with new file content through dataframe

df = pd.DataFrame(columns=['emp_no','emp_name','emp_sal'])

for files in read_file:

    df_file = pd.read_csv(files)

    df= pd.concat([df,df_file],axis=0)

   

df

Output

         emp_no   emp_name emp_sal

0        E1001    Aayansh  1000

1        E1002    Prayansh 2000

2        E1003    Rishika  1500

3        E1004    Mishty   900

0        E2001    Sidhika  1000

1        E2002    Kavita   2000

2        E2003    Happy    1500

3        E2004    Sandeep  900

Data Science with…Python J

Post Reference: Vikram Aristocratic Elfin Share

What is Repeatable Read problem and how to solve it?

Let’s replicate the problem, for which I am creating a table StudentMarks and insert three records

create table studentMarks (

       id int

       ,name varchar(50)

       ,total_marks int

       )

    insert into studentMarks(id,name,total_marks) values( 1,'Prayansh',430);

    insert into studentMarks(id,name,total_marks) values( 2,'Rishika',345);

    insert into studentMarks(id,name,total_marks) values( 3,'Aayansh',390);         

Now lets open a transaction in a new session and try to read this table twice, and in between two read keep a delay of 18 Sec

Open Session1, and execute below transaction

    begin tran

    select * from studentMarks where ID in(1,2)

    waitfor delay '00:00:18'

    select * from studentMarks where ID in (1,2)

    rollback

Parallely open another session and execute below session

update studentMarks

set    total_marks=499 where id=1

Now the output from the first session you will find

 id          name         total_marks

----------- ----------------------

1           Prayansh      430

2           Rishika       345

(2 row(s) affected)

id          name          total_marks

----------- -------------------------

1           Prayansh      499

2           Rishika       345

(2 row(s) affected)

Here if you look at session1 output, a same select statement gives two different outputs, that is because , while session one is waiting for 18 second, session two updated the marks of id=1 to 499.

This is a problem with repeatable read if any outside transaction updates the data in between two reads.

How to avoid it

It can be avoided by setting transaction isolation level to repeatable read, let’s see how

set transaction isolation level repeatable read

    begin tran

    select * from studentMarks where ID in(1,2)

    waitfor delay '00:00:15'

    select * from studentMarks where ID in (1,2)

    rollback

Now here what will happen, if there is any outside transaction tries to modify the data which affect the Transaction one query then, the outside transaction will go on wait for getting update lock until transaction1 complete its operation.

Post Reference: Vikram Aristocratic Elfin Share