Advanced Python pandas reshape

Question

I think this is similar to this post but not exactly the same and I cannot get my head around it.

So, I currently have a (quite weird) pandas dataframe with lists in each cell like this:

>>> data = pd.DataFrame({'myid' : ['1', '2', '3'],
                         'num' : [['1', '2', '3'], ['1', '2'], []],
                         'text' : [['aa', 'bb', 'cc'], ['cc', 'dd'],
                         []]}).set_index('myid')

>>> print(data)
                num          text
    myid                         
    1     [1, 2, 3]  [aa, bb, cc]
    2        [1, 2]      [cc, dd]
    3            []            []

I would like to achieve this:

  myid num text
0    1   1   aa
0    1   2   bb
0    1   3   cc
1    2   1   cc
1    2   2   dd
2    3

How do I get there?

Are the data types in the `num` and `text` columns actually lists? Reason I ask is because I ran into something similar when outputting a neo4j dataset to pandas, and the values were not lists, but strings instead. — Scratch'N'Purr, Apr 24 '17 at 17:49
You should do your best to provide easily reproducible examples. — juanpa.arrivillaga, Apr 24 '17 at 17:49
@Scratch'N'Purr good point, I checked it, these really are lists in my case. — absurd, Apr 25 '17 at 09:43

score 6 · Accepted Answer · answered Apr 24 '17 at 18:04

6

I'd use str.len to determine lengths of imbedded lists/arrays. Then use repeat and concatenate

lens = df.num.str.len()

pd.DataFrame(dict(
        myid=df.myid.repeat(lens),
        num=np.concatenate(df.num),
        text=np.concatenate(df.text)
    )).append(
    pd.DataFrame(
        df.loc[~df.num.astype(bool), 'myid']
    )
).fillna('')

  myid num text
0    1   1   aa
0    1   2   bb
0    1   3   cc
1    2   1   cc
1    2   2   dd
2    3

answered Apr 24 '17 at 18:04

piRSquared

285,575
57
475
624

2

it's a brilliant solution ! – MaxU - stand with Ukraine Apr 24 '17 at 19:33
I tried to create a [bit more generic solution based on your answer](http://stackoverflow.com/a/43596829/5741205) – MaxU - stand with Ukraine Apr 24 '17 at 20:07

score 2 · Answer 2 · edited May 23 '17 at 12:02

I tried here to create bit more generic solution based on brilliant @piRSquared's solution:

DataFrame:

df = pd.DataFrame({
 'aaa': {0: 10, 1: 11, 2: 12, 3: 13},
 'myid': {0: 1, 1: 2, 2: 3, 3: 4},
 'num': {0: [1, 2, 3], 1: [1, 2], 2: [], 3: []},
 'text': {0: ['aa', 'bb', 'cc'], 1: ['cc', 'dd'], 2: [], 3: []}
})

Solution:

lst_cols = ['num','text']
idx_cols = df.columns.difference(lst_cols)

lens = df[lst_cols[0]].str.len()

pd.DataFrame({
    col:np.repeat(df[col].values, df[lst_cols[0]].str.len())
    for col in idx_cols
}).assign(**{col:np.concatenate(df[col].values) for col in lst_cols}) \
  .append(df.loc[lens==0, idx_cols]).fillna('') \
  .loc[:, df.columns]

Source DF:

In [25]: df
Out[25]:
   aaa  myid        num          text
0   10     1  [1, 2, 3]  [aa, bb, cc]
1   11     2     [1, 2]      [cc, dd]
2   12     3         []            []
3   13     4         []            []

Result:

In [26]: pd.DataFrame({
    ...:     col:np.repeat(df[col].values, df[lst_cols[0]].str.len())
    ...:     for col in idx_cols
    ...: }).assign(**{col:np.concatenate(df[col].values) for col in lst_cols}) \
    ...:   .append(df.loc[lens==0, idx_cols]).fillna('') \
    ...:   .loc[:, df.columns]
    ...:
Out[26]:
   aaa  myid num text
0   10     1   1   aa
1   10     1   2   bb
2   10     1   3   cc
3   11     2   1   cc
4   11     2   2   dd
2   12     3
3   13     4

Thank you MaxU. That certainly will be useful. – absurd Apr 25 '17 at 09:44 — absurd, Apr 25 '17 at 09:44

Advanced Python pandas reshape

2 Answers2

Related