converting list of header and row lists into pandas DataFrame

Question

I am reading contents of a spreadsheet into pandas. DataNitro has a method that returns a rectangular selection of cells as a list of lists. So

table = Cell("A1").table

gives

table = [['Heading1', 'Heading2'], [1 , 2], [3, 4]]

headers = table.pop(0) # gives the headers as list and leaves data

I am busy writing code to translate this, but my guess is that it is such a simple use that there must be method to do this. Cant seem to find it in documentation. Any pointers to the method that would simplify this?

cs95 · Accepted Answer · 2019-01-30 22:24:28Z

344

Call the pd.DataFrame constructor directly:

df = pd.DataFrame(table, columns=headers)
df

   Heading1  Heading2
0         1         2
1         3         4

edited Jan 30, 2019 at 22:24

cs95

406k106 gold badges744 silver badges797 bronze badges

answered Oct 1, 2013 at 9:41

EdChum

397k204 gold badges836 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1700890 Over a year ago

2 by 2 is unfortunate example. I was expecting individual lists to become columns, but pandas goes across lists and creates columns

Monica Heddneck · Accepted Answer · 2020-12-12 01:16:52Z

112

With approach explained by EdChum above, the values in the list are shown as rows. To show the values of lists as columns in DataFrame instead, simply use transpose() as following:

table = [[1 , 2], [3, 4]]
df = pd.DataFrame(table)
df = df.transpose()
df.columns = ['Heading1', 'Heading2']

The output then is:

      Heading1  Heading2
0         1        3
1         2        4

edited Dec 12, 2020 at 1:16

Monica Heddneck

3,17511 gold badges59 silver badges94 bronze badges

answered Jan 25, 2016 at 15:59

Shoresh

2,8832 gold badges19 silver badges9 bronze badges

1 Comment

Sinux1 Over a year ago

Are you sure about df = df.transpose() ? I have a list of lists (inner list has 11 elements) and need them in a pandas dataframe the same way, but to get the output you are displaying, I leave out the transpose.

BENY · Accepted Answer · 2019-12-24 22:29:39Z

10

Even without pop the list we can do with set_index

pd.DataFrame(table).T.set_index(0).T
Out[11]: 
0 Heading1 Heading2
1        1        2
2        3        4

Update from_records

table = [['Heading1', 'Heading2'], [1 , 2], [3, 4]]

pd.DataFrame.from_records(table[1:],columns=table[0])
Out[58]: 
   Heading1  Heading2
0         1         2
1         3         4

edited Dec 24, 2019 at 22:29

answered Dec 30, 2018 at 3:06

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Timothy C. Quinn · Accepted Answer · 2022-09-06 12:48:15Z

4

From the table example, call DataFrame constructor as follow:

table = [['Heading1', 'Heading2'], [1 , 2], [3, 4]]

df = pd.DataFrame(table[1:], columns=table[0])

answered Sep 6, 2022 at 12:48

Timothy C. Quinn

4,6391 gold badge47 silver badges56 bronze badges

Collectives™ on Stack Overflow

converting list of header and row lists into pandas DataFrame

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related