How to optimize time while converting list to dataframe?

user_14364775 0
Viewed 104

According to my previous question, How to fill the values in the list and convert it into the dataframe?

This works but takes a lot of time for a large amount of data.

For example, I have a dataframe:

Id  Name  Photo1  Photo2
1   Mark  1.jpg   2.jpg
2   Julia 1.jpg
3   Andy  1.jpg   2.jpg

I tried:

import pandas as pd

df = pd.read_csv('PyCharmProjects/book1.csv')

df1 = df.reindex(['I','Id','Name','P','46','N','Photo1','Photo2','PH'],axis=1)
df1['I'] = df1['I'].fillna('I')
df1['P'] = df1['P'].fillna('P')
df1['46'] = df1['46'].fillna('46')
df1['N'] = df1['N'].fillna('N')
df1['PH'] = df1['PH'].fillna('PH')
df1 = df1.astype(str)

vals = [['I','Id'],['N','Name'],['P','46']]

photo_df = df1.fillna('').filter(like='Photo')

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

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

df1 = pd.concat(L)

df1

This shows the result as :

I     1
Name  Mark
PH    1.jpg
PH    2.jpg
P     46
I     2
Name  Julia
PH    1.jpg
P     46
I     3
Name  Andy
PH    1.jpg
PH    2.jpg
P     46

This works fine, but when I tried with large datasets, this would take an enormous amount of time.

This line takes a lot of time:

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

Any ideas to minimize the time or any alternatives to this approach.

pandas dataframe numpy time python
2 Answers
out = (df.assign(P=46)
         .stack(dropna=False)
         .reset_index(level=-1)
         .set_axis([0, 1], axis=1)
         .replace({0: {"Id": "I", "Name": "N", r"^Photo\d+$": "PH"}}, regex=True))

We first assign a column P with values 46, then stack it whilst keeping NaNs i.e., columns come next to index, then reset_index the last level i.e., the newly-came columns became a column of their own, then set_axis names as 0, 1 of columns. Lastly, perform the required replacements in column 0 which are "ID" to "I" etc.

to get

    0      1
0   I      1
0   N   Mark
0  PH  1.jpg
0  PH  2.jpg
0   P     46
1   I      2
1   N  Julia
1  PH  1.jpg
1  PH   None
1   P     46
2   I      3
2   N   Andy
2  PH  1.jpg
2  PH  2.jpg
2   P     46
user_93321870

Solution using df.stack()

Code:

df['P'] = 46
df.rename(columns={'Id':'I','Photo1':'PH','Photo2':'PH'}, inplace=True)
df = df.stack(dropna=True).reset_index()[['level_1',0]].rename(columns={'level_1':'var',0:'value'})
df

Prints:

     var  value
0      I      1
1   Name   Mark
2     PH  1.jpg
3     PH  2.jpg
4      P     46
5      I      2
6   Name  Julia
7     PH  1.jpg
8      P     46
9      I      3
10  Name   Andy
11    PH  1.jpg
12    PH  2.jpg
13     P     46
user_136288830
Related