how to read text data and Convert to pandas dataframe

user_12443958 0
Viewed 85

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

pandas python-3.x
2 Answers

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
user_15236930

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.

user_113249940
Related