Explode dataframe with string values without explode function in pandas

Question

I have a DataFrame like this (Pandas version 0.23.4)

Emp   Factors         Comments        Action     ActionText
1   "['1','1']"  "["not","some"]"    "['1']"    "['good','as']"
2   "['1']"      "['textB']"          "[]"       "['da']"

I cannot use

df.set_index('Emp').apply(lambda x: x.apply(pd.Series).stack()).reset_index().drop('level_1', 1)

as it is not unique always. PS: type of Factors, Comments, Action, ActionText are String in df. For any values of multiple entries ([,]) of the column, I want a new row in output DF. I want the output df be like

Emp Factors Comments Action   ActionText
1    1        not     1         good
1    1        some    1         as
2    1        textB   ""or nan  da

@AlexandreB. I'm assuming a typo, since everything else seems to line up — C.Nivs, Mar 23 '20 at 15:11
is your quoted text consitent? for example in comments your have "not" in row 1 and 'textB' in row2 — Umar.H, Mar 23 '20 at 15:12
hi @Datanovice I have editied the question, made a typo earlier, please check now. — R.singh, Mar 23 '20 at 15:14
@a_guest maybe they don't want to avoid using it - they just can't upgrade to 0.25+ from 0.23.4... — Jon Clements, Mar 23 '20 at 15:17
this [question](https://stackoverflow.com/questions/53218931/how-to-unnest-explode-a-column-in-a-pandas-dataframe) has a few different ways to `unnest` or `explode` a dataframe — Umar.H, Mar 23 '20 at 15:43

piRSquared · Answer 1 · 2020-03-23T16:01:54.810

2

`zip_longest`

from itertools import zip_longest

data = [
    (emp, *tup)
    for emp, *other in df.itertuples(index=False)
    for tup in zip_longest(*other)
]

pd.DataFrame(data, columns=df.columns)

   Emp Factors Comments Action ActionText
0    1       1      not      1       good
1    1       1     some   None         as
2    2       1    textB   None         da

Setup

What I assumed df to be:

df = pd.DataFrame([
    [1, ['1', '1'], ['not', 'some'], ['1'], ['good', 'as']],
    [2, ['1'], ['textB'], [], ['da']]
], columns=['Emp', 'Factors', 'Comments', 'Action', 'ActionText'])

edited Mar 23 '20 at 16:01

answered Mar 23 '20 at 15:39

piRSquared

285,575
57
475
624

Cannot hardcode e,f,c,a,t as the number of columns can varie – R.singh Mar 23 '20 at 15:58
@R.singh I generalized it – piRSquared Mar 23 '20 at 16:03

score 1 · Answer 2 · answered Mar 23 '20 at 16:02

I had to fix Comments for Emp 1 into "['not','some']" in order to make it parseable. Then I used 2 utility functions: first one to convert the strings to lists, second one to process the rows of the original dataframe.

After my fix the dataframe is:

df = pd.DataFrame({'Emp': [1, 2], 'Factors': ['"[\'1\',\'1\']"', '"[\'1\']"'],
                   'Comments': ['"[\'not\',\'some\']"', '"[\'textB\']"'],
                   'Action': ['"[\'1\']"', '"[]"'],
                   'ActionText': ['"[\'good\',\'as\']"', '"[\'da\']"']})

or

   Emp      Factors          Comments   Action       ActionText
0    1  "['1','1']"  "['not','some']"  "['1']"  "['good','as']"
1    2      "['1']"       "['textB']"     "[]"         "['da']"

My code is:

def do_eval(x):
    if not isinstance(x, str): return x
    while(isinstance(x, str)):
        x = ast.literal_eval(x)
    return x if len(x) > 1 else x[0] if len(x) == 1 else None

def make_df(row):
    d = row.apply(do_eval).to_dict()
    for v in d.values():
        if isinstance(v, list):
            return pd.DataFrame(d)
    return pd.DataFrame(d, index=[0])

resul = pd.concat(df.apply(make_df, axis=1).values)

It gives:

   Emp Factors Comments Action ActionText
0    1       1      not      1       good
1    1       1     some      1         as
0    2       1    textB   None         da

Explode dataframe with string values without explode function in pandas

2 Answers2

zip_longest

Setup

`zip_longest`