CODEWITHSUNDEEP
pythonlistpandasdataframeflatten
Stefan Falk
Stefan Falk

Reputation: 25397

Convert DataFrame or Series of lists into one stacked DataFrame (or Series)

Having a DataFrame (or Series) consisting of lists, looking like this:

df = pd.DataFrame([[[1,3], [2,3,4], [1,4,2,5]]], columns=['A', 'B', 'C']).T
print(df)

Output:

              0
A        [1, 3]
B     [2, 3, 4]
C  [1, 4, 2, 5]

How can I transform it into

   0
A  1
A  2
B  2
B  3 
B  4
C  1
C  4
C  2 
C  5

I've tried to use apply() but that didn't quite work. Can I implicitly convert that? I also tried to extract all number as tuples [('A', 1), ('A', 3), ..] for from_records() but I wasn't able to do that as well.

I think I could do it like this:

pd.DataFrame.from_records(df[0].map(lambda x: [(0, v) for v in x]).sum())

but I don't know how to access the index.. note (0, v) should actually be something like (x.index, v).

Upvotes: 1

Views: 38

Answers (2)

jezrael
jezrael

Reputation: 862731

Need flattening values in column and then repeat index by len of lists:

df = pd.DataFrame({0:np.concatenate(df.iloc[:, 0].values.tolist())},
                  index=df.index.repeat(df[0].str.len()))

from  itertools import chain
df=pd.DataFrame({0:list(chain.from_iterable(df.iloc[:, 0].values.tolist()))}, 
                 index=df.index.repeat(df[0].str.len()))

print (df)
   0
A  1
A  3
B  2
B  3
B  4
C  1
C  4
C  2
C  5

Timings:

np.random.seed(456)

N = 100000
a = [list(range(np.random.randint(5, 20))) for _ in range(N)]
L = list('abcdefghijklmno') 
df = pd.DataFrame({0:a}, index=np.random.choice(L, size=N))
print (df)

In [348]: %timeit pd.DataFrame({0:np.concatenate(df.iloc[:, 0].values.tolist())}, index=df.index.repeat(df[0].str.len()))
1 loop, best of 3: 218 ms per loop

In [349]: %timeit pd.DataFrame({0:list(chain.from_iterable(df[0].values.tolist()))}, index=df.index.repeat(df[0].str.len()))
1 loop, best of 3: 388 ms per loop

In [350]: %timeit pd.DataFrame(df.iloc[:, 0].tolist(), index=df.index).stack().reset_index(level=1, drop=1).to_frame().astype(int)
1 loop, best of 3: 384 ms per loop

Upvotes: 1

cs95
cs95

Reputation: 402553

Use the pd.DataFrame + stack + reset_index + to_frame:

df = pd.DataFrame(df.iloc[:, 0].tolist(), index=df.index)\
                         .stack().reset_index(level=1, drop=1).to_frame()
df

     0
A  1.0
A  3.0
B  2.0
B  3.0
B  4.0
C  1.0
C  4.0
C  2.0
C  5.0

Upvotes: 1

Related Questions