1
$\begingroup$
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

$\endgroup$

1 Answer 1

2
$\begingroup$

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
$\endgroup$
1
  • $\begingroup$ Yes, I want to get the number after the colon, the number before refers to attribute name. Thank you for the amazing code, I will try it out asap with complete data and let you know $\endgroup$
    – Srihari
    Feb 10, 2018 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.