CODEWITHSUNDEEP
python-3.xpandas
Pavan Suvarna
Pavan Suvarna

Reputation: 501

how to read text data and Convert to pandas dataframe

I am reading from the URL

import pandas as pd
url = 'https://ticdata.treasury.gov/Publish/mfh.txt'
Data = pd.read_csv(url, delimiter= '\t')

But when I export the Dataframe, I see all the columns are combined in a single column I tried with different separators but didn't work. I want to get the Proper data frame. How I can achieve it.

Please help

Upvotes: 2

Views: 152

Answers (2)

Daniel R
Daniel R

Reputation: 2042

I just opened this file in a text editor. This is not a tab delimited file, or anything delimited file. This is a fixed width fields files from line 9 to 48. You should use pd.read_fwf instead skiping some lines.

This would work:

import pandas as pd
url = 'https://ticdata.treasury.gov/Publish/mfh.txt'
Data = pd.read_fwf(url, widths=(31,8,8,8,8,8,8,8,8,8,8,8,8,8), skiprows=8, skipfooter=16, header=(0,1))
Data

Upvotes: 3

Marcel
Marcel

Reputation: 1048

The file you're linking is not in the CSV/TSV format. You need to transform the data to look something like this before loading it in this way.

Upvotes: 1

Related Questions