Convert string list in column of Pandas data frame into mean integer

Question

I have two data frames like the following:

Id    Name    Price   
1    [ABC]        33900
1    [XYZ]        33900

When I groupby using tolist by id, I got a list as:

Id   Name         Price
1    [[ABC],[XYZ]]   [33900,33900]

I want to convert this to single value as:

Id    Name     Price
1   [ABC,XYZ]   33900

I am not able to apply np.mean or any other operation as it is showing as list. Dataframe also included string which successfully grouped using tolist but integers also got added as list.

I think need `df.groupby(['Id','Price'])['Name'].apply(list)` — jezrael, Mar 27 '18 at 10:53
If want aggregate another functions like `mean` - `df.groupby('Id').agg({'Name': lambda x: x.tolist(), 'Price':'mean'})` — jezrael, Mar 27 '18 at 10:56
Jezrael solution solves it. But still strings are coming as lists e.g. `[[ABC],[XYZ]]` , is it possible to have column like `[ABC,XYZ]` — Himanshu Sharma, Mar 27 '18 at 11:21
Those are list of strings. I scrapped on news articles to produce them date wise and combined various articles date wise. — Himanshu Sharma, Mar 27 '18 at 11:31
Prices are same for each date, I took mean as it will come to same. Eg. [100,100,100] will be 100.. But I don't need it. I just need price. — Himanshu Sharma, Mar 27 '18 at 11:35
Ok, how working `df = df.groupby(['Id','Price'])['Name'].apply(lambda x: [i for li in x for i in li])` ? — jezrael, Mar 27 '18 at 11:38

score 1 · Accepted Answer · answered Mar 27 '18 at 12:15

1

I think need GroupBy.apply with flattening lists:

df = (df.groupby(['Id','Price'])['Name']
        .apply(lambda x: [i for li in x for i in li])
        .reset_index())
print (df)
   Id  Price        Name
0   1  33900  [ABC, XYZ]

answered Mar 27 '18 at 12:15

jezrael

822,522
95
1,334
1,252

Convert string list in column of Pandas data frame into mean integer

1 Answers1