Data looks like when I open this csv in Notepad.
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.
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
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