CODEWITHSUNDEEP
pythonpandasdataframepython-3.6
Mitch
Mitch

Reputation: 23

Replace values in csv1 column with csv2 column 2 if column 1 is a match

So as I am so close to finally finish my script I am stuck with the conditional comparing and replacing a value. I hope for your guidance :)

CSV1 with names a,b,host
addprinter,terminal,PC001

CSV2 with names oldhost,newhost
PC001,DC001

I would like now to check for csv1.host and if found in CSV2.oldhost to read CSV2.newhost and replace CSV1.host with CSV2.newhost Therefore in the end in this example above CSV1 (or probably better CSV3 as new output) would read like this: addprinter,terminal,DC001

CSV1 contains of few hundred rows so it should loop through complete file.

I thought I would have it but the following code is unfortunately not complete and seems only usable for series:

pd.np.where(result.host.str.contains(hosts.oldhost), hosts.newhost)

'result' is the output of a .loc ifin

I have the feeling it is a for-loop and followed by a replace. As I learned already so much it would be frustrating to end the day with that lose end. Therefore all guidance and ideas are highly appreciated.

Kind regards

Mitch

Upvotes: 0

Views: 51

Answers (1)

Orenshi
Orenshi

Reputation: 1873

Here's an example of what I have working. I used merge but if CSV2 is really just a map you could alternatively just use it as a dict and use the map function on a series.

>>> CSV1
              a         b   host
0  addsomething       ssh  DC001
1    addprinter  terminal  PC001
>>> CSV2
  oldhost newhost
0   PC001   DC001
>>> CSV3 = CSV1.merge(CSV2, left_on=['host'], right_on=['oldhost'], how='left')
>>> CSV3
              a         b   host oldhost newhost
0  addsomething       ssh  DC001     NaN     NaN
1    addprinter  terminal  PC001   PC001   DC001
>>> CSV3.host = CSV3.newhost.combine_first(CSV3.host)
>>> CSV3[['a', 'b', 'host']]
              a         b   host
0  addsomething       ssh  DC001
1    addprinter  terminal  DC001

Below is an example using a dict, map, and avoiding a merge

>>> CSV1
              a         b   host
0  addsomething       ssh  DC001
1    addprinter  terminal  PC001
>>> host_map = CSV2.set_index('oldhost').newhost.to_dict()
>>> host_map
{'PC001': 'DC001'}
>>> CSV1.host = CSV1.host.map(host_map).combine_first(CSV1.host)
>>> CSV1
              a         b   host
0  addsomething       ssh  DC001
1    addprinter  terminal  DC001

Upvotes: 1

Related Questions