pythonpandastext-filesread-csv

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


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.

Solution

  • 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