Split Explode a column of dictionaries into separate columns with pandas

Question

I have data saved in a postgreSQL database  I am querying this data using Python2 7 and turning it into a Pandas DataFrame  However  the last column of this dataframe has a dictionary of values inside it  The DataFrame df looks like this  Station ID     Pollutants 8809             quot a quot    quot 46 quot    quot b quot    quot 3 quot    quot c quot    quot 12 quot   8810             quot a quot    quot 36 quot    quot b quot    quot 5 quot    quot c quot    quot 8 quot   8811             quot b quot    quot 2 quot    quot c quot    quot 7 quot   8812             quot c quot    quot 11 quot   8813             quot a quot    quot 82 quot    quot c quot    quot 15 quot    I need to split this column into separate columns  so that the DataFrame  df2 looks like this  Station ID     a      b       c 8809           46     3       12 8810           36     5       8 8811           NaN    2       7 8812           NaN    NaN     11 8813           82     NaN     15  The major issue I m having is that the lists are not the same lengths  But all of the lists only contain up to the same 3 values   a    b   and  c   And they always appear in the same order   a  first   b  second   c  third   The following code USED to work and return exactly what I wanted  df2   objs    df  pandas DataFrame df  Pollutant Levels   tolist    iloc     3   df2   pandas concat objs  axis 1  drop  Pollutant Levels   axis 1  print df2   I was running this code just last week and it was working fine  But now my code is broken and I get this error from line  4   IndexError  out-of-bounds on slice  end    I made no changes to the code but am now getting the error  I feel this is due to my method not being robust or proper  Any suggestions or guidance on how to split this column of lists into separate columns would be super appreciated  EDIT  I think the  tolist   and  apply methods are not working on my code because it is one Unicode string  i e    My data format  u  a    1    b    2    c    3     and not  u a    1   u b    2   u c    3    The data is imported from the postgreSQL database in this format  Any help or ideas with this issue  is there a way to convert the Unicode

User · Answer

I know the question is quite old  but I got here searching for answers  There is actually a better  and faster  way now of doing this using json normalize  import pandas as pd  df2   pd json normalize df  Pollutant Levels     This avoids costly apply functions

User · Answer

Merlin s answer is better and super easy  but we don t need a lambda function  The evaluation of dictionary can be safely ignored by either of the following two ways as illustrated below   Way 1  Two steps    step 1  convert the  Pollutants  column to Pandas dataframe series df pol ps   data df  Pollutants   apply pd Series   df pol ps      a   b   c 0   46  3   12 1   36  5   8 2   NaN 2   7 3   NaN NaN 11 4   82  NaN 15    step 2  concat columns  a  b  c  and drop remove the  Pollutants   df final   pd concat  df  df pol ps   axis   1  drop  Pollutants   axis   1   df final      StationID   a   b   c 0   8809    46  3   12 1   8810    36  5   8 2   8811    NaN 2   7 3   8812    NaN NaN 11 4   8813    82  NaN 15   Way 2  The above two steps can be combined in one go   df final   pd concat  df  df  Pollutants   apply pd Series    axis   1  drop  Pollutants   axis   1   df final      StationID   a   b   c 0   8809    46  3   12 1   8810    36  5   8 2   8811    NaN 2   7 3   8812    NaN NaN 11 4   8813    82  NaN 15

User · Answer

How do I split a column of dictionaries into separate columns with pandas   pd DataFrame df  val   tolist    is the canonical method for exploding a column of dictionaries Here s your proof using a colorful graph   Benchmarking code for reference  Note that I am only timing the explosion since that s the most interesting part of answering this question - other aspects of result construction  such as whether to use pop or drop  are tangential to the discussion and can be ignored  it should be noted however that using pop avoids the followup drop call  so the final solution is a bit more performant  but we are still listifying the column and passing it to pd DataFrame either way   Additionally  pop destructively mutates the input DataFrame  making it harder to run in benchmarking code which assumes the input is not changed across test runs   Critique of other solutions  df  val   apply pd Series  is extremely slow for large N as pandas constructs Series objects for each row  then proceeds to construct a DataFrame from them  For larger N the performance dips to the order of minutes or hours   pd json normalize df  val     is slower simply because json normalize is meant to work with a much more complex input data - particularly deeply nested JSON with multiple record paths and metadata  We have a simple flat dict for which pd DataFrame suffices  so use that if your dicts are flat   Some answers suggest df pop  val   values tolist   or df pop  val   to numpy   tolist    I don t think it makes much of a difference whether you listify the series or the numpy array  It s one operation less to listify the series directly and really isn t slower so I d recommend avoiding generating the numpy array in the intermediate step

User · Answer

I strongly recommend the method extract the column  Pollutants    df pollutants   pd DataFrame df  Pollutants   values tolist    index df index   it s much faster than   df pollutants   df  Pollutants   apply pd Series   when the size of df is giant

User · Answer

I ve concatenated those steps in a method  you have to pass only the dataframe and the column which contains the dict to expand   def expand dataframe dw  pd DataFrame  column to expand  str  - gt  pd DataFrame              dw  DataFrame with some column which contain a dict to expand         in columns     column to expand  String with column name of dw             import pandas as pd      def convert to dict sequence  str  - gt  Dict          import json         s   sequence         json acceptable string   s replace                    d   json loads json acceptable string          return d          expanded dataframe   pd concat  dw drop  column to expand   axis 1                                       dw column to expand                                       apply convert to dict                                       apply pd Series                                        axis 1      return expanded dataframe

User · Answer

You can use join with pop   tolist  Performance is comparable to concat with drop   tolist  but some may find this syntax cleaner   res   df join pd DataFrame df pop  b   tolist       Benchmarking with other methods   df   pd DataFrame   a   1 2 3    b     c  1     d  3     c  5   d  6      def joris1 df       return pd concat  df drop  b   axis 1   df  b   apply pd Series    axis 1   def joris2 df       return pd concat  df drop  b   axis 1   pd DataFrame df  b   tolist      axis 1   def jpp df       return df join pd DataFrame df pop  b   tolist      df   pd concat  df  1000  ignore index True    timeit joris1 df copy       1 33 s per loop  timeit joris2 df copy       7 42 ms per loop  timeit jpp df copy          7 68 ms per loop

User · Answer

Try this   The data returned from SQL has to converted into a Dict   or could it be   Pollutant Levels   is now Pollutants      StationID                   Pollutants 0       8809    a   46   b   3   c   12   1       8810     a   36   b   5   c   8   2       8811              b   2   c   7   3       8812                     c   11   4       8813            a   82   c   15     df2  Pollutants     df2  Pollutants   apply lambda x   dict eval x     df3   df2  Pollutants   apply pd Series        a    b   c 0   46    3  12 1   36    5   8 2  NaN    2   7 3  NaN  NaN  11 4   82  NaN  15   result   pd concat  df  df3   axis 1  drop  Pollutants   axis 1  result     StationID    a    b   c 0       8809   46    3  12 1       8810   36    5   8 2       8811  NaN    2   7 3       8812  NaN  NaN  11 4       8813   82  NaN  15

User · Answer

The fastest method to normalize a column of flat  one-level dicts  as per the timing analysis performed by Shijith in this answer   df join pd DataFrame df pop  Pollutants   values tolist     It will not resolve other issues with columns of list or dicts that are addressed below  such as rows with NaN  or nested dicts      pd json normalize df Pollutants  is significantly faster than df Pollutants apply pd Series   See the   timeit below  For 1M rows   json normalize is 47 times faster than  apply    Whether reading data from a file  or from an object returned by a database  or API  it may not be clear if the dict column has dict or str type   If the dictionaries in the column are str type  they must be converted back to a dict type  using ast literal eval    Use pd json normalize to convert the dicts  with keys as headers and values for rows   Has additional parameters  e g  record path  amp  meta  for dealing with nested dicts    Use pandas DataFrame join to combine the original DataFrame  df  with the columns created using pd json normalize  If the index isn t integers  as in the example   first use df reset index   to get an index of integers  before doing the normalize and join    Finally  use pandas DataFrame drop  to remove the unneeded column of dicts   As a note  if the column has any NaN  they must be filled with an empty dict  df Pollutants   df Pollutants fillna  i     for i in df index    If the  Pollutants  column is strings  use       Also see How to json normalize a column with NaNs        import pandas as pd from ast import literal eval import numpy as np  data     Station ID    8809  8810  8811  8812  8813  8814            Pollutants       quot a quot    quot 46 quot    quot b quot    quot 3 quot    quot c quot    quot 12 quot        quot a quot    quot 36 quot    quot b quot    quot 5 quot    quot c quot    quot 8 quot        quot b quot    quot 2 quot    quot c quot    quot 7 quot        quot c quot    quot 11 quot        quot a quot    quot 82 quot    quot c quot    quot 15 quot     np nan    df   pd DataFrame data     display df     Station ID                        Pollutants 0        8809    quot a quot    quot 46 quot    quot b quot    quot 3 quot    quot c quot    quot 12 quot   1        8810     quot a quot    quot 36 quot    quot b quot    quot 5 quot    quot c quot    quot 8 quot   2        8811                quot b quot    quot 2 quot    quot c quot    quot 7 quot   3        8812                         quot c quot    quot 11 quot   4        8813              quot a quot    quot 82 quot    quot c quot    quot 15 quot   5        8814                               NaN    replace NaN with      if the column is strings  otherwise replace with      df Pollutants   df Pollutants fillna          if the NaN is in a column of strings df Pollutants   df Pollutants fillna  i     for i in df index      if the column is not strings    Convert the column of stringified dicts to dicts   skip this line  if the column contains dicts df Pollutants   df Pollutants apply literal eval     reset the index if the index is not unique integers from 0 to n-1   df reset index inplace True     uncomment if needed    normalize the column of dictionaries and join it to df df   df join pd json normalize df Pollutants      drop Pollutants df drop columns   Pollutants    inplace True     display df     Station ID    a    b    c 0        8809   46    3   12 1        8810   36    5    8 2        8811  NaN    2    7 3        8812  NaN  NaN   11 4        8813   82  NaN   15 5        8814  NaN  NaN  NaN    timeit   dataframe with 1M rows dfb   pd concat  df  200000  reset index drop True     timeit dfb join pd json normalize dfb Pollutants    out   5 44 s    32 3 ms per loop  mean    std  dev  of 7 runs  1 loop each     timeit pd concat  dfb drop columns   Pollutants     dfb Pollutants apply pd Series    axis 1   out   4min 17s    2 44 s per loop  mean    std  dev  of 7 runs  1 loop each

User · Answer

One line solution is following    gt  gt  gt  df   pd concat  df  Station ID    df  Pollutants   apply pd Series    axis 1   gt  gt  gt  print df     Station ID    a    b   c 0        8809   46    3  12 1        8810   36    5   8 2        8811  NaN    2   7 3        8812  NaN  NaN  11 4        8813   82  NaN  15

User · Answer

my df   pd DataFrame from dict my dict  orient  index   columns   my col       would have parsed the dict properly  putting each dict key into a separate df column  and key values into df rows   so the dicts would not get squashed into a single column in the first place

User · Answer

gt  gt  gt  df     Station ID                        Pollutants 0        8809    quot a quot    quot 46 quot    quot b quot    quot 3 quot    quot c quot    quot 12 quot   1        8810     quot a quot    quot 36 quot    quot b quot    quot 5 quot    quot c quot    quot 8 quot   2        8811                quot b quot    quot 2 quot    quot c quot    quot 7 quot   3        8812                         quot c quot    quot 11 quot   4        8813              quot a quot    quot 82 quot    quot c quot    quot 15 quot    speed comparison for a large dataset of 10 million rows  gt  gt  gt  df   pd concat  df  100000  reset index drop True   gt  gt  gt  df   pd concat  df  20  reset index drop True   gt  gt  gt  print df shape   10000000  2   def apply drop df       return df join df  Pollutants   apply pd Series   drop  Pollutants   axis 1     def json normalise drop df       return df join pd json normalize df Pollutants   drop  Pollutants   axis 1     def tolist drop df       return df join pd DataFrame df  Pollutants   tolist     drop  Pollutants   axis 1     def vlues tolist drop df       return df join pd DataFrame df  Pollutants   values tolist     drop  Pollutants   axis 1     def pop tolist df       return df join pd DataFrame df pop  Pollutants   tolist        def pop values tolist df       return df join pd DataFrame df pop  Pollutants   values tolist        gt  gt  gt   timeit apply drop df copy    1 loop  best of 3  53min 20s per loop  gt  gt  gt   timeit json normalise drop df copy    1 loop  best of 3  54 9 s per loop  gt  gt  gt   timeit tolist drop df copy    1 loop  best of 3  6 62 s per loop  gt  gt  gt   timeit vlues tolist drop df copy    1 loop  best of 3  6 63 s per loop  gt  gt  gt   timeit pop tolist df copy    1 loop  best of 3  5 99 s per loop  gt  gt  gt   timeit pop values tolist df copy    1 loop  best of 3  5 94 s per loop   --------------------- -----------    apply drop            53min 20s     json normalise drop      54 9 s     tolist drop              6 62 s     vlues tolist drop        6 63 s     pop tolist               5 99 s     pop values tolist        5 94 s    --------------------- -----------    df join pd DataFrame df pop  Pollutants   values tolist     is the fastest

User · Answer

To convert the string to an actual dict  you can do df  Pollutant Levels   map eval   Afterwards  the solution below can be used to convert the dict to different columns     Using a small example  you can use  apply pd Series    In  2   df   pd DataFrame   a   1 2 3    b     c  1     d  3     c  5   d  6      In  3   df Out 3      a                   b 0  1            u c   1  1  2            u d   3  2  3   u c   5  u d   6   In  4   df  b   apply pd Series  Out 4        c    d 0  1 0  NaN 1  NaN  3 0 2  5 0  6 0   To combine it with the rest of the dataframe  you can concat the other columns with the above result   In  7   pd concat  df drop   b    axis 1   df  b   apply pd Series    axis 1  Out 7      a    c    d 0  1  1 0  NaN 1  2  NaN  3 0 2  3  5 0  6 0     Using your code  this also works if I leave out the iloc part   In  15   pd concat  df drop  b   axis 1   pd DataFrame df  b   tolist      axis 1  Out 15      a    c    d 0  1  1 0  NaN 1  2  NaN  3 0 2  3  5 0  6 0

User · Answer

df   pd concat  df  a    df b apply pd Series    axis 1

[python] Split / Explode a column of dictionaries into separate columns with pandas

Examples related to python

Examples related to json

Examples related to pandas

Examples related to dataframe

Examples related to dictionary