python convert multiple lists to row for certain lists

Question

I have the following dataframe :

network   date       count2    count3  user2     user3 
3         20170721   [6, 7]    [1,3]   [57,88]   [47,58] 
4         20170721   [6]       []      [12]      []
43        20170721   []        [7,2]   []        [57,62]

and I would like to split the list per row but count and user must correspond :

network   date       count2   count3  user2    user3 
3         20170727   6       Nan      57       Nan
3         20170727   7       Nan      88       Nan
3         20170727   Nan     1        Nan      47
3         20170727   Nan     3        Nan      58
4         20170727   6       Nan      12       Nan
43        20170727   Nan     7        Nan      57
43        20170727   Nan     2        Nan      62

How can I do it in a fast way ? The user list is in reality really long (more than 50k entry). Thank you!

What have you tried so far? Could you show me what the results are when you convert the dataframe into an array using .asarray? — Julian Rachman
– Julian Rachman, Commented Jul 27, 2017 at 16:04
What do you mean by "count and user must correspond"? You don't list any examples of corresponding count and user values except Nan. What is your expected outcome? What exactly does "split the list per row" mean? What happens to each row of data??? — A Magoon
– A Magoon, Commented Jul 27, 2017 at 16:06
I have tried this solution : stackoverflow.com/questions/33793622/… for user 2 then user3 and concat the dataframe but it is not optimal — eleanor
– eleanor, Commented Jul 27, 2017 at 16:07
@AMagoon. I think the example shows what is to be done pretty clearly. — Mad Physicist
– Mad Physicist, Commented Jul 27, 2017 at 16:07

Scott Boston · Accepted Answer · 2017-07-28 20:34:36Z

One way you could do this and achieve the result you are looking for without all the extra NaN.

df = pd.DataFrame({'network':[3,4,43],'date':['20170721']*3,
                   'count2':[[6,7],[6],[]],
                   'count3':[[1,3],[],[7,2]],
                   'user2':[[57,88],[12],[]],
                   'user3':[[47,58],[],[57,62]]})

df = df.set_index(['network','date'])

(df.apply(lambda x: pd.DataFrame(x.tolist(),index=x.index)
                      .stack()
                      .rename(x.name))
   .reset_index())

Output:

   network      date  level_2  level_0  count2  count3  user2  user3
0        3  20170721        0      0.0     6.0     1.0   57.0   47.0
1        3  20170721        1      NaN     7.0     3.0   88.0   58.0
2        4  20170721        0      1.0     6.0     NaN   12.0    NaN
3       43  20170721        0      2.0     NaN     7.0    NaN   57.0
4       43  20170721        1      NaN     NaN     2.0    NaN   62.0

Collectives™ on Stack Overflow

python convert multiple lists to row for certain lists

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related