Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 712
Atom Store Avatar asked May 04 '21 09:05

Atom Store


People also ask

How do I convert a list to a DataFrame column in Python?

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.

Can we create DataFrame from 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.

How do I convert a list to pandas?

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 .

What can I do with a list converted to a Dataframe?

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:

How to create a data frame in Python?

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.

How to combine lists into one column in a Dataframe?

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?

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.


Video Answer


2 Answers

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
    
like image 60
jezrael Avatar answered Oct 27 '22 16:10

jezrael


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
like image 28
Aditya Avatar answered Oct 27 '22 17:10

Aditya