CODEWITHSUNDEEP
python-3.xpandas
Kumar AK
Kumar AK

Reputation: 1037

split the text by comma and append rows pandas

I have a dataframe a like below:

df = pd.DataFrame({'User':['101','102','103','104'],             
                  'Text':["""{"y":["8","8 plus"]""","""{"x":["7"]}""","""{"x":["7","7+","7++"]}""","""{"x":["7"]}"""]})

wanted output:

enter image description here

i have tried this one how to extract only those exact values

df2 = df.set_index('User').Text .str.split(',', expand=True).stack().reset_index()

Upvotes: 0

Views: 203

Answers (2)

Dani Mesejo
Dani Mesejo

Reputation: 61920

Assuming the first dictionary is missing the closing bracket (}). You could use ast.literal_eval:

import ast
import pandas as pd

df = pd.DataFrame({'User': ['101', '102', '103', '104'],
                   'Text': ["""{"y":["8","8 plus"]}""", """{"x":["7"]}""", """{"x":["7","7+","7++"]}""",
                            """{"x":["7"]}"""]})

# convert to dictionary and drop the text column
dictionaries = df.assign(D=df.Text.apply(ast.literal_eval)).drop('Text', axis=1)

# convert each row to multiple ones (given by the values of each dictionary)
tuples = [(u, k, v) for u, r in dictionaries.values for k, vs in r.items() for v in vs]

result = pd.DataFrame(tuples, columns=['User', 'Text1', 'Text2'])
print(result)

Output

  User Text1   Text2
0  101     y       8
1  101     y  8 plus
2  102     x       7
3  103     x       7
4  103     x      7+
5  103     x     7++
6  104     x       7

Upvotes: 1

anky
anky

Reputation: 75100

May be this:

import ast
df[['Text1','Text2']]=df.pop('Text').str.split(":",expand=True)
df.Text2=df.Text2.replace("}","",regex=True).apply(ast.literal_eval)
df.Text1=df.Text1.replace("\W",'',regex=True)

s=pd.DataFrame({'B':np.concatenate(df.Text2.values)},index=df.index.repeat(df.Text2.str.len()))
df.join(s).drop('Text2',1).rename(columns={'B':'Text2'})

Output

  User Text1   Text2
0  101     y       8
0  101     y  8 plus
1  102     x       7
2  103     x       7
2  103     x      7+
2  103     x     7++
3  104     x       7

Upvotes: 1

Related Questions