奇妙な改行文字が入って適切に改行されていない入力ファイルを直したい

Question

お世話になります。
初心者です。pandasになれてきたと思うのですが、とあるcsvファイルが、奇妙な改行コードを持っているために、
適切に改行されません。Windowsのサクラエディタなどで見ても普通に改行しているのですが、jupyter notebookで
該当部分を見ると例えば、以下のように\w\nとなって、適切に改行しておりません。

ol_0011\r\ns02264,2020-09-02 15:43:09"

入力ファイルをhexファイルにしたのが以下のものです。
###########################################################
0000 83 86 81 5b 83 55 81 5b 49 44 2c 89 f1 93 9a 8e ...[.U.[ID,.....
0010 9e 8d 8f 2c 46 6f 72 6d 4e 6f 2c 73 69 73 65 74 ...,FormNo,siset
0020 75 43 6f 64 65 2c 73 69 73 65 74 75 4e 61 6d 65 uCode,sisetuName
0030 2c 64 72 43 6f 64 65 2c 64 72 4e 61 6d 65 2c 6b ,drCode,drName,k
0040 61 6d 6f 6b 75 43 6f 64 65 2c 6b 61 6d 6f 6b 75 amokuCode,kamoku
0050 4e 61 6d 65 2c 74 61 62 64 61 74 65 2c 74 61 62 Name,tabdate,tab
0060 5f 6e 6f 2c 73 6c 69 64 65 72 5f 31 2c 73 6c 69 _no,slider_1,sli
0070 64 65 72 5f 32 2c 73 6c 69 64 65 72 5f 33 2c 73 der_2,slider_3,s
0080 6c 69 64 65 72 5f 34 2c 73 6c 69 64 65 72 5f 35 lider_4,slider_5
0090 2c 63 6e 74 5f 6e 65 77 2c 63 6e 74 5f 74 6f 74 ,cnt_new,cnt_tot
00a0 61 6c 2c 63 6f 6d 70 2c 75 73 65 72 5f 69 64 2c al,comp,user_id,
00b0 75 73 65 72 5f 69 64 5f 32 2c 2c 2c 2c 2c 2c 2c user_id_2,,,,,,,
00c0 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 0d 0a 69 73 ,,,,,,,,,,,,..is
00d0 6f 6c 5f 30 30 31 31 2c 32 30 32 30 2f 38 2f 32 ol_0011,2020/8/2
00e0 30 20 31 38 3a 34 38 2c 53 5f 30 30 30 38 2c 31 0 18:48,S_0008,1
00f0 2c 2c 31 2c 2c 31 2c 2c 32 30 32 30 2f 38 2f 32 ,,1,,1,,2020/8/2
0100 30 2c 31 2c 32 2c 34 2c 32 2c 32 2c 31 2c 31 2c 0,1,2,4,2,2,1,1,
0110 31 2c 30 2c 69 73 6f 6c 5f 30 30 31 31 2c 22 6f 1,0,isol_0011,"o
0120 6c 5f 30 30 31 31 0d 0a 73 30 32 32 36 34 2c 32 l_0011..s02264,2
0130 30 32 30 2d 30 39 2d 30 32 20 31 35 3a 34 33 3a 020-09-02 15:43:
0140 30 39 22 22 22 2c 53 5f 30 30 30 38 2c 31 2c 2c 09""",S_0008,1,,
0150 31 2c 2c 31 2c 2c 32 30 32 30 2f 39 2f 32 2c 31 1,,1,,2020/9/2,1
0160 2c 35 2c 35 2c 35 2c 33 2c 33 2c 31 30 30 2c 31 ,5,5,5,3,3,100,1
0170 30 30 2c 30 2c 73 30 32 32 36 34 2c 32 32 36 34 00,0,s02264,2264
0180 0d 0a 73 30 34 36 32 34 2c 32 30 32 30 2f 39 2f ..s04624,2020/9/
0190 31 20 31 39 3a 30 33 2c 53 5f 30 30 30 38 2c 31 1 19:03,S_0008,1
01a0 2c 2c 31 2c 2c 31 2c 2c 32 30 32 30 2f 39 2f 31 ,,1,,1,,2020/9/1
01b0 2c 31 2c 34 2c 32 2c 30 2c 33 2c 31 2c 2c 30 2c ,1,4,2,0,3,1,,0,
01c0 31 2c 73 30 34 36 32 34 2c 34 36 32 34 2c 2c 2c 1,s04624,4624,,,
01d0 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c ,,,,,,,,,,,,,,,,
01e0 0d 0a 73 31 30 36 34 37 2c 32 30 32 30 2f 39 2f ..s10647,2020/9/
01f0 32 20 31 30 3a 32 39 2c 53 5f 30 30 30 38 2c 31 2 10:29,S_0008,1
0200 2c 2c 31 2c 2c 31 2c 2c 32 30 32 30 2f 39 2f 32 ,,1,,1,,2020/9/2
0210 2c 31 2c 35 2c 33 2c 32 2c 33 2c 33 2c 30 2c 30 ,1,5,3,2,3,3,0,0
0220 2c 31 2c 73 31 30 36 34 37 2c 31 30 36 34 37 2c ,1,s10647,10647,
0230 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c ,,,,,,,,,,,,,,,,
0240 2c 2c 0d 0a 73 31 32 33 38 32 2c 32 30 32 30 2f ,,..s12382,2020/
0250 38 2f 32 35 20 31 35 3a 32 32 2c 53 5f 30 30 30 8/25 15:22,S_000
0260 38 2c 31 2c 2c 31 2c 2c 31 2c 2c 32 30 32 30 2f 8,1,,1,,1,,2020/
0270 38 2f 32 35 2c 31 2c 32 2c 34 2c 32 2c 33 2c 33 8/25,1,2,4,2,3,3
0280 2c 32 33 2c 32 33 2c 31 2c 73 31 32 33 38 32 2c ,23,23,1,s12382,
0290 31 32 33 38 32 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 12382,,,,,,,,,,,
02a0 2c 2c 2c 2c 2c 2c 2c 2c 0d 0a 73 31 32 33 38 32 ,,,,,,,,..s12382
02b0 2c 32 30 32 30 2f 38 2f 32 35 20 31 38 3a 30 32 ,2020/8/25 18:02
02c0 2c 53 5f 30 30 30 38 2c 31 2c 2c 31 2c 2c 31 2c ,S_0008,1,,1,,1,
02d0 2c 32 30 32 30 2f 38 2f 32 36 2c 32 2c 34 2c 34 ,2020/8/26,2,4,4
02e0 2c 33 2c 32 2c 32 2c 36 2c 32 39 2c 31 2c 73 31 ,3,2,2,6,29,1,s1
02f0 32 33 38 32 2c 31 32 33 38 32 2c 2c 2c 2c 2c 2c 2382,12382,,,,,,
0300 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 2c 0d 0a ,,,,,,,,,,,,,..
##################################################################################

エクセルでみるとやはり正しく改行されておりません。

これにより、次のレコードが同じ行に表示されてしまって、カラム数も倍に増えてしまっています。

確かめたことは、例えば、xfl_hearingというデータフレームで受けて、user_id_2のカラムを変換したのですが、
うまくいきませんでした。

xfl_hearing['user_id_2'] =xfl_hearing['user_id_2'].replace("\r\n","\n", regex=True)

などとしてみたのですが、\nになるだけで、適切に改行されませんでした。

xfl_hearing['user_id_2'].head()
を行うと
以下のように、1行目に2行目の情報がコンタミしています。
0 ol_0011\r\ns02264,2020-09-02 15:43:09"
1 4624
2 10647
3 12382
4 12382

どうすれば、この奇妙な改行コードを取り除くためにはどうすればよろしいでしょうか。
何卒、ご教示ただけますと幸いです。
宜しくお願い申し上げます。

Comments

[Daregada]

実際にJupyter Notebookでどのようなコードを実行しているのか質問文に追加する。
CSVファイルの一部(先頭から何行か)だけでも質問文に追加する。
コードやCSVファイルの内容は、それぞれ```だけの行と```だけの行で囲む。

[onosan]

ご連絡ありがとうございます。csvファイルそのものを添付することは可能でしょうか。ちょっと説明が難しく、csvを見ていただいた方が良いような気がしますが、可能でしたでしょうか。

[Daregada]

質問文を編集して、質問内容として記載してください。
ファイルの内容を16進表示したものを載せるには、コマンドプロンプトで、
certutil -f -encodehex XXXX.csv XXXX.hex 11
というコマンドを実行すると、XXXX.hexというファイルが作成されるので、その内容をエディタで開いてコピー&ペーストすればいいです。XXXXは実際のファイル名に合わせて変更してください。

Answer 1

CSVで、ダブルクォートで囲まれている部分は、カンマ、改行含めてひとつのセルとして扱われます。
なので、"ol_0011\r\ns02264,2020-09-02 15:43:09"""はひとかたまり扱いです。

ダブルクォート中の連続するダブルクォート""は、エスケープで、"一文字のことです。

この辺りは、CSVを扱うプログラムよって違うこともあるのですが、pandasでもそうなっています。(Excelも)

今回の場合、ダブルクォートは不要そうなので、エディタ(サクラエディタ)でダブルクォートを全部消してしまえばうまく動きそうに思います。

Comments

[onosan]

適切に動きました！問題が氷解しました。また勉強にもなりました。ありがとうございます！！

Answer 2

まず、pandas.read_csv自体は、改行コードが\nだけでも\r\nでも、それらが混在していても、問題なくデータを読み込めます。

今回の問題は、対象のCSVファイルに、"で囲まれた部分に\r\nが入ったデータが含まれていることです。2行目の"ol_0011の直後ですね。"に囲まれているので\r\nがそのままデータの一部として読み込まれて、3行目のデータが2行目の一部として扱われています。以下は、16進ダンプされた内容から元のCSVを復元したものです。

ユーザーID,回答時刻,FormNo,sisetuCode,sisetuName,drCode,drName,kamokuCode,kamokuName,tabdate,tab_no,slider_1,slider_2,slider_3,slider_4,slider_5,cnt_new,cnt_total,comp,user_id,user_id_2,,,,,,,,,,,,,,,,,,,
isol_0011,2020/8/20 18:48,S_0008,1,,1,,1,,2020/8/20,1,2,4,2,2,1,1,1,0,isol_0011,"ol_0011
s02264,2020-09-02 15:43:09""",S_0008,1,,1,,1,,2020/9/2,1,5,5,5,3,3,100,100,0,s02264,2264
s04624,2020/9/1 19:03,S_0008,1,,1,,1,,2020/9/1,1,4,2,0,3,1,,0,1,s04624,4624,,,,,,,,,,,,,,,,,,,
s10647,2020/9/2 10:29,S_0008,1,,1,,1,,2020/9/2,1,5,3,2,3,3,0,0,1,s10647,10647,,,,,,,,,,,,,,,,,,,
s12382,2020/8/25 15:22,S_0008,1,,1,,1,,2020/8/25,1,2,4,2,3,3,23,23,1,s12382,12382,,,,,,,,,,,,,,,,,,,
s12382,2020/8/25 18:02,S_0008,1,,1,,1,,2020/8/26,2,4,4,3,2,2,6,29,1,s12382,12382,,,,,,,,,,,,,,,,,,,

該当部分を"で囲む必要はなく、2行目の不要な"と3行目の"""を削除すれば、read_csvで読み込めました。元データはエンコーディングがシフトJISなので、読み込む際にはencoding="cp932"が必要です。

import pandas as pd

df = pd.read_csv("blended2_mod.csv", encoding="cp932", parse_dates=['回答時刻'], skipinitialspace=True)
print(df)

末尾にデータの存在しない項目が並んでいるのは、2,3行目が(すでに説明した理由で)結合していまった影響でしょうか。DataFrameで読み込んだ後で削除すればいいでしょう。

Comments

[onosan]

ご連絡がおくれてすみません。先に回答いただいた方をベストアンサーにさせていただきました。丁寧にご回答いただき、ありがとうございました。

Answer 3

改行コードというのは、
\r\n と、\n の2通りが一般的です
あなたの言う正しい改行コードというのは、どういうコードでしょうか.

Comments

[onosan]

ご連絡ありがとうございます。もう少し具体的に書きますと、本来は、ol_0011\r\ns02264,2020-09-02 15:43:09"と書いている部分で、ol_0011で改行して、次の行の最初のカラムにns02264が入るはずで、サクラエディタで見ても、確かにそうなっているのですが、read_csvでみると、なぜか、ol_0011\r\ns02264,2020-09-02とつながっているようなのです。それで質問させていただいた次第です。自分でも何が起きているのかが分からないという状態です。質問の意図が不明確ですみません。よろしくお願い申し上げます。

[y_waiwai]

なら、それを\nに変換すればいい、と言う回答になるかと思いますが、
それでなにが問題なんでしょうか。

[onosan]

すみません。それを実施するために、xfl_hearing['user_id_2'] =xfl_hearing['user_id_2'].replace("\r\n","\n", regex=True)

を行ったと書いたつもりなのですが、改行はされておりませんでした。何か対応策はありますでしょうか。よろしくお願い申し上げます。

[y_waiwai]

実際にそれでどういうデータになったか確認しよう。
バイナリエディタなどでどういうコードになってるのか確認してみたらどうでしょう

＃それで改行できてないと言われても困るわけで。

[onosan]

少し、説明が難しいのですが、実際にエディタで見ると質問で説明しているように、改行しているのです。しかし、jupyter notebookでみると改行しておらず、エクセルでも改行しておりません。\r\nを\nにしても、実際には、改行されていないようなのです。こんなことってあるのでしょうか。ということで質問させていただきました。