Extract values form two 2D list and store them into a panda dataframe

Question

I have two 2D list:

1. [['VDM:1', 'VDM:2', 'VDM:3', 'VDM:4', 'VDM:5'], ['MDM:1', 'MDM:2', 'MDM:3', 'MDM:4', 'MDM:5'], ['OM:1', 'OM:2', 'OM:3', 'OM:4', 'OM:5']]
2. [[9, 2, 0, 0, 1], [2, 6, 0, 3, 1], [2, 6, 0, 3, 1]]

I want to store these value in a dataset in the format:

Attribute:Value     Support
VDM:1               9
VDM:2               2
VDM:3               0
VDM:4               0
VDM:5               1
MDM:1               2
MDM:2               6
MDM:3               0
MDM:4               3
MDM:5               1
OM:1                2
OM:2                6
OM:3                0
OM:4                3
OM:5                1

score 1 · Accepted Answer · answered Jun 10 '19 at 09:37

Using itertools.chain

Ex:

import pandas as pd
from itertools import chain

Attribute = [['VDM:1', 'VDM:2', 'VDM:3', 'VDM:4', 'VDM:5'], ['MDM:1', 'MDM:2', 'MDM:3', 'MDM:4', 'MDM:5'], ['OM:1', 'OM:2', 'OM:3', 'OM:4', 'OM:5']]
Support = [[9, 2, 0, 0, 1], [2, 6, 0, 3, 1], [2, 6, 0, 3, 1]]

df= pd.DataFrame({"Attribute:Value": list(chain.from_iterable(Attribute)), "Support": list(chain.from_iterable(Support))})
print(df)

Output:

   Attribute:Value  Support
0            VDM:1        9
1            VDM:2        2
2            VDM:3        0
3            VDM:4        0
4            VDM:5        1
5            MDM:1        2
6            MDM:2        6
7            MDM:3        0
8            MDM:4        3
9            MDM:5        1
10            OM:1        2
11            OM:2        6
12            OM:3        0
13            OM:4        3
14            OM:5        1

score 1 · Answer 2 · answered Jun 10 '19 at 09:43

Using np.concatenate flatten the list.

a = [['VDM:1', 'VDM:2', 'VDM:3', 'VDM:4', 'VDM:5'], ['MDM:1', 'MDM:2', 'MDM:3', 'MDM:4', 'MDM:5'], ['OM:1', 'OM:2', 'OM:3', 'OM:4', 'OM:5']]
s = [[9, 2, 0, 0, 1], [2, 6, 0, 3, 1], [2, 6, 0, 3, 1]]

a = np.concatenate(a)
s = np.concatenate(s)

df = pd.DataFrame({'Attribute:value': a, 'Support': s})

Output:

    Attribute:value Support
0   VDM:1           9
1   VDM:2           2
2   VDM:3           0
3   VDM:4           0
4   VDM:5           1
5   MDM:1           2
6   MDM:2           6
7   MDM:3           0
8   MDM:4           3
9   MDM:5           1
10  OM:1            2
11  OM:2            6
12  OM:3            0
13  OM:4            3
14  OM:5            1

Great answer, like this one, didn't think of `numpy` myself here. — Erfan, Jun 10 '19 at 11:02

score 1 · Answer 3 · answered Jun 10 '19 at 09:47

One simple way is to flatten your list. You can do it with list comprehension (no extra modules needed). Here is one discussion on how to flatten a list.

Here the code:

# Import module
import pandas as pd

# Your data
attributs = [['VDM:1', 'VDM:2', 'VDM:3', 'VDM:4', 'VDM:5'], [
    'MDM:1', 'MDM:2', 'MDM:3', 'MDM:4', 'MDM:5'], ['OM:1', 'OM:2', 'OM:3', 'OM:4', 'OM:5']]
support = [[9, 2, 0, 0, 1], [2, 6, 0, 3, 1], [2, 6, 0, 3, 1]]

# Flatten the list
attributs_flatten = [item for sublist in attributs for item in sublist]
support_flatten = [item for sublist in support for item in sublist]

# create dataframe
df = pd.DataFrame({'Attributes:Value': attributs_flatten, "Support": support_flatten})

print(df)
#    Attributes:Value  Support
# 0             VDM: 1        9
# 1             VDM: 2        2
# 2             VDM: 3        0
# 3             VDM: 4        0
# 4             VDM: 5        1
# 5             MDM: 1        2
# 6             MDM: 2        6
# 7             MDM: 3        0
# 8             MDM: 4        3
# 9             MDM: 5        1
# 10             OM: 1        2
# 11             OM: 2        6
# 12             OM: 3        0
# 13             OM: 4        3
# 14             OM: 5        1

score 1 · Answer 4 · answered Jun 10 '19 at 10:08

simplest approach would be,

pd.DataFrame(list(zip(sum(l1, []),sum(l2,[]))))

O/P:

        0  1
0   VDM:1  9
1   VDM:2  2
2   VDM:3  0
3   VDM:4  0
4   VDM:5  1
5   MDM:1  2
6   MDM:2  6
7   MDM:3  0
8   MDM:4  3
9   MDM:5  1
10   OM:1  2
11   OM:2  6
12   OM:3  0
13   OM:4  3
14   OM:5  1

Explanation, flatten both dataframes and perform zipping finally convert into dataframe

Extract values form two 2D list and store them into a panda dataframe

4 Answers4