Daten lesen und schreiben

Digits as File Input and Output

All die starken Daten-Strukturen wie Series und DataFrames würden fast nichts nützen, wenn das Pandas-Modul keine Funktionalitäten unterstützen würde, um Daten einzulesen und rauszuschreiben. Dabei geht es nicht um die einfache Möglichkeit mit Dateien umzugehen. Damit der Nutzen für Data-Scientists sichtbar wird, müssen die wichtigsten Daten-Formate unterstützt werden, wie z.B.:

Trenner-Separierte Werte

Die meisten Menschen verwenden "CSV-Dateien" als Synonym für Trenner-Separierte Dateien. Sie beachten nicht die Tatsache, das CSV ein Akronym ist für "comma separated values", was in den meisten Situationen nicht der Fall ist. Pandas verwendet "csv" ebenfalls in Zusammenhängen, in denen "dsv" die passendere Bezeichnung wäre.

Trenner-Separierte Werte (Delimiter-separated values - DSV) sind definiert und abgelegt in zwei-dimensionalen Arrays, bei denen die Werte mit zweckmässig definierten Trennzeichen in jeder Zeile getrennt sind. Diese Arte und Weise wird oft in Kombination mit Tabellen-Programmen eingesetzt, die Data als DSV ein- und auslesen können. Auch wird die Implementierung in allgemeinen Datenaustauscheformaten verwendet.

Wir bezeichnen eine Test-Datei als "Getrennte Text Datei", wenn es Text im DSV-Format enthält.

Zum Beispiel ist die Datei dollar_euro.txt eine getrennte Text-Datei und nutzt Tabulatoren (\t) als Trennzeichen.

CSV und DSV Dateien lesen

Pandas bietet 2 Wege um CSV/DSV Dateien zu lesen.Das bedeutet konkret:

Es gibt zwischen beiden Methoden keinen grossen Unterschied.D.h. es gibt in manchen Fällen verschiedene Default-Werte und </code>read_csv</csv> hat mehr Parameter.Wir konzentrieren uns auf </code>read_csv</csv>, weil </code>DataFrame.from_csv</csv> wegen Auf- und Abwärtskompatibilität innerhalb von Pandas gehalten wird.

import pandas as pdexchange_rates = pd.read_csv("data1/dollar_euro.txt",                             sep="\t")print(exchange_rates)
    Year   Average  Min USD/EUR  Max USD/EUR  Working days0   2016  0.901696     0.864379     0.959785           2471   2015  0.901896     0.830358     0.947688           2562   2014  0.753941     0.716692     0.823655           2553   2013  0.753234     0.723903     0.783208           2554   2012  0.778848     0.743273     0.827198           2565   2011  0.719219     0.671953     0.775855           2576   2010  0.755883     0.686672     0.837381           2587   2009  0.718968     0.661376     0.796495           2568   2008  0.683499     0.625391     0.802568           2569   2007  0.730754     0.672314     0.775615           25510  2006  0.797153     0.750131     0.845594           25511  2005  0.805097     0.740357     0.857118           25712  2004  0.804828     0.733514     0.847314           25913  2003  0.885766     0.791766     0.963670           25514  2002  1.060945     0.953562     1.165773           25515  2001  1.117587     1.047669     1.192748           25516  2000  1.085899     0.962649     1.211827           25517  1999  0.939475     0.848176     0.998502           261

Wie wir gesehen haben, benutzt read_csv() automatisch die erste Zeile als Überschriften für die Spalten.Wir können den Spalten auch beliebige andere Namen geben. Dazu muss die erste Zeile überprungen werden header = 0 und eine Liste mit Spalten-Namen zugewiesen werden an den Parameter names:

import pandas as pdexchange_rates = pd.read_csv("data1/dollar_euro.txt",                             sep="\t",                             header=0,                             names=["year", "min", "max", "days"])print(exchange_rates)
          year       min       max  days2016  0.901696  0.864379  0.959785   2472015  0.901896  0.830358  0.947688   2562014  0.753941  0.716692  0.823655   2552013  0.753234  0.723903  0.783208   2552012  0.778848  0.743273  0.827198   2562011  0.719219  0.671953  0.775855   2572010  0.755883  0.686672  0.837381   2582009  0.718968  0.661376  0.796495   2562008  0.683499  0.625391  0.802568   2562007  0.730754  0.672314  0.775615   2552006  0.797153  0.750131  0.845594   2552005  0.805097  0.740357  0.857118   2572004  0.804828  0.733514  0.847314   2592003  0.885766  0.791766  0.963670   2552002  1.060945  0.953562  1.165773   2552001  1.117587  1.047669  1.192748   2552000  1.085899  0.962649  1.211827   2551999  0.939475  0.848176  0.998502   261

Exercise

The file "countries_population.csv" is a csv file, containing the population numbers of all countries (July 2014). The delimiter of the file is a space and commas are used to separate groups of thousands in the numbers. The method 'head(n)' of a DataFrame can be used to give out only the first n rows or lines. Read the file into a DataFrame.

Solution:

pop = pd.read_csv("data1/countries_population.csv",                   header=None,                  names=["Country", "Population"],                  index_col=0,                  quotechar="'",                   sep=" ",                   thousands=",")print(pop.head(5))  
                PopulationCountry                   China           1355692576India           1236344631European Union   511434812United States    318892103Indonesia        253609643

Writing csv Files

Writing CSV Files

We can create csv (or dsv) files with the method "to_csv". Before we do this, we will prepare some data to output, which we will write to a file. We have two csv files with population data for various countries. countries_male_population.csv contains the figures of the male populations and countries_male_population.csv correspondingly the numbers for the female populations. We will create a new csv file with the sum:

column_names = ["Country"] + list(range(2002, 2013))male_pop = pd.read_csv("data1/countries_male_population.csv",                  header=None,                  index_col=0,                  names=column_names)female_pop = pd.read_csv("data1/countries_female_population.csv",                         header=None,                         index_col=0,                         names=column_names)population = male_pop + female_pop
population
Wir können die folgende Ausgabe erwarten, wenn wir den obigen Python-Code ausführen:
2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012
Country
Australia 19640979.0 19872646 20091504 20339759 20605488 21015042 21431781 21874920 22342398 22620554 22683573
Austria 8139310.0 8067289 8140122 8206524 8265925 8298923 8331930 8355260 8375290 8404252 8443018
Belgium 10309725.0 10355844 10396421 10445852 10511382 10584534 10666866 10753080 10839905 10366843 11035958
Canada NaN 31361611 31372587 31989454 32299496 32649482 32927372 33327337 33334414 33927935 34492645
Czech Republic 10269726.0 10203269 10211455 10220577 10251079 10287189 10381130 10467542 10506813 10532770 10505445
Denmark 5368354.0 5383507 5397640 5411405 5427459 5447084 5475791 5511451 5534738 5560628 5580516
Finland 5194901.0 5206295 5219732 5236611 5255580 5276955 5300484 5326314 5351427 5375276 5401267
France 59337731.0 59630121 59900680 62518571 62998773 63392140 63753140 64366962 64716310 65129746 65394283
Germany 82440309.0 82536680 82531671 82500849 82437995 82314906 82217837 82002356 81802257 81751602 81843743
Greece 10988000.0 11006377 11040650 11082751 11125179 11171740 11213785 11260402 11305118 11309885 11290067
Hungary 10174853.0 10142362 10116742 10097549 10076581 10066158 10045401 10030975 10014324 9985722 9957731
Iceland 286575.0 288471 290570 293577 299891 307672 315459 319368 317630 318452 319575
Ireland 3882683.0 3963636 4027732 4109173 4209019 4239848 4401335 4450030 4467854 4569864 4582769
Italy 56993742.0 57321070 57888245 58462375 58751711 59131287 59619290 60045068 60340328 60626442 60820696
Japan 127291000.0 127435000 127620000 127687000 127767994 127770000 127771000 127692000 127510000 128057000 127799000
Korea 47639618.0 47925318 48082163 48138077 48297184 48456369 48606787 48746693 48874539 49779440 50004441
Luxembourg 444050.0 448300 451600 455000 469086 476187 483799 493500 502066 511840 524853
Mexico 101826249.0 103039964 104213503 103001871 103946866 104874282 105790725 106682518 107550697 108396211 115682867
Netherlands 16105285.0 16192572 16258032 16305526 16334210 16357992 16405399 16485787 16574989 16655799 16730348
New Zealand 3939130.0 4009200 4062500 4100570 4139470 4228280 4268880 4315840 4367740 4405150 4433100
Norway 4524066.0 4552252 4577457 4606363 4640219 4681134 4737171 4799252 4858199 4920305 4985870
Poland 38632453.0 38218531 38190608 38173835 38157055 38125479 38115641 38135876 38167329 38200037 38538447
Portugal 10335559.0 10407465 10474685 10529255 10569592 10599095 10617575 10627250 10637713 10636979 10542398
Slovak Republic 5378951.0 5379161 5380053 5384822 5389180 5393637 5400998 5412254 5424925 5435273 5404322
Spain 40409330.0 41550584 42345342 43038035 43758250 44474631 45283259 45828172 45989016 46152926 46818221
Sweden 8909128.0 8940788 8975670 9011392 9047752 9113257 9182927 9256347 9340682 9415570 9482855
Switzerland 7261210.0 7313853 7364148 7415102 7459128 7508739 7593494 7701856 7785806 7870134 7954662
Turkey NaN 70171979 70689500 71607500 72519974 72519974 70586256 71517100 72561312 73722988 74724269
United Kingdom 58706905.0 59262057 59699828 60059858 60412870 60781346 61179260 61595094 62026962 62498612 63256154
United States 277244916.0 288774226 290810719 294442683 297308143 300184434 304846731 305127551 307756577 309989078 312232049
population.to_csv("data1/countries_total_population.csv")

We want to create a new DataFrame with all the information, i.e. female, male and complete population. This means that we have to introduce an hierarchical index. Before we do it on our DataFrame, we will introduce this problem in a simple example:

import pandas as pdshop1 = {"foo":{2010:23, 2011:25}, "bar":{2010:13, 2011:29}}shop2 = {"foo":{2010:223, 2011:225}, "bar":{2010:213, 2011:229}}shop1 = pd.DataFrame(shop1)shop2 = pd.DataFrame(shop2)both_shops = shop1 + shop2print("Sales of shop1:\n", shop1)print("\nSales of both shops\n", both_shops)
Sales of shop1:       foo  bar2010   23   132011   25   29Sales of both shops       foo  bar2010  246  2262011  250  258
shops = pd.concat([shop1, shop2], keys=["one", "two"])shops
Wir können die folgende Ausgabe erwarten, wenn wir den obigen Python-Code ausführen:
foo bar
one 2010 23 13
2011 25 29
two 2010 223 213
2011 225 229

We want to swap the hierarchical indices. For this we will use 'swaplevel':

shops.swaplevel()shops.sort_index(inplace=True)shops
Der obige Python-Code führt zu folgender Ausgabe:
foo bar
one 2010 23 13
2011 25 29
two 2010 223 213
2011 225 229

We will go back to our initial problem with the population figures. We will apply the same steps to those DataFrames:

pop_complete = pd.concat([population.T,                           male_pop.T,                          female_pop.T],                           keys=["total", "male", "female"])df = pop_complete.swaplevel()df.sort_index(inplace=True)df[["Austria", "Australia", "France"]]
Wir können die folgenden Ergebnisse erwarten, wenn wir den obigen Python-Code ausführen:
Country Austria Australia France
2002 female 4179743.0 9887846.0 30510073.0
male 3959567.0 9753133.0 28827658.0
total 8139310.0 19640979.0 59337731.0
2003 female 4158169.0 9999199.0 30655533.0
male 3909120.0 9873447.0 28974588.0
total 8067289.0 19872646.0 59630121.0
2004 female 4190297.0 10100991.0 30789154.0
male 3949825.0 9990513.0 29111526.0
total 8140122.0 20091504.0 59900680.0
2005 female 4220228.0 10218321.0 32147490.0
male 3986296.0 10121438.0 30371081.0
total 8206524.0 20339759.0 62518571.0
2006 female 4246571.0 10348070.0 32390087.0
male 4019354.0 10257418.0 30608686.0
total 8265925.0 20605488.0 62998773.0
2007 female 4261752.0 10570420.0 32587979.0
male 4037171.0 10444622.0 30804161.0
total 8298923.0 21015042.0 63392140.0
2008 female 4277716.0 10770864.0 32770860.0
male 4054214.0 10660917.0 30982280.0
total 8331930.0 21431781.0 63753140.0
2009 female 4287213.0 10986535.0 33208315.0
male 4068047.0 10888385.0 31158647.0
total 8355260.0 21874920.0 64366962.0
2010 female 4296197.0 11218144.0 33384930.0
male 4079093.0 11124254.0 31331380.0
total 8375290.0 22342398.0 64716310.0
2011 female 4308915.0 11359807.0 33598633.0
male 4095337.0 11260747.0 31531113.0
total 8404252.0 22620554.0 65129746.0
2012 female 4324983.0 11402769.0 33723892.0
male 4118035.0 11280804.0 31670391.0
total 8443018.0 22683573.0 65394283.0
df.to_csv("data1/countries_total_population.csv")

Exercise

lands = pd.read_csv('data1/bundeslaender.txt', sep=" ")print(lands.columns.values)
Führt man obigen Code aus, erhält man Folgendes:
---------------------------------------------------------------------------FileNotFoundError                         Traceback (most recent call last)<ipython-input-20-5116ab4aa9d4> in <module>()----> 1 lands = pd.read_csv('data1/bundeslaender.txt', sep=" ")      2 print(lands.columns.values)/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py in parser_f(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, skipfooter, doublequote, delim_whitespace, low_memory, memory_map, float_precision)    676                     skip_blank_lines=skip_blank_lines)    677 --> 678         return _read(filepath_or_buffer, kwds)    679     680     parser_f.__name__ = name/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py in _read(filepath_or_buffer, kwds)    438     439     # Create the parser.--> 440     parser = TextFileReader(filepath_or_buffer, **kwds)    441     442     if chunksize or iterator:/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py in __init__(self, f, engine, **kwds)    785             self.options['has_index_names'] = kwds['has_index_names']    786 --> 787         self._make_engine(self.engine)    788     789     def close(self):/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py in _make_engine(self, engine)   1012     def _make_engine(self, engine='c'):   1013         if engine == 'c':-> 1014             self._engine = CParserWrapper(self.f, **self.options)   1015         else:   1016             if engine == 'python':/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py in __init__(self, src, **kwds)   1706         kwds['usecols'] = self.usecols   1707 -> 1708         self._reader = parsers.TextReader(src, **kwds)   1709    1710         passed_names = self.names is Nonepandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader.__cinit__()pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._setup_parser_source()FileNotFoundError: File b'data1/bundeslaender.txt' does not exist
In [ ]:
# swap the columns of our DataFrame:lands = lands.reindex(columns=['land', 'area', 'female', 'male'])lands[:2]
In [ ]:
lands.insert(loc=len(lands.columns),              column='population',              value=lands['female'] + lands['male'])
In [ ]:
lands[:3]
In [ ]:
lands.insert(loc=len(lands.columns),              column='density',              value=(lands['population'] * 1000 / lands['area']).round(0))lands[:4]
In [ ]:
print(lands.loc[(lands.area>30000) & (lands.population>10000)])

Reading and Writing Excel Files

It is also possible to read and write Microsoft Excel files. The Pandas functionalities to read and write Excel files use the modules 'xlrd' and 'openpyxl'. These modules are not automatically installed by Pandas, so you may have to install them manually!

We will use a simple Excel document to demonstrate the reading capabilities of Pandas. The document sales.xls contains two sheets, one called 'week1' and the other one 'week2'.
An Excel file can be read in with the Pandas function "read_excel". This is demonstrated in the following example Python code:

In [ ]:
excel_file = pd.ExcelFile("data1/sales.xls")sheet = pd.read_excel(excel_file)sheet

The document "sales.xls" contains two sheets, but we only have been able to read in the first one with "read_excel". A complete Excel document, which can consist of an arbitrary number of sheets, can be completely read in like this:

In [ ]:
document = {}for sheet_name in xls.sheet_names:    document[sheet_name] = xls.parse(sheet_name)for sheet_name in document:    print("\n" + sheet_name + ":\n", document[sheet_name])