Tech

Excel Header Parsing

Abstract: This report aims to introduce a methodology for robustly extracting structured data from Excel, containing multi-layer headers by using pandas(Python) only. And in the end the weakness of this method will be shown.

1. Problem Description

Normally, an Excel table is composed by couples of header lines and data lines. For a multi-layer headers table, the header may looks like this:

And in pandas’ view, it looks like that(N for nan):

1
2
3
4
a b    N     N   c
N b1   N     b2  N
N b1.1 b1.2  N   N
any value in first data line can be N or not N

The purpose is extracting headers from whole table.

2. Solution and Algorithm Design

Assuming 1:
We are able to find the start of the header. Maybe line 0 or maybe some filters can be used.
Assuming 2:
The values in first line of data block must be different to the last line of header block.

In each line, we are trying to find if next line is valid header. Let A, B to be current line and next line. And let S to be a statue of judging wether current value is a potential merge cell.

Firstly, find the right value of Ai, if Ai is not nan, Ai+1 is not nan, it means Ai absolutely is not a merge cell, like a in my example. Set s = False

If Ai is not nan and Ai+1 is nan, it means we can consider Ai as a potential merge cell, like b, b1, b2, b1.2. Set s = True.

Secondly, another judging should be done. If s = True, it means the value below it can be anything, like b1 != b, b1.1 != b1.

If s = False, if Bi is nan, it is valid for next line is table, like Ai = a; Bi = N.

If s = False, if Bi != nan and Ai != Bi, as Assuming 2, it means B is not header anymore. So we can say the last line of header is A.

Algorithm Implementation (Python)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
def is_valid(upper, lower):
has_digit = any(str(cell).replace('.','').isdigit() for cell in lower if pd.notna(cell))

if has_digit:
	return False

length = len(upper)
statue = False

for i in range(length):
	val0 = upper.iloc[i]
	val1 = lower.iloc[i]
	str0 = str(val0).replace('\n', '').strip() if pd.notna(val0) else ""
	str1 = str(val1).replace('\n', '').strip() if pd.notna(val1) else ""

	if pd.notna(val0):
		if i + 1 < length and pd.isna(upper.iloc[i+1]): # right is nan
			statue = True
		else: # right not nan
			statue = False
	else:
		pass
	
	if statue:
		pass
	else:
		if str1 == "" or str1.lower() == 'nan':
		pass
	elif str1 != str0:
		return False

return True

3. Deficiency

The biggest problem would be the situation like Ai = b1.2. In this circumstance, Ai would be recognised as a potential merge cell, but actually the Ai+1 = N is belongs to b2.