Question

In which we convert a dataframe into a normalized database.

names = ['ann', 'bob', 'ann', 'bob', 'carl', 'delia', 'ann']
tests = ['wbc', 'wbc', 'rbc', 'rbc', 'wbc', 'rbc', 'platelets']
values1 = [10, 11.2, 300, 204, 9.8, 340, 125]
values2 = [10.6, 13.2, 322, 214, 10.3, 343, 145]
df = pd.DataFrame([names, tests, values1, values2]).T
df.columns = ['names', 'tests', 'values1', 'values2']
df

	names	tests	values1	values2
0	ann	wbc	10	10.6
1	bob	wbc	11.2	13.2
2	ann	rbc	300	322
3	bob	rbc	204	214
4	carl	wbc	9.8	10.3
5	delia	rbc	340	343
6	ann	platelets	125	145

# names are put into their own table so there is no dubplication

name_table = pd.DataFrame(df['names'].unique(), columns=['name'])
name_table['name_id'] = name_table.index
columns = ['name_id', 'name']
name_table[columns]

	name_id	name
0	0	ann
1	1	bob
2	2	carl
3	3	delia

# tests are put inot their own table so there is no duplication

test_table = pd.DataFrame(df['tests'].unique(), columns=['test'])
test_table['test_id'] = test_table.index
columns = ['test_id', 'test']
test_table[columns]

	test_id	test
0	0	wbc
1	1	rbc
2	2	platelets

# the values1 and values2 correspond to visit 1 and 2, so
# we create a visits table

visit_table = pd.DataFrame([1,2], columns=['visit'])
visit_table['visit_id'] = visit_table.index
columns = ['visit_id', 'visit']
visit_table[columns]

	visit_id	visit
0	0	1
1	1	2

# finally, we link each value to a triple(name_id, test_id, visit_id)

value_table = pd.DataFrame([
    [0,0,0,10], [1,0,0,11.2], [0,1,0,300], [1,1,0,204], [2,0,0,9.8], [3,1,0,340], [0,2,0,125],
   [0,0,1,10.6], [1,0,1,13.2], [0,1,1,322], [1,1,1,214], [2,0,1,10.3], [3,1,1,343], [0,2,1,145]
], columns=['name_id', 'test_id', 'visit_id', 'value'])
value_table

	name_id	test_id	visit_id	value
0	0	0	0	10.0
1	1	0	0	11.2
2	0	1	0	300.0
3	1	1	0	204.0
4	2	0	0	9.8
5	3	1	0	340.0
6	0	2	0	125.0
7	0	0	1	10.6
8	1	0	1	13.2
9	0	1	1	322.0
10	1	1	1	214.0
11	2	0	1	10.3
12	3	1	1	343.0
13	0	2	1	145.0

At the end of the normalizaiton, we have gone from 1 dataframe with multiple redundancies to 4 tables with unique entries in each row. This organization helps maintain data integrity and is necesssary for effficeincy as the number of test values grows, possibly into millions of rows. As we have seen, we can use SQL queries to recreate the origianl dataformat if that is more convenient for analysis.