{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Minimalist Guide to Getting Data in Any Format\n\nReading data with Python is a key skills for data scientists. This notebook will walk through how to read data in various kinds of commonly encountered data format so that you can start your analysis quickly.","execution_count":null},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport os","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# CSV files\n\nIt is quite straightforward to read csv files using read_csv() function of pandas. However, there are some small tricks to make your data easier to read and analyze. We will walk through:\n- Parsing dates\n- Selectively read columns\n- set index column\n\nWe use [Netflix Movies and TV Shows](https://www.kaggle.com/shivamb/netflix-shows) dataset for illustration.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"# read_csv without arguments\nnf = pd.read_csv('/kaggle/input/netflix-shows/netflix_titles.csv')\nnf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Parse dates with \"parse_dates\" argument\nnf = pd.read_csv('/kaggle/input/netflix-shows/netflix_titles.csv', parse_dates=['date_added'])\nnf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Read selected columns using \"usecols\" argument\nlist_cols = ['show_id', 'type', 'title', 'country', 'date_added', 'rating', 'duration']\nnf = pd.read_csv('/kaggle/input/netflix-shows/netflix_titles.csv', parse_dates=['date_added'], usecols = list_cols)\nnf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Set a column as index by \"index_col\" argument - useful for making time series\nnf = pd.read_csv('/kaggle/input/netflix-shows/netflix_titles.csv', parse_dates=['date_added'], \n                 usecols = list_cols, index_col = 'show_id')\nnf.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are many arguments one can use to read csv files more effectively. Please see [pandas documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html) for details.","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"# Images files\n\nIn doing image related machine learning tasks, we need to inspect the images in our dataset before building models. In this section, we will walk through folders to read image files, and show images with labels. We use [Flowers Recognition](https://www.kaggle.com/alxmamaev/flowers-recognition) dataset as an example.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"data_dir = '/kaggle/input/flowers-recognition/flowers/flowers'\n\n# Dictionary of all images paths\nflowers = {'sunflower':[], 'tulip':[], 'daisy':[], 'rose': [], 'dandelion': []}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Populate the dictionary with image paths\nfor flower in flowers.keys():\n    for dirname, _, filenames in os.walk(os.path.join(data_dir, flower)):\n        for filename in filenames:\n            flowers[flower].append((os.path.join(\n                os.path.join(data_dir, flower), filename)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Showing 2 random images from each of the categories\nfrom tensorflow.keras.preprocessing import image\n\nfor flower in list(flowers.keys()):\n    plt.figure(figsize=(8, 5))\n    flower_choice = np.random.choice(len(flowers[flower]),2) # Choose two images by random\n    plt.subplot(1, 2, 1)\n    img_path1 = flowers[flower][flower_choice[0]]\n    img = image.load_img(img_path1)\n    plt.imshow(img)\n    plt.title(flower)\n    plt.subplot(1, 2, 2)\n    img_path2 = flowers[flower][flower_choice[1]]\n    img = image.load_img(img_path2)\n    plt.imshow(img)\n    plt.title(flower)\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Files of texts\n\nIn natural language processing problems, it may involve loading directories of text files. This section will introduce how to read the files and put it in a pandas dataframe. We use [The Works of Charles Dickens](https://www.kaggle.com/fuzzyfroghunter/dickens) dataset as an example.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"# The dataset contains a metadata table\n\npath = '../input/dickens/dickens'\nmeta = pd.read_csv(os.path.join(path, 'metadata.tsv'), delimiter='\\t')\nmeta.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Read the first part of one txt file\nfile1 = open(os.path.join(path, '924-0.txt'),'r')\n_ = file1.read(500)\nprint(_)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"files = {} # Create a dictionary of file names and contents\nfor f in os.listdir('/kaggle/input/dickens/dickens'):\n    if f.endswith('.txt'): \n        with open(os.path.join(path, f), \"r\") as file:\n            files[f] = file.read()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Convert to dataframe\n_ = pd.DataFrame.from_dict(files, orient='index', columns=['content']).reset_index()\ndickens = meta.merge(_, left_on='Path', right_on='index')\ndickens.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# BigQuery SQL\n\nBigQuery is a cloud data service by Google which we can use SQL language to retrieve data from databases hosted on Google Cloud. More information can be found on this [Kaggle tutorial](https://www.kaggle.com/rtatman/sql-scavenger-hunt-handbook) and [documentation of BigQuery](https://cloud.google.com/bigquery/docs). In this section we will walk through:\n- Getting datasets\n- Inspecting table lists and schema\n- Loading table to dataframe\n- Read data through a SQL query\n- Estimate data size before loading\n\nThere are many public datasets stored in BigQuery and accessible to public. See [this page](https://console.cloud.google.com/marketplace/browse?filter=solution-type:dataset&_ga=2.5846323.181324053.1598680127-1821649181.1596854139&pli=1) for details. In this section we will use Hacker News dataset as an example.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"from google.cloud import bigquery\n\nclient = bigquery.Client()\n\n# Get dataset\nhacker_ref = client.dataset('hacker_news', project='bigquery-public-data')\nhacker = client.get_dataset(hacker_ref)\n\n# List all table names\ntables = list(client.list_tables(hacker))\nfor table in tables:  \n    print(table.table_id)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get a table\ntable_ref = hacker.table('full')\ntable = client.get_table(table_ref)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Before loading the table, we can get some attributes\nprint ('No. of rows: ' + str(table.num_rows))\nprint ('Size (MB): ' + str(int(table.num_bytes / 1024768)))\nprint ('Columns:')\nprint (list(c.name for c in table.schema))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get first 100 rows\ntable_df = client.list_rows(table, max_results=100).to_dataframe()\ntable_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Write SQL query to get data\n# Get top 10 authors by number of articles\nquery = \"\"\"\nSELECT author, count(id) as stories\nFROM `bigquery-public-data.hacker_news.stories`\nGROUP BY author\nORDER BY count(id) DESC\n\"\"\"\n\nquery_job = client.query(query)\niterator = query_job.result()\nrows = list(iterator)\n\n# Transform the rows into a nice pandas dataframe\ntop_authors = pd.DataFrame(data=[list(x.values()) for x in rows], columns=list(rows[0].keys()))\n\n# Look at the first 10 headlines\ntop_authors.head(10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Reference: [BigQuery API reference](https://googleapis.dev/python/bigquery/latest/reference.html)","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"# Parquet files\n\nParquet is a file format suitable for storing large data, such as image data. read_parquet() function in pandas reads a parquet file into a pandas dataframe. We use dataset for [Bengali.AI Handwritten Grapheme Classification](https://www.kaggle.com/c/bengaliai-cv19/data) as an example.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"img = pd.read_parquet('../input/bengaliai-cv19/train_image_data_0.parquet')\nimg.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"img.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The documentation says: \n> Each parquet file contains tens of thousands of 137x236 grayscale images. The images have been provided in the parquet format for I/O and space efficiency. Each row in the parquet files contains an image_id column, and the flattened image.\n\nTo show the image, we need to reshape the vector in each row into a two-dimensional array.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"img2 = img.iloc[:,1:].values.reshape((-1,137,236,1))\n\nrow=3; col=4;\nplt.figure(figsize=(20,(row/col)*12))\nfor x in range(row*col):\n    plt.subplot(row,col,x+1)\n    plt.imshow(img2[x,:,:,0])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Sometimes we may want to resize the images for future analysis. We can use cv2 package.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"import cv2\n\nDIM = 64\n\nimg3 = np.zeros((img2.shape[0],DIM,DIM,1),dtype='float32')\nfor j in range(img2.shape[0]):\n    img3[j,:,:,0] = cv2.resize(img2[j,],(DIM,DIM),interpolation = cv2.INTER_AREA)\n\nrow=3; col=4;\nplt.figure(figsize=(20,(row/col)*12))\nfor x in range(row*col):\n    plt.subplot(row,col,x+1)\n    plt.imshow(img3[x,:,:,0])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Free up memory\ndel img","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# JSON files\n\nThis section will demonstrate how to use json package to read JSON data into a dataframe. We use [arXiv dataset](https://www.kaggle.com/Cornell-University/arxiv) as an example.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"import json\n\ndata  = []\nwith open(\"/kaggle/input/arxiv/arxiv-metadata-oai-snapshot.json\", 'r') as f:\n    for line in f: \n        data.append(json.loads(line))\n\nprint(\"No. of records: {}\".format(len(data)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# See first item\ndata[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Convert to dataframe - due to memory issue we only load the first 1000 items\ndf = pd.DataFrame(data[:1000])\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Free up some memory\ndel data","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# XML files\n\nThe last section involves loading xml files, which requires xml package. We use [COVID-19 Clinical Trials dataset](https://www.kaggle.com/parulpandey/covid19-clinical-trials-dataset) as example.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"from xml.etree import ElementTree\n\npath = '../input/covid19-clinical-trials-dataset/COVID-19 CLinical trials studies/'\n\nfiles = os.listdir(path)\nprint('Total Researches going on: ',len(files))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Inspect the first element\nfile_path = os.path.join(path, files[0])\ntree = ElementTree.parse(file_path)\nroot = tree.getroot()\nprint (root.tag, root.attrib)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Look at all the children nodes\nfor child in root:\n    print (child.tag, child.attrib)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_temp = pd.DataFrame()\ndf = pd.DataFrame()\ni = 0\n\nfor file in files[:10]: # Get the first 10 studies\n    file_path = os.path.join(path, file)\n    tree = ElementTree.parse(file_path)\n    root = tree.getroot()\n    trial = {} # Initialize dictionary\n    \n    # read tags using root.find() method\n    trial['nct_id'] = root.find('id_info').find('nct_id').text\n    trial['brief_title'] = root.find('brief_title').text\n    trial['overall_status'] = root.find('overall_status').text\n    \n    df_temp  = pd.DataFrame(trial,index=[i])\n    i=i+1\n    \n    df = pd.concat([df, df_temp])    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","collapsed":true,"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":false},"cell_type":"markdown","source":"Reference: [The ElementTree XML API](https://docs.python.org/3/library/xml.etree.elementtree.html)\n\nThat's is for now. The best way to practice reading data is to find as many datasets you feel interested and load them and inspect them. You will encounter different issues or requirements in different datasets, and this notebook will be updated to illustrate more useful skills. Go find your favorite datasets to practice!","execution_count":null}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}