Read file using Python/Pandas

mial4567 0
Viewed 54

I have a tab-delimited file with data like:

id  Name    address dept    sal
1   abc "bangalore,
        Karnataka,
        Inida"  10  500
2   xyz "Hyderabad
         Inida" 20  500

Here the columns are id,Name,address,dept, and sal.

The issue is with address columns that can contain a new line character. I tried different methods to read the file using Pandas and Python but instead of two rows, I am getting multiple rows as output.

Here are the few commands I tried:

file1 = open('C:/dummy/dummy.csv', 'r')

lines = file1.readlines()

for i in lines:

    print(i)

and

df = pd.read_csv("C:/dummy/dummy.csv",sep='\t',quotechar='"')

Can anyone please help?

pandas python
1 Answers
df = pd.read_csv("C:/dummy/dummy.csv",sep='\t',quotechar='"')

The corresponding output is, in case the columns are tab-delimited in the csv-file, as you say

   id Name                            address  dept  sal
0   1  abc  bangalore,\r\nKarnataka,\r\nInida    10  500
1   2  xyz                 Hyderabad\r\nInida    20  500

If you like to remove the CR-LF within the string, you can remove them via post-processing. Additionally you could define the index-column via

df = pd.read_csv("C:/dummy/dummy.csv",sep='\t',quotechar='"',index_col=0)

What is your desired/expected output?

flyakite0
Related