Read .txt File With Python Pandas - Strings And Floats

I would like to read a .txt file in Python (3.6.0) using Pandas. The first lines of the .txt file is shown below: Text file to read Location: XXX Campaign Name: XXX

Solution 1:

You want to pass skiprows=11, and skipinitial_space=True to read_csv along with sep=';' as you have spaces along with your separator:

In [83]:
import io
import pandas as pd
t="""Location:           XXX
Campaign Name:      XXX
Date of log start:  2016_10_09
Time of log start:  04:27:28
Sampling Frequency: 1Hz
Config file:        XXX
Logger Serial:      XXX
​
CH Mapping;;XXXC1;XXXC2;XXXC3;XXXC4
CH Offsets in ms;;X;X,X;X;X,X
CH Units;;mA;mA;mA;mA
Time;msec;Channel1;Channel2;Channel3;Channel4
04:30:00;000; 0.01526;10.67903;10.58366; 0.00000
04:30:01;000; 0.17090;10.68666;10.58518; 0.00000
04:30:02;000; 0.25177;10.68284;10.58442; 0.00000"""
​
df = pd.read_csv(io.StringIO(t), skiprows=11, sep=';', skipinitialspace=True)
df

Out[83]:
       Time  msec  Channel1  Channel2  Channel3  Channel4
0  04:30:0000.0152610.6790310.583660.01  04:30:01     00.1709010.6866610.585180.02  04:30:02     00.2517710.6828410.584420.0

You can see the dtypes are now correct:

In [84]:
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 6 columns):
Time        3 non-nullobject
msec        3 non-null int64
Channel1    3 non-null float64
Channel2    3 non-null float64
Channel3    3 non-null float64
Channel4    3 non-null float64
dtypes: float64(4), int64(1), object(1)
memory usage: 224.0+ bytes

You may also want to optionally parse the times into datetimes:

In [86]:    
df = pd.read_csv(io.StringIO(t), skiprows=11, sep=';', skipinitialspace=True, parse_dates=['Time'])
df

Out[86]:
                 Time  msec  Channel1  Channel2  Channel3  Channel4
02017-03-1604:30:0000.0152610.6790310.583660.012017-03-1604:30:0100.1709010.6866610.585180.022017-03-1604:30:0200.2517710.6828410.584420.0In [87]:    
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0to2
Data columns (total 6 columns):
Time3 non-null datetime64[ns]
msec        3 non-null int64
Channel1    3 non-null float64
Channel2    3 non-null float64
Channel3    3 non-null float64
Channel4    3 non-null float64
dtypes: datetime64[ns](1), float64(4), int64(1)
memory usage: 224.0 bytes