How to fill dataframe content NA value in empty cell?

Question

I have a dataframe df:

                  Open    Volume  Adj Close     Ticker  
Date                                                                   
2006-11-22  140.750000   45505300   114.480649   SPY

I want to change df to another dataframe Open price like below:

                  SPY    AGG  
Date                                                                   
2006-11-22  140.750000   NA

It only use open's data and two tickers, so how to change one dataframe to another?

Possible duplicate of [How to replace NaNs by preceding values in pandas DataFrame?](http://stackoverflow.com/questions/27905295/how-to-replace-nans-by-preceding-values-in-pandas-dataframe) — amin, Nov 21 '16 at 07:12

jezrael · Answer 1 · 2016-11-21T08:18:50.410

1

I think you can use DataFrame constructor with reindex by list of ticker L:

L = ['SPY','AGG']
df1 = pd.DataFrame({'SPY': [df.Open.iloc[0]]}, 
                   index=[df.index[0]])

df1 = df1.reindex(columns=L)
print (df1)
               SPY  AGG
2006-11-22  140.75  NaN

You can use read_html for find list of Tickers:

df2 = pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies', header=0)[0]
#print (df2)

#filter only Ticker symbols starts with SP
df2 = df2[df2['Ticker symbol'].str.startswith('SP')]
print (df2)
    Ticker symbol                  Security SEC filings  \
407           SPG  Simon Property Group Inc     reports   
415          SPGI          S&P Global, Inc.     reports   
418          SPLS              Staples Inc.     reports   

                GICS Sector               GICS Sub Industry  \
407             Real Estate                           REITs   
415              Financials  Diversified Financial Services   
418  Consumer Discretionary                Specialty Stores   

       Address of Headquarters Date first added      CIK  
407      Indianapolis, Indiana              NaN  1063761  
415         New York, New York              NaN    64040  
418  Framingham, Massachusetts              NaN   791519  

#convert column to list, add SPY because missing
L = ['SPY'] + df2['Ticker symbol'].tolist() 
print (L)
['SPY', 'SPG', 'SPGI', 'SPLS']

df1 = pd.DataFrame({'SPY': [df.Open.iloc[0]]}, 
                   index=[df.index[0]])

df1 = df1.reindex(columns=L)
print (df1)
               SPY  SPG  SPGI  SPLS
2006-11-22  140.75  NaN   NaN   NaN

edited Nov 21 '16 at 08:18

answered Nov 21 '16 at 07:15

jezrael

822,522
95
1,334
1,252

Or I should add one row AGG ticker in df( all NaN)?Then use "df = pd.pivot(index=df.index, columns=df.Ticker, values=df.Open)" – yensheng Nov 21 '16 at 07:21
Is possible add all ticker to `list` ? – jezrael Nov 21 '16 at 07:24
Could you explain how to add all tickers to list? – yensheng Nov 21 '16 at 07:28
Hard question, is possible find some table in web? Can you help me? – jezrael Nov 21 '16 at 07:33
I find all s&p500 tickers:https://en.wikipedia.org/wiki/List_of_S%26P_500_companies – yensheng Nov 21 '16 at 08:07
You can use 4 or 5 tickers to test it. Thank you. – yensheng Nov 21 '16 at 08:07
Please check answer, `SPY` was missing, so I add it manually to another `Tickers` – jezrael Nov 21 '16 at 08:19

score 0 · Answer 2 · answered Apr 07 '18 at 21:00

Suppose you have a list of data frame df_list for different tickers and every item of of list have the same look of the df in your example

You can first concatenate them into one frame with

df1 = pd.concat(df_list)

Then with

df1[["Open", "Ticker"]].reset_index().set_index(["Date", "Ticker"]).unstack()

It should give you an output like

            Open
Ticker      AGG     SPY
Date        
2006-11-22  NAN  140.75

How to fill dataframe content NA value in empty cell?

2 Answers2