"""
<a id="2"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">DIFFERENT FILE FORMATS</p>
<img src="https://fiverr-res.cloudinary.com/images/q_auto,f_auto/gigs/167661619/original/a0f482c0fbcef87db57a1e47976c5fbd3c255041/split-or-break-join-or-combine-csv-json-xml-xlsx-files.png">
"""
"""
<a id="2"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">INTRODUCTION</p>
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">In this notebook, We will learn to work with different file formats like csv, json, xml, ods and many more. I will keep updating this notebook as well as the Dataset used, by adding new file formats. My goal is to make this notebook a one-stop shop for all kinds of information concerning file formats so that we don't have to go though multiple sources on internet to understand it. </p>
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">If you like my effort please <b>UPVOTE</b>, it really motivates me. </p>
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">Dataset used:- <a href="https://www.kaggle.com/raj401/understanding-different-file-formats" id="idoflink">https://www.kaggle.com/raj401/understanding-different-file-formats</a> </p>
"""
"""
<a id='top'></a>
<div class="list-group" id="list-tab" role="tablist">
<p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">TABLE OF CONTENTS</p>   
    
* [1. IMPORTING LIBRARIES](#1)
    
* [2. CSV](#2)

* [3. TSV](#3)
    
* [4. JSON](#4)  
    
* [5. ODS](#5)  
    
* [6. XLS](#6) 
      
* [7. XML](#7)
    
* [8. CONCLUSION](#8)
    
* [9. END](#9)


<a id="1"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">IMPORTING LIBRARIES</p>
"""
import numpy as np
import pandas as pd
import json
"""
<a id="2"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">CSV</p>
"""
csv_df = pd.read_csv("../input/understanding-different-file-formats/datafile.csv")
print(type(csv_df))
csv_df.head(3)
"""
<a id="3"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">TSV</p>
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">TSV is Tab separated value file. It is very similar to csv. So we read it by pd.read_csv() by just passing an extra delimiter="\t" to make it aware that it is tab separated and not a csv. </p>
"""
tsv_df = pd.read_csv("../input/understanding-different-file-formats/datafile.tsv", delimiter="\t")  # you can remove delimiter part to see how our output chages
print(type(tsv_df))
tsv_df.head(3)
"""
<a id="4"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">JSON</p>
<p style="font-family:newtimeroman;font-size:120%;color:#444160;"> <i>note:- When reading <b>json</b> file on your local machine, specify the full path(starting from root directory), as using relative path may give error. </p>
"""
f = open("../input/understanding-different-file-formats/datafile.json")
B = json.load(f)
print(type(B))
"""
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">Here doing <b><i>pd.DataFrame(B)</i></b> will give error because the keys of this dictionary don't contain same no of elements. Let's us find what are it's keys.</p>
"""
print(B.keys())
"""
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">So it has two keys 'fields' which contain the information about the fields/columns and 'data' which is the actual data.
Let's look at individual keys</p>
"""
Field_df = pd.DataFrame(B['fields'])
Field_df.head(3)
Data_df = pd.DataFrame(B['data'])
Data_df.head(3)
"""
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">So we need to create dataframe using 'data' and give name of features/columns using 'fields'.</p>
"""
Field_df.label.tolist() # this is list of all the field names
json_df = pd.DataFrame(B['data'],columns = Field_df.label.tolist())
json_df.head(3)
"""
<a id="5"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">ODS</p>
"""
ods_df = pd.read_excel("../input/understanding-different-file-formats/datafile.ods", engine='odf')
print(type(ods_df))
ods_df.head(3)
"""
<a id="6"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">XLS</p>
"""
!pip install xlrd  # install the dependency
D = pd.read_excel("../input/understanding-different-file-formats/datafile.xls", sheet_name = None)
print(type(D))
"""
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">It is a dictionary because it assumes multiple sheet and it stores each sheet by a key. Each sheet is stored as a DataFrame inside the dictionary. Let's look at it's keys to understand more.</p>
"""
D.keys() 
"""
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">It has only one key which means it has only one sheet. We can now acces this sheet directly by the key. </p>
"""
xls_df = D['Worksheet']  # it is a DataFrame
print(type(xls_df))  
xls_df.head(3)
"""
<a id="7"></a>
# <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">XML</p>
"""
"""
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">Let's convert the csv_df dataframe to xml.</p>
"""
def func(row):
    xml = ['<item>']
    for field in row.index:
        xml.append('  <field name="{0}">{1}</field>'.format(field, row[field]))
    xml.append('</item>')
    return '\n'.join(xml)
t= "\n".join(csv_df.apply(func, axis=1)) # converts dataframe to xml
"""
<a id="8"></a>
## <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">CONCLUSION</p>
<p style="font-family:newtimeroman;font-size:120%;color:#444160;">That is all for now. I will be adding more file formats in the collection. If you have any doubt or suggestion feel free to ask below. </p>

**<span style="color:#444160;"> Thanks!</span>**
<a id="9"></a>
## <p style="background-color:#FBAB7E;font-family:newtimeroman;color:#444160;font-size:150%;text-align:center;border-radius:20px 60px;">END</p>
"""