0

I have the following dataframe:

  symbol           PSAR
0   AAPL  [nan,100,200]
1   PYPL  [nan,300,400]
2    SPY  [nan,500,600]

I am trying to turn the PSAR list values into rows like the following:

symbol   PSAR

AAPL     nan
AAPL     100
AAPL     200
PYPL     nan
PYPL     300
...      ...
SPY      600

I have been trying to solve it by following the answers in this post(one key difference being that that post has a list of list) but cant get there. How to convert column with list of values into rows in Pandas DataFrame.

df['PSAR'].stack().reset_index(level=1, drop=True).to_frame('PSAR')
.join(df[['symbol']], how='left')
gibbz00
  • 1,947
  • 1
  • 19
  • 31
  • 3
    `df[['symbol']].join(pd.DataFrame(df.PSAR.tolist())).melt(id_vars='symbol').drop('variable', 1)` – user3483203 Nov 01 '18 at 16:28
  • 2
    @user3483203 unnesting again :-(, I will open a topic in git , may the developer can help us build that sweet function . – BENY Nov 01 '18 at 16:28
  • @user3483203 not sure what I am doing wrong, but I literally get the original `df` back when I run your code. – gibbz00 Nov 01 '18 at 16:34
  • Do you remember to assign that result to a new variable? – user3483203 Nov 01 '18 at 16:34
  • @user3483203 I am doing `df1 = df[['symbol']].join(pd.DataFrame(df.PSAR.tolist())).melt(id_vars='symbol').drop('variable', 1)` and printing `df1`. – gibbz00 Nov 01 '18 at 16:35
  • 1
    https://repl.it/repls/QuintessentialGivingWatchdog Try it out here, should be working – user3483203 Nov 01 '18 at 16:39
  • @user3483203 Thank you! I will find the problem. Would you mind posting your solution as an answer? – gibbz00 Nov 01 '18 at 16:41
  • @user3483203 Also, I would like to keep the order as show all the values of aapl, then pypl then spy as as opposed to recurring aapl,pypl,spy. – gibbz00 Nov 01 '18 at 16:55

2 Answers2

1

Not a slick one but this does the job:

list_of_lists = []
df_as_dict = dict(df.values)
for key,values in df_as_dict.items():
    list_of_lists+=[[key,value] for value in values]
pd.DataFrame(list_of_lists)

returns:

    0      1
0   AAPL    NaN
1   AAPL    100.0
2   AAPL    200.0
3   PYPL    NaN
4   PYPL    300.0
5   PYPL    400.0
6   SPY     NaN
7   SPY    500.0
8   SPY    600.0
Johannes Wachs
  • 1,270
  • 11
  • 15
1

Pandas >= 0.25:

df1 = pd.DataFrame({'symbol':['AAPL', 'PYPL', 'SPY'],
               'PSAR':[[None,100,200], [None,300,400], [None,500,600]]})
print(df1)

symbol  PSAR
0   AAPL    [None, 100, 200]
1   PYPL    [None, 300, 400]
2   SPY [None, 500, 600]

df1.explode('PSAR')

    symbol  PSAR
0   AAPL    None
0   AAPL    100
0   AAPL    200
1   PYPL    None
1   PYPL    300
1   PYPL    400
2   SPY     None
2   SPY     500
2   SPY     600
Pygirl
  • 12,969
  • 5
  • 30
  • 43