0

As the title implies I have converted two dictionaries into series like so and I tried to insert them into the data frame df. 

first_series = pd.Series(first_dict, name='State Names')
second_series = pd.Series(second_dict, name='City Names')
column_loc=list(df.columns.values).index("ipAddr")
df.insert(column_loc+1, 'State Names', first_series)
df.insert(column_loc+2, 'City Names', second_series)

When I run this however I get

              ipAddr State Names City Names    ...       
respID                                         ...        
10018         ***.**.**.**  NaN        NaN     ...        
10025         **.**.**.**   NaN        NaN     ...

the series are as follows

10018       Bedford
10025     Vancouver
        ...    
10267        Lompoc
10280    Pikesville
Name: State Names, dtype: object
--------------------------------------------------------
10018          Ohio
10025    Washington
        ...    
10267    California
10280      Maryland
Name: City Names, dtype: object

I've checked that both the dictionaries and the resulting series are populated so I don't understand why this is occurring.

Thank you.

edit: A similar question got asked here but wasn't answered When I insert pandas Series into dataframe, all values become NaN

Community
  • 1
  • 1
MilesConn
  • 301
  • 2
  • 13
  • 2
    Can you give a short example of your DF and both Series which can reproduce the problem? – Allen Qin May 11 '17 at 20:35
  • `10018 Bedford 10025 Vancouver ... 10267 Lompoc 10280 Pikesville Name: State Names, dtype: object` this is the first series and the second series is similar `10018 Ohio 10025 Washington ... 10267 California 10280 Maryland Name: City Names, dtype: object`
    the index for both is the index of the data frame. I can't post the head of the data frame (character limit) but it is what's seen above. (apologies for the messy comment I'm new to stack and I don't know how to properly format yet)     – MilesConn May 11 '17 at 22:04
  • Does your Series have the same length as the df? – Allen Qin May 11 '17 at 22:12
  • Will df.insert(column_loc+1, 'State Names', first_series.values) work? – Allen Qin May 11 '17 at 22:13
  • The series has 808 rows while the data frame has 809. This discrepancy is because the datable has named columns that take up a row. – MilesConn May 11 '17 at 22:14
  • Using first_series.values return `ValueError: Length of values does not match length of index` To clarify, the series are inserted into the data frame but their values are lost. – MilesConn May 11 '17 at 22:16
  • That's the problem, your series has less rows than your df. Column is not counted in length. Do a len(df) and len(first_series), what's the output? – Allen Qin May 11 '17 at 22:18
  • The data frame is 809 while the first_series is 808. I thought the column names might be considered in the rows because it was created by reading from an excel file. I'll try and find why there is a discrepancy in this code. – MilesConn May 11 '17 at 22:22
  • Once u remove the offending row from your df, it should work. – Allen Qin May 11 '17 at 22:23
  • Turns out that was an issue but I just checked now and the length of the first_series and the df are the same yet the values are still NaN. – MilesConn May 11 '17 at 22:33
  • Did u use df.insert(column_loc+1, 'State Names', first_series.values)? – Allen Qin May 11 '17 at 22:36
  • It totally worked now! Thank you so much the first time I tried the series.values it gave me an error because the series was the wrong length but now it worked. Thank you so much. – MilesConn May 11 '17 at 22:40
  • No worries. Please accept the answer so this question will be marked as answered. – Allen Qin May 11 '17 at 22:43

1 Answers1

0

It turned out the Series has less rows than the df. Also, Series.values need to be used while assigning it to a df column.

Allen Qin
  • 19,507
  • 8
  • 51
  • 67