Want to remove the text from a particular column in excel

[shantanu97]

Data looks like when I open this csv in Notepad.

• Year Latitude Longitude Magnetic Declination SourceFile
  1985 88 75 " magneticFields
  D 41.971 deg" https://api.geomagnetism.ga.gov.au/agrf
  1986 88 75 " magneticFields
  D 42.179 deg" https://api.geomagnetism.ga.gov.au/agrf
  1987 88 75 " magneticFields
  D 42.390 deg" https://api.geomagnetism.ga.gov.au/agrf
  

I want to replace the "magneticfields\nD" with nothing in the Magnetic Declination column.

data['Magnetic Declination'] = data['Magnetic Declination'].apply(lambda x: x.replace(r"magneticFields\nD ", ""))

import pandas as pd
import re
data = pd.read_csv("magnetic_declination_australia_1.csv") 
data.head()
data['Magnetic Declination'] = data['Magnetic Declination'].apply(lambda x: x.replace(r"magneticFields\nD     ", ""))
data.head()

can anyone one help how to fixed this? csv file and code are attached.

[Larz60+]

Here's a different way to tackle this:

import csv
from pathlib import Path
import os
import pandas as pd

# assure we are are in proper starting directory
os.chdir(os.path.abspath(os.path.dirname(__file__)))
homepath = Path('.')


csvfile = homepath / "magnetic_declination_australia_1.csv"
csvout = homepath / "magnetic_declination_australia_1_new.csv"
newdata = []
with csvfile.open() as fp, csvout.open('w') as fout:
    crdr = csv.reader(fp)
    cwrtr = csv.writer(fout)
    for n, row in enumerate(crdr):
        if n:
            row[3] = (row[3].strip().split('\nD'))[1].strip()
            cwrtr.writerow(row)
        else:
            cwrtr.writerow(row)
data = pd.read_csv(csvout)
print(data)

which renders:

Output:
    Year  Latitude  Longitude Magnetic Declination                               SourceFile
0   1985        88         75           41.971 deg  https://api.geomagnetism.ga.gov.au/agrf
1   1986        88         75           42.179 deg  https://api.geomagnetism.ga.gov.au/agrf
2   1987        88         75           42.390 deg  https://api.geomagnetism.ga.gov.au/agrf
3   1988        88         75           42.603 deg  https://api.geomagnetism.ga.gov.au/agrf
4   1989        88         75           42.819 deg  https://api.geomagnetism.ga.gov.au/agrf
5   1990        88         75           43.436 deg  https://api.geomagnetism.ga.gov.au/agrf
6   1991        88         75           43.777 deg  https://api.geomagnetism.ga.gov.au/agrf
7   1992        88         75           44.121 deg  https://api.geomagnetism.ga.gov.au/agrf
8   1993        88         75           44.467 deg  https://api.geomagnetism.ga.gov.au/agrf
9   1994        88         75           44.817 deg  https://api.geomagnetism.ga.gov.au/agrf
10  1995        88         75           45.680 deg  https://api.geomagnetism.ga.gov.au/agrf
11  1996        88         75           46.240 deg  https://api.geomagnetism.ga.gov.au/agrf
12  1997        88         75           46.807 deg  https://api.geomagnetism.ga.gov.au/agrf
13  1998        88         75           47.382 deg  https://api.geomagnetism.ga.gov.au/agrf
14  1999        88         75           47.963 deg  https://api.geomagnetism.ga.gov.au/agrf
15  2000        88         75           49.087 deg  https://api.geomagnetism.ga.gov.au/agrf
16  2001        88         75           49.840 deg  https://api.geomagnetism.ga.gov.au/agrf
17  2002        88         75           50.601 deg  https://api.geomagnetism.ga.gov.au/agrf
18  2003        88         75           51.369 deg  https://api.geomagnetism.ga.gov.au/agrf
19  2004        88         75           52.145 deg  https://api.geomagnetism.ga.gov.au/agrf
20  2005        88         75           52.718 deg  https://api.geomagnetism.ga.gov.au/agrf
21  2006        88         75           53.619 deg  https://api.geomagnetism.ga.gov.au/agrf
22  2007        88         75           54.527 deg  https://api.geomagnetism.ga.gov.au/agrf
23  2008        88         75           55.441 deg  https://api.geomagnetism.ga.gov.au/agrf
24  2009        88         75           56.358 deg  https://api.geomagnetism.ga.gov.au/agrf
25  2010        88         75           57.495 deg  https://api.geomagnetism.ga.gov.au/agrf
26  2011        88         75           58.683 deg  https://api.geomagnetism.ga.gov.au/agrf
27  2012        88         75           59.887 deg  https://api.geomagnetism.ga.gov.au/agrf
28  2013        88         75           61.103 deg  https://api.geomagnetism.ga.gov.au/agrf
29  2014        88         75           62.335 deg  https://api.geomagnetism.ga.gov.au/agrf
30  2015        88         75           63.888 deg  https://api.geomagnetism.ga.gov.au/agrf
31  2016        88         75           65.268 deg  https://api.geomagnetism.ga.gov.au/agrf
32  2017        88         75           66.686 deg  https://api.geomagnetism.ga.gov.au/agrf
33  2018        88         75           68.147 deg  https://api.geomagnetism.ga.gov.au/agrf
34  2019        88         75           69.650 deg  https://api.geomagnetism.ga.gov.au/agrf
35  2020        88         75           71.195 deg  https://api.geomagnetism.ga.gov.au/agrf
36  2021        88         75           72.777 deg  https://api.geomagnetism.ga.gov.au/agrf
37  2022        88         75           74.400 deg  https://api.geomagnetism.ga.gov.au/agrf
38  2023        88         75           76.062 deg  https://api.geomagnetism.ga.gov.au/agrf
39  2024        88         75           77.762 deg  https://api.geomagnetism.ga.gov.au/agrf

[eddywinch82]

Hi shantanu97,

The following, is an alternative simpler method than Larz60+'s, it worked for me :-

import pandas as pd
import re

data = pd.read_csv("magnetic_declination_australia_1.csv") 
#print(data.head())
#data['Magnetic Declination'] = data['Magnetic Declination'].apply(lambda x: x.replace(r"magneticFields\nD ", ""))
data['Magnetic Declination'] = data['Magnetic Declination'].str.replace('magneticFields\nD', '')
#print(data.head())
data

Best Regards

Eddie Winch