Python Forum
Want to remove the text from a particular column in excel
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Want to remove the text from a particular column in excel
#1
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.

Attached Files

.py   c-1.py (Size: 248 bytes / Downloads: 274)
.csv   magnetic_declination_australia_1.csv (Size: 3.49 KB / Downloads: 294)
Reply
#2
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
Reply
#3
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 Smile
Larz60+ likes this post
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Thumbs Up Need to compare the Excel file name with a directory text file. veeran1991 1 1,063 Dec-15-2022, 04:32 PM
Last Post: Larz60+
  How to remove footer from PDF when extracting to text jh67 3 4,849 Dec-13-2022, 06:52 AM
Last Post: DPaul
  How to remove patterns of characters from text aaander 4 1,068 Nov-19-2022, 03:34 PM
Last Post: snippsat
  Two text files, want to add a column value zxcv101 8 1,841 Jun-20-2022, 03:06 PM
Last Post: deanhystad
  How to format Excel column with comma? dee 0 1,337 Jun-13-2022, 10:11 PM
Last Post: dee
  Appending Excel column value as CSV file name sh1704 0 1,268 Feb-06-2022, 10:32 PM
Last Post: sh1704
  How to remove a column or two columns in a correlation heatmap? lulu43366 3 5,077 Sep-30-2021, 03:47 PM
Last Post: lulu43366
  beginner text formatting single line to column jafrost 4 3,160 Apr-28-2021, 07:03 PM
Last Post: jafrost
  More elegant way to remove time from text lines. Pedroski55 6 3,840 Apr-25-2021, 03:18 PM
Last Post: perfringo
  Copy column from one existing excel file to another file mkujawsk 0 5,486 Apr-14-2021, 06:33 PM
Last Post: mkujawsk

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020