I have two lists and an array that need to be transformed into a DataFrame. This is more or less what I have:
listA = ['a', 'b', 'c', 'd']
listB = ['x', 'y', 'z']
array = [[10, 11, 12], [20, 21, 22], [30, 31, 32], [40, 41, 42]]
I want my DataFrame to be like this:
I've tried creating the DataFrame by inserting the lists into
pd.Dataframe()
and then specifying what the column's name should be. But no luck so far.
pd.MultiIndex.from_product helps here; it will take the cartesian product of two lists given and give a MultiIndex with 2 levels populated as such:
>>> pd.DataFrame(np.ravel(array),
index=pd.MultiIndex.from_product([listA, listB]),
columns=["value"])
value
a x 10
y 11
z 12
b x 20
y 21
z 22
c x 30
y 31
z 32
d x 40
y 41
z 42
df.loc[("b", "z"), "value"]: this will look up the value of the "value" column where listA is "b" and listB is "z". Therefore it gives 22. Is that what you meant? Currently, the listA and listB values are in the index as a MultiIndex with 2 levels. That allows us to reach values with .loc as shown above with a tuple in the row-selection part.out = pd.DataFrame(array, index=listA, columns=listB).stack()
out:
a x 10
y 11
z 12
b x 20
y 21
z 22
c x 30
y 31
z 32
d x 40
y 41
z 42
dtype: int64
