flatten entries in python dataframe like Apache PIG bag

Question

I have dataframe like:

dem = {
   '{(dt-au=120000),(dt-au=120100)}': ['Y'],
   '{(dt-au=120000),(dt-au=120400)}': ['N'],
   '{(dt-au=120600),(dt-bi=130450)}': ['Y']
    };
df = pd.DataFrame(dem)
pd.melt(df)

                          variable value
0  {(dt-au=120000),(dt-au=120100)}     Y
1  {(dt-au=120000),(dt-au=120400)}     N
2  {(dt-au=120600),(dt-bi=130450)}     Y

I want generate another dataframe that works in the same way as PIG's FLATTEN bag function. This data frame would look like:

       variable value
0  dt-au=120000     Y
1  dt-au=120100     Y
2  dt-au=120400     N
3  dt-au=120000     N
4  dt-au=120600     Y
5  dt-bi=130450     Y

NOTE: dt-au=120000 appears twice with different VALUES (Y and N).

I was wondering how do that in pandas.

Answer 1

You can use string functions of pandas:

import pandas as pd
import numpy as np
import io

dem = {
   '{(dt-au=120000),(dt-au=120100)}': ['Y'],
   '{(dt-au=120000),(dt-au=120400)}': ['N'],
   '{(dt-au=120600),(dt-bi=130450)}': ['Y']
    };
df = pd.DataFrame(dem)
df = pd.melt(df)

#cast to str, remove {}
df.variable  = df.variable.astype(str).str.strip('{}')

s = df['variable'].str.split(',').apply(pd.Series, 1).stack()
s.index = s.index.droplevel(-1)
s.name = 'variable'
#remove ()
s  = s.str.strip('()')
print s
0    dt-au=120000
0    dt-au=120100
1    dt-au=120000
1    dt-au=120400
2    dt-au=120600
2    dt-bi=130450

df = df.drop( ['variable'], axis=1)
df = df.join(s).reset_index(drop=True)

print df

  value      variable
0     Y  dt-au=120000
1     Y  dt-au=120100
2     N  dt-au=120000
3     N  dt-au=120400
4     Y  dt-au=120600
5     Y  dt-bi=130450

Answer 2

Not sure if there is a way because you will need to split the key-string in dem

So, assuming there is no way to do it with Pandas, here is a pre-Pandas brute-force approach.

import numpy as np
import pandas as pd

dem = {
   '{(dt-au=120000),(dt-au=120100)}': ['Y'],
   '{(dt-au=120000),(dt-au=120400)}': ['N'],
   '{(dt-au=120600),(dt-bi=130450)}': ['Y']
    };

col1 = []
col2 = []
for k,v in dem.items():
    keys = k.strip('{}').split(',')
    col1.extend(key.strip('()') for key in keys)
    col2.extend(v[0] for key in keys)

# not familiar enough with numpy, so you may be able to build this above
cols = np.array([col1, col2])
df = pd.DataFrame(cols) # may need to transpose this

Output (of transpose)

              0  1
0  dt-au=120000  Y
1  dt-au=120100  Y
2  dt-au=120000  N
3  dt-au=120400  N
4  dt-au=120600  Y
5  dt-bi=130450  Y