CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
question.it
question.it

Reputation: 2978

How to convert web URL.txt Data to Dataframa

I was trying to download data from here and put in single Dataframe.

I am stuck in retrieving proper Data Column Names, I want to find proper Table Column Header, below code not working.

Header = pd.read_csv(io.StringIO(URL_Data.decode('utf-8')), delimiter='\t', skiprows=4, nrows=4)

Data Fetch:

Data = pd.read_csv(io.StringIO(URL_Data.decode('utf-8')), skiprows=9, delim_whitespace=True, header=Header, error_bad_lines=False)
Data.to_csv(r"D:\Sunil_Work\psnl\python\DataFile.csv")

Upvotes: 2

Views: 38

Answers (1)

jezrael
jezrael

Reputation: 863226

Use read_fwf with DataFrame.agg and join:

url = 'https://www.federalreserve.gov/paymentsystems/files/check_commcheckcolqtr.txt '

Indicator_Name = pd.read_csv(url, nrows=1, header=None)

Header = pd.read_fwf(url, skiprows=4, header=None, error_bad_lines=False, nrows=5)
Header = Header.agg(lambda x: ' '.join(x.dropna()))
print (Header)
0                                      Quarter
1                   Volume (millions of items)
2                        Volume percent change
3                  Value (billions of dollars)
4                         Value percent change
5     Average daily volume (millions of items)
6    Average daily value (billions of dollars)
7            Average value per check (dollars)
dtype: object

Data = pd.read_csv(url, skiprows=9, delim_whitespace=True, names=Header, error_bad_lines=False)

print (Data.head(3))
   Quarter Volume (millions of items) Volume percent change  \
0  2018:Q4                      1,169                   1.9   
1  2018:Q3                      1,147                  -5.4   
2  2018:Q2                      1,212                   0.1   

  Value (billions of dollars) Value percent change  \
0                       2,072                  0.0   
1                       2,072                 -8.9   
2                       2,274                 10.0   

  Average daily volume (millions of items)  \
0                                     18.9   
1                                     18.2   
2                                     18.9   

  Average daily value (billions of dollars) Average value per check (dollars)  
0                                      33.4                             1,772  
1                                      32.9                             1,805  
2                                      35.5                             1,875

Upvotes: 1

Related Questions