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
Use the tolist() Method to Convert a Dataframe Column to a List. A column in the Pandas dataframe is a Pandas Series . So if we need to convert a column to a list, we can use the tolist() method in the Series . tolist() converts the Series of pandas data-frame to a list.
The pandas DataFrame can be created by using the list of lists, to do this we need to pass a python list of lists as a parameter to the pandas. DataFrame() function. Pandas DataFrame will represent the data in a tabular format, like rows and columns.
Convert data to list. Since there is no method to convert pandas. DataFrame , pandas. Series directly to list , first get the NumPy array ndarray with the values attribute, and then use tolist() method to convert to list .
And here is the result: Once you converted your list into a DataFrame, you’ll be able to perform an assortment of operations and calculations using Pandas. For instance, you may use Pandas to derive some statistics about your data. In the context of our example, you can apply the code below in order to get the mean, max and min price using Pandas:
We can create the data frame by zipping two lists. We can create a data frame using multi-dimensional lists. We can create the data frames by specifying the column name and dtype of them. We can create data frames using lists in the dictionary.
Here we are taking separate lists as input such that each list will act as one column, so the number of lists = n columns in the dataframe, and using zip function we are combining the lists.
How to Convert a List to a DataFrame Row in Python? In this article, we will discuss how to convert a list to a dataframe row in Python. This is known as the Transpose function, this will convert the list into a row. Here each value is stored in one column.
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
A little long but here you go:
index = []
values = []
cnt = 0
for x in vals:
if x[0] == 'I':
cnt += 1
index.append(x[0])
values.append(df1.iloc[cnt-1][x[1]])
pd.DataFrame({'index': index, 'values':values})
But I do not understand why you want to do it in a roundabout manner when you can do it just a few lines with your original dataframe df
:
df2 = df.stack().reset_index()
df2.drop(columns=['level_0'],inplace=True)
df2['level_1'] = df2['level_1'].replace({'Item':'I', 'Quantity':'Q', 'Price':'P', 'Photo1':'PH', 'Photo2':'PH', 'Photo3':'PH', 'Photo4':'PH'})
df2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With