Creating a table from existing data file, only last row value remains

user_20003845 0
Viewed 45

I'm just starting with python and I have a basic question about creating tables.

I have a file called example.txt as follow:

# Header information 1
# Header information 2
# Header information 3
# Header information 4
# var1  var2      var3      var4      var5
# Header information 5
# Header information 6
    1   6500.0    5.052300  1.020020  9.355021
    2   2500.7    3.038809  0.033637  1.736393
    3    750.6    5.003003  0.047254  3.287092
...
...
...
  100   2700.6    9.002800  0.088105  8.289851

I want to create a table using the name of the variables (var1, var2, etc.) as header names. I have tried this:

fnExample = open('example.txt', 'r')
i = 0
for line in fnExample:
    i = i + 1
    if i==5 :
        varExample = [word.strip() for word in line.split()]
    if (i>7 and i<100):
        dataExample = [word.strip() for word in line.split()]
        datatable[varExample[1]]=dataExample[0]
        datatable[varExample[2]]=dataExample[1]
        datatable[varExample[3]]=dataExample[2]
        datatable[varExample[4]]=dataExample[3]
        datatable[varExample[5]]=dataExample[4]

print(datatable['var1']) # gives only the last value of var1

Why does my datatable only contain the last value, and how can I fix it?

python
2 Answers

You overwrite the values at each key of datatable in every iteration, it is not surprising that what remains after the last iteration is the values from the last iteration. You should create a list to hold the dictionaries for all rows before you start iterating, then create a new dictionary for each row and append it to the list in every iteration.

I used a loop / dict comprehension to do this instead of writing five lines. Also I'm not sure why you associated the 1th column name from the header to the 0th column, but I've left that behavior intact in case it's correct. If you want to change it and associate the 0th header to the 0th column, remove the varExample = varExample[1:] line.

fnExample = open('example.txt', 'r')

datatable = []

for i, line in enumerate(fnExample):
    if i == 5 :
        varExample = [word.strip() for word in line.split()]
        varExample = varExample[1:] # Discard the first element of the vars because you don't use it
    elif i > 7 and i < 100:
        dataExample = [word.strip() for word in line.split()]
        row_dict = {var: data for var, data in zip(varExample, dataExample)}
        datatable.append(row_dict)

fnExample.close()

print(datatable) # prints the entire list

If you're using pandas, you can now create a full dataframe using

df = pd.DataFrame(datatable)

Mind you, if you're using pandas anyway, you can specify a list of rows to skip as the skiprows argument to read_csv and you'll have the same result in a single function call

I am not familiar with the datatable package if you're using that, but I'm sure there's a way to convert your list of dicts into one if you look through the docs.

If you want your data to be structured as a dictionary instead, where each key gives you a list of data in that column, that is easy too. In this case, create a dict when you read the column names, and set the values of those keys to empty lists. Then append to these lists when you read the rows.

for i, line in enumerate(fnExample):
    if i == 5 :
        varExample = [word.strip() for word in line.split()]

        datatable = {var: [] for var in varExample[1:]} # Ignore the first element of the vars because you don't use it
        
    elif i > 7 and i < 100:
        dataExample = [word.strip() for word in line.split()]
        for lst, data in zip(datatable.values(), dataExample):
            lst.append(data)

Now, print(datatable['var1']) will print all the values in the column var1.

user_8439530

Instead of iterating over the the whole table, you can use pandas method pandas.read_csv and ignore the lines you don't need:

import pandas
datatable = pandas.read_csv('example.txt', sep=r'\s+',skiprows=[0,1,2,3,5,6])

Just have in mind that the separator sep=' ' must spaces for every column, '\s+' allows being one or more space. Also, you don't have to use the parameter head=4 because the other rows are ignored and the 4th row becomes the 1st row (header by default)

Example file:

Header information 1
Header information 2
Header information 3
Header information 4
var1 var2 var3 var4 var5
Header information 5
Header information 6
1 6500.0 5.052300 1.020020 9.355021
2 2500.7 3.038809 0.033637 1.736393
3 750.6 5.003003 0.047254 3.287092

Output:

var1    var2    var3    var4    var5
0   1   6500.0  5.052300    1.020020    9.355021
1   2   2500.7  3.038809    0.033637    1.736393
2   3   750.6   5.003003    0.047254    3.287092
user_180910400
Related