Import Unclean Excel Data

Pandas Introduction

Jan Kirenz

Import unclean Excel data

Data from GENESIS-Online

  • GENESIS-Online is the main database of the Federal Statistical Office

GENESIS-Online

  • Contains deeply structured results of official statistics.

  • You can use the database without registration.

  • All data are available free of charge.

  • We use this dataset as an example

Setup

import pandas as pd

Data

First data import

Data source

df_unclean = pd.read_excel('12211-9014_de.xlsx', sheet_name='12211-9014')
  • Take a look at the data
df_unclean

First data import

Bevölkerung (ab 15 Jahren): Bundesländer, Jahre (bis 2019),\nGeschlecht, Allgemeine Schulausbildung Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10
0 Mikrozensus NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 Bevölkerung (ab 15 Jahren) (1000) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 Jahr\nGeschlecht\nBundesländer NaN NaN Allgemeine Schulausbildung NaN NaN NaN NaN NaN NaN NaN
3 NaN NaN NaN Noch in schulischer Ausbildung Haupt- (Volks-)schulabschluss Abschluss der polytechnischen Oberschule Realschule oder gleichwertiger Abschluss Fachhochschul- oder Hochschulreife Ohne Angabe zur Art des Abschlusses Ohne allgemeinen Schulabschluss Insgesamt
4 2019 männlich Baden-Württemberg 168 1512 37 1023 1723 11 191 4674
... ... ... ... ... ... ... ... ... ... ... ...
67 Zensus 2011. NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
68 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
69 Ab 2017: NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
70 Ohne Bevölkerung in Gemeinschaftsunterkünften. NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
71 © Statistisches Bundesamt (Destatis), 2023 | S... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

72 rows × 11 columns

Adjusted data import

df = pd.read_excel('12211-9014_de.xlsx', 
  sheet_name='12211-9014', 
  skiprows=[0,1,2,3], 
  skipfooter=20, 
  na_values='/', 
  thousands='.', 
  decimal=','
  )

View data

df.head()
Unnamed: 0 Unnamed: 1 Unnamed: 2 Noch in schulischer Ausbildung Haupt- (Volks-)schulabschluss Abschluss der polytechnischen Oberschule Realschule oder gleichwertiger Abschluss Fachhochschul- oder Hochschulreife Ohne Angabe zur Art des Abschlusses Ohne allgemeinen Schulabschluss Insgesamt
0 2019.0 männlich Baden-Württemberg 168 1512 37.0 1023 1723 11.0 191 4674
1 NaN NaN Bayern 175 2142 49.0 1129 1828 16.0 153 5507
2 NaN NaN Berlin 55 205 130.0 284 765 NaN 72 1511
3 NaN NaN Brandenburg 35 118 349.0 193 325 NaN 31 1052
4 NaN NaN Bremen 11 74 NaN 67 115 NaN 20 290 
df.tail()
Unnamed: 0 Unnamed: 1 Unnamed: 2 Noch in schulischer Ausbildung Haupt- (Volks-)schulabschluss Abschluss der polytechnischen Oberschule Realschule oder gleichwertiger Abschluss Fachhochschul- oder Hochschulreife Ohne Angabe zur Art des Abschlusses Ohne allgemeinen Schulabschluss Insgesamt
43 NaN NaN Saarland 26 337 NaN 185 264 NaN 35 853
44 NaN NaN Sachsen 96 589 1090.0 658 968 NaN 67 3470
45 NaN NaN Sachsen-Anhalt 53 273 760.0 316 427 5.0 50 1892
46 NaN NaN Schleswig-Holstein 93 741 21.0 736 772 8.0 86 2471
47 NaN NaN Thüringen 52 283 723.0 282 463 NaN 28 1833

Rename columns

df = df.rename(columns={
  'Unnamed: 0': 'Jahr',
  'Unnamed: 1': 'Geschlecht',
  'Unnamed: 2': 'Bundesland' }
  )

df.head()
Jahr Geschlecht Bundesland Noch in schulischer Ausbildung Haupt- (Volks-)schulabschluss Abschluss der polytechnischen Oberschule Realschule oder gleichwertiger Abschluss Fachhochschul- oder Hochschulreife Ohne Angabe zur Art des Abschlusses Ohne allgemeinen Schulabschluss Insgesamt
0 2019.0 männlich Baden-Württemberg 168 1512 37.0 1023 1723 11.0 191 4674
1 NaN NaN Bayern 175 2142 49.0 1129 1828 16.0 153 5507
2 NaN NaN Berlin 55 205 130.0 284 765 NaN 72 1511
3 NaN NaN Brandenburg 35 118 349.0 193 325 NaN 31 1052
4 NaN NaN Bremen 11 74 NaN 67 115 NaN 20 290

Fill up columns

df['Jahr'] = 2019
df['Geschlecht'] = df['Geschlecht'].fillna(method='ffill')

df.head()
Jahr Geschlecht Bundesland Noch in schulischer Ausbildung Haupt- (Volks-)schulabschluss Abschluss der polytechnischen Oberschule Realschule oder gleichwertiger Abschluss Fachhochschul- oder Hochschulreife Ohne Angabe zur Art des Abschlusses Ohne allgemeinen Schulabschluss Insgesamt
0 2019 männlich Baden-Württemberg 168 1512 37.0 1023 1723 11.0 191 4674
1 2019 männlich Bayern 175 2142 49.0 1129 1828 16.0 153 5507
2 2019 männlich Berlin 55 205 130.0 284 765 NaN 72 1511
3 2019 männlich Brandenburg 35 118 349.0 193 325 NaN 31 1052
4 2019 männlich Bremen 11 74 NaN 67 115 NaN 20 290

Data format

df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 48 entries, 0 to 47
Data columns (total 11 columns):
 #   Column                                    Non-Null Count  Dtype  
---  ------                                    --------------  -----  
 0   Jahr                                      48 non-null     int64  
 1   Geschlecht                                48 non-null     object 
 2   Bundesland                                48 non-null     object 
 3   Noch in schulischer Ausbildung            48 non-null     int64  
 4   Haupt- (Volks-)schulabschluss             48 non-null     int64  
 5   Abschluss der polytechnischen Oberschule  42 non-null     float64
 6   Realschule oder gleichwertiger Abschluss  48 non-null     int64  
 7   Fachhochschul- oder Hochschulreife        48 non-null     int64  
 8   Ohne Angabe zur Art des Abschlusses       16 non-null     float64
 9   Ohne allgemeinen Schulabschluss           48 non-null     int64  
 10  Insgesamt                                 48 non-null     int64  
dtypes: float64(2), int64(7), object(2)
memory usage: 4.3+ KB

Convert categorical data

LIST_CAT = df.columns[1:3].tolist()
LIST_CAT
['Geschlecht', 'Bundesland']
for i in LIST_CAT:
  df[i] = df[i].astype('category')

Convert numerical data

LIST_INT = df.columns[3:].tolist()
LIST_INT
['Noch in schulischer Ausbildung',
 'Haupt- (Volks-)schulabschluss',
 'Abschluss der polytechnischen Oberschule',
 'Realschule oder gleichwertiger Abschluss',
 'Fachhochschul- oder Hochschulreife',
 'Ohne Angabe zur Art des Abschlusses',
 'Ohne allgemeinen Schulabschluss',
 'Insgesamt']
for i in LIST_INT:
  df[i] = df[i].astype('Int64')

Take a look at the data

df
Jahr Geschlecht Bundesland Noch in schulischer Ausbildung Haupt- (Volks-)schulabschluss Abschluss der polytechnischen Oberschule Realschule oder gleichwertiger Abschluss Fachhochschul- oder Hochschulreife Ohne Angabe zur Art des Abschlusses Ohne allgemeinen Schulabschluss Insgesamt
0 2019 männlich Baden-Württemberg 168 1512 37 1023 1723 11 191 4674
1 2019 männlich Bayern 175 2142 49 1129 1828 16 153 5507
2 2019 männlich Berlin 55 205 130 284 765 <NA> 72 1511
3 2019 männlich Brandenburg 35 118 349 193 325 <NA> 31 1052
4 2019 männlich Bremen 11 74 <NA> 67 115 <NA> 20 290
5 2019 männlich Hamburg 32 149 6 153 361 5 49 763
6 2019 männlich Hessen 103 722 24 621 1029 <NA> 118 2623
7 2019 männlich Mecklenburg-Vorpommern 19 93 248 123 172 <NA> 21 677
8 2019 männlich Niedersachsen 120 1022 35 949 1073 7 126 3338
9 2019 männlich Nordrhein-Westfalen 311 2389 42 1515 2752 12 405 7430
10 2019 männlich Rheinland-Pfalz 61 626 11 374 569 <NA> 68 1713
11 2019 männlich Saarland 13 166 <NA> 85 136 <NA> 17 420
12 2019 männlich Sachsen 47 257 528 334 499 <NA> 37 1703
13 2019 männlich Sachsen-Anhalt 28 126 364 163 214 <NA> 26 928
14 2019 männlich Schleswig-Holstein 46 373 9 326 394 <NA> 45 1205
15 2019 männlich Thüringen 26 130 352 150 232 <NA> 16 907
16 2019 weiblich Baden-Württemberg 171 1431 41 1356 1531 7 205 4757
17 2019 weiblich Bayern 166 2070 60 1545 1614 10 157 5638
18 2019 weiblich Berlin 48 210 151 323 778 <NA> 76 1587
19 2019 weiblich Brandenburg 35 139 380 185 328 <NA> 25 1092
20 2019 weiblich Bremen 11 70 <NA> 83 109 <NA> 20 295
21 2019 weiblich Hamburg 27 154 6 192 362 <NA> 49 804
22 2019 weiblich Hessen 105 716 25 794 941 <NA> 128 2714
23 2019 weiblich Mecklenburg-Vorpommern 19 93 275 123 170 <NA> 19 700
24 2019 weiblich Niedersachsen 125 1019 43 1109 986 <NA> 137 3428
25 2019 weiblich Nordrhein-Westfalen 318 2413 56 1839 2704 5 453 7791
26 2019 weiblich Rheinland-Pfalz 66 609 13 477 527 <NA> 67 1763
27 2019 weiblich Saarland 13 171 <NA> 100 128 <NA> 18 434
28 2019 weiblich Sachsen 49 333 562 324 468 <NA> 30 1767
29 2019 weiblich Sachsen-Anhalt 25 147 395 153 214 <NA> 24 964
30 2019 weiblich Schleswig-Holstein 48 367 11 409 379 <NA> 41 1267
31 2019 weiblich Thüringen 26 153 371 132 231 <NA> 13 927
32 2019 Insgesamt Baden-Württemberg 339 2943 79 2379 3254 17 395 9431
33 2019 Insgesamt Bayern 342 4212 109 2674 3442 26 310 11146
34 2019 Insgesamt Berlin 103 415 281 607 1543 <NA> 147 3098
35 2019 Insgesamt Brandenburg 70 258 728 378 653 <NA> 55 2144
36 2019 Insgesamt Bremen 21 145 <NA> 150 224 <NA> 40 585
37 2019 Insgesamt Hamburg 58 302 12 345 724 9 98 1567
38 2019 Insgesamt Hessen 208 1438 50 1415 1969 6 247 5337
39 2019 Insgesamt Mecklenburg-Vorpommern 38 186 523 246 342 <NA> 41 1377
40 2019 Insgesamt Niedersachsen 245 2041 78 2058 2059 11 263 6766
41 2019 Insgesamt Nordrhein-Westfalen 629 4802 98 3354 5456 17 858 15221
42 2019 Insgesamt Rheinland-Pfalz 127 1235 24 851 1097 <NA> 135 3476
43 2019 Insgesamt Saarland 26 337 <NA> 185 264 <NA> 35 853
44 2019 Insgesamt Sachsen 96 589 1090 658 968 <NA> 67 3470
45 2019 Insgesamt Sachsen-Anhalt 53 273 760 316 427 5 50 1892
46 2019 Insgesamt Schleswig-Holstein 93 741 21 736 772 8 86 2471
47 2019 Insgesamt Thüringen 52 283 723 282 463 <NA> 28 1833

Create new dataframe with subgroub

Create new dataframe

  • Let’s assume we only want to keep the total number and don’t need to differentiate between sex
df_total = df[df['Geschlecht'] == 'Insgesamt']
df_total.head()
Jahr Geschlecht Bundesland Noch in schulischer Ausbildung Haupt- (Volks-)schulabschluss Abschluss der polytechnischen Oberschule Realschule oder gleichwertiger Abschluss Fachhochschul- oder Hochschulreife Ohne Angabe zur Art des Abschlusses Ohne allgemeinen Schulabschluss Insgesamt
32 2019 Insgesamt Baden-Württemberg 339 2943 79 2379 3254 17 395 9431
33 2019 Insgesamt Bayern 342 4212 109 2674 3442 26 310 11146
34 2019 Insgesamt Berlin 103 415 281 607 1543 <NA> 147 3098
35 2019 Insgesamt Brandenburg 70 258 728 378 653 <NA> 55 2144
36 2019 Insgesamt Bremen 21 145 <NA> 150 224 <NA> 40 585

Save new dataframe

  • We can drop the variable “Geschlecht”
df_total = df_total.drop(columns=['Geschlecht'])
  • Save data
df_total.to_excel('12211-9014_de_clean.xlsx', index=False)

What’s next?

Congratulations! You have completed this tutorial 👍

Next, you may want to go back to the lab’s website

Jan Kirenz