{"id":517,"date":"2018-08-09T16:39:29","date_gmt":"2018-08-09T20:39:29","guid":{"rendered":"http:\/\/brian.digitalmaddox.com\/blog\/?p=517"},"modified":"2018-08-09T16:39:29","modified_gmt":"2018-08-09T20:39:29","slug":"manipulating-a-csv-with-pandas","status":"publish","type":"post","link":"https:\/\/brian.digitalmaddox.com\/blog\/?p=517","title":{"rendered":"Manipulating a CSV with Pandas"},"content":{"rendered":"

At my day job I am working on some natural language processing and need to generate a list of place names so I can further train the excellent spacy<\/a> library.\u00a0 I previously imported the full Planet OSM<\/a> so went there to pull a list of places.\u00a0 However, the place names in OSM are typically in the language of the person who did the collection, so they can be anything from English to Arabic.\u00a0 I stored the OSM data using imposm3<\/a> and included a PostgreSQL<\/a> hstore<\/a> column to store all of the user tags so we would not lose any data.\u00a0 I did a search for all tags that had values like name<\/em> and en<\/em> in them and exported those keys and values to\u00a0 several CSV<\/a> files based on the points, lines, and polygons tables.\u00a0 I thought I would write a quick post to show how easy it can be to manipulate data outside of traditional spreadsheet software.<\/p>\n

The next thing I needed to do was some data reduction, so I went to my go-to library of Pandas<\/a>.\u00a0 If you have been living under a rock and have not heard of it, Pandas is an exceptional data processing library that allows you to easily manipulate data from Python.\u00a0 In this case, I knew some of my data rows were empty and that I would have duplicates due to how things get named in OSM.\u00a0 Pandas makes cleaning data incredibly easy in this case.<\/p>\n

First I needed to load the files into Pandas to being cleaning things up.\u00a0 My personal preference for a Python interpreter is ipython\/jupyter<\/a> in a console window. To do this I ran ipython<\/em> and then imported Pandas by doing the following:<\/p>\n

In [1]: import pandas as pd<\/pre>\n
Next I needed to load up the CSV into Pandas to start manipulating the data.<\/p>\n
In [2]: df = pd.read_csv('osm_place_lines.csv', low_memory=False)<\/pre>\n
At this point, I could examine how many columns and rows I have by running:<\/p>\n
In [3]: df.shape\r\nOut[3]: (611092, 20)<\/pre>\n
Here we can see that I have 611,092 rows and 20 columns.\u00a0 My original query pulled a lot of columns because I wanted to try to capture as many pre-translated English names as I could.\u00a0 To see what all of the column names are, I just had to run:<\/p>\n
In [<\/span>10<\/span>]: <\/span>df.columns<\/span>\r\nOut[<\/span>10<\/span>]: <\/span>\r\nIndex(['name', 'alt_name_1_en', 'alt_name_en', 'alt_name_en_2',<\/span>\r\n\u00a0\u00a0 \u00a0 \u00a0 <\/span>'alt_name_en_3', 'alt_name_en_translation', 'en_name',<\/span>\r\n\u00a0\u00a0 \u00a0 \u00a0 <\/span>'gns_n_eng_full_name', 'name_en', 'name_ena', 'name_en1', 'name_en2',<\/span>\r\n\u00a0\u00a0 \u00a0 \u00a0 <\/span>'name_en3', 'name_en4', 'name_en5', 'name_en6', 'nam_en', 'nat_name_en',<\/span>\r\n\u00a0\u00a0 \u00a0 \u00a0 <\/span>'official_name_en', 'place_name_en'],<\/span>\r\n\u00a0 \u00a0 \u00a0 <\/span>dtype='object')<\/span><\/pre>\n
The first task I then wanted to do was drop any rows that had no values in them.\u00a0 In Pandas, empty cells<\/em> default to the NaN<\/a> value.\u00a0 So to drop all the empty rows, I just had to run:<\/p>\n
In [4]: df = df.dropna(how='all')<\/pre>\n
To see how many rows fell out, I again checked the shape of the data.<\/p>\n
In [5]: df.shape\r\nOut[5]: (259564, 20)<\/pre>\n
Here we can see that the CSV had\u00a0351,528 empty rows where the line had no name or English name translations.<\/p>\n
Next, I assumed that I had some duplicates in the data.\u00a0 Some things in OSM get generic names, so these can be filtered out since I only want the first row from each duplicate.\u00a0\u00a0With no options, drop_duplicates() in Pandas only keeps the first value.<\/p>\n
In [<\/span>6<\/span>]: <\/span>df = df.drop_duplicates()<\/span><\/pre>\n
Checking the shape again, I can see that I had 68,131 rows of duplicated data.<\/p>\n
In [7]: df.shape\r\nOut[7]: (191433, 20)<\/pre>\n
At this point I was interested in how many cells in each row still contained no data.\u00a0 The CSV was already sparse since I converted each hstore key into a separate column in my output.\u00a0 To do this, I ran:<\/p>\n
In [<\/span>8<\/span>]: <\/span>df.isna().sum()<\/span>\r\nOut[<\/span>8<\/span>]: <\/span>\r\nname\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>188<\/span>\r\nalt_name_1_en\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191432<\/span>\r\nalt_name_en\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>190310<\/span>\r\nalt_name_en_2\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191432<\/span>\r\nalt_name_en_3\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191432<\/span>\r\nalt_name_en_translation\u00a0 \u00a0 <\/span>191432<\/span>\r\nen_name\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191430<\/span>\r\ngns_n_eng_full_name\u00a0 \u00a0 \u00a0 \u00a0 <\/span>191432<\/span>\r\nname_en\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191430<\/span>\r\nname_ena \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>172805<\/span>\r\nname_en1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191409<\/span>\r\nname_en2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191423<\/span>\r\nname_en3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191429<\/span>\r\nname_en4 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191430<\/span>\r\nname_en5 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191432<\/span>\r\nname_en6 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191432<\/span>\r\nnam_en \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191432<\/span>\r\nnat_name_en\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191431<\/span>\r\nofficial_name_en \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191427<\/span>\r\nplace_name_en\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>191429<\/span>\r\ndtype: int64<\/span><\/pre>\n
Here we can see the sparseness of the data.\u00a0 Considering I am now down to 191,433 columns, some of the columns only have a single entry in them.\u00a0 We can also see that I am probably not going to have a lot of English translations to work with.<\/p>\n
At this point I wanted to save the modified dataset so I would not loose it.\u00a0 This was a simple<\/p>\n
In [8]: df.to_csv('osm_place_lines_nonull.csv', index=False)<\/pre>\n
The index=False option tells Pandas to not output its internal index field to the CSV.<\/p>\n
Now I was curious what things looked like, so I decided to check out the name<\/em> column.\u00a0 First I increased some default values in Pandas because I did not want it to abbreviate rows or columns.<\/p>\n
pd.set_option<\/span>(<\/span>'display.max_rows'<\/span>, <\/span>200<\/span>)<\/span>\r\npd.set_option<\/span>(<\/span>'display.max_columns'<\/span>, <\/span>25<\/span>)<\/span><\/pre>\n
To view the whole row where the value in a specific column is null, I did the following and I will abbreviate the output to keep the blog shorter \ud83d\ude42<\/p>\n
df<\/span>[<\/span>df[<\/span>'name'<\/span>].isnull()<\/span>]<\/span>\r\n...\r\n\u00a0\u00a0 \u00a0 \u00a0 <\/span>name_en \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>name_ena name_en1 name_en2\u00a0 <\/span>\\<\/span>\r\n166\u00a0 \u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Orlovskogo Island\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n129815 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Puukii Island\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n159327 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Ometepe Island\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n162420 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Tortuga\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n164834 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Jack Adan Island\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n191664 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Hay Felistine\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n193854 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Albor\u00e1n Island Military Base\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n197893 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Carabelos Island\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n219472 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Little Fastnet\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n219473 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Fastnet Rock\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n220004 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Doonmanus Rock\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n220945 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Tootoge Rock\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n229446 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Achallader\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n238355 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Ulwile Island\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n238368 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Mvuna Island\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n238369 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Lupita Island\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n238370 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Mvuna Rocks\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n259080 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Kafouri\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n259235 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Al Thawra 8\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n259256 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Beit al-Mal\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n261584 \u00a0 \u00a0 <\/span>NaN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>Al Fao\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n262200 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/span>May 1st\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0 \u00a0 <\/span>NaN\u00a0 \u00a0<\/span><\/span>\r\n...<\/pre>\n
Now that I have an idea how things look, I can do things like fill out the rest of the name<\/em> columns with the English names found the various other columns.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
At my day job I am working on some natural language processing and need to generate a list of place names so I can further train the excellent spacy library.\u00a0 I previously imported the full Planet OSM so went there … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[24,25],"class_list":["post-517","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-pandas","tag-quicktip"],"_links":{"self":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/517","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=517"}],"version-history":[{"count":1,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/517\/revisions"}],"predecessor-version":[{"id":518,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/517\/revisions\/518"}],"wp:attachment":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=517"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=517"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=517"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}