How to load this data from .dat into dataframe using python

Question

1 1:31080.410200 2:2.871828 3:5.862267 4:7.100850 5:8.283706 6:-5.427875 7:-6.667087 8:-8.888233 9:28898.943400

Can someone please tell me how to load this data into a dataframe from .dat file. The data is given such that attribute number:value. I want only the values into the dataframe

Answer 1

Given just one line of the data, it's a little hard to go off of, but I'm assuming you're trying to get at the number after each colon, and the number before it refers to the column name?

If so, you can use read_csv with a little tweaking:

import pandas as pd
from pandas.compat import StringIO

temp='1 1:31080.410200 2:2.871828 3:5.862267 4:7.100850 5:8.283706 6:-5.427875 7:-6.667087 8:-8.888233 9:28898.943400'
#after testing replace StringIO(temp) to filename

df = pd.read_csv(StringIO(temp), 
                 sep="\s+", #separator whitespace
                 index_col=0,
                 header=None) 

for c in df.columns.values:
    df[c] = df[c].apply(lambda x: float(str(x).split(':')[1]))

df.head()

Which will output:

    1           2           3           4       5           6           7           8           9                               
1   31080.4102  2.871828    5.862267    7.10085 8.283706    -5.427875   -6.667087   -8.888233   28898.9434