Parsing text in complex format to python data table

Question

I have a txt file as like this

 Station coordinates and velocities:
  ----------------------------------
  Reference epoch: 2017-01-02 12:00:00

  Station name          Typ   A priori value  Estimated value    Correction     RMS error      3-D ellipsoid        2-D ellipse
  ------------------------------------------------------------------------------------------------------------------------------------
  CHNG 99128            X     -3233224.72062   -3233224.72062       0.00000       0.00120
                        Y      4067916.18933    4067916.18933       0.00000       0.00118
                        Z      3686212.02917    3686212.02917       0.00000       0.00091

                        U           61.78989         61.78989       0.00000       0.00160     0.00161    7.0
                        N         35.5333100       35.5333100       0.00000       0.00046     0.00044    1.8     0.00046    3.2
                        E        128.4780652      128.4780652       0.00000       0.00095     0.00094   -5.4     0.00095

  CHSG 98109            X     -3237146.18442   -3237146.18442       0.00000       0.00119
                        Y      3989513.50556    3989513.50556       0.00000       0.00130
                        Z      3767338.46367    3767338.46367       0.00000       0.00096

                        U          250.04930        250.04930       0.00000       0.00170     0.00170    5.4
                        N         36.4356270       36.4356270       0.00001       0.00048     0.00045    0.6     0.00048    0.6
                        E        129.0563506      129.0563506       0.00000       0.00095     0.00095    0.0     0.00095

  CHWN 09131            X     -3260411.69912   -3260411.69912       0.00000       0.00127
                        Y      4070678.22490    4070678.22490       0.00000       0.00143
                        Z      3659345.19301    3659345.19301       0.00000       0.00103

                        U           88.37577         88.37577       0.00000       0.00187     0.00188    5.0
                        N         35.2361197       35.2361197       0.00000       0.00048     0.00046    0.5     0.00048    0.3
                        E        128.6929994      128.6929994       0.00000       0.00098     0.00098   -0.6     0.00098

But I really Don't Know how can I change this one to python dataframe

especially station name isn't fill all cells so it make a problem

Could you help me with a solution please?

If this is not a html or xml file, beautifoulsoup is irrelevant. Hint: this is a fixed lenght fields file. You should 1/ read it line by line 2/ skip the headers 3/ use indices of fields in line to extract fields 4/ copy station name from previous line if empty. Try to write some code using these hints and and feel free to ask here it you get stuck. — Serge Ballesta
– Serge Ballesta, Commented Mar 18, 2019 at 10:14

Daniel Labbe · Accepted Answer · 2019-03-18 10:22:59Z

1

This code uses Pandas.read_fwf() to read the file by position, skips the first 6 rows, remove the blank lines and fill the Station Name for the empty rows with fill_na():

cols = [(3,23),(24,30),(30,45),(46,62),(63,76),(77,90), (91,102), (103, 109), (110,121), (122,128)]
names = ['Station name','Typ', 'A priori value', 'Estimated value', 'Correction', 'RMS error', '3-D', 'ellipsoid', '2-D', 'ellipse']
df = pd.read_fwf('my_file_24.txt', header=None, colspecs = cols, names = names, skiprows = 6)
df = df[~df.Typ.isnull()]
df['Station name'].fillna(method='ffill',inplace = True)

It generates the following Pandas data frame:

answered Mar 18, 2019 at 10:22

Daniel Labbe

2,0193 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parsing text in complex format to python data table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related