How to convert a list of lists into a dataframe where first element is index, second is column name

Viewed 1397

We have a list of lists:

[['130', '2020-12-17 12:02:19', [52.1846976, 21.0525275]], ['213', '2020-12-17 12:02:22', [52.1757618, 21.2319711]]

and want to convert it into a dataframe as such:

    index  2020-12-17 12:02:19          2020-12-17 12:02:22
    130    [52.1846976, 21.0525275]       NaN
    213    NaN                      [52.1757618, 21.2319711]

Can't figure it out.

5 Answers

You can munge your list into a list of dict's and then provide the index explicitly to the construtor:

In [1]: import pandas as pd

In [2]: data = [['130', '2020-12-17 12:02:19', [52.1846976, 21.0525275]], ['213', '2020-12-17 12:02:22', [52.1757618, 21.2319711]]]

In [3]: pd.DataFrame([{col: val} for _, col, val in data], index=[item[0] for item in data])
Out[3]:
          2020-12-17 12:02:19       2020-12-17 12:02:22
130  [52.1846976, 21.0525275]                       NaN
213                       NaN  [52.1757618, 21.2319711]

This is not the sexiest solution, but saves any pre-processing outside of pandas.

A better solution would be to structure your input data at source before moving it into pandas.

d = [['130', '2020-12-17 12:02:19', [52.1846976, 21.0525275]], ['213', '2020-12-17 12:02:22', [52.1757618, 21.2319711]]]

df = pd.DataFrame(d).set_index([0,1]).unstack(1).droplevel(0,1).rename_axis(None)

print(df)

1         2020-12-17 12:02:19       2020-12-17 12:02:22
130  [52.1846976, 21.0525275]                       NaN
213                       NaN  [52.1757618, 21.2319711]

--

handling duplicate keys.

df = pd.DataFrame(d).set_index([0,1])

df = df.set_index(df.groupby(level=[0,1]).cumcount(),append=True).unstack(1)

1           2020-12-17 12:02:19       2020-12-17 12:02:22
130 0  [52.1846976, 21.0525275]                       NaN
    1  [52.1846976, 21.0525275]                       NaN
213 0                       NaN  [52.1757618, 21.2319711]

You need to convert the list into this format:

{'130': {'2020-12-17 12:02:19': [52.1846976, 21.0525275]},
 '213': {'2020-12-17 12:02:22': [52.1757618, 21.2319711]}}

then apply pd.DataFrame to it.

Try:

u = {i[0]:{i[1]: i[2]} for i in l}   
df = pd.DataFrame(u).T

            2020-12-17 12:02:19         2020-12-17 12:02:22
130         [52.1846976, 21.0525275]    NaN
213         NaN                         [52.1757618, 21.2319711]
import pandas as pd 
data = [['130', '2020-12-17 12:02:19', [52.1846976, 21.0525275]], ['213', '2020-12-17 12:02:22', [52.1757618, 21.2319711]]] 
df = pd.DataFrame.from_records(data, columns=['a', 'b', 'c'])
df = df.set_index('a')
df


              b             c
a       
130 2020-12-17 12:02:19 [52.1846976, 21.0525275]
213 2020-12-17 12:02:22 [52.1757618, 21.2319711]

You can first convert the list to DataFrame. And use set_index() to set the first column as index.

Related