228

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?

Danny Varod
  • 17,324
  • 5
  • 69
  • 111
Joop
  • 7,840
  • 9
  • 43
  • 58

4 Answers4

336

Call the pd.DataFrame constructor directly:

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

   Heading1  Heading2
0         1         2
1         3         4
cs95
  • 379,657
  • 97
  • 704
  • 746
EdChum
  • 376,765
  • 198
  • 813
  • 562
  • 2 by 2 is unfortunate example. I was expecting individual lists to become columns, but pandas goes across lists and creates columns – user1700890 Apr 06 '23 at 17:27
111

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
Monica Heddneck
  • 2,973
  • 10
  • 55
  • 89
Shoresh
  • 2,693
  • 2
  • 16
  • 9
  • 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`. – Sinux1 Oct 12 '22 at 18:28
9

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
BENY
  • 317,841
  • 20
  • 164
  • 234
2

From the table example, call DataFrame constructor as follow:

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

df = pd.DataFrame(table[1:], columns=table[0])
Timothy C. Quinn
  • 3,739
  • 1
  • 35
  • 47