Step 0: json to pandas-dataframe

import boto3
import pandas as pd

# Ceph data location
S3_ENDPOINT_URL = "https://s3.upshift.redhat.com/"
BUCKET_NAME = "DH-PLAYPEN"
PREFIX = "nwatkins/smart_data_Q4_2018"

# create a S3 client that will access Ceph
# NOTE: assumes aws credentials are stored in ~/.aws/credentials. if not, then must be passed as parameters
s3 = boto3.client("s3", "us-east-1", endpoint_url=S3_ENDPOINT_URL)

# keys of the data files in the Ceph bucket
data_s3_keys = [
    content["Key"]
    for content in s3.list_objects(Bucket=BUCKET_NAME, Prefix=PREFIX)["Contents"]
]

# get the first file
ret = s3.get_object(Bucket=BUCKET_NAME, Key=data_s3_keys[0])
df = pd.read_json(ret["Body"].read().decode("utf-8"), lines=True)

print(df.shape)
df.head()

# #################### USE THIS TO READ THE FILE LINE BY LINE #################### #

# # get the first json file
# ret = s3.get_object(Bucket=BUCKET_NAME, Key=data_s3_keys[0])

# i = 1
# MAX_NUM_LINES = 5
# data = None
# labels = None

# ########## MORE EFFICIENT METHOD
# # pd.concat([pd.DataFrame([i], columns=['A']) for i in range(5)],
# # ...           ignore_index=True)
# ##########

# ########## BUGFIX
# # labels = pd.concat([labels, pd.DataFrame.from_dict(tmp['hints'], orient='index').transpose()])
# ##########

# for line in ret['Body'].iter_lines():

#     if data is None and labels is None:
#         tmp = json.loads(line)
#         data = pd.DataFrame.from_dict(tmp['smartctl_json'], orient='index').transpose()
#         labels = pd.DataFrame.from_dict(tmp['hints'], orient='index').transpose()

#     else:
#         tmp = json.loads(line)
#         data = data.append(tmp['smartctl_json'], ignore_index=True)
#         labels = labels.append(tmp['hints'], ignore_index=True)

#     if i == MAX_NUM_LINES:
#         break
#     else:
#         i += 1

# labels.head()
# data.head()

# get labels (the "Y")
labels = df["hints"].apply(pd.Series)
labels.head()

# get data (the "X")
data = df["smartctl_json"].apply(pd.Series)
data.head()

# change from dict type {'bytes': 123} to just int64 type 123
data["user_capacity"] = data["user_capacity"].apply(lambda x: x["bytes"])
data.head()

# change from dict type {'table': [{}, {}, {}]}  to list type [{}, {}, {}]
data["ata_smart_attributes"] = data["ata_smart_attributes"].apply(lambda x: x["table"])
data.head()

# change from list of dict type [{}, {}, {}] to list of tuples type [(), (), ()]
data["ata_smart_attributes"] = data["ata_smart_attributes"].apply(
    lambda x: [tuple(kv.values()) for kv in x]
)
data.head()

# split list of tuples [(), (), ()] to columns
# NOTE: each list contains tuples corresponding to only those attributes which are non null
# for e.g. hdd1's list may look like [{'id': 252, 'value': 10, 'raw': {...}}, {'id': 187, 'value': 10, 'raw': {...}}]
# but hdd2's list may look like [{'id': 12, 'value': 5, 'raw': {...}}, {'id': 5, 'value': 10, 'raw': {...}}]
for row_idx in range(data.shape[0]):

    # get the stats for current drive
    stats = data.loc[row_idx]["ata_smart_attributes"]

    for smart_id, norm_val, raw_val_dict in stats:
        # extract normalized value, and int form of raw value
        data.at[row_idx, "smart_" + str(smart_id) + "_normalized"] = norm_val
        data.at[row_idx, "smart_" + str(smart_id) + "_raw"] = raw_val_dict["value"]

# drop the original column as we have parsed its information and don't need it any more
data.drop("ata_smart_attributes", axis=1, inplace=True)
data.head()

# are all the timestamps same for a json file
(labels["backblaze_ts"] == 1538352000000.0).all()

# are these two columns different for any row?
(data["model_name"] != data["model_family"]).any()

# drop duplicate columns
data.drop(labels=["model_family"], axis=1, inplace=True)
data.head()

bbdf = pd.read_csv("/home/kachauha/Downloads/data_Q4_2018/2018-10-01.csv")
bbdf.head()