Pandas - Spacebar
I am using python csvkit
to compare 2 files:
df1 = pd.read_csv('input1.csv', sep=',\s+', delimiter=',', encoding="utf-8")
df2 = pd.read_csv('input2.csv', sep=',\s,', delimiter=',', encoding="utf-8")
df3 = pd.merge(df1,df2, on='employee_id', how='right')
df3.to_csv('output.csv', encoding='utf-8', index=False)
I am currently running the file using a script before stripping the spaces from the column employee_id
.
Example employee_id
s:
37 78973 3
23787
2 22 3
123
Is there a way to get csvkit
this and keep the step for me?
source to share
You can do a strip()
whole lot in Pandas with . str.strip () :
df1['employee_id'] = df1['employee_id'].str.strip()
df2['employee_id'] = df2['employee_id'].str.strip()
This will remove the leading / trailing spaces in a column employee_id
in df1
, and indf2
Alternatively, you can change your strings read_csv
to also useskipinitialspace=True
df1 = pd.read_csv('input1.csv', sep=',\s+', delimiter=',', encoding="utf-8", skipinitialspace=True)
df2 = pd.read_csv('input2.csv', sep=',\s,', delimiter=',', encoding="utf-8", skipinitialspace=True)
It looks like you are trying to remove spaces in a string containing numbers. You can do it:
df1['employee_id'] = df1['employee_id'].str.replace(" ","")
df2['employee_id'] = df2['employee_id'].str.replace(" ","")
source to share
You can do strip()
in pandas.read_csv()
like:
pandas.read_csv(..., converters={'employee_id': str.strip})
And if you just need to pad the leading spaces:
pandas.read_csv(..., converters={'employee_id': str.lstrip})
And remove all spaces:
def strip_spaces(a_str_with_spaces):
return a_str_with_spaces.replace(' ', '')
pandas.read_csv(..., converters={'employee_id': strip_spaces})
source to share