4

I'm trying to transform a list of lists of tuples to a pandas dataframe but can't figure out how to do so. My addresses are structured like so: 

addresses = [
 [('the vicars inn', 'house'), ('68', 'house_number'), ('church lane', 'road'), ('arlesey', 'city'), ('beds', 'house')],
 [('the old oak', 'house'), ('85', 'house_number'), ('church lane', 'road'), ('arlesey', 'city'), ('beds', 'house')],
 [('adj', 'road'), ('85', 'house_number'), ('high street', 'road'), ('arlesey', 'city'), ('beds', 'house')],
 [('arlesey community centre', 'house'), ('high street', 'road'), ('arlesey', 'city'), ('beds', 'house')],
 [('arlesey community centre', 'house'), ('high street', 'road'), ('arlesey', 'city'), ('beds', 'house')]
]

Ideally, I need to return a dataframe like: 

      city           house             house_number       road
0     arlesey        the vicars inn    68                 church lane
1     arlesey        the old oak       85                 church lane

What I've tried so far is to pivot the table but it is not producing the intended outcome:

pd.DataFrame.from_records(addresses[0]).pivot(columns=1, values=0)

Does anyone have any guidance on methods I should be looking at to achieve my ideal dataframe?

Sam

Sam Comber
  • 1,237
  • 1
  • 15
  • 35

1 Answers1

5

You can convert each record to a dictionary and then use DataFrame.from_records:

pd.DataFrame.from_records([{k: v for v, k in row} for row in addresses])

#      city house   house_number    road
#0  arlesey beds              68    church lane
#1  arlesey beds              85    church lane
#2  arlesey beds              85    high street
#3  arlesey beds             NaN    high street
#4  arlesey beds             NaN    high street
Psidom
  • 209,562
  • 33
  • 339
  • 356