This is another post based on a demo from my SQLSaturday session about Python integration in Power BI. The data file is also available from this link or download your own file from the Reserve Bank link below.

It is a pattern I use fairly often because flat files lend itself to the artistic license of the data provider i.e. they can do just about anything they want with the flat file.

Lets take a look at a real world example. The South African Reserve Bank provides daily exchange rate data (ZAR per USD) through an embedded Reporting Services environment. When you download the data as Comma Separated Value (CSV), this is what you get:

The data represents 3 days of data. We only require the section indicated as “useful data”. If you look at the file structure carefully, you will notice that it could still be treated as a CSV file. When you open the file with the Query Editor, you will see that Power BI already treats the file like a CSV (or an Excel file that does not have all cells populated).

I am going to illustrate two methods of dealing with this in Python. You may encounter scenarios where either one could be useful.

Method 1: Treat the file as CSV

This method will essentially mimic what the Power BI Query Editor does with the file. We will use the Pandas read_csv method, but we will use some parameters to handle our requirements. Without indicating that there are three columns, read_csv will throw an exception when it encounters a third field further down the file. To indicate three columns we simply specify the columns.

import pandas
df = pandas.read_csv('D:\\data\\dirty\\ExchangeRateDetail.csv',
                     skiprows=4,
                     names =['Date','Value','Extra'])

You will notice that we have ignored the first four rows that we don’t need.

From this point on its a simple process that we have encountered in previous posts. We are going to do three things to get the file into a ready to use state:

• Drop the extra column
• Remove (filter) unwanted rows
• Explicitly cast the data types

import pandas
df = pandas.read_csv('D:\\data\\dirty\\ExchangeRateDetail.csv',
                     skiprows=4,
                     names =['Date','Value','Extra'])
df = df.drop(['Extra'], axis=1) # drop "Extra" column
df = df[df.Date != 'The Value'] # filter rows
df = df[df.Date != 'The Value_label'] # filter rows
df['Date'] = pandas.to_datetime(df['Date']) # explicit data type cast
df['Value'] = pandas.to_numeric(df['Value']) # explicit data type cast

The final output can now be used as a Python data source in Power BI

Method 2: Read the file line by line

In this example we will read the data line by line. Each line will get add to a list i.e. we will end up with a list of lines. We will then use some list comprehension techniques filter the data before adding it to a dataframe.

with open('D:\\data\\dirty\\ExchangeRateDetail.csv') as f:
    lines = [line for line in f]

The new line characters form part of the line as shown below in our list where each item is a line in the file.

There are also special characters at the start of the file, but we dont need to worry about this as we will not use this item in the list.

To strip off the “\n” we will use the rstrip method which removes characters from the right hand side of a string. By not specifying a character, whitespace characters are removed. This includes “\n”.

with open('D:\\data\\dirty\\ExchangeRateDetail.csv') as f:
    lines = [line.rstrip() for line in f]

With the newline removed, we can now start filtering the list of items(lines). We start by filtering out the empty items (blank lines).

lines = list(filter(None, lines))

Next we remove the first 3 items, but keep the rest of the list.

lines = lines[3:]

Our final filter condition is to remove all items that start with “The”

lines = [t for t in lines if not t.startswith('The')]

Now our list is ready to convert to a dataframe.

import pandas
df = pandas.DataFrame(lines)

Note that we end up with a single column (0) dataframe. Our final steps to get the dataframe into a ready to use state are:

• split the column on comma separator
• drop the original column
• Explicitly cast the data types

Lets put a complete final script together that include the final steps.

import pandas

with open('D:\\data\\dirty\\ExchangeRateDetail.csv') as f:
    lines = [line.rstrip() for line in f]

lines = list(filter(None, lines)) # drop blank lines
lines = lines[3:] # remove top rows
lines = [t for t in lines if not t.startswith('The')] # remove rows starting with "The"

df = pandas.DataFrame(lines) # add remaining rows to a dataframe
df['Date'] = pandas.to_datetime(df[0].str.split(',').str.get(0)) # split of the Date column
df['Value'] = df[0].str.split(',').str.get(1) # split off the value column
df = df.drop([0], axis=1) # drop the original column (0)
df['Date'] = pandas.to_datetime(df['Date']) # explicit data type cast
df['Value'] = pandas.to_numeric(df['Value']) # explicit data type cast

The final result is the same as method 1.

Hopefully that provides some ideas on how to deal with flat files in general. As mentioned at the start of this post, it is often challenging to deal with flat files as it is up to the data provider to produce a well structured file.

This post is is based on another demo from my SQLSaturday session on Python integration in Power BI. Its a handy real world scenario that could easily be re-applied with minor changes to the script. The primary pattern is unpivot functionality which is achieved through the melt method in pandas.  A few additional useful dataframe techniques are illustrated, including pivot_table.

I am using the Tourist Accommodation data from the Statistics South Africa website. The file (tourism.xlsx) is also available on the SQLSaturday website. Once you open the file observe the following:

• the first 8 columns (starting with the letter H) are the header columns
• the remaining columns are a series of months i.e. MO012007 is month 1 (Jan), 2007
• the numeric values have mixed scale including thousands, millions & percentages
• column H04 is a mix of metric and accommodation type

The Python script will provide a way to transform the data even if the columns increase as months get added.

Step 1: Load Excel data into a dataframe

import pandas
df = pandas.read_excel('D:\\data\\excel\\tourism.xlsx')

The read_excel method accepts a host of parameters, including specifying the sheet_name. Our Excel file has only 1 sheet, so the sheet_name is not required.

Step 2: Split columns into actual headers and months

We start by adding the dataframe columns to a list. Then we loop through the list and split the month columns from the actual headers.

#create a list of all the columns
columns = list(df)
#create lists to hold headers & months
headers = []
months = []
#split columns list into headers and months
for col in columns:
    if col.startswith('MO'):
        months.append(col)
    else:
        headers.append(col)

Here we have the best case scenario because all month columns start with “MO”. If no pattern exists and the columns are sequenced, you could split on position i.e. headers = columns[:8], months = columns[8:] will produce the same result. Worst scenario is when the order of the columns are random. In this case you would manually specify the list elements.

Step 3: Melt (unpivot) the month columns

Unpivoting will transpose all month columns to rows. For each month column a new row is created using the same header columns.

df2 = pandas.melt(df,
                  id_vars=headers,
                  value_vars=months,
                  var_name='Date',
                  value_name='Val')

All month columns will be dropped and a new column ‘Date’ will contain the original month column header. The values will bed added to a new column called ‘Val’.

Now reconstruct and convert the Date column to an actual date. We get the year and month parts from the column and just assume the day is always 1. This is explicitly converted to a date using the to_datetime method.

df2['Date'] = pandas.to_datetime(df2['Date'].str[4:9] + '-' + df2['Date'].str[2:4] + '-01')

Step 4: Standardize the values 

We noted at the start that the values were a mixture of numeric scales which is indicated in column H17. To determine the possible value scales we use df2.H17.unique() which returns the following list of unique values: [‘Thousand’, ‘Percentage’, ‘Rands’, ‘R million’]. We create a function to apply the scale to the Val column.

 def noscale(row):
    if row['H17'] == 'Thousand':
        return row['Val'] * 1000
    elif row['H17'] =='R million':
        return row['Val'] * 1000000
    elif row['H17'] =='Percentage':
        return row['Val'] / 100
    else:
        return row['Val']

df2['FullValue'] = df2.apply(noscale, axis=1)

“Rands” are just the local currency (ZAR), so no scale is required.

Step 5: Additional clean up 

The next 3 steps in our script splits the composite column and removes unwanted rows and columns.

#split composite column
df2['Metric'] = df2['H04'].str.split(' - ').str.get(0)
df2['Category'] = df2['H04'].str.split(' - ').str.get(1)
#remove aggregate rows
df2 = df2[df2.Category != 'Total industry']
#remove unwanted columns
df2 = df2.drop(['H01','H02','H03','H04','H15','H17','H25','Val'], axis=1)

Note how rows are filtered. We set the dataframe equal to the existing dataframe where the Category column is not equal to ‘Total Industry’

At this point the script could be used as is, but in Power BI we would need to understand how the measure(s) would be created. For example, we could have a generic measure [Value] = SUM(‘Tourism'[FullValue]). We would then use the Metric column as a filter/slicer. Most likely you would rather want a measure such as [Units Available] = CALCULATE ( SUM ( ‘Tourism'[FullValue] ), ‘Tourism'[Metric]=”Stay units available” ). In this case it is cumbersome to create the measures, so we rather reshape the table to make things easier.

Step 6: Pivot the Metrics (optional)

If the metrics provided are fixed (consistent), then pivoting the metrics  is a good idea. This creates a column for each metric. This is possible with the Pandas pivot_table method.

df3 = pandas.pivot_table(df2,
                         values='FullValue',
                         columns=['Metric'],
                         index=['H16','Date','Category'])

From the results below, this appears to be correct.

The side effect of the pivot_table method is that the dataframe is re-indexed based on the index parameter passed to the method. This is not really a problem, but for Power BI it is. You may have noted from earlier posts that the Python Script in Power BI does not provide access to the dataframe index. So we would in effect lose the first three columns from our result above.

The fix is as simple as re-indexing the dataframe. We can also drop the ‘Total income’ column as we can let Power BI handle the aggregations.

df3.reset_index(inplace=True)
df3 = df3.drop(['Total income'], axis=1)

Our final script below will produce 3 dataframes which will be available for selection in Power BI.

import pandas

#read Excel into dataframe
df = pandas.read_excel('D:\\data\\excel\\tourism.xlsx')

#create place holder lists for actaul headers vs months
headers = []
months = []

#split columns into headers and months
for col in list(df):
    if col.startswith('MO'):
        months.append(col)
    else:
        headers.append(col)

#unpivot months
df2 = pandas.melt(df,
                  id_vars=headers,
                  value_vars=months,
                  var_name='Date',
                  value_name='Val')

#create a date field from the month field
df2['Date'] = pandas.to_datetime(df2['Date'].str[4:] + '-' + df2['Date'].str[2:4] + '-01')

#create a function to resolve mixed numeric scale in the data
def noscale(row):
    if row['H17'] == 'Thousand':
        return row['Val'] * 1000
    elif row['H17'] =='R million':
        return row['Val'] * 1000000
    elif row['H17'] =='Percentage':
        return row['Val'] / 100
    else:
        return row['Val']

#apply the noscale function to the dataframe
df2['FullValue'] = df2.apply(noscale, axis=1)

#split composite column
df2['Metric'] = df2['H04'].str.split(' - ').str.get(0)
df2['Category'] = df2['H04'].str.split(' - ').str.get(1)
#remove aggregate rows
df2 = df2[df2.Category != 'Total industry']
#remove unwanted columns
df2 = df2.drop(['H01','H02','H03','H04','H15','H17','H25','Val'], axis=1)

#unpivot metrics
df3 = pandas.pivot_table(df2,
                         values='FullValue',
                         columns=['Metric'],
                         index=['H16','Date','Category'])
#reset index
df3.reset_index(inplace=True)
#drop aggregate column
df3 = df3.drop(['Total income'], axis=1)

All that is left is to paste your script into Power BI and select the dataframe that works best for you.

Of course go and experiment with Python visualization 😊