I have a dataset which is in a txt.file but when i read it using pandas it gets into one colunm.
for example:
FSXC_20129_2024120000.txt
1017.0 18 26.6 15.6 51 11.07
1000.0 157 25.0 14.0 50 10.14
1983.1 305 23.8 13.2 52 9.80
2949.2 610 21.3 11.6 54 9.11
1925.0 835 19.4 10.4 56 8.63
its read as using pandas and converting into df the columns are one but i need it to be seperate row by column and also to add the headers to each columns eg, ID_1, ID_2, ID_3, etc.
your assistance would be greatly appreciated.
1
Code
import io
import pandas as pd
txt = '''FSXC_20129_2024120000.txt
1017.0 18 26.6 15.6 51 11.07
1000.0 157 25.0 14.0 50 10.14
1983.1 305 23.8 13.2 52 9.80
2949.2 610 21.3 11.6 54 9.11
1925.0 835 19.4 10.4 56 8.63
'''
df = (pd.read_csv(io.StringIO(txt), sep='s+', skiprows=1, header=None)
.rename(columns=lambda x: f'ID_{x + 1}')
)
df:
ID_1 ID_2 ID_3 ID_4 ID_5 ID_6
0 1017.0 18 26.6 15.6 51 11.07
1 1000.0 157 25.0 14.0 50 10.14
2 1983.1 305 23.8 13.2 52 9.80
3 2949.2 610 21.3 11.6 54 9.11
4 1925.0 835 19.4 10.4 56 8.63
you can replace io.StringIO(txt) to your file path.
You can use delim_whitespace to load whitespace or tab separated “.txt” into pandas dataframe and assign column names. Sample code is as follows:
import pandas as pd
df = pd.read_csv("FSXC_20129_2024120000.txt", delim_whitespace=True)
col_name = [f"ID_{i+1}" for i in range(df.shape[1])]
df.columns = col_name
df.head(2)
New contributor
Himanshu Sharma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.