Unreadable test with list of encoding in pandas

Question

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?

score 1 · Accepted Answer · answered Jun 26 '23 at 08:13

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

Unreadable test with list of encoding in pandas

1 Answers1