Numeric columns have no ,, so converting to strings is not necessary, only use DataFrame.replace with regex=True for substrings replacement:
df = df.replace(',','', regex=True)
Or:
df.replace(',','', regex=True, inplace=True)
And last convert strings columns to numeric, thank you @anki_91:
c = df.select_dtypes(object).columns
df[c] = df[c].apply(pd.to_numeric,errors='coerce')
Numeric columns have no ,, so converting to strings is not necessary, only use DataFrame.replace with regex=True for substrings replacement:
df = df.replace(',','', regex=True)
Or:
df.replace(',','', regex=True, inplace=True)
And last convert strings columns to numeric, thank you @anki_91:
c = df.select_dtypes(object).columns
df[c] = df[c].apply(pd.to_numeric,errors='coerce')
Well, you can simplely do:
df = df.apply(lambda x: x.str.replace(',', ''))
Hope it helps!
I have a csv file with a "Prices" column. Right now entries look like 1,000 or 12,456. I could probably remove them in Excel and re-save but I want to know how I can transform the column to remove non-numeric characters so 'objects' like $1,299.99 will become 'float' 1299.99. Thanks
Pandas has a built in replace method for "object" columns.
df["column"] = df["column"].str.replace(",","").astype(float)Alternatively check out the pandas.to_numeric() function- I think this should work.
df["column"] = pd.to_numeric(df["column"])You can also pass arguments for error handling with the pd.to_numeric() function. See the pandas documentation on it.
First, make a function that can convert a single string element to a float:
valid = '1234567890.' #valid characters for a float
def sanitize(data):
return float(''.join(filter(lambda char: char in valid, data)))
Then use the apply method to apply that function to every entry in the column. Reassign to the same column if you want to overwrite your old data.
df['column'] = df['column'].apply(sanitize)you need to capture the digits into group (\d+),(\d+)
import re
items = ['Hello, world!', 'Warhammer 40,000', 'Codename 1,337']
for item in items:
item = re.sub(r'(\d+),(\d+)', r'\1\2', item)
print(item)
Results:
Hello, world!
Warhammer 40000
Codename 1337
Using @uingtea regex, but for pandas dataframe, you can do in this way:
import pandas as pd
import re
df = pd.DataFrame({'col':['Hello, world!', 'Warhammer 40,000', 'Codename 1,337']})
df['col'] = df['col'].apply(lambda x: re.sub(r'(\d+),(\d+)', r'\1\2', x))
Hey guys, I have a csv file with any number greater than 999 being listed as a string in the form “1,000”. including the quotes. I’m trying to get rid of these commas so I can turn them into an integer, however I’m unsure how to do it without touching the other commas used to seperate the values. Any suggestions? So far I have thought this out but it’s not quite right.
‘’’ import pandas as pd df = pd.read_csv(‘..., sep = “, “)
firstline = True
if firstline: firstline = False else: for line in df: if “,” in line[3]: #the column with the values line[3].replace(“,”, “ “)
‘’’
Sorry for the formatting I am on phone. Thanks for the help :)
You have to access the str attribute per http://pandas.pydata.org/pandas-docs/stable/text.html
df1['Avg_Annual'] = df1['Avg_Annual'].str.replace(',', '')
df1['Avg_Annual'] = df1['Avg_Annual'].str.replace('$', '')
df1['Avg_Annual'] = df1['Avg_Annual'].astype(int)
alternately;
df1['Avg_Annual'] = df1['Avg_Annual'].str.replace(',', '').str.replace('$', '').astype(int)
if you want to prioritize time spent typing over readability.
Shamelessly stolen from this answer... but, that answer is only about changing one character and doesn't complete the coolness: since it takes a dictionary, you can replace any number of characters at once, as well as in any number of columns.
# if you want to operate on multiple columns, put them in a list like so:
cols = ['col1', 'col2', ..., 'colN']
# pass them to df.replace(), specifying each char and it's replacement:
df[cols] = df[cols].replace({'\$': '', ',': ''}, regex=True)
@shivsn caught that you need to use regex=True; you already knew about replace (but also didn't show trying to use it on multiple columns or both the dollar sign and comma simultaneously).
This answer is simply spelling out the details I found from others in one place for those like me (e.g. noobs to python an pandas). Hope it's helpful.
I think you can add parameter thousands to read_csv, then values in columns Total Apples and Good Apples are converted to integers:
Maybe your separator is different, dont forget change it. If separator is whitespace, change it to sep='\s+'.
import pandas as pd
import io
temp=u"""Farm_Name;Total Apples;Good Apples
EM;18,327;14,176
EE;18,785;14,146
IW;635;486
L;33,929;24,586
NE;12,497;9,609
NW;30,756;23,765
SC;8,515;6,438
SE;22,896;17,914
SW;11,972;9,114
WM;27,251;20,931
Y;21,495;16,662"""
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), sep=";",thousands=',')
print df
Farm_Name Total Apples Good Apples
0 EM 18327 14176
1 EE 18785 14146
2 IW 635 486
3 L 33929 24586
4 NE 12497 9609
5 NW 30756 23765
6 SC 8515 6438
7 SE 22896 17914
8 SW 11972 9114
9 WM 27251 20931
10 Y 21495 16662
print df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 11 entries, 0 to 10
Data columns (total 3 columns):
Farm_Name 11 non-null object
Total Apples 11 non-null int64
Good Apples 11 non-null int64
dtypes: int64(2), object(1)
memory usage: 336.0+ bytes
None
try this:
locale.setlocale(locale.LC_NUMERIC, '')
df = df[['Farm Name']].join(df[['Total Apples', 'Good Apples']].applymap(locale.atof))
I am assuming that the text file you are reading is a csv file. What you can do is to use the thousands argument built in the pd.read_csv.
df = pd.concat([df, pd.read_csv(file, sep='\s+', header=None, skiprows=(0, 1)[is_h])],
axis=0,
ignore_index=True,
thousands=',')
You can simply replace the comma's with "" (an empty string)
example = "TEST 3,498,300 2.600"
example = example.replace(",", "")
print(a)
The code above prints
TEST 3498300 2.600
If you're reading in from csv then you can use the thousands arg:
df.read_csv('foo.tsv', sep='\t', thousands=',')
This method is likely to be more efficient than performing the operation as a separate step.
You need to set the locale first:
In [ 9]: import locale
In [10]: from locale import atof
In [11]: locale.setlocale(locale.LC_NUMERIC, '')
Out[11]: 'en_GB.UTF-8'
In [12]: df.applymap(atof)
Out[12]:
0 1
0 1200 4200.00
1 7000 -0.03
2 5 0.00
You can convert one column at a time like this :
df['colname'] = df['colname'].str.replace(',', '').astype(float)
With replace, we need regex=True because otherwise it looks for exact match in a cell, i.e., cells with , in them only:
>>> df["size"] = df["size"].replace(",", "", regex=True)
>>> df
number name size
0 1 Car 932123
1 2 Bike 100000
2 3 Truck 1032111
I am using python3 and Pandas module for handling this csv
Note that pandas.read_csv function has optional argument thousands, if , are used for denoting thousands you might set thousands="," consider following example
import io
import pandas as pd
some_csv = io.StringIO('value\n"1"\n"1,000"\n"1,000,000"\n')
df = pd.read_csv(some_csv, thousands=",")
print(df)
output
value
0 1
1 1000
2 1000000
For brevity I used io.StringIO, same effect might be achieved providing name of file with same content as first argument in io.StringIO.
You can use pandas.Series.str.replace then use pandas.Series.astype.
df['Active Cases'] = df['Active Cases'].str.replace(',', '').astype(int)
print(df['Active Cases'])
0 1741147
1 1755
2 95532
3 216022
4 208134
Name: Active Cases, dtype: int64
The operation is not inplace, you need assign the result back
df['Active Cases'] = df['Active Cases'].replace(',','').astype(np.int64)
If you want to make the replace more robust, like also replace possible spaces after comma, you can do Series.str.replace
df['Active Cases'] = df['Active Cases'].str.replace(', *', '', regex=True).astype(np.int64)
I would recommend do pd.to_numeric instead to avoid possible error
df['Active Cases'] = pd.to_numeric(df['Active Cases'].str.replace(', *', '', regex=True), errors='coerce')