CODEWITHSUNDEEP
pythonlistpandasdictionarydataframe
Minoru Tsuru
Minoru Tsuru

Reputation: 79

Creating a pandas Dataframe from a list of Dictionaries, dictionary keys as columns

not sure if the title is explanatory enough.

I have a list of dictionaries from which I want to create a dataframe. For simplicity the list is: 

list=[]

DIC1={'value': 69.0, 'time': 10, 'from': 'Mexico', 'signal_name': 'Part1'}
DIC2={'value': 65.0, 'time': 10, 'from': 'Mexico', 'signal_name': 'Part2'}
DIC3={'value': 60.0, 'time': 11, 'from': 'Mexico', 'signal_name': 'Part1'}
DIC4={'value': 67.0, 'time': 10, 'from': 'Mexico', 'signal_name': 'Part3'}
DIC5={'value': 69.0, 'time': 11, 'from': 'Mexico', 'signal_name': 'Part2'}
DIC6={'value': 70.0, 'time': 12, 'from': 'Mexico', 'signal_name': 'Part1'}
DIC7={'value': 68.0, 'time': 12, 'from': 'Mexico', 'signal_name': 'Part2'}
DIC8={'value': 71.0, 'time': 11, 'from': 'Mexico', 'signal_name': 'Part3'}
DIC9={'value': 50.0, 'time': 12, 'from': 'Mexico', 'signal_name': 'Part3'}

list=[DIC1,DIC2,DIC3,DIC4,DIC5,DIC6,DIC7,DIC8,DIC9]
#Convert to Dataframe
df=pd.DataFrame(list)

If I simply convert the list of dictionaries to a dataframe like in the example above I get a dataframe like:

    from    signal_name time    value
0   Mexico  Part1       10      69.0
1   Mexico  Part2       10      65.0
2   Mexico  Part1       11      60.0
3   Mexico  Part3       10      67.0
4   Mexico  Part2       11      69.0
5   Mexico  Part1       12      70.0
6   Mexico  Part2       12      68.0
7   Mexico  Part3       11      71.0
8   Mexico  Part3       12      50.0

But I'm trying to get a filtered table, I guess kind of a pivot table? Something like:

    time    from    part1(value)    part2(value)    part3(value)
0   10      Mexico      69.0            65.0            67.0
1   11      Mexico      60.0            69.0            71.0
2   12      Mexico      70.0            68.0            50.0

Is this possible?

Upvotes: 1

Views: 919

Answers (2)

BENY
BENY

Reputation: 323376

Solution from unstack

df.set_index(['from','signal_name','time']).unstack(-2)
Out[437]: 
            value            
signal_name Part1 Part2 Part3
from   time                  
Mexico 10    69.0  65.0  67.0
       11    60.0  69.0  71.0
       12    70.0  68.0  50.0

Upvotes: 2

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

With the help of pivot table 

df.pivot_table(index=['from','time'],columns=['signal_name'],values=['value']).reset_index()

Output:

               from time value            
signal_name              Part1 Part2 Part3
0            Mexico   10  69.0  65.0  67.0
1            Mexico   11  60.0  69.0  71.0
2            Mexico   12  70.0  68.0  50.0

If you dont want value as separate header then pass values parameter as a string i.e 

df.pivot_table(index=['from','time'],columns=['signal_name'],values='value').reset_index()

signal_name    from  time  Part1  Part2  Part3
0            Mexico    10   69.0   65.0   67.0
1            Mexico    11   60.0   69.0   71.0
2            Mexico    12   70.0   68.0   50.0

Upvotes: 3

Related Questions