Pandas: How to read data file and align columns when there are blank data entries?

user_14366676 0
Viewed 255

I have this file and when I import it:

reading     depth       cone   friction       pore    slope_X    slope_Y
    2934     0.001     0.0009                            -0.17      -0.49
    2935     0.007     0.0014               -0.0001      -0.19      -0.47
    2936     0.014     0.0012               -0.0003      -0.21      -0.45
    2937     0.021     0.0009                0.0001      -0.19      -0.48
    2938     0.029     0.0017                0.0002      -0.16      -0.42
    2939     0.041     0.0015                0.0005      -0.17      -0.44
    2940     0.052     0.0017                0.0003      -0.18      -0.52
    2941     0.065     0.0017                0.0002      -0.16      -0.45
    2942     0.078     0.0020                0.0007      -0.15      -0.48
    2943     0.090     0.0021                0.0008      -0.16      -0.47
    2944     0.099     0.0019                0.0011      -0.24      -0.42
    2945     0.109     0.0024                0.0009      -0.22      -0.45
    2946     0.119     0.0029                0.0008      -0.20      -0.48
    2947     0.130     0.0033                0.0013      -0.17      -0.43
    2948     0.140     0.0030                0.0015      -0.22      -0.41
    2949     0.151     0.0030                0.0011      -0.19      -0.47
    2950     0.162     0.0046     0.0000     0.0014      -0.18      -0.46
    2951     0.173     0.0049     0.0001     0.0018      -0.16      -0.44
    2952     0.183     0.0055     0.0001     0.0018      -0.13      -0.43
    2953     0.193     0.0064     0.0001     0.0017      -0.20      -0.45
    2954     0.204     0.0067     0.0001     0.0018      -0.22      -0.46
    2955     0.215     0.0073     0.0001     0.0018      -0.20      -0.49
    2956     0.226     0.0065     0.0002     0.0020      -0.12      -0.46
    2957     0.236     0.0075     0.0002     0.0024      -0.08      -0.45
    2958     0.247     0.0076     0.0002     0.0021      -0.12      -0.45
    2959     0.258     0.0084     0.0002     0.0024      -0.15      -0.46
    2960     0.268     0.0097     0.0002     0.0026      -0.16      -0.49

It looks like this when I use df=pd.read_csv(filename, delim_whitespace=True)

It shifts the next column values and thats what I dont want to. I want to read the data for each column. Do you know how can I do it?

     reading  depth     cone  friction    pore  slope_x  slope_y
0       2933  0.000   0.0010   -0.1900 -0.4800      NaN      NaN
1       2934  0.001   0.0009   -0.1700 -0.4900      NaN      NaN
2       2935  0.007   0.0014   -0.0001 -0.1900    -0.47      NaN
3       2936  0.014   0.0012   -0.0003 -0.2100    -0.45      NaN
4       2937  0.021   0.0009    0.0001 -0.1900    -0.48      NaN
..       ...    ...      ...       ...     ...      ...      ...
486     3930  4.351  17.6757    0.0183 -0.0179    -0.16    -0.17
487     3931  4.353  18.1356    0.0193 -0.0184    -0.17    -0.15
488     3932  4.356  18.3872    0.0206 -0.0287    -0.19    -0.19
489     3933  4.357  18.3862    0.0214 -0.0278    -0.13    -0.15
490     3934  4.357  18.5794    0.0230 -0.0300    -0.17    -0.12

When I use df = pd.read_csv(filename, sep='\r\t') is returns: So the data don't match to columns. The idea is to replace that blank spaces with NaN

enter image description here

pandas csv python
1 Answers

The problem appears to be that you have columns with missing data entries, so when Pandas parses the data file using all white space as delimiters, then these blank sections end up shifting the columns out of alignment.

Check the data file to see if the columns are delimited with spaces, tab characters, or something else. For example, if the delimiter is tabs, then you could change the code to:

df = pd.read_csv(filename, sep='\r\t')

If there are multiple spaces between each column, then the sep could be set to:

df = pd.read_csv(filename, sep='   ')  # Needs to exactly match # of spaces between columns.

You may also want to specify the encoding of the data file, for example:

df = pd.read_csv(filename, sep='\r\t', encoding='utf-8')
user_11644650
Related