I have a below list.
[['Name', 'Mike', 'score', '15', 'day','12'],
['Name', 'Dan', 'score', '65'],
['Name', 'John', 'score', '88', 'day','12'],
['Name', 'Jack', 'day','12']]
I would like to get dataframe as output with all missing data as na. Can you help?
Name | Score | Day
Mike | 15 | 12
Dan | 65 | na
John | 88 | 12
Jack | na | 12
Create dictionary with zip pair and unpairs values of nested lists to list of dictionaries and pass to DataFrame constructor:
L = [['Name', 'Mike', 'score', '15', 'day','12'],
['Name', 'Dan', 'score', '65'],
['Name', 'John', 'score', '88', 'day','12'],
['Name', 'Jack', 'day','12']]
df = pd.DataFrame([dict(zip(x[::2], x[1::2])) for x in L])
print (df)
Name score day
0 Mike 15 12
1 Dan 65 NaN
2 John 88 12
3 Jack NaN 12
Thank you @Jon Clements for improvement:
df = pd.DataFrame(dict(zip(x[::2], x[1::2])) for x in L)
print (df)
Name score day
0 Mike 15 12
1 Dan 65 NaN
2 John 88 12
3 Jack NaN 12
dicts here - just pd.DataFrame(dict(zip(el[::2], el[1::2])) for el in data) will sufficeYou can convert this to a list of dictionaries with:
DataFrame([dict(zip(i, i)) for i in map(iter, data)])
For the given sample data, this yields:
>>> pd.DataFrame([dict(zip(i, i)) for i in map(iter, data)])
Name day score
0 Mike 12 15
1 Dan NaN 65
2 John 12 88
3 Jack 12 NaN
