2

I have a data set structured like this:

mydic = {'2017-9-11': {'Type1': [15, 115452.0, 3], 'Type2': [47, 176153.0, 4], 'Type3': [0, 0, 0]}, '2017-9-12': {'Type1': [26, 198223.0, 5], 'Type2': [39, 178610.0, 6], 'Type3': [0, 0, 0]}}
df = pd.DataFrame.from_dict(mydic, orient='index')

I need to split values in the lists into different columns and group them by Types. This is what I do:

df_new = df[list(df)].unstack().apply(pd.Series)
df_new.head()

And it works:

                    0       1           2
Type1   2017-9-11   15.0    115452.0    3.0
        2017-9-12   26.0    198223.0    5.0
Type3   2017-9-11   0.0     0.0         0.0
        2017-9-12   0.0     0.0         0.0
Type2   2017-9-11   47.0    176153.0    4.0

BUT when I apply this code to a larger real-life data set it seems like apply(pd.Series) doesn't work and I get just one column 0 with lists of values like this:

                    0    
Type1  2017-9-11    [15, 115452.0, 3]
       2017-9-12    [26, 198223.0, 5]
Type2  2017-9-11    [47, 176153.0, 4]
       2017-9-12    [39, 178610.0, 6]
Type3  2017-9-11            [0, 0, 0]

Can anyone suggest what might be wrong? Or suggest an alternative solution?

aviss
  • 2,179
  • 7
  • 29
  • 52

2 Answers2

2

It think faster solution is DataFrame constructor, see timings:

s = df.unstack()
df = pd.DataFrame(s.values.tolist(), index=s.index)
print (df)
                  0         1  2
Type1 2017-9-11  15  115452.0  3
      2017-9-12  26  198223.0  5
Type2 2017-9-11  47  176153.0  4
      2017-9-12  39  178610.0  6
Type3 2017-9-11   0       0.0  0
      2017-9-12   0       0.0  0

EDIT:

If values are strings:

df = df.unstack().str.strip('[]').str.split(', ', expand=True).astype(float)
print (df)
                    0         1    2
Type1 2017-9-11  15.0  115452.0  3.0
      2017-9-12  26.0  198223.0  5.0
Type2 2017-9-11  47.0  176153.0  4.0
      2017-9-12  39.0  178610.0  6.0
Type3 2017-9-11   0.0       0.0  0.0
      2017-9-12   0.0       0.0  0.0

Or is possible convert values to lists:

import ast

s = df.unstack().apply(ast.literal_eval)
df = pd.DataFrame(s.values.tolist(), index=s.index).astype(float)
print (df)
                    0         1    2
Type1 2017-9-11  15.0  115452.0  3.0
      2017-9-12  26.0  198223.0  5.0
Type2 2017-9-11  47.0  176153.0  4.0
      2017-9-12  39.0  178610.0  6.0
Type3 2017-9-11   0.0       0.0  0.0
      2017-9-12   0.0       0.0  0.0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Hi, the df constructor gives me the same result - one column "0" with lists of values. When I use s[0] as you suggested it doesn't change anything - still one column. There is probably something wrong with my larger dataset... – aviss Sep 24 '17 at 15:50
  • Are values `lists` ? What return `print (df.applymap(type))` ? – jezrael Sep 24 '17 at 15:51
  • It returns ``... So I have strings there. – aviss Sep 24 '17 at 15:53
  • Converting back to list worked! Thanks! But the option for strings using `df.unstack().str.strip('[]').str.split(', ', expand=True).astype(float)` gives me this error: `AttributeError: 'DataFrame' object has no attribute 'str'` – aviss Sep 24 '17 at 16:13
  • What is `print (df.unstack().columns)` ? It seems unstack return DataFrame, not Series. – jezrael Sep 24 '17 at 16:16
  • 1
    I restarted the notebook and it worked this time. I most probably converted something at some point... So both options work! Thank you again! – aviss Sep 24 '17 at 16:23
1

For Data frame , point out witch columns to apply for .

df.unstack().to_frame()[0].apply(pd.Series)

Out[545]: 
                    0         1    2
Type2 2017-9-11  47.0  176153.0  4.0
      2017-9-12  39.0  178610.0  6.0
Type1 2017-9-11  15.0  115452.0  3.0
      2017-9-12  26.0  198223.0  5.0
Type3 2017-9-11   0.0       0.0  0.0
      2017-9-12   0.0       0.0  0.0

Break done :

df1=df.unstack().to_frame()
df1
Out[546]: 
                                 0
Type2 2017-9-11  [47, 176153.0, 4]
      2017-9-12  [39, 178610.0, 6]
Type1 2017-9-11  [15, 115452.0, 3]
      2017-9-12  [26, 198223.0, 5]
Type3 2017-9-11          [0, 0, 0]
      2017-9-12          [0, 0, 0]

then do apply :

df1[0].apply(pd.Series)

Out[550]: 
                    0         1    2
Type2 2017-9-11  47.0  176153.0  4.0
      2017-9-12  39.0  178610.0  6.0
Type1 2017-9-11  15.0  115452.0  3.0
      2017-9-12  26.0  198223.0  5.0
Type3 2017-9-11   0.0       0.0  0.0
      2017-9-12   0.0       0.0  0.0
BENY
  • 317,841
  • 20
  • 164
  • 234