Reading csv file with multiple delimiters in pandas
![Reading csv file with multiple delimiters in pandas](https://vucavucalife.com/wp/wp-content/uploads/2020/04/panda-813749_1920-890x500.jpg)
Introduction
This is a memorandum about reading a csv file with read_csv of Python pandas with multiple delimiters.
specifying the delimiter using sep (or delimiter) with stuffing these delimiters into “[]”
So I’ll try it right away.
Details
Suppose I have the following csv file (tempo.csv) and I want to read it as separated with some delimiters (the right side of the time has a tab).
s1,s2;s3,datetime f1,f2,f3 a,b;c,2020/07/27 03:00 1.2,3.4,5.6 d,e;f,2021/09/28 13:03 2.3,4.5,6.7 g,h;i,2022/11/29 23:45 3.4,5.6,7.8
Here, let’s use the following seven types of delimiters to separate them.
“,” “;” “/” ” ” (space) “:” “t”(tab) “.”
How to specify the delimiter with sep (or delimiter) is just writing multiple delimiters in [] like this.
sep = “[]”
And specify engine =’python’ together.
# import pandas
import pandas as pd
#specifying the delimiter with sep (or delimiter), put multiple delimiters into "[ ]" .
#and specify engine ='python'
df = pd.read_csv("tempo.csv", sep = "[,;/ :t.]", engine='python')
df
![csv_delimiter](https://vucavucalife.com/wp/wp-content/uploads/2020/07/20200728csv_01.png)
Done!
By the way, if you read a file without specifying anything, the default delimiter will be
“,”
Therefore
# import pandas
import pandas as pd
#default delimiter is ","
df = pd.read_csv("tempo.csv")
df
![csv_default](https://vucavucalife.com/wp/wp-content/uploads/2020/07/20200728csv_02.png)
It will be like above.
And more;reorder header
You may already know by now… Reading a csv file as divided by multiple delimiters, the column header will be shifted and indexed weirdly….
![csv_delimiter](https://vucavucalife.com/wp/wp-content/uploads/2020/07/20200728csv_01.png)
So, replace the header with a list of column names according to the newly generated columns.
# import pandas
import pandas as pd
#Create a list of colmumn names in advance
cols = ["s1","s2","s3","year","month","day","h","m","f1-1","f1-2","f2-1","f2-2","f3-1","f3-2"]
#Specify the list of column names as "names"
df = pd.read_csv("tempo.csv", sep = "[,;/ :t.]", engine='python', names = cols)
#drop original header
df.drop(df.index[0], inplace = True)
df
![csv_revised](https://vucavucalife.com/wp/wp-content/uploads/2020/07/20200728csv_03.png)
All done!
Reference site. Thank you.
https://stackoverflow.com/questions/26551662/import-text-to-pandas-with-multiple-delimiters
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html?highlight=delimiter%20csv
Environment
Python; 3.7.2
pandas; 1.0.5
ちょっと広告です
https://business.xserver.ne.jp/
![](https://www13.a8.net/0.gif?a8mat=3BDOYJ+ECSAMY+CO4+6LHDU)
https://www.xdomain.ne.jp/
![](https://www13.a8.net/0.gif?a8mat=3BFNHP+G9UKT6+CO4+15UCEA)
★LOLIPOP★
![](https://www12.a8.net/0.gif?a8mat=3BHQOS+GDF6FU+348+5ZMCI)
.tokyo
![](https://www15.a8.net/0.gif?a8mat=35HCWD+9N3R8A+50+2HT8LU)
MuuMuu Domain!
![](https://www11.a8.net/0.gif?a8mat=3BHQOS+GEM1NE+348+1BNJ9E)