how to change the iterrows method to apply-CodePudding

I have this code, in which I have rows around 60k. It taking around 4 hrs to complete the whole process. This code is not feasible and want to use apply instead iterrow because of time constraints.

Here is the code,

all_merged_k = pd.DataFrame(columns=all_merged_f.columns)
for index, row in all_merged_f.iterrows():
    if (row['route_count'] == 0):
        all_merged_k = all_merged_k.append(row)
    else:
        for i in range(row['route_count']):
            row1 = row.copy()
            row['Route Number'] = i
            row['Route_Broken'] = row1['routes'][i]
            all_merged_k = all_merged_k.append(row)

Basically, what the code is doing is that if the route count is 0 then append the same row, if not then whatever the number of counts is it will append that number of rows with all same value except the routes column (as it contains nested list) so breaking them in multiple rows. And adding them in new columns called Route_Broken and Route Number.

Sample of data:

               routes  route_count
          [[CHN-IND]]            1
[[CHN-IND],[IND-KOR]]            2

O/P data:

               routes  route_count  Broken_Route Route Number
          [[CHN-IND]]            1   [CHN-IND]       1
[[CHN-IND],[IND-KOR]]            2   [CHN-IND]       1
[[CHN-IND],[IND-KOR]]            2   [IND-KOR]       2

Can it be possible using apply because 4 hrs is very high and cant be put into production. I need extreme help. Pls help me.

CodePudding user response：

Are you expect something like that:

>>> df.join(df['routes'].explode().rename('Broken_Route')) \
      .assign(**{'Route Number': lambda x: x.groupby(level=0).cumcount().add(1)})

                   routes  route_count Broken_Route  Route Number
0             [[CHN-IND]]            1    [CHN-IND]             1
1  [[CHN-IND], [IND-KOR]]            2    [CHN-IND]             1
1  [[CHN-IND], [IND-KOR]]            2    [IND-KOR]             2
2                                    0                          1

Setup:

data = {'routes': [[['CHN-IND']], [['CHN-IND'], ['IND-KOR']], ''], 
        'route_count': [1, 2, 0]}
df = pd.DataFrame(data)

Update 1: added a record with route_count=0 and routes=''.

CodePudding user response：

You can assign the routes and counts and explode:

(df.assign(Broken_Route=df['routes'],
           count=df['routes'].str.len().apply(range))
   .explode(['Broken_Route', 'count'])
)

NB. multi-column explode requires pandas ≥1.3.0, if older use this method

output:

                   routes  route_count Broken_Route count
0             [[CHN-IND]]            1    [CHN-IND]     0
1  [[CHN-IND], [IND-KOR]]            2    [CHN-IND]     0
1  [[CHN-IND], [IND-KOR]]            2    [IND-KOR]     1

CodePudding user response：

You are appending to a dataframe on every loop. This is actually a slow operation because the dataframe is practically recreated on every iteration. Instead, it is more efficient to store everything in a list, then create the dataframe itself at the very end.

Refer to Improve Row Append Performance On Pandas DataFrames for more info.