1

I would like to identify that data format. Is there an official name for it that I could use in a search engine (of my trust) to go on with research about supporting tools? Is this a (quasi) standardized data format?

This question is not about to understand the data or how to process them. I do understand the structure and I'm able to write my own code to process it. But I assume the format is quit old and there must be some tools out there. I also want to understand the design decisions behind that (IMHO wired) format.

It comes form Destatis (Federal Statistical Office of Germany) and can be downloaded here. The codebook for that data is a PDF file and is also bundeled with the archive behind that download link.

102023013101          Schleswig-Holstein                                Kiel                                                                                                                     
402023013101001       Flensburg, Stadt                                  Flensburg                                         41                                                                     
502023013101001   0000Flensburg, Stadt                                                                                    50                                                                     
6020230131010010000000Flensburg, Stadt                                                                                    61    000000056730000009111300000045336    24937*****  2115111211901001
402023013101002       Kiel, Landeshauptstadt                            Kiel                                              41                                                                     
502023013101002   0000Kiel, Landeshauptstadt                                                                              50                                                                     
6020230131010020000000Kiel, Landeshauptstadt                                                                              61    000000118650000024624300000119860    24103*****  2119151713101005
402023013101003       Lübeck, Hansestadt                                Lübeck                                            41                                                                     
502023013101003   0000Lübeck, Hansestadt                                                                                  50                                                                     
6020230131010030000000Lübeck, Hansestadt                                                                                  61    000000214190000021627700000104005    23552*****  2122172113501011
402023013101004       Neumünster, Stadt                                 Neumünster                                        41                                                                     
502023013101004   0000Neumünster, Stadt                                                                                   50                                                                     
6020230131010040000000Neumünster, Stadt                                                                                   61    000000071660000007949600000039382    24534*****  2124151913901006
402023013101051       Dithmarschen                                      Heide                                             43                                                                     
502023013101051   0011Brunsbüttel, Stadt                                                                                  50                                                                     
502023013101051   0044Heide, Stadt                                                                                        50                                                                     
502023013101051   5163Burg-St. Michaelisdonn                            Burg (Dith.)                                      51                                                                     
502023013101051   5166Marne-Nordsee                                     Marne                                             51                                                                     
502023013101051   5169Eider                                             Hennstedt                                         55                                                                     
502023013101051   5172Heider Umland                                     Heide                                             55                                                                     
502023013101051   5175Mitteldithmarschen                                Meldorf                                           51                                                                     
502023013101051   5178Büsum-Wesselburen                                 Büsum                                             51                                                                     
6020230131010510015175Albersdorf                                                                                          64    000000017120000000377200000001837    25767       2123131912701003
6020230131010510025175Arkebek                                                                                             64    000000006920000000022000000000114    25767       2123131912701003
6020230131010510035163Averlak                                                                                             64    000000009060000000055800000000290    25715       2123131912705003
6020230131010510045175Bargenstedt                                                                                         64    000000011900000000096500000000468    25704       2123131912713003
6020230131010510055169Barkenholm                                                                                          64    000000005110000000016400000000092    25791       2116131912701002
6020230131010510065175Barlt                                                                                               64    000000022880000000077700000000385    25719       2123131912713003
6020230131010510085169Bergewöhrden                                                                                        64    000000002640000000003700000000017    25779       2116131912701002
6020230131010510105163Brickeln                                                                                            64    000000006070000000019800000000108    25712       2123131912705003

The codebook or description of the format comes as a (non machine readable) PDF file. Here is a snippet from it describing the Satzart 10.

enter image description here

Here is another part of the codbook describing the Satzart 60.

enter image description here

Some facts about that format.

  • There is no field separator.
  • Fields have a fixed length.
  • But number of fields and their length is different between the lines. The format of one line is not consistent of the whole file.
  • The first two characters of each line decide how the rest of the line should be interpreted.
2
  • 1
    I don't see that the length of the fields vary between lines. They're fixed width and padded with whitespace, where some fields use leftpad and others use rightpad.
    – gerrit
    Jan 19, 2023 at 17:31
  • Please post the link to the non-machine readable pdf file that is EXPLAINING the format. Then please provide example lines, where the format is inconsistent. Please make it as easy as possible for StackExchangers to help you. This will increase probability of getting answers that help.
    – Grimaldi
    Jan 19, 2023 at 17:46

2 Answers 2

1

I don't know the format, but it's easy to read fixed width with pandas.

import pandas as pd
df = pd.read_fwf(filename, colspecs=[(0, 15), (15, 22), (22, 50), (72, 100), (122, 124), (128, 163), (165, 177), (179, 200)])
print(df.head())
      102023013101  Unnamed: 1      Schleswig-Holstein       Kiel  Unnamed: 4                         Unnamed: 5  Unnamed: 6    Unnamed: 7
0  402023013101001         NaN        Flensburg, Stadt  Flensburg          41                                NaN         NaN           NaN
1  502023013101001         0.0        Flensburg, Stadt        NaN          50                                NaN         NaN           NaN
2  602023013101001         0.0        Flensburg, Stadt        NaN          61  000000056730000009111300000045336  24937*****  1.511121e+13
3  402023013101002         NaN  Kiel, Landeshauptstadt       Kiel          41                                NaN         NaN           NaN
4  502023013101002         0.0  Kiel, Landeshauptstadt        NaN          50                                NaN         NaN           NaN

getting the columns labeled correctly or get numeric IDs as str or object rather than int or float is left as an exercise for the reader.

0

The problem is a misinterpretation of the codebook.

It is true that the meaning of each line is different and defined by the two first characters in that file.

But technically the lines are of equal format. The length of the fields are equal. Some fields are joined together depending on the first two characters. E.g. Satzart-60 defines from the field F8 till F19 19 fields. In Satzart-10 this 19 fields are joined together and represented by F8 and F9 having the same length in characters/bytes then the 19 fields from Satzart-60.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.