I am trying to read in this dataset
path = "https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/1165013/UK_Sanctions_List.ods"
By using this code (I have seen there are quite a lot of threads/suggested solutions to this around, but the following one seems to be the most reasonable one):
encoding_list = ['ascii', 'big5', 'big5hkscs', 'cp037', 'cp273', 'cp424', 'cp437', 'cp500', 'cp720', 'cp737'
, 'cp775', 'cp850', 'cp852', 'cp855', 'cp856', 'cp857', 'cp858', 'cp860', 'cp861', 'cp862'
, 'cp863', 'cp864', 'cp865', 'cp866', 'cp869', 'cp874', 'cp875', 'cp932', 'cp949', 'cp950'
, 'cp1006', 'cp1026', 'cp1125', 'cp1140', 'cp1250', 'cp1251', 'cp1252', 'cp1253', 'cp1254'
, 'cp1255', 'cp1256', 'cp1257', 'cp1258', 'euc_jp', 'euc_jis_2004', 'euc_jisx0213', 'euc_kr'
, 'gb2312', 'gbk', 'gb18030', 'hz', 'iso2022_jp', 'iso2022_jp_1', 'iso2022_jp_2'
, 'iso2022_jp_2004', 'iso2022_jp_3', 'iso2022_jp_ext', 'iso2022_kr', 'latin_1', 'iso8859_2'
, 'iso8859_3', 'iso8859_4', 'iso8859_5', 'iso8859_6', 'iso8859_7', 'iso8859_8', 'iso8859_9'
, 'iso8859_10', 'iso8859_11', 'iso8859_13', 'iso8859_14', 'iso8859_15', 'iso8859_16', 'johab'
, 'koi8_r', 'koi8_t', 'koi8_u', 'kz1048', 'mac_cyrillic', 'mac_greek', 'mac_iceland', 'mac_latin2'
, 'mac_roman', 'mac_turkish', 'ptcp154', 'shift_jis', 'shift_jis_2004', 'shift_jisx0213', 'utf_32'
, 'utf_32_be', 'utf_32_le', 'utf_16', 'utf_16_be', 'utf_16_le', 'utf_7', 'utf_8', 'utf_8_sig']
for encoding in encoding_list:
worked = True
try:
df = pd.read_csv(path, encoding=encoding, nrows=5)
print(df)
except:
worked = False
if worked:
print(encoding, ':\n', df.head())
But when I print the dataframe the results look unreadable, like this:
ËäjñEÎÜ'g
«sQğøÆÿŞmÿ´;Ğ´³µÇÇm®©sbH«iw... ¿`Òìş#mxOnBXvFî&ƪPÊz1á3uoj_g
¢x>æi7¸}Z«¤õÔ3ÎílW|ùÍx¡c;PÓ©kê+_ëͪ... NaN
qJ|HfÆzÖ¤c[¨ÿ`ÉŞ` *ª
b¾?]ÔüR~
¾GÌOmxÜ?=v좦Í` NaN
D¾Å¢
Æ·äÎQ´
ûò£^×%óÒ·$]qÓ´În[l'ß NaN
&.
ËäjñEÎ"'g
«sQ}øÆÿ@mÿ´;!´³µ¢¢m®©sbH«iw... ¿ýÒì¦ÖmxOnBXvFî&ƪPÊz1á3uoj_g
^x>æi7¸ðZ«€õÔ3ÎílW]ùÍx¡c;PÓ©kê+_ëͪ... NaN
qJ]HfÆz#€cǨÿýÉ@ý *ª
b¾?ÐÔ\Rö
¾GÌOmx"?=vì^þÍý NaN
D¾Å^
Æ·äÎQ´
ûò£¬×%óÒ·ÝÐqÓ´ÎnÇl'ß NaN
cp1140 :
&.
ËäjñEÎ"'g
«sQ}øÆÿ@mÿ´;!´³µ¢¢m®©sbH«iw... ¿ýÒì¦ÖmxOnBXvFî&ƪPÊz1á3uoj_g
^x>æi7¸ðZ«€õÔ3ÎílW]ùÍx¡c;PÓ©kê+_ëͪ... NaN
qJ]HfÆz#€cǨÿýÉ@ý *ª
b¾?ÐÔ\Rö
¾GÌOmx"?=vì^þÍý NaN
D¾Å^
Æ·äÎQ´
ûò£¬×%óÒ·ÝÐqÓ´ÎnÇl'ß NaN
Does anybody know how I can read it in by any chance?
This is not a CSV file, but rather an ODS (Open Document Spreadsheet) file.
You should use pandas.read_excel (ensuring the odpfy module is installed):
# pip install odfpy
df = pd.read_excel("UK_Sanctions_List_2.ods", skiprows=2)
NB. the process is quite slow, so be patient. The original file wasn't working for me but opening and saving it in LibreOffice did the trick. Another option would be to open the data in LibreOffice and to convert to CSV from there.
Output (first 5 rows):
Last Updated Unique ID OFSI Group ID UN Reference Number Name 6 Name 1 Name 2 Name 3 Name 4 Name 5 ... IMO number Current owner/operator (s) \
0 2022-01-12 AFG0001 12703 TAe.010 HAJI KHAIRULLAH HAJI SATTAR MONEY EXCHANGE NaN NaN NaN NaN NaN ... NaN NaN
1 2022-01-12 AFG0001 12703 TAe.010 HAJI KHAIRULLAH HAJI SATTAR MONEY EXCHANGE NaN NaN NaN NaN NaN ... NaN NaN
2 2022-01-12 AFG0001 12703 TAe.010 HAJI KHAIRULLAH HAJI SATTAR MONEY EXCHANGE NaN NaN NaN NaN NaN ... NaN NaN
3 2022-01-12 AFG0001 12703 TAe.010 HAJI KHAIRULLAH HAJI SATTAR MONEY EXCHANGE NaN NaN NaN NaN NaN ... NaN NaN
4 2022-01-12 AFG0001 12703 TAe.010 HAJI KHAIRULLAH HAJI SATTAR MONEY EXCHANGE NaN NaN NaN NaN NaN ... NaN NaN
Previous owner/operator (s) Current believed flag of ship Previous flags Type of ship Tonnage of ship Length of ship Year Built Hull identification number (HIN)
0 NaN NaN NaN NaN NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN NaN NaN NaN
3 NaN NaN NaN NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN NaN NaN NaN