{"id":725,"date":"2019-03-08T20:22:18","date_gmt":"2019-03-08T20:22:18","guid":{"rendered":"http:\/\/marcel-jan.eu\/datablog\/?p=725"},"modified":"2019-04-24T21:30:44","modified_gmt":"2019-04-24T21:30:44","slug":"showing-a-complex-excel-sheet-whos-boss-with-python-and-pandas","status":"publish","type":"post","link":"https:\/\/marcel-jan.eu\/datablog\/2019\/03\/08\/showing-a-complex-excel-sheet-whos-boss-with-python-and-pandas\/","title":{"rendered":"Showing a complex Excel sheet who’s boss with Python and pandas"},"content":{"rendered":"

Data engineering isn’t always creating serverless APIs and ingressing terrabyte a minute streams with do-hickeys on Kubernetes. Sometimes people just want their Excel sheet in the data lake. Is that big data? Not even close. It’s very small. But for some people it’s a first step in a data driven world.<\/p>\n

But does Hadoop read Excel? Not to my knowledge. But NiFi, that wonderful open source data flow software has an Excel processor. It can even help you to work the data a little. But some Excel sheets simply need too much reworking. And that’s simply too big a job for NiFi. I’ve used Python and the pandas library to create a csv file that Hadoop can handle.<\/p>\n

<\/p>\n

 <\/p>\n

Files<\/h2>\n

You can download the example files for this on GitHub:<\/p>\n

Pandas moon fueling github.xlsx<\/a><\/p>\n

pandas_moon_fueling.py<\/a><\/p>\n

The Excel sheet doesn’t have the exact amount of worksheets mentioned here, but the moon fuel stations sheet is identical.<\/p>\n

 <\/p>\n

In the left corner: the Excel sheet…<\/h2>\n

Clearly at my work we don’t maintain fueling stations on the Moon (yet), but the Excel sheet I encountered looked sort of like the layout of this one. Let’s take a look.<\/p>\n

\"\"<\/p>\n

On the lefthand side you see three columns that define the fueling stations. The customers each have their own column for every week. There also is a summation of the data per week. Not seen here are the summations per year, per customer and totals per week. Every alternate row is either the amount of fuel and the money the customer paid.<\/p>\n

I could save this as CSV file and store it in Hadoop, but it would give any data scientist a head ache. It’s more or less understandable for the human eye, but software won’t get this.<\/p>\n

I think that this layout below would be much easier to use in analytics software:<\/p>\n

\"\"<\/p>\n

But how do we accomplish that?<\/p>\n

 <\/p>\n

In the right corner: pandas!<\/h2>\n

Guess what? Python’s pandas library can read Excel. It even has a read_excel function. It’s almost as if other people got data delivered in Excel format..<\/p>\n

Let’s start by importing pandas.<\/p>\n

import pandas as pd<\/pre>\n
In my terminal window I have room enough to make my pandas output a little wider.<\/p>\n
# Show wider tables. I've got the screen for it.\r\npd.set_option('display.width', 300)<\/pre>\n
Now open the Excel file and let’s show the sheets in it.<\/p>\n
fuel_file = \"Pandas moon fueling.xlsx\"\r\nxl = pd.ExcelFile(fuel_file)\r\nprint(xl.sheet_names)<\/pre>\n
Because we have fueling stations all over the solar system (as you do), this results in:<\/p>\n
['Moon', 'Eros', 'Ceres', 'Mars', 'Europa', 'Ganymedes', 'Enceladus']<\/pre>\n
But in this case I just want to start by reading the Moon data. Now pandas has read_excel, but you can also use xl.parse. xl being where I stored my Excel data previously. It’s basically the same. I just found this on StackOverflow first \ud83d\ude42 .<\/p>\n
 <\/p>\n
Headers and indexes<\/h2>\n
But in this case that simple example you’ll find first when googling “pandas read excel” is not enough. This has to do with headers and indexes. If you don’t pick your headers and indexes from the Excel sheet, pandas will pick something.<\/p>\n
I’ve been reading everywhere to learn how to choose these. Because normally you have just a bunch of column names in a row. That would be your header row. Not so now. We have multiple levels of rows.<\/p>\n
\"\"<\/p>\n
So this is how you read the Moon sheet and store it in a dataframe.<\/p>\n
fuel_df = xl.parse(\"Moon\", header=[1], index_col=[0,1,2])<\/pre>\n
I chose the row that has the weeknumbers: row 2 in the screenshot above. And counting from zero, that would be row 1. Therefor, header=[1].<\/p>\n
The index is basically your row name. Database people might think of this one like a primary key. In this case I wanted the first three columns combined. Therefor, index_col=[0,1,2].<\/p>\n
Let’s have a look at what we got now:<\/p>\n
print(fuel_df.head())<\/pre>\n
                                                     Unnamed: 3   Unnamed: 4 Unnamed: 5 Week 01  \r\nLocation  Description                   Fueling port        NaN          NaN        NaN   Total  \r\n                                        Fueling port     SpaceX  Blue Origin  Landspace     NaN  \r\nCOPS-001O Copernicus Station 1 Oxygen   C003              97916          NaN          0   97916  \r\n                                        C003             133686          NaN     1012.5  134698  \r\nCOPS-001H Copernicus Station 1 Hydrogen C003             195040          NaN        NaN  195040\r\n[..]<\/pre>\n
The first row with the “Unnamed: 3 \u00a0 Unnamed: 4\u00a0 ” is now our header. You can see the weeknumber in there and that’s what I was going for.<\/p>\n
You can see the complete header with this:<\/p>\n
print(fuel_df.columns)<\/pre>\n
Index(['Unnamed: 3', 'Unnamed: 4', 'Unnamed: 5', 'Week 01', 'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10', 'Week 02', 'Unnamed: 12', 'Unnamed: 13', 'Week 03', 'Unnamed: 15', 'Unnamed: 16', 'Unnamed: 17', 'Week 04'], dtype='object')<\/pre>\n
Clearly this is not sufficient. Let’s work on that.<\/p>\n
 <\/p>\n
Weeknumbers in the header<\/h2>\n
One weakness in the Excel sheet, is that weeknumbers are to the right of the data from that week. Uninformed software would assume the data of that week is in the columns right of it. What I want, is that every column of a certain weeknumber get that weeknumber in the header.<\/p>\n
I start by storing the column names (the header) in a variable and work on that. I also store the row with the customer names. We’ll get to that later.<\/p>\n
weeknumbers_list = list(fuel_df.columns)\r\nspacecustomers = fuel_df.iloc[1]<\/pre>\n
My way of doing this might be more cumbersome than necessary, but it does the job.<\/p>\n
start_position = 0\r\n# Loop through values with \"Week\" in the name\r\nfor weeknr_pos in positions_weeknumbers:\r\n    # Go from the start position of that week and name everything with that weeknumber\r\n    # until the end of that week.\r\n    for pos in range(start_position, weeknr_pos):\r\n        # Cell gets weeknumber in the \r\n        weeknumbers_list[pos] = weeknumbers_list[weeknr_pos]\r\n    # Determine the first column position of the next weeknumber.\r\n    start_position = weeknr_pos + 1\r\n    # The last column of weeknr has the totals. We'll remove that one.\r\n    weeknumbers_list[weeknr_pos] = \"Remove\"<\/pre>\n
Last step: feed the weeknumber list to the dataframe header.<\/p>\n
fuel_df.columns = weeknumbers_list<\/pre>\n
 <\/p>\n
Remove the unnecessary stuff<\/h2>\n
Next, let’s remove the first two rows and let’s remove all columns with Total in the name. We already indicated them with the term “Remove”.<\/p>\n
fuel_df = fuel_df.iloc[2:]<\/pre>\n
fuel_df.drop(\"Remove\", axis=1, inplace=True)<\/pre>\n
So what do we got now?<\/p>\n
                                             Week 01 Week 01 Week 01 Week 02 Week 02\r\nCOPS-001O Copernicus Station 1 Oxygen   C003   97916     NaN       0     NaN     NaN\r\n                                        C003  133686     NaN  1012.5     NaN     NaN\r\nCOPS-001H Copernicus Station 1 Hydrogen C003  195040     NaN     NaN     NaN     NaN\r\n                                        C003   90371     NaN     NaN     NaN     NaN\r\nMRUM-003  Mons R\u00fcmker Base 2            B12      NaN     NaN     NaN     NaN     NaN\r\n[..]<\/pre>\n
 <\/p>\n
Multiple headers<\/h2>\n
We’re missing something, aren’t we? We’re missing the names of the customers. But I have prepared something for that. There’s a spacecustomers variable with all the names in them. But it also has some of those NaN values in them. So let’s clean that up.<\/p>\n
spacecustomers_cleanlist = [x for x in spacecustomers if str(x) != 'nan']<\/pre>\n
Now I want to add the customer list to the header as an extra layer. Why? Because I want those columns not just identified by weeknumbers, but also by customer names.<\/p>\n
I’ve been looking for SO long on how to do this. I’ve been trying and trying and this is what I came up with. Use this to your advantage.<\/p>\n
column_array = []\r\ncolumn_array.append(fuel_df.columns)\r\ncolumn_array.append(spacecustomers_cleanlist)\r\n<\/pre>\n
column_array here is basically a list of lists. Maybe I shouldn’t have called it an array. Too confusing. Anyway, this is a way to feed two lists as two headers in the fuel_df dataframe.<\/p>\n
fuel_df.columns = column_array<\/pre>\n
Now our dataframe looks like this<\/p>\n
                                                     Week 01                        Week 02\r\n                                                      SpaceX Blue Origin Landspace   SpaceX\r\nCOPS-001O Copernicus Station 1 Oxygen    C003          97916         NaN         0      NaN\r\n                                         C003         133686         NaN    1012.5      NaN\r\nCOPS-001H Copernicus Station 1 Hydrogen  C003         195040         NaN       NaN      NaN\r\n                                         C003          90371         NaN       NaN      NaN\r\nMRUM-003  Mons R\u00fcmker Base 2             B12             NaN         NaN       NaN      NaN\r\n                                         B12             NaN         NaN       NaN      NaN\r\n[..]<\/pre>\n
I’m so happy that I managed to do this. I’ve been reading about pandas’ MultiIndex feature, and all kinds of crazy schemes and possible solutions. It would not work. Until I saw this video by Zax Rosenberg speaking at a Chicaco Python Users Group meeting about just MultiIndexes and related features. Then I understood what I needed to do.<\/p>\n