python - How to fill the values in the list and convert it into the dataframe?

Question

I want to make some modifications to my previous Question:

Iterating over conditions from columns and Dataframe to list conversion(pandas)

The dataframe is:

 Item   Quantity  Price     Photo1     Photo2    Photo3    Photo4

A        2         30      A1.jpg      A2.jpg 
B        4         10      B1.jpg      B2.jpg    B3.jpg    B4.jpg
C        5         15      C1.jpg

I tried:

df1 = df.reindex(['Item','Quantity','Price','Photo1','Photo2','Photo3','Photo4','I','Q','P','PH',] axis=1)
df1['I'] = df1['I'].fillna['I']
df1['Q'] = df1['Q'].fillna['Q']
df1['P'] = df1['P'].fillna['P']
df1['PH'] = df1['PH'].fillna['PH']
vals = [['I','Item'],['Q','Quantity'],['P','Price']]

photo_df = df1.filter(like='Photo')
photo_df = photo_df.transform(lambda x: np.where(x.isnull(), x, x.name)) 
photo_df = photo_df.fillna('')

vals = [y for x in photo_df.to_numpy() 
         for y in vals[:3] + [['PH',z] for z in x[x!='']] ]

vals returns:

[['I', 'Item'], ['Q', 'Quantity'], ['P', 'Price'], ['PH', 'Photo1'], ['PH', 'Photo2'], 
['I', 'Item'], ['Q', 'Quantity'], ['P', 'Price'], ['PH', 'Photo1'], ['PH', 'Photo2'], 
['PH', 'Photo3'], ['PH', 'Photo4'], ['I', 'Item'], ['Q', 'Quantity'], ['P', 'Price'], ['PH', 'Photo1']]

Now I want to fill in the values from the previous data frame:

I tried:

L = [df1.loc[:, x].set_axis(range(len(x)), axis=1) for x in vals]

This returned in the format:

[I,A,I,B,I,C,Q,2,Q,4,Q,5....................]

I want the L as:

[I,A,Q,2,P,30,PH,A1.jpg,PH,A2.jpg,I,B..............]

Expected dataframe:

I       A
Q       2
P       4
PH      A1.jpg
PH      A2.jpg
I       B
Q       4
P       10 
PH      B1.jpg
PH      B2.jpg
PH      B3.jpg
PH      B4.jpg
I       C
Q       5
P       15
PH      C1.jpg

Answer 1

Use DataFrame.stack for reshape with Series.map columns names with replace not matched values to PH:

d = {'Item':'I' , 'Quantity':'Q' ,'Price': 'P'}
df = df.stack().reset_index(level=1).reset_index(drop=True)
df.columns = ['a','b']
df['a'] = df['a'].map(d).fillna('PH')
print (df)
     a       b
0    I       A
1    Q       2
2    P      30
3   PH  A1.jpg
4   PH  A2.jpg
5    I       B
6    Q       4
7    P      10
8   PH  B1.jpg
9   PH  B2.jpg
10  PH  B3.jpg
11  PH  B4.jpg
12   I       C
13   Q       5
14   P      15
15  PH  C1.jpg

EDIT: To values vals are added values of indices and then used for selecting:

vals = [(i, y) for i, x in enumerate(photo_df.to_numpy())
          for y in vals[:3] + [['PH',z] 
          for z in photo_df.columns[x!='']]]
print (vals)
[(0, ['I', 'Item']), (0, ['Q', 'Quantity']), (0, ['P', 'Price']), 
 (0, ['PH', 'Photo1']), (0, ['PH', 'Photo2']), (1, ['I', 'Item']),
 (1, ['Q', 'Quantity']), (1, ['P', 'Price']), (1, ['PH', 'Photo1']), 
 (1, ['PH', 'Photo2']), (1, ['PH', 'Photo3']), (1, ['PH', 'Photo4']),
 (2, ['I', 'Item']), (2, ['Q', 'Quantity']), (2, ['P', 'Price']), 
 (2, ['PH', 'Photo1'])]

L = [df1.loc[df1.index[[i]], x].set_axis(range(len(x)), axis=1) for i, x in vals]

df  = pd.concat(L)
print (df)
    0       1
0   I       A
0   Q       2
0   P      30
0  PH  A1.jpg
0  PH  A2.jpg
1   I       B
1   Q       4
1   P      10
1  PH  B1.jpg
1  PH  B2.jpg
1  PH  B3.jpg
1  PH  B4.jpg
2   I       C
2   Q       5
2   P      15
2  PH  C1.jpg