Converting a 1D list into a 2D DataFrame

user_13812982 0
Viewed 231

I have scraped a webpage table, and the table items are in a sequential 1D list, with repeated headers. I want to reconstitute the table into a DataFrame.

I have an algorithm to do this, but I'd like to know if there is a more pythonic/efficient way to achieve this? NB. I don't necessarily know how many columns there are in my table. Here's an example:

input = ['A',1,'B',5,'C',9,
         'A',2,'B',6,'C',10,
         'A',3,'B',7,'C',11,
         'A',4,'B',8,'C',12]

output = {}

it = iter(input)
val = next(it)

while val:
    if val in output:
        output[val].append(next(it))
    else:
        output[val] = [next(it)]

    val = next(it,None)

df = pd.DataFrame(output)

print(df)

with the result:

   A  B   C
0  1  5   9
1  2  6  10
2  3  7  11
3  4  8  12
pandas dataframe python
2 Answers

If your data is always "well behaved", then something like this should suffice:

import pandas as pd

data = ['A',1,'B',5,'C',9,
         'A',2,'B',6,'C',10,
         'A',3,'B',7,'C',11,
         'A',4,'B',8,'C',12]

result = {}

for k,v in zip(data[::2], data[1::2]):
    result.setdefault(k, []).append(v)

df = pd.DataFrame(output)
user_50144550

You can also use numpy reshape:

import numpy as np
cols = sorted(set(l[::2]))
df = pd.DataFrame(np.reshape(l, (int(len(l)/len(cols)/2), len(cols)*2)).T[1::2].T, columns=cols)

   A  B   C
0  1  5   9
1  2  6  10
2  3  7  11
3  4  8  12

Explaination:

# get columns
cols = sorted(set(l[::2]))

# reshape list into list of lists
shape = (int(len(l)/len(cols)/2), len(cols)*2)
np.reshape(l, shape)

# get only the values of the data
.T[1::2].T
# this transposes the data and slices every second step
user_119717850
Related