How to store properly in a pandas Datframe?
link to dataset:"https://lib.stat.cmu.edu/datasets/boston"
Snippet from data
[ 21 lines of header text]
0.00632 18.00 2.310 0 0.5380 6.5750 65.20 4.0900 1 296.0 15.30
396.90 4.98 24.00
0.02731 0.00 7.070 0 0.4690 6.4210 78.90 4.9671 2 242.0 17.80
396.90 9.14 21.60
0.02729 0.00 7.070 0 0.4690 7.1850 61.10 4.9671 2 242.0 17.80
392.83 4.03 34.70
0.03237 0.00 2.180 0 0.4580 6.9980 45.80 6.0622 3 222.0 18.70
394.63 2.94 33.40
# Importing
data_url = "http://lib.stat.cmu.edu/datasets/boston"
# Define your column names
column_names = ['CRIM', 'ZN', 'INDUS', 'CHAS', 'NOX', 'RM', 'AGE', 'DIS', 'RAD', 'TAX', 'PTRATIO', 'B', 'LSTAT', 'MEDV']
boston = pd.read_csv(data_url, skiprows=21, sep =r'\s+', names = column_names, header= None, engine='python')
boston.head(2)
Result with current approach:
>>> boston.head()
CRIM ZN INDUS CHAS NOX RM AGE DIS RAD TAX PTRATIO B LSTAT MEDV
0 0.00632 18.00 2.31 0.0 0.538 6.575 65.2 4.0900 1.0 296.0 15.3 NaN NaN NaN
1 396.90000 4.98 24.00 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
...
It's probably better to not try to process your input data as a CSV - mainly because it's not really a CSV file!
You could do this:
import requests
import pandas as pd
from itertools import cycle
from collections import defaultdict
with requests.get("http://lib.stat.cmu.edu/datasets/boston") as response:
response.raise_for_status()
lines = response.text.splitlines()
data = defaultdict(list)
columns = [line.split()[0] for line in lines[7:21]]
keys = cycle(columns)
for line in lines[22:]:
for token in line.split():
data[next(keys)].append(token)
df = pd.DataFrame(data)
print(df.head())
Output:
CRIM ZN INDUS CHAS NOX RM AGE DIS RAD TAX PTRATIO B LSTAT MEDV
0 0.00632 18.00 2.310 0 0.5380 6.5750 65.20 4.0900 1 296.0 15.30 396.90 4.98 24.00
1 0.02731 0.00 7.070 0 0.4690 6.4210 78.90 4.9671 2 242.0 17.80 396.90 9.14 21.60
2 0.02729 0.00 7.070 0 0.4690 7.1850 61.10 4.9671 2 242.0 17.80 392.83 4.03 34.70
3 0.03237 0.00 2.180 0 0.4580 6.9980 45.80 6.0622 3 222.0 18.70 394.63 2.94 33.40
4 0.06905 0.00 2.180 0 0.4580 7.1470 54.20 6.0622 3 222.0 18.70 396.90 5.33 36.20