how to load raw data into python

Question

i have a raw data which can be found at http://www.jmulti.de/download/datasets/e6.dat which is like this . how to convert it to this.

Answer 1

This code is valid for all datasets (e1, e2, e3, e4, e5, e6 dat files).

import urllib
import io
import pandas as pd

URL1 = "http://www.jmulti.de/download/datasets/e1.dat"
URL2 = "http://www.jmulti.de/download/datasets/e2.dat"
URL3 = "http://www.jmulti.de/download/datasets/e3.dat"
URL4 = "http://www.jmulti.de/download/datasets/e4.dat"
URL5 = "http://www.jmulti.de/download/datasets/e5.dat"
URL6 = "http://www.jmulti.de/download/datasets/e6.dat"


def read_data(url):
    OFFSETS = {"Q": 3, "M": 0}
    stream = io.StringIO(urllib.request.urlopen(url).read().decode("iso8859"))
    while True:
        sre = re.match(r"<(\d{4}) ([QM])(\d+)>", stream.readline())
        if sre:
            year = int(sre.group(1))
            freq = sre.group(2)
            offset = int(sre.group(3))
            break
    cols = stream.readline().split()
    df = pd.read_fwf(stream, header=None, names=cols)

    dti = pd.date_range(f"{year}-1-1", periods=len(df), freq=freq+"S") \
            + pd.DateOffset(months=(offset-1)*OFFSET[freq])

    df["month"] = dti.month
    df["quarter"] = dti.quarter
    df["year"] = dti.year

    return df

>>> read_data(URL6)
           Dp      R  month  quarter  year
0   -0.003133  0.083      4        2  1972
1    0.018871  0.083      7        3  1972
2    0.024804  0.087     10        4  1972
3    0.016278  0.087      1        1  1973
4    0.000290  0.102      4        2  1973
..        ...    ...    ...      ...   ...
102  0.024245  0.051     10        4  1997
103 -0.014647  0.047      1        1  1998
104 -0.002049  0.047      4        2  1998
105  0.002475  0.041      7        3  1998
106  0.023923  0.038     10        4  1998

[107 rows x 5 columns]

>>> read_data(URL5)
     i_short  i_long  month  quarter  year
0       4.36     6.2      1        1  1960
1       4.47     6.2      2        1  1960
2       4.71     6.2      3        1  1960
3       4.59     6.2      4        2  1960
4       4.64     6.2      5        2  1960
..       ...     ...    ...      ...   ...
331     3.95     6.0      8        3  1987
332     3.99     6.2      9        3  1987
333     4.70     6.5     10        4  1987
334     3.94     6.0     11        4  1987
335     3.65     5.8     12        4  1987

[336 rows x 5 columns]

Answer 2

Get Raw Data

You could start by using requests to get the raw data and then split the data and create a dataframe.

import pandas as pd
import requests

response = requests.get('http://www.jmulti.de/download/datasets/e6.dat')
data = response.text.split('\n')[11:]

data = [row.split() for row in data]

df = pd.DataFrame(data, columns=['Dp', 'R'], dtype=float).dropna()

Add Quarter and Year

We can then add the quarter and year for each row like this.

datelist = pd.date_range(start='1972-06-30', end='1999-01-01', freq='3M')

df['quarter'] = datelist.quarter

df['year'] = datelist.year

Note, the start/end dates in the above code are currently hard-coded but you could use something like this to get them from the raw data.

# extract the period the data covers from the 2nd line of the file
period = data.split('\n')[1]

# get the dates for the start/end quarters for the period the data covers
start, end = pd.PeriodIndex([period[8:14].replace('Q','-Q'), period[18:24].replace('Q','-Q')], freq='Q').to_timestamp()

Sample output

Here's a sample of the output.

           Dp      R  quarter  year
0   -0.003133  0.083        2  1972
1    0.018871  0.083        3  1972
2    0.024804  0.087        4  1972
3    0.016278  0.087        1  1973
4    0.000290  0.102        2  1973
..        ...    ...      ...   ...
102  0.024245  0.051        4  1997
103 -0.014647  0.047        1  1998
104 -0.002049  0.047        2  1998
105  0.002475  0.041        3  1998
106  0.023923  0.038        4  1998

[107 rows x 4 columns]

Answer 3

This is not a standard format, so you need a custom parser here.

This should work for any such file.

import requests
import re
import pandas as pd

url = 'http://www.jmulti.de/download/datasets/e6.dat'

data = requests.get(url).text

# to match the header
pattern = '/\*[\s\S]*\*/'

# removes the header content from data.
data = re.sub(pattern, '', data)

# data is a single string with newlines escaped in it.
# So splitting would make it iterable
data = data.split('\n')

# there might be some blank lines so we will discard them
data = [x for x in data if x != '']

# remove the shifts(<>) from Quarter info line so that '<1971 Q1>' becomes '1971 Q1'
# however it can be done with regex as well.
quarterinfo = data[0].replace('<', '').replace('>', '')

year, quarter = quarterinfo.split()

df_data = []

for line in data[2:]:
    # removing the newlines if there are any
    # and blank spaces
    line = line.replace('\n', '').strip()
    
    # converting the values for each data row. 
    # Leaving it as is would make the df values str.
    dp, r = [float(x) for x in line.split()]

    df_data.append({
        'year': year,
        'quarter': quarter,
        'DP': dp, 
        'R': r
        })

df = pd.DataFrame(df_data)

print(df.head())

Run output ->

   year quarter        DP      R
0  1972      Q2 -0.003133  0.083
1  1972      Q2  0.018871  0.083
2  1972      Q2  0.024804  0.087
3  1972      Q2  0.016278  0.087
4  1972      Q2  0.000290  0.102