Add a new row to a CSV Dataframe inside a for loop with Pandas

Question

Hello I am really stuck and cannot get my head around this problem really appreciate any help or guidance. I have tried to ask this question a couple of times different ways but have had no full success on completing my task.

I am attempting to take a cell from each row in spreadsheet"a.csv" and then use that value to check multiple regular expression and if an item exsists from another row in another spreadsheet "b.csv"

Now I have got this working with all of the regular expression and when I print the data to the screen it runs perfectly showing me all of the data and doing the checks correctly.

The problem lies for me that I cannot take a value from "b.csv" and apply to "a.csv" inside the loop and the if statements (only apply the value from "b.csv" to the correct row in "a.csv")

Here is my code currently:

import pandas as pd
import re

df1 = pd.read_csv('a.csv', sep=",")
df2 = pd.read_csv('b.csv', sep=",")

for index, row in df1.iterrows():

    for i, r in df2.iterrows():

        if r['Model'] in row['Title']:

            df1[index, 'Tag'] = r['Tag']
            # if you print df1[index, 'Tag'] HERE it prints the correct tag for each index/row and then possible will continue searching through b.csv with the same index in a.csv which is what i need to do as there may be mutiple of the same.
            # This is the information I need to put in a new row under the index row but currently it only adds to the existing row and then gets wiped after another search.

#if you print df1 here it only applies to a couple of rows and not all of them.
df1.to_csv('a.csv', sep=",", index=False)

A.CSV - Example Data

IDNumber   Title
1          Vauxhall Astra Model H 92-93
2          VW Golf MK2 GTI 90-91
3          BMW 1 Series 89-93

B.CSV - Example Data

Manufacturer  Model      Type     Year                        Tag
VW            Golf       MK2      1990|1991|1993              1000
VW            Golf       MK2 GTI  1990|1991|1993              1001
VW            Golf       MK2      1896|1897|1898|1899         1002
Vauxhall      Astra      Model H  1991|1992|1993|1994         1003
BMW           2 Series            2000|2001|2002              1004

A.CSV - OUTPUT I Require

IDNumber   Title                         Tag
1          Vauxhall Astra Model H 92-93
                                         1003
2          VW Golf MK2 GTI 90-91         
                                         1000
                                         1001
3          BMW 1 Series 89-93

I believe the error is something to do with the nested loops and how its iterating through the data but I am pulling my hair out. Would greatly apperciate an answer or guidance if I am attempting to do this incorrectly.

Serge Ballesta · Accepted Answer · 2020-04-16 15:39:27Z

1

A possible way is to add the new row at the end of the dataframe and store the IDNumber in it. At the end of the loops, you can sort the dataframe on IDNumber and set it to blank on lines having no Title. Here is a possible code:

for index, row in df1.iterrows():
    for i, r in df2.iterrows():
        if r['Model'] in row['Title']:
            ix = len(df1)
            df1.loc[ix, 'Tag'] = r['Tag']
            df1.loc[ix, 'IDNumber'] = row['IDNumber']

df1 = df1.sort_values(['IDNumber']).reset_index(drop=True)
df1.loc[df1['Title'].isna(), 'IDNumber'] = ''
df1 = df1.fillna('')

You finally get:

  IDNumber                         Title   Tag
0        1  Vauxhall Astra Model H 92-93      
1                                         1003
2        2         VW Golf MK2 GTI 90-91      
3                                         1000
4                                         1001
5                                         1002
6        3            BMW 1 Series 89-93

Nota: you also get the 1002 tag because this code has no check for year...

answered Apr 16, 2020 at 15:39

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Notsoprosql Over a year ago

Thank you so much life saver. I have another if loop nested inside this that handles the year but to make the example eaiser to read I took everything else out thank you dude!

Collectives™ on Stack Overflow

Add a new row to a CSV Dataframe inside a for loop with Pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related