CODEWITHSUNDEEP
pandas
EmJ
EmJ

Reputation: 4618

How to change the orientation of a list in pandas

I have three lists as follows.

mylist = [["sensor9", [[0.5, 0.3, 0.8, 0.9, 0.8], [0.5, 0.6, 0.8, 0.9, 0.9]]], 
      ["sensor12", [[10.6, 0.5, 0.9, 1.0, 0.9], [10.6, 0.9, 0.8, 0.8, 0.8]]]]

columns = ['score_1', 'score_2']
years = [2001, 2002, 2003, 2004, 2005]

I want to change the orientation of mylist as follows using columns as the headings and years for each element in mylist. More specifically, my final output should look as follows.

id, sensor, time, score_1, score_2
0, sensor9, 2001, 0.5, 0.5
0, sensor9, 2002, 0.3, 0.6
0, sensor9, 2003, 0.8, 0.8
0, sensor9, 2004, 0.9, 0.9
0, sensor9, 2005, 0.8, 0.9
1, sensor12, 2001, 0.6, 0.6
1, sensor12, 2002, 0.5, 0.9
1, sensor12, 2003, 0.9, 0.8
1, sensor12, 2004, 1.0, 0.8
1, sensor12, 2005, 0.9, 0.8

Dataframe that describes the id of the above dataframe

id, sensor
0, sensor9
1, sensor12

I was trying to do this with DataFrame.from_dict in pandas. However, I am not sure how to change the orientation of the mylist and align it with the years in pandas. Is it possible to do this?

I am happy to provide more details if needed.

Upvotes: 2

Views: 1653

Answers (1)

jezrael
jezrael

Reputation: 863226

Use list comprehension for generate DataFrame for second values of lists (nested lists) with transpose by DataFrame.T, then concat together and last create new column id by Series.map and DataFrame.insert for first position:

df1 = pd.DataFrame({'id':[0,1],
                    'sensor':['sensor9','sensor12']})

mylist = [["sensor9",  [[0.5, 0.3, 0.8, 0.9, 0.8], [0.5, 0.6, 0.8, 0.9, 0.9]]], 
          ["sensor12", [[10.6, 0.5, 0.9, 1.0, 0.9], [10.6, 0.9, 0.8, 0.8, 0.8]]]]

columns = ['score_1', 'score_2']
years = [2001, 2002, 2003, 2004, 2005]

L = [pd.DataFrame(x[1], index=columns, columns=years).T for x in mylist]

df = pd.concat(L, keys=[x[0] for x in mylist]).rename_axis(('sensor','time')).reset_index()
df.insert(0, 'id', df['sensor'].map(df1.set_index('sensor')['id']))
print (df)

   id    sensor  time  score_1  score_2
0   0   sensor9  2001      0.5      0.5
1   0   sensor9  2002      0.3      0.6
2   0   sensor9  2003      0.8      0.8
3   0   sensor9  2004      0.9      0.9
4   0   sensor9  2005      0.8      0.9
5   1  sensor12  2001     10.6     10.6
6   1  sensor12  2002      0.5      0.9
7   1  sensor12  2003      0.9      0.8
8   1  sensor12  2004      1.0      0.8
9   1  sensor12  2005      0.9      0.8

EDIT:

mylist = [["sensor9",  [[0.5, 0.3, 0.8, 0.9, 0.8], [0.5, 0.6, 0.8, 0.9, 0.9]]], 
          ["sensor12", [[10.6, 0.5, 0.9, 1.0, 0.9], [10.6, 0.9, 0.8, 0.8, 0.8]]]]

columns = ['score_1', 'score_2']
years = [2001, 2002, 2003, 2004, 2005]

L = [pd.DataFrame(x[1], index=columns, columns=years).T for x in mylist]

df = pd.concat(L, keys=[x[0] for x in mylist]).rename_axis(('sensor','time')).reset_index()
df.insert(0, 'id', pd.factorize(df['sensor'])[0])
print (df)
   id    sensor  time  score_1  score_2
0   0   sensor9  2001      0.5      0.5
1   0   sensor9  2002      0.3      0.6
2   0   sensor9  2003      0.8      0.8
3   0   sensor9  2004      0.9      0.9
4   0   sensor9  2005      0.8      0.9
5   1  sensor12  2001     10.6     10.6
6   1  sensor12  2002      0.5      0.9
7   1  sensor12  2003      0.9      0.8
8   1  sensor12  2004      1.0      0.8
9   1  sensor12  2005      0.9      0.8

Upvotes: 1

Related Questions