How to split datas from columns and add to a list from a dataframe, also repeat the list elements for a single row? (Pandas)

user_14364775 0
Viewed 97

I have a data frame as

  Product  Photo 1  Photo 2 Photo 3 Photo 4  Price
  Shirt    a.jpg    b.jpg   c.jpg   d.jpg     100
  Pant     e.jpg                              245
  Coat     f.jpg    g.jpg                     433

and the list as

values = [['A,'B','C'],['D','E','F','G'],['H','I','J','K]]

These data frames should be added and should start from index values[2] and the list should increase from the index as per the number of rows in the data frame. The columns should be split too like in the output format. The elements should be looped for every row.

Expected output

values = [['A,'B','C'],['D','E','F','G'],['PHOTO','a.jpg'],['PHOTO','b.jpg'],
          ['PHOTO','c.jpg'],['PHOTO','d.jpg'],['H','I','J','K],
          ['A,'B','C'],['D','E','F','G'],
          ['PHOTO','e.jpg'],['H','I','J','K], ['A,'B','C'],['D','E','F','G'], 
          ['PHOTO','f.jpg',], ['PHOTO','g.jpg'], ['H','I','J','K]
          ]

Then, I want to convert this list to data frames. What I've tried:

L = [df.loc[:, x].set_axis(range(len(x)), axis=1) for x in values]
df = pd.concat(L).sort_index(kind='mergesort').fillna('').reset_index(drop=True)
df = df.fillna('')

output from this code in the previous case:

A  B  C  
D  E  F  G
H  I  J  K


 #the data frame repeats till the number of rows in the previous df.
pandas dataframe python-3.x numpy python
2 Answers

Use nested list comprehension with add PHOTO and then values list:

values = [['A','B','C'],['D','E','F','G'],['H','I','J','K']]

df1 = df.fillna('').filter(like='Photo')
print (df1)
  Photo 1 Photo 2 Photo 3 Photo 4
0   a.jpg   b.jpg   c.jpg   d.jpg
1   e.jpg                        
2   f.jpg   g.jpg 


out = [y for x in df1.to_numpy() 
         for y in values[:2] + [['PHOTO', z] for z in x[x!='']] + values[2:]]
print (out)
[['A', 'B', 'C'], ['D', 'E', 'F', 'G'], ['PHOTO', 'a.jpg'], ['PHOTO', 'b.jpg'], ['PHOTO', 'c.jpg'], ['PHOTO', 'd.jpg'], ['H', 'I', 'J', 'K'], 
 ['A', 'B', 'C'], ['D', 'E', 'F', 'G'], ['PHOTO', 'e.jpg'], ['H', 'I', 'J', 'K'],
 ['A', 'B', 'C'], ['D', 'E', 'F', 'G'], ['PHOTO', 'f.jpg'], ['PHOTO', 'g.jpg'], ['H', 'I', 'J', 'K']]
user_29010020

You can try something like this:

rows = [
        ['PHOTO'] + r.strip().split() 
        for r in df.filter(regex = 'Photo').to_string(header = False, index = False).split('\n')
       ]
values = values[:2] + rows + values[2:]

Output

values
[['A', 'B', 'C'], ['D', 'E', 'F', 'G'], ['PHOTO', 'a.jpg', 'b.jpg', 'c.jpg', 'd.jpg'], ['PHOTO', 'e.jpg'], ['PHOTO', 'f.jpg', 'g.jpg'], ['H', 'I', 'J', 'K']]

If the empty cells are None, then you have to do this first:

df = df.fillna('')

Update after the edit:

jpgs =  df.filter(regex = 'Photo').stack()
rows = [["PHOTO", jpg] for jpg in jpgs[jpgs != ''].unique()]
values = values[:2] + rows + values[2:]

Output

values
[['A', 'B', 'C'], ['D', 'E', 'F', 'G'], ['PHOTO', 'a.jpg'], ['PHOTO', 'b.jpg'], ['PHOTO', 'c.jpg'], ['PHOTO', 'd.jpg'], ['PHOTO', 'e.jpg'], ['PHOTO', 'f.jpg'], ['PHOTO', 'g.jpg'], ['H', 'I', 'J', 'K']]
user_137432330
Related