<jupyter_start><jupyter_text>Data formats 1 - introduction [Download exercises zip](../_static/generated/formats.zip)[Browse files online](https://github.com/DavidLeoni/softpython-en/tree/master/formats) IntroductionIn these tutorials we will see how to load and write tabular data such as CSV, and we will mention tree-like data such as JSON files. We will also spend a couple of words about  opendata catalogs and licenses (creative commons). In these tutorials we will review main data formats:Textual formats* Line files* CSV (tabular data)* JSON (tree-like data, just mention)Binary formats (just mention)* fogli ExcelWe will also mention open data catalogs and licenses (Creative Commons)  What to do1. unzip exercises in a folder, you should get something like this: ```formats    formats1-lines.ipynb         formats1-lines-sol.ipynb    formats2-csv.ipynb         formats2-csv-sol.ipynb        formats3-json.ipynb         formats3-json-sol.ipynb        formats4-chal.ipynb    jupman.py```**WARNING**: to correctly visualize the notebook, it MUST be in an unzipped folder !2. open Jupyter Notebook from that folder. Two things should open, first a console and then browser. The browser should show a file list: navigate the list and open the notebook `formats/formats1-lines.ipynb`3. Go on reading that notebook, and follow instuctions inside.Shortcut keys:- to execute Python code inside a Jupyter cell, press `Control + Enter`- to execute Python code inside a Jupyter cell AND select next cell, press `Shift + Enter`- to execute Python code inside a Jupyter cell AND a create a new cell aftwerwards, press `Alt + Enter`- If the notebooks look stuck, try to select `Kernel -> Restart`  Line filesLine files are typically text files which contain information grouped by lines. An example using historical characters might be like the following:```Leonardoda VinciSandroBotticelliNiccolò Macchiavelli```We can immediately see a regularity: first two lines contain data of <NAME>, second one the name and then the surname. Successive lines instead have data of Sandro Botticelli, with again first the name and then the surname and so on.We might want to do a program that reads the lines and prints on the terminal names and surnames like the following:```<NAME> <NAME>```To start having an approximation of the final result, we can open the file, read only the first line and print it:<jupyter_code>with open('people-simple.txt', encoding='utf-8') as f:
    line=f.readline()
    print(line)<jupyter_output>Leonardo<jupyter_text>What happened? Let's examing first rows: open commandThe command```pythonopen('people-simple.txt', encoding='utf-8')```allows us to open the text file by telling PYthon the file path `'people-simple.txt'` and the encoding in which it was written (`encoding='utf-8'`).   The encodingThe encoding dependes on the operating system and on the editor used to write the file. When we open a file, Python is not capable to divine the encoding, and if we do not specify anything Python might open the file assuming an encoding different from the original - in other words, if we omit the encoding (or we put a wrong one) we might end up seeing weird characters (like little squares instead of accented letters).In general, when you open a file, try first to specify the encoding `utf-8` which is the most common one. If it doesn't work try others, for example for files written in south Europe with Windows you might check `encoding='latin-1'`. If you open a file written elsewhere, you might need other encodings. For more in-depth information, you can read [Dive into Python - Chapter 4 - Strings](https://diveintopython3.problemsolving.io/strings.html), and [Dive into Python - Chapter 11 - File](https://diveintopython3.problemsolving.io/files.html), **both of which are extremely recommended readings**.  with blockThe `with` defines a block with instructions inside:```pythonwith open('people-simple.txt', encoding='utf-8') as f:    line=f.readline()    print(line)```We used the `with` to tell PYthon that in any case, even if errors occur, we want that after having used the file, that is after having executed the instructions inside the internal block (the `line=f.readline()` and `print(line)`) Python must automatically close the file. Properly closing a file avoids to waste memory resources and creating hard to find paranormal errors. If you want to avoid hunting for never closed zombie files, always remember to open all files in `with` blocks! Furthermore, at the end of the row in the part `as f:` we assigned the file to a variable hereby called `f`, but we could have used any other name we liked.**WARNING**: To indent the code, ALWAYS use sequences of four white spaces. Sequences of 2 spaces. Sequences of only 2 spaces even if allowed are not recommended.**WARNING**: Depending on the editor you use, by pressing TAB you might get a sequence o f white spaces like it happens in Jupyter (4 spaces which is the recommended length), or a special tabulation character (to avoid)! As much as this annoying this distinction might appear, remember it because it might generate very hard to find errors.**WARNING**: In the commands to create blocks such as `with`, always remember to put the character of colon `:` at the end of the line ! The command```    line=f.readline()```puts in the variable `line` the entire line, like a string. Warning: the string will contain at the end the special character of line return !You might wonder where that `readline` comes from. Like everything in Python, our variable `f` which represents the file we just opened is an object, and like any object, depending on its type, it has particular methods we can use on it. In this case the method is `readline`. The following command prints the string content:```python    print(line) ``` **✪ 1.1 EXERCISE**: Try to rewrite here the block we've just seen, and execute the cell by pressing Control-Enter. Rewrite the code with the fingers, not with copy-paste ! Pay attention to correct indentation with spaces in the block.<jupyter_code># write here

with open('people-simple.txt', encoding='utf-8') as f:
    line=f.readline()
    print(line)<jupyter_output>Leonardo<jupyter_text>**✪ 1.2 EXERCISE**: you might wondering what exactly is that `f`, and what exatly the method `readlines` should be doing. When you find yourself in these situations, you might help yourself with functions `type` and `help`. This time, directly copy paste the same code here, but insert inside `with` block the commands:* `print(type(f))`* `help(f)`* `help(f.readline)`       Attention: remember the f. before the readline !!Every time you add something, try to execute with Control+Enter and see what happens<jupyter_code># write here the code (copy and paste)
with open('people-simple.txt', encoding='utf-8') as f:
    line=f.readline()
    print(line)
    print(type(f))    
    help(f.readline)
    help(f)<jupyter_output>Leonardo

<class '_io.TextIOWrapper'>
Help on built-in function readline:

readline(size=-1, /) method of _io.TextIOWrapper instance
    Read until newline or EOF.
    
    Returns an empty string if EOF is hit immediately.

Help on TextIOWrapper object:

class TextIOWrapper(_TextIOBase)
 |  TextIOWrapper(buffer, encoding=None, errors=None, newline=None, line_buffering=False, write_through=False)
 |  
 |  Character and line based layer over a BufferedIOBase object, buffer.
 |  
 |  encoding gives the name of the encoding that the stream will be
 |  decoded or encoded with. It defaults to locale.getpreferredencoding(False).
 |  
 |  errors determines the strictness of encoding and decoding (see
 |  help(codecs.Codec) or the documentation for codecs.register) and
 |  defaults to "strict".
 |  
 |  newline controls how line endings are handled. It can be None, '',
 |  '\n', '\r', and '\r\n'.  It works as follows:
 |  
 |  * On input, if newline is None, universal newlines mode is
 |    en[...]<jupyter_text>First we put the content of the first line into the variable `line`, now we might put it in a variable witha  more meaningful name, like `name`. Also, we can directly read the next row into the variable `surname` and then print the concatenation of both:<jupyter_code>with open('people-simple.txt', encoding='utf-8') as f:
    name=f.readline()
    surname=f.readline()
    print(name + ' ' + surname)<jupyter_output>Leonardo
 <NAME><jupyter_text>**PROBLEM !** The printing puts a weird carriage return. Why is that? If you remember, first we said that `readline` reads the line content in a string adding to the end also the special newline character. To eliminate it, you can use the command `rstrip()`:<jupyter_code>with open('people-simple.txt', encoding='utf-8') as f:
    name=f.readline().rstrip()
    surname=f.readline().rstrip()
    print(name + ' ' + surname)<jupyter_output><NAME><jupyter_text>**✪ 1.3 EXERCISE**: Again, rewrite the block above in the cell below, ed execute the cell with Control+Enter. Question: what happens if you use `strip()` instead of `rstrip()`? What about `lstrip()`? Can you deduce the meaning of `r` and `l`? If you can't manage it, try to use python command `help` by calling `help(string.rstrip)`<jupyter_code># write here

with open('people-simple.txt', encoding='utf-8') as f:
    name=f.readline().rstrip()
    surname=f.readline().rstrip()
    print(name + ' ' + surname)<jupyter_output><NAME><jupyter_text>Very good, we have the first line ! Now we can read all the lines in sequence. To this end, we can use a `while` cycle:<jupyter_code>with open('people-simple.txt', encoding='utf-8') as f:
    line=f.readline()
    while line != "":                
        name = line.rstrip()
        surname=f.readline().rstrip()
        print(name + ' ' + surname)
        line=f.readline()<jupyter_output><NAME>
<NAME>
<NAME><jupyter_text>**NOTE**: In Python there are [shorter ways](https://thispointer.com/5-different-ways-to-read-a-file-line-by-line-in-python/) to read a text file line by line, we used this approach  to make explicit all passages. What did we do? First, we added a `while` cycle in a new block**WARNING**: In new block, since it is already within the external `with`, the instructions are indented of 8 spaces and not 4! If you use the wrong spaces, bad things happen !We first read a line, and two cases are possible: a. we are the end of the file (or file is empty) : in this case  `readline()` call returns an empty stringb. we are not at the end of the file: the first line is put as a string inside the variable `line`. Since Python internally uses a pointer to keep track at which position we are when reading inside the file, after the read such pointer is moved at the beginning of the next line. This way the next call to `readline()` will read a line from the new position.In `while` block we tell Python to continue the cycle as long as `line` is _not_ empty. If this is the case, inside the `while` block we parse the name from the line and put it in variable `name` (removing extra newline character with `rstrip()` as we did before), then we proceed reading the next line and parse the result inside the `surname` variable. Finally, we read again a line into the `line` variable so it will be ready for the next round of name extraction. If line is empty the cycle will terminate:```pythonwhile line != "":                    enter cycle if line contains characters    name = line.rstrip()             parses the name    surname=f.readline().rstrip()    reads next line and parses surname    print(name + ' ' + surname)         line=f.readline()                read next line``` **✪ 1.4 EXERCISE**: As before, rewrite in the cell below the code with the `while`, paying attention to the indentation (for the external `with` line use copy-and-paste):<jupyter_code># write here the code of internal while

with open('people-simple.txt', encoding='utf-8') as f:
    line=f.readline()
    while line != "":                
        name = line.rstrip()
        surname=f.readline().rstrip()
        print(name + ' ' + surname)
        line=f.readline()<jupyter_output><NAME>
<NAME>
<NAME><jupyter_text>people-complex line fileLook at the file `people-complex.txt`:```name: Leonardosurname: da Vincibirthdate: 1452-04-15name: Sandrosurname: Botticellibirthdate: 1445-03-01name: Niccolò surname: Macchiavellibirthdate: 1469-05-03```Supposing to read the file to print this output, how would you do it? ```<NAME>, 1452-04-15<NAME>, 1445-03-01<NAME>, 1469-05-03``` **Hint 1**: to obtain the string `'abcde'`, the substring  `'cde'`, which starts at index 2, you can ue the operator square brackets, using the index followed by colon `:`<jupyter_code>x = 'abcde'
x[2:]
x[3:]<jupyter_output><empty_output><jupyter_text>**Hint 2**: To know the length of a string, use the function `len`:<jupyter_code>len('abcde')<jupyter_output><empty_output><jupyter_text>**✪ 1.5 EXERCISE**: Write here the solution of the exercise 'People complex':<jupyter_code># write here  
        
with open('people-complex.txt', encoding='utf-8') as f:
    line=f.readline()
    while line != "":                
        name = line.rstrip()[len("name: "):]
        surname= f.readline().rstrip()[len("surname: "):]
        born = f.readline().rstrip()[len("birthdate: "):]
        print(name + ' ' + surname + ', ' + born)
        line=f.readline()<jupyter_output><NAME>, 1452-04-15
<NAME>, 1445-03-01
<NAME>, 1469-05-03<jupyter_text>Exercise - line file immersione-in-python-toc✪✪✪ This exercise is more challenging, if you are a beginner you might skip it and go on to CSVsThe book Dive into Python is nice and for the italian version there is a PDF, which has a problem though: if you try to print it, you will discover that the index is missing. Without despairing, we found a program to extract titles in a file as follows, but you will discover it is not exactly nice to see. Since we are Python ninjas, we decided to transform raw titles in a [real table of contents](http://softpython.readthedocs.io/it/latest/_static/toc-immersione-in-python-3.txt). Sure enough there are smarter ways to do this, like loading the pdf in Python with an appropriate module for pdfs, still this makes for an interesting exercise.You are given the file `immersione-in-python-toc.txt`:```BookmarkBeginBookmarkTitle: Il vostro primo programma PythonBookmarkLevel: 1BookmarkPageNumber: 38BookmarkBeginBookmarkTitle: Immersione!BookmarkLevel: 2BookmarkPageNumber: 38BookmarkBeginBookmarkTitle: Dichiarare funzioniBookmarkLevel: 2BookmarkPageNumber: 41BookmarkBeginintBookmarkTitle: Argomenti opzionali e con nomeBookmarkLevel: 3BookmarkPageNumber: 42BookmarkBeginBookmarkTitle: Scrivere codice leggibileBookmarkLevel: 2BookmarkPageNumber: 44BookmarkBeginBookmarkTitle: Stringhe di documentazioneBookmarkLevel: 3BookmarkPageNumber: 44BookmarkBeginBookmarkTitle: Il percorso di ricerca di importBookmarkLevel: 2BookmarkPageNumber: 46BookmarkBeginBookmarkTitle: Ogni cosa &232; un oggettoBookmarkLevel: 2BookmarkPageNumber: 47```Write a python program to print the following output:```   Il vostro primo programma Python  38      Immersione!  38      Dichiarare funzioni  41         Argomenti opzionali e con nome  42      Scrivere codice leggibile  44         Stringhe di documentazione  44      Il percorso di ricerca di import  46      Ogni cosa è un oggetto  47```For this exercise, you will need to insert in the output artificial spaces, in a qunatity determined by the rows `BookmarkLevel`**QUESTION**: what's that weird value `&232;` at the end of the original file? Should we report it in the output?**HINT 1**: To convert a string into an integer number, use the function `int`:<jupyter_code>x = '5'
x
int(x)<jupyter_output><empty_output><jupyter_text>**Warning**: `int(x)` returns a value, and never modifies the argument `x`!   **HINT 2**: To substitute a substring in a string, you can use the method `.replace`:<jupyter_code>x = 'abcde'
x.replace('cd', 'HELLO' )<jupyter_output><empty_output><jupyter_text>**HINT 3**: while there is only one sequence to substitute, `replace` is fine, but if we had a milion of horrible sequences like `&gt;`, `&62;`, `&x3e;`, what should we do? As good data cleaners, we recognize these are [HTML escape sequences](https://corsidia.com/materia/web-design/caratterispecialihtml), so we could use  methods specific to sequences like [html.escape](https://docs.python.org/3/library/html.htmlhtml.unescape). TRy it instead of `replace` and check if it works!NOTE: Before using `html.unescape`, import the module `html` with the command: ```pythonimport html``` **HINT 4**: To write _n_ copies of a character, use `*` like this:<jupyter_code>"b" * 3
"b" * 7<jupyter_output><empty_output><jupyter_text>**IMPLEMENTATION**: Write here the solution for the line file `immersione-in-python-toc.txt`, and try execute it by pressing Control + Enter:<jupyter_code># write here 

import html

with open("immersione-in-python-toc.txt", encoding='utf-8') as f:

    line=f.readline()  
    while line != "":
        line = f.readline().strip()
        title = html.unescape(line[len("BookmarkTitle: "):])
        line=f.readline().strip()
        level = int(line[len("BookmarkLevel: "):])
        line=f.readline().strip()
        page = line[len("BookmarkPageNumber: "):]
        print(("   " * level) + title + "  " + page)
        line=f.readline()<jupyter_output>Il vostro primo programma Python  38
      Immersione!  38
      Dichiarare funzioni  41
         Argomenti opzionali e con nome  42
      Scrivere codice leggibile  44
         Stringhe di documentazione  44
      Il percorso di ricerca di import  46
      Ogni cosa è un oggetto  47
{'fraction_non_alphanumeric': 0.09320498301245753, 'fraction_numerical': 0.011325028312570781, 'mean_word_length': 4.343721633888048, 'pattern_counts': {'":': 5, '<': 70, '<?xml version=': 0, '>': 71, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '11617', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 5235, 'n_tokens_neox': 5020, 'n_words': 2220, 'path': 'formats/formats1-lines-sol.ipynb', 'repo_name': 'simoneb1x/softpython-en', 'size': '17881'}