Unlist multiple columns into rows with pandas

Question

I have a json file like this:

{
  "A": {
    "type1": [
      2,
      26,
      288,
      ...
     ],
     "type2": [
      2,
      3,
      5,
      ...
     ],
     "type3": [
      23,
      26,
      288,
      ...
     ]
  },
  "B": {
    "type1": [
      2,
      26,
      288,
      ...
     ],
     "type2": [
      2,
      3,
      5,
      ...
     ],
     "type3": [
      23,
      26,
      288,
      ...
     ]
  },
...
  "K": {
    "type1": [
      2,
      26,
      288,
      ...
     ],
     "type2": [
      2,
      3,
      5,
      ...
     ],
     "type3": [
      23,
      26,
      288,
      ...
     ]
  }
}

Here is a sample dictionary of it for replication (I have about 20 columns from A to J, but only 3 types: type1,type2,type3):

pd.DataFrame({'A': {'type1': ['32',
   '21',
   '43',
   '43',
   '43',
   '43',
   '43',
   '43'],
  'type2': [
   0.133333333333333,
   0.36666666666666703,
   0.1,
   0.30000000000000004,
   0.16666666666666702,
   0.033333333333333,
   0.2,
   0.066666666666666],
  'type3': [
   3,
   9,
   5,
   1,
   6,
   2]},
 'B': {'type1': [
   '43',
   '43',
   '43',
   '43',
   '43'],
  'type2': [
   0.23333333333333303,
   0.266666666666667,
   0.30000000000000004,
   0.5666666666666671,
   0.16666666666666702,
   0.266666666666667],
  'type3': [
   10,
   6,
   17,
   7,
   8,
   9,
   17,
   5,
   8]},
 'C': {'type1': [
   '43',
   '43',
   '43',
   '43',
   '43',
   '43'],
  'type2': [
   5.23333333333333,
   6.1,
   5.4,
   3.23333333333333,
   17.4,
   5.56666666666667,
   10.4333333333333,
   2.1],
  'type3': [
   183,
   162,
   97,
   522,
   167,
   313,
   63]},
 })

which gives a df like this:

My df should look like this in the end:

  type1  type2  type3
A 32     0.13    3
A 21     0.36    9
A
........
B
........
C
........
J

So I transposed it: But then I'm unsure how to unpack those lists. Every time I try I end up with some loops:

for x in df.index:
    for y in df.loc[x]["type1"]:
        df.iloc[index] = df.append({"index": x, "type": y}, ignore_index=True)
        index += 1

and it is obviously not the way to go.

I was wondering if there is a easier way, maybe as I load the json?

Thanks!

type1, type2 and type3 are not of the same length - do you want to have NaN's for missing values? — MaxU - stand with Ukraine, Feb 14 '18 at 22:36
@MaxU yes, they are not of the same length. So I'll have to have NaNs — Claudiu Creanga, Feb 14 '18 at 22:37

score 6 · Accepted Answer · answered Feb 14 '18 at 22:39

I am using for loop here .apply(pd.Series).stack is the key

df=df.T
l=[df[x].apply(pd.Series).stack() for x in df.columns]

s=pd.concat(l,1).reset_index(level=1,drop=True)
s.columns=df.columns

s
Out[347]: 
  type1      type2  type3
A    32   0.133333    3.0
A    21   0.366667    9.0
A    43   0.100000    5.0
A    43   0.300000    1.0
A    43   0.166667    6.0
A    43   0.033333    2.0
A    43   0.200000    NaN
A    43   0.066667    NaN
B    43   0.233333   10.0
B    43   0.266667    6.0
B    43   0.300000   17.0
B    43   0.566667    7.0
B    43   0.166667    8.0
B   NaN   0.266667    9.0
B   NaN        NaN   17.0
B   NaN        NaN    5.0
B   NaN        NaN    8.0
C    43   5.233333  183.0
C    43   6.100000  162.0
C    43   5.400000   97.0
C    43   3.233333  522.0
C    43  17.400000  167.0
C    43   5.566667  313.0
C   NaN  10.433333   63.0
C   NaN   2.100000    NaN

@ClaudiuCreanga yw~ :-) happy coding, would you like accept it :-) — BENY, Feb 14 '18 at 22:45

ascripter · Answer 2 · 2018-02-14T22:54:13.573

There is no unambiguous solution since the lists are not of equal length. So first you should take care of that, maybe by padding with zeros:

# assuming 'data' is your dict
for x in data:    
    max_length = max([len(data[x][y]) for y in data[x]])    
    for y in data[x]:
        data[x][y] += [np.nan] * (max_length - len(data[x][y]))

Then you can import with a Panel and play around with indices and sorting until you get what you want :)

df = pd.Panel.from_dict(data).to_frame(filter_observations=False).stack().unstack(1).swaplevel(0, 1).sort_index()

minor   type1      type2 type3
  major                       
A 0        32   0.133333     3
  1        21   0.366667     9
  2        43        0.1     5
  3        43        0.3     1
  4        43   0.166667     6
  5        43  0.0333333     2
  6        43        0.2   NaN
  7        43  0.0666667   NaN
B 0        43   0.233333    10
  1        43   0.266667     6
  2        43        0.3    17
  3        43   0.566667     7
  4        43   0.166667     8
  5       NaN   0.266667     9
  6       NaN        NaN    17
  7       NaN        NaN     5
  8       NaN        NaN     8
C 0        43    5.23333   183
  1        43        6.1   162
  2        43        5.4    97
  3        43    3.23333   522
  4        43       17.4   167
  5        43    5.56667   313
  6       NaN    10.4333    63
  7       NaN        2.1   NaN

ZaxR · Answer 3 · 2018-02-14T23:51:13.453

Awesome, creative answers by both Wen and ascripter. ascripter's answer is the fastest though it's worth noting that Panel is deprecated and will be removed in a future version. See the link above for alternatives.

Building on Wen's code, it can be sped up by using values.tolist() instead of apply():

In[1]
df = pd.DataFrame.from_dict(data).transpose()

def my_test(df):
    l = [pd.DataFrame(df[col].values.tolist(), index=df.index).stack() for col in df.columns]
    df2 = pd.concat(l, axis=1).reset_index(level=1, drop=True)
    df2.columns = df.columns
    return df2

def wen_test(df):
    l=[df[x].apply(pd.Series).stack() for x in df.columns]
    s=pd.concat(l,1).reset_index(level=1,drop=True)
    s.columns=df.columns
    return s

%timeit my_test(df)
%timeit wen_test(df) 

Out[1]
100 loops, best of 3: 13.2 ms per loop
100 loops, best of 3: 15.7 ms per loop

Unlist multiple columns into rows with pandas

3 Answers3

Linked