Replace a row by a new Dataframe

Question

I am looking for a more elegant way to replace a dataframe in another dataframe from the values of a dictionary.

here its an example of the type of data i have to use

d = {1 : {'name' : 'bob','age' : 22,'Data' : {}},
 4 : {'name' : 'sam','age' : 30,'Data' : {}},
 2 : {'name' : 'tom','age' : 20,'Data' : [{'Mail':'B','MailValue': 89},
                                          {'Mail':'C','MailValue' : 100}]},
 3 : {'name' : 'mat','age' : 19,'Data' : [{'Mail':'D','MailValue': 71}]}}                                     '
df = pd.DataFrame(d).T
df
                                                 Data age name
1                                                 {}  22  bob
4                                                 {}  30  sam
2  [{'Mail': 'B', 'MailValue': 89}, {'Mail': 'C',...  20  tom
3                   [{'Mail': 'D', 'MailValue': 71}]  19  mat

here is my actual solution for append value of Data cell and replicate name and age columns in the final dataframe df2

df2 = pd.DataFrame()
for idx, row in df[:].iterrows():
    dfx = pd.DataFrame(row.Data)
    dfx['idx'] = idx
    df2 = df2.append(dfx)

df2.set_index('idx', inplace= True)
df2[df.columns] = df
df2 = df2.append(df.drop(df2.index.unique())).drop(columns = ['Data'])

print(df2)
  Mail  MailValue age name
2    B       89.0  20  tom
2    C      100.0  20  tom
3    D       71.0  19  mat
1  NaN        NaN  22  bob
4  NaN        NaN  30  sam

jpp · Accepted Answer · 2019-01-14 21:18:22Z

2

One way is to use pd.concat with an iterable of split dataframes, taking care to construct a one-row dataframe for empty dictionaries:

splits = [pd.DataFrame(x if x else [{}]) for x in df.pop('Data')]

lens = list(map(len, splits))

df = pd.DataFrame({'age': np.repeat(df['age'].values, lens),
                   'name': np.repeat(df['name'].values, lens)})\
       .join(pd.concat(splits, ignore_index=True))


print(df)
#   age name Mail  MailValue
# 0  22  bob  NaN        NaN
# 1  20  tom    B       89.0
# 2  20  tom    C      100.0
# 3  19  mat    D       71.0
# 4  30  sam  NaN        NaN

edited Jan 14, 2019 at 21:18

answered Jan 14, 2019 at 14:27

jpp

166k37 gold badges301 silver badges362 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Egos Over a year ago

thanks for your answer @jpp that's why I love python / pandas there is a thousand ways to find 1 answer No problem to validate your answer on the other hand I would be surprised by pandas that there is not a more elegant method, I agree that the concept of elegance is very subjective Proceed by creating a new pd.Dataframe is perhaps the only solution I will continue to do tests on my side

Collectives™ on Stack Overflow

Replace a row by a new Dataframe

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related