0

I have a Pandas Dataframe as per below, with an index and two columns. "Image_main" column consists of a list of urls.

What I want to do is to separate each of the items in the list of the column "image_main" into new columns in the same dataframe. The length of the list is different in each row. For example, list in row 1 has 4 urls, while row 3 has only 2 urls.

index   image_main                                      referenceID
0     ['https://x.com/1.jpg','https://x.com/2.jpg',...  3.297439e+10
1     ['https://y.com/1.jpg','https://y.com/2.jpg',...  4.000220e+12
2     ['https://z.com/1.jpg','https://z.com/2.jpg',...  4.000130e+12
3     ['https://v.com/1.jpg','https://v.com/2.jpg',...  3.296914e+10
4     ['https://a.com/1.jpg','https://a.com/2.jpg',...  4.000080e+12

So far, I have tried below based on the answers given to the following question: Pandas: split column of lists of unequal length into multiple columns . However, it does not seem to be working since I get the same result as I had before

df['image_main'] = pd.DataFrame(df['image_main'].values.tolist()).add_prefix('code_')
print(df)

    image_main                                         referenceID
0   ['https://x.com/1.jpg','https://x.com/2.jpg',...   3.297439e+10
1    ['https://y.com/1.jpg','https://y.com/2.jpg',...   4.000220e+12
2    ['https://z.com/1.jpg','https://z.com/2.jpg',...   4.000130e+12
3    ['https://v.com/1.jpg','https://v.com/2.jpg',...   3.296914e+10
4    ['https://a.com/1.jpg','https://a.com/2.jpg',...   4.000080e+12

How can I split each of the items in the column image_main into new separate columns in the same dataframe?

The desired result would something similar to below:

    image_main     referenceID.     image_1.                  image 2                ....
0   ...,...        3.297439e+10.    'https://x.com/1.jpg'    'https://x.com/2.jpg' 
1   ...,...        3.297439e+10.    'https://y.com/1.jpg'    'https://y.com/2.jpg' 
2   ...,...        3.297439e+10.    'https://z.com/1.jpg'    'https://z.com/2.jpg' 
3   ...,...        3.297439e+10.    'https://v.com/1.jpg'    'https://v.com/2.jpg' 
4   ...,...        3.297439e+10.    'https://a.com/1.jpg'    'https://a.com/2.jpg'
Jose Manuel
  • 105
  • 4
  • 10

2 Answers2

1

The solution in a thread you linked worked fine when I tried it.

You don't assign the transformation to a column, but join it with a main dataframe

df.join(pd.DataFrame(df["image_main"].values.tolist()).add_prefix('image_'))

EDIT:

To convert image_main string values to a list, use the following: 

df["image_main"] = df["image_main"].str.replace("\[|\]|\'", "").str.split(",")
df.join(pd.DataFrame(df["image_main"].values.tolist()).add_prefix('image_'))
help-ukraine-now
  • 3,850
  • 4
  • 19
  • 36
0

I think what you're missing is a pd.merge:

df:

     A
0   [1, 2, 3, 4]
1   [1, 2, 3, 4]
2   [1, 2, 3, 4]

merge into new df:

pd.merge(df, pd.DataFrame(df['A'].values.tolist()).add_prefix('code_'), on=df.index)

output:

    key_0   A             code_0    code_1  code_2  code_3
0   0       [1, 2, 3, 4]    1         2       3      4
1   1       [1, 2, 3, 4]    1         2       3      4
2   2       [1, 2, 3, 4]    1         2       3      4
B Man
  • 89
  • 4
  • It does not work for me. I just realised that the problem is that the values in "image_main" is a string and not a list. "['1,2,3']". How can I convert it to list so that the code shared with you will work? – Jose Manuel Oct 13 '19 at 20:29
  • `df['A'] = df['A'].apply(lambda x: re.match('\[(.*)\]',x).group(1))` `pd.merge(df, pd.DataFrame(df['A'].str.split(',').values.tolist()).add_prefix('code_'), on=df.index)` should do the trick, but it feels a tad inefficient. I'll try give it some thought in the morning – B Man Oct 13 '19 at 20:40