2.9. DataFrame Export

2.9.1. Export data

  • File paths works also with DATAs

  • SQL functions uses SQLAlchemy, which supports many RDBMS

import pandas as pd


df = pd.DataFrame()

# Important
df.to_csv()
df.to_dict()
df.to_excel()
df.to_json()
df.to_sql()

# Other
df.to_clipboard()
df.to_dense()
df.to_feather()
df.to_gbq()
df.to_hdf()
df.to_html()
df.to_latex()
df.to_msgpack()
df.to_numpy()
df.to_parquet()
df.to_period()
df.to_pickle()
df.to_records()
df.to_sparse()
df.to_stata()
df.to_string()
df.to_timestamp()
df.to_xarray()

2.9.2. Assignments

Code 2.50. Solution
"""
* Assignment: DataFrame Export CSV
* Complexity: easy
* Lines of code: 3 lines
* Time: 5 min

English:
    1. Read data from `DATA` as `result: pd.DataFrame`
    2. Select 146 head rows, and last 11 from it
    3. Export data from column `Event` to file the `FILE`
    4. Data has to be in CSV format
    5. Run doctests - all must succeed

Polish:
    1. Wczytaj dane z `DATA` jako `result: pd.DataFrame`
    2. Wybierz pierwszych 146 wierszy, a z nich ostatnie 11
    3. Wyeksportuj dane z kolumny `Event` do pliku `FILE`
    4. Dane mają być w formacie CSV
    5. Uruchom doctesty - wszystkie muszą się powieść

Tests:
    >>> import sys; sys.tracebacklimit = 0

    >>> result = open(FILE).read()
    >>> print(result)  # doctest: +NORMALIZE_WHITESPACE
    ,Event
    135,LM lunar landing.
    136,LM powered descent  engine cutoff.
    137,Decision made to  proceed with EVA prior to first rest period.
    138,Preparation for EVA  started.
    139,EVA started (hatch  open).
    140,CDR completely outside  LM on porch.
    141,Modular equipment  stowage assembly deployed (CDR).
    142,First clear TV picture  received.
    143,"CDR at foot of ladder  (starts to report, then pauses to listen)."
    144,CDR at foot of ladder  and described surface as “almost like a powder.”
    145,1st step  taken lunar surface (CDR). “That’s one small step for a man…one giant leap  for mankind.”
    <BLANKLINE>
    >>> from os import remove
    >>> remove(FILE)
"""

import pandas as pd

DATA = 'https://python.astrotech.io/_static/apollo11.html'
FILE = r'_temporary.csv'


Code 2.51. Solution
"""
* Assignment: DataFrame Export JSON
* Complexity: easy
* Lines of code: 3 lines
* Time: 3 min

English:
    1. Read data from `DATA` as `result: pd.DataFrame`
    2. Select 146 head rows, and last 11 from it
    3. Export data from column `Event` to file the `FILE`
    4. Data has to be in JSON format
    5. Run doctests - all must succeed

Polish:
    1. Wczytaj dane z `DATA` jako `result: pd.DataFrame`
    2. Wybierz pierwszych 146 wierszy, a z nich ostatnie 11
    3. Wyeksportuj dane z kolumny `Event` do pliku `FILE`
    4. Dane mają być w formacie JSON
    5. Uruchom doctesty - wszystkie muszą się powieść

Tests:
    >>> import sys; sys.tracebacklimit = 0

    >>> result = open(FILE).read()
    >>> import json
    >>> json.loads(result)  # doctest: +NORMALIZE_WHITESPACE
    {'135': 'LM lunar landing.',
     '136': 'LM powered descent  engine cutoff.',
     '137': 'Decision made to  proceed with EVA prior to first rest period.',
     '138': 'Preparation for EVA  started.',
     '139': 'EVA started (hatch  open).',
     '140': 'CDR completely outside  LM on porch.',
     '141': 'Modular equipment  stowage assembly deployed (CDR).',
     '142': 'First clear TV picture  received.',
     '143': 'CDR at foot of ladder  (starts to report, then pauses to listen).',
     '144': 'CDR at foot of ladder  and described surface as “almost like a powder.”',
     '145': '1st step  taken lunar surface (CDR). “That’s one small step for a man…one giant leap  for mankind.”'}
    >>> from os import remove
    >>> remove(FILE)
"""

import pandas as pd

DATA = 'https://python.astrotech.io/_static/apollo11.html'
FILE = r'_temporary.json'

# pd.DataFrame: dump DATA to FILE in JSON format
result = ...


Code 2.52. Solution
"""
* Assignment: DataFrame Export Pickle
* Complexity: easy
* Lines of code: 3 lines
* Time: 3 min

English:
    1. Read data from `DATA` as `result: pd.DataFrame`
    2. Select 146 head rows, and last 11 from it
    3. Export data from column `Event` to file the `FILE`
    4. Data has to be in JSON format
    5. Run doctests - all must succeed

Polish:
    1. Wczytaj dane z `DATA` jako `result: pd.DataFrame`
    2. Wybierz pierwszych 146 wierszy, a z nich ostatnie 11
    3. Wyeksportuj dane z kolumny `Event` do pliku `FILE`
    4. Dane mają być w formacie JSON
    5. Uruchom doctesty - wszystkie muszą się powieść

Tests:
    >>> import sys; sys.tracebacklimit = 0

    >>> type(result) is pd.DataFrame
    True
    >>> pd.set_option('display.width', 500)
    >>> pd.set_option('display.max_columns', 10)
    >>> pd.set_option('display.max_rows', 20)

    >>> pd.read_pickle(FILE)
    135                                    LM lunar landing.
    136                   LM powered descent  engine cutoff.
    137    Decision made to  proceed with EVA prior to fi...
    138                        Preparation for EVA  started.
    139                           EVA started (hatch  open).
    140                 CDR completely outside  LM on porch.
    141    Modular equipment  stowage assembly deployed (...
    142                    First clear TV picture  received.
    143    CDR at foot of ladder  (starts to report, then...
    144    CDR at foot of ladder  and described surface a...
    145    1st step  taken lunar surface (CDR). “That’s o...
    Name: Event, dtype: object

    >>> from os import remove
    >>> remove(FILE)
"""

import pandas as pd

DATA = 'https://python.astrotech.io/_static/apollo11.html'
FILE = r'_temporary.pkl'

# pd.DataFrame: dump DATA to FILE in Pickle format
result = ...

Code 2.53. Solution
"""
* Assignment: DataFrame Export SQL
* Complexity: easy
* Lines of code: 4 lines
* Time: 5 min

English:
    1. Read data from `DATA` as `result: pd.DataFrame`
    2. Select 146 head rows, and last 11 from it
    3. Export data from column `Event` to database `FILE` to table `apollo11`
    4. Run doctests - all must succeed

Polish:
    1. Wczytaj dane z `DATA` jako `result: pd.DataFrame`
    2. Wybierz pierwszych 146 wierszy, a z nich ostatnie 11
    3. Wyeksportuj dane z kolumny `Event` do bazy danych `FILE` do tabeli `apollo11`
    4. Uruchom doctesty - wszystkie muszą się powieść

Tests:
    >>> import sys; sys.tracebacklimit = 0

    >>> db = sqlite3.connect(FILE)
    >>> result = db.execute('SELECT * FROM apollo11')
    >>> list(result)  # doctest: +NORMALIZE_WHITESPACE
    [(135, 'LM lunar landing.'),
     (136, 'LM powered descent  engine cutoff.'),
     (137, 'Decision made to  proceed with EVA prior to first rest period.'),
     (138, 'Preparation for EVA  started.'),
     (139, 'EVA started (hatch  open).'),
     (140, 'CDR completely outside  LM on porch.'),
     (141, 'Modular equipment  stowage assembly deployed (CDR).'),
     (142, 'First clear TV picture  received.'),
     (143, 'CDR at foot of ladder  (starts to report, then pauses to listen).'),
     (144, 'CDR at foot of ladder  and described surface as “almost like a powder.”'),
     (145, '1st step  taken lunar surface (CDR). “That’s one small step for a man…one giant leap  for mankind.”')]
    >>> from os import remove
    >>> remove(FILE)
"""

import sqlite3
import pandas as pd

DATA = 'https://python.astrotech.io/_static/apollo11.html'
FILE = r'_temporary.sqlite3'


# pd.DataFrame: dump DATA to FILE in SQLite3 format
result = ...