Read .txt file in python, assign first line to a variable and from 2nd line to a dataframe

Question

Working on Python script to read .txt files(space seperated) to a pandas dataframe. But 1st line contain server information. How do I extract 1st line to other variable and remaining file content to a pandas dataframe?

Sample file1.txt

srv123 12_45/56-01V top
Date location character1 character2 character3
2023-01-24 asd 3434.56 67.567 898.898
2023-01-24 axs 345.56 78.567 934.898
2023-01-24 ert 4567.56 89.123 901.898
2023-01-25 tgb 7879.56 90.567 456.898

Expected Dataframe :

server	Date	location	character1	character2	character3
srv123	2023-01-24	asd	3434.56	67.567	898.898
srv123	2023-01-24	axs	345.56	78.567	934.898
srv123	2023-01-24	ert	4567.56	89.123	901.898
srv123	2023-01-25	tgb	7879.56	90.567	456.898

I tried with read_csv but first line and header are messed up.

mozway · Accepted Answer · 2023-08-18T07:09:25.087

2

You can read the first line with next, then pass the rest of the file to read_csv:

with open('Sample file1.txt') as f:
    my_var = next(f)
    df = pd.read_csv(f, sep=' +')

Output:

# my_var
srv123 12_45/56-01V top

# df
         Date location  character1  character2  character3
0  2023-01-24      asd     3434.56      67.567     898.898
1  2023-01-24      axs      345.56      78.567     934.898
2  2023-01-24      ert     4567.56      89.123     901.898
3  2023-01-25      tgb     7879.56      90.567     456.898

edited Aug 18 '23 at 07:09

answered Aug 18 '23 at 06:55

mozway

194,879
13
39
75

this worked perfectly !! thank you – Kavya shree Aug 18 '23 at 07:13
@Kavyashree I'm surprised you say that it's perfect when the dataframe is not as you require it to be. Specifically, it's missing the *server* column – DarkKnight Aug 18 '23 at 07:21
I can get server name from variable my_var and add new column to dataframe. This solved 99% of the requirement – Kavya shree Aug 18 '23 at 07:23
@Kavyashree what is the missing 1%? – mozway Aug 18 '23 at 07:25
server = my_var.split('\t')[0] df[server] = server so this will add column server to the df – Kavya shree Aug 18 '23 at 07:32

Paramjot Singh · Answer 2 · 2023-08-18T06:59:30.107

0

The skiprows parameter in the read_csv call, when set to 1, will skip the first row of your file. This should skip the line you want to avoid adding to the dataframe.

edited Aug 18 '23 at 06:59

answered Aug 18 '23 at 06:57

Paramjot Singh

1
2

This however requires you to read the file twice. – mozway Aug 18 '23 at 07:09
How would you do this and also get the **server** column? That can only be deduced from the first line in the file – DarkKnight Aug 18 '23 at 07:12
skiprows are deleting header, where has next() is working fine – Kavya shree Aug 18 '23 at 07:14
I can add another column to dataframe and fill all rows with the my_var – Kavya shree Aug 18 '23 at 07:15

Read .txt file in python, assign first line to a variable and from 2nd line to a dataframe

2 Answers2