Create pandas dataframe from list of lists with unequal lengths and NaN values

user_3130747 0
Viewed 592

Given the following:

a = [
    [1],
    [1, 2],
    [1, 2, 3],
    [],
    [1, 2, 3, 4],
    np.nan,
    [1, 2, 3, 4, 5],
]

How to go about creating:

import pandas as pd
import numpy as np
n = np.nan
# to this
n = np.nan
dt = pd.DataFrame(
    {
        "col_1": [1, 1, 1, n, 1, n, 1],
        "col_2": [n, 2, 2, n, 2, n, 2],
        "col_3": [n, n, 3, n, 3, n, 3],
        "col_4": [n, n, n, n, 4, n, 4],
        "col_5": [n, n, n, n, n, n, 5],
    }
)

attempt

The following seems to work:

s = pd.Series([x if str(x) != "nan" else [] for x in a])

s.astype(str).str.split(",", expand=True).applymap(
    lambda x: str(x).replace("[", "").replace("]", "")
).rename(columns=lambda x: f"col_{x+1}")

But it doesn't feel very idiomatic.

pandas reshape python
3 Answers

try this:

result=pd.DataFrame()
for sub in a:
    result = result.append([sub])

This code adds each sublist as a row

output:

|    |   0 |   1 |   2 |   3 |   4 |
|---:|----:|----:|----:|----:|----:|
|  0 |   1 | nan | nan | nan | nan |
|  0 |   1 |   2 | nan | nan | nan |
|  0 |   1 |   2 |   3 | nan | nan |
|  0 | nan | nan | nan | nan | nan |
|  0 |   1 |   2 |   3 |   4 | nan |
|  0 | nan | nan | nan | nan | nan |
|  0 |   1 |   2 |   3 |   4 |   5 |
user_51165590

Just make all of your list elements to be lists:

pd.DataFrame([x if isinstance(x, list) else [x] for x in a])

To rename columns as given in the original example you could use:

pd.DataFrame([x if isinstance(x, list) else [x] for x in a]).rename(columns = lambda x: f"col_{x+1}")

Which gives:

   col_1  col_2  col_3  col_4  col_5
0    1.0    NaN    NaN    NaN    NaN
1    1.0    2.0    NaN    NaN    NaN
2    1.0    2.0    3.0    NaN    NaN
3    NaN    NaN    NaN    NaN    NaN
4    1.0    2.0    3.0    4.0    NaN
5    NaN    NaN    NaN    NaN    NaN
6    1.0    2.0    3.0    4.0    5.0
user_122818920

This can be one of the way.

import pandas as pd
import numpy as np

a = [
    [1],
    [1, 2],
    [1, 2, 3],
    [],
    [1, 2, 3, 4],
    np.nan,
    [1, 2, 3, 4, 5],
]

num=0
list_dict = {}
for list in a:
    list_dict[num] = pd.Series(list)
    num+=1
df = pd.DataFrame(list_dict).T

print(df)

Output:

+----+-----+-----+-----+-----+-----+
|    |   0 |   1 |   2 |   3 |   4 |
|----+-----+-----+-----+-----+-----|
|  0 |   1 | nan | nan | nan | nan |
|  1 |   1 |   2 | nan | nan | nan |
|  2 |   1 |   2 |   3 | nan | nan |
|  3 | nan | nan | nan | nan | nan |
|  4 |   1 |   2 |   3 |   4 | nan |
|  5 | nan | nan | nan | nan | nan |
|  6 |   1 |   2 |   3 |   4 |   5 |
+----+-----+-----+-----+-----+-----+
user_49914690
Related