Conditionally appending a multiple line dataframe

user_7615751 0
Viewed 96

I'm iterating a dataframe and trying to append new values inside from a different dataframe whenever a row in the first dataframe is a certain value.

Consider the following two dataframes:

print(full_df)
                                             AccessName                                          PolicyArn
0                                   arn:aws:glue:sample  arn:aws:iam::971340810992:policy/service-role/...
1                                  arn:aws:glue:sample2  arn:aws:iam::971340810992:policy/service-role/...
2                                                   ---  arn:aws:iam::971340810992:policy/service-role/...
3                                  arn:aws:s3:::sample3  arn:aws:iam::971340810992:policy/service-role/...


print(side_df)
                                            AccessName
0                                          sample-test
1                                         query_sample
2                                     us-east-1-sample

If the AccessName in the full_df is a certain value, the side_df is appended to the full_df, having the second row value always be arn for all rows.

arn = 'fixed_value'
for index, row in full_df.iterrows():
    if row['AccessName'] == '---':
        #Here I don't know how I'd define the code to append the side_df values:
        #full_df['AccessName'] = side_df['AccessName']
        #full_df['PolicyArn'] = arn

Would it be best to make another if statement at that point and iterate the side_df values and append row by row?

This for function is nested in the actual code and the arn is dynamically generated.

Desired output:

                                             AccessName                                          PolicyArn
0                                   arn:aws:glue:sample  arn:aws:iam::971340810992:policy/service-role/...
1                                  arn:aws:glue:sample2  arn:aws:iam::971340810992:policy/service-role/...
2                                                   ---  arn:aws:iam::971340810992:policy/service-role/...
3                                           sample-test  fixed_value
4                                          query_sample  fixed_value
5                                      us-east-1-sample  fixed_value
6                                  arn:aws:s3:::sample3  arn:aws:iam::971340810992:policy/service-role/...

What would be the optimal way to code this?

pandas python
2 Answers

you can reindex and append the dataframes then fillna() as follows:

INITIALIZATION:

df = pd.read_csv(io.StringIO(''' AccessName     PolicyArn
0                                   arn:aws:glue:sample  arn:aws:iam::971340810992:policy/service-role/...
1                                  arn:aws:glue:sample2  arn:aws:iam::971340810992:policy/service-role/...
2                                                   ---  arn:aws:iam::971340810992:policy/service-role/...
3                                  arn:aws:s3:::sample3  arn:aws:iam::971340810992:policy/service-role/...
'''),sep='\s+')

df2 = pd.read_csv(io.StringIO('''   AccessName
0                                          sample-test
1                                         query_sample
2                                     us-east-1-sample'''),sep='\s+')

SOLUTION

id = df.index[df['AccessName'] == '---'][0] +1
start, end = id + df2.shape[0],df2.shape[0] + df.shape[0]
df.index = np.append(df.index[:id],np.arange(start,end)) # index : [0, 1, 2, 6]
df2.index = np.arange(id,id +df2.shape[0]) # index [3, 4, 5]
solution_df = df.append(df2).sort_index().fillna('fixed_value')
solution_df
>>> AccessName              PolicyArn
0   arn:aws:glue:sample     arn:aws:iam::971340810992:policy/service-role/...
1   arn:aws:glue:sample2    arn:aws:iam::971340810992:policy/service-role/...
2   ---                     arn:aws:iam::971340810992:policy/service-role/...
3   sample-test             fixed_value
4   query_sample            fixed_value
5   us-east-1-sample        fixed_value
6   arn:aws:s3:::sample3    arn:aws:iam::971340810992:policy/service-role/...

Important Note: generally for large dataset it is not advised to look for solutions involving iterations, try hard to find a vectorized solution, and avoid approaches like .iterrows() and .apply(). good luck!

user_88938270

First let's construct your side_df:

side_df = pd.DataFrame([['sample-test'], ['query_sample'], ['us-east-1-sample']]
                       , columns=['AccessName'])
fixed_series = pd.Series(['fixed_value'] * len(side_df), name='PolicyArn').to_frame()
side_df_extended = pd.concat([side_df, fixed_series], axis=1)
print(side_df_extended)

       AccessName    PolicyArn
0       sample-test  fixed_value
1      query_sample  fixed_value
2  us-east-1-sample  fixed_value

Assume full_df is as follows:

    AccessName              PolicyArn
0   arn:aws:glue:sample     arn:aws:iam::971340810992:policy/service-role/
1   arn:aws:glue:sample2    arn:aws:iam::971340810992:policy/service-role/
2   arn:aws:s3:::sample3    arn:aws:iam::971340810992:policy/service-role/
3   arn:aws:glue:sample4    arn:aws:iam::971340810992:policy/service-role/
4   arn:aws:s3:::sample5    arn:aws:iam::971340810992:policy/service-role/

Now let's grab indices of rows that have your condition, e.g.:

indices = full_df['AccessName'] == 'arn:aws:glue:sample2'
rows = full_df[indices].index.tolist()
rows

[1]

Now, you want to append side_df after the occurrence of your condition:

final_df = pd.concat([full_df.iloc[:(rows[0] + 1)], side_df_extended, full_df.iloc[(rows[0] + 1):]], ignore_index=True)
final_df

    AccessName              PolicyArn
0   arn:aws:glue:sample     arn:aws:iam::971340810992:policy/service-role/
1   arn:aws:glue:sample2    arn:aws:iam::971340810992:policy/service-role/
2   sample-test             fixed_value
3   query_sample            fixed_value
4   us-east-1-sample        fixed_value
5   arn:aws:s3:::sample3    arn:aws:iam::971340810992:policy/service-role/
6   arn:aws:glue:sample4    arn:aws:iam::971340810992:policy/service-role/
7   arn:aws:s3:::sample5    arn:aws:iam::971340810992:policy/service-role/
user_72067010
Related