pd.read_html changed number formatting

Viewed 70

Cannot get 1,2,3,4,5,6 from the column of CCCCCCC, after pd.read_html format changed to 123456, and my expected result should be keep 1,2,3,4,5,6

HTML code

html = """<html>
<body>
<div id="MMMMMMMM" class="MMMMMMMMMMM" style="">
        <table class="OOOOOOOO" style="">
            <thead>
                <tr class="PPPPPPPPPP">
                    <td colspan="3" style="font-size:14px;font-weight:bold;" class="QQQQQQQQQQ">AAAAAAA</td>
                </tr>
                <tr class="RRRRRRRRRR">
                    <td>BBBBBB</td>
                    <td>CCCCCCC</td>
                    <td>AAAAAAA</td>
                </tr>
            </thead>
            <tbody>
                    <tr class="SSSSSSSS">
                        <td rowspan="1">DDDDDD</td>
                        <td class="L_LLLL67">1,2,3,4,5,6</td>
                        <td class="L_LLLL67 f_tar">1234.56</td>
                    </tr>
                    <tr class="">
                        <td rowspan="3">EEEEEEEEE</td>
                        <td class="L_LLLL67">1,2,3,4,5,6</td>
                        <td class="L_LLLL67 f_tar">1234.56</td>
                    </tr>
                        <tr class="">
                            <td class="L_LLLL67">1,2,3,4,5,6</td>
                            <td class="L_LLLL67 f_tar">1234.56</td>
                        </tr>
                        <tr class="">
                            <td class="L_LLLL67">1,2,3,4,5,6</td>
                            <td class="L_LLLL67 f_tar">1234.56</td>
                        </tr>
                    <tr class="">
                        <td rowspan="1">FFFFFFFFF</td>
                        <td class="L_LLLL67">1,2,3,4,5,6</td>
                        <td class="L_LLLL67 f_tar">1234.56</td>
                    </tr>
                    <tr class="TTTTTT">
                        <td rowspan="1">GGGGGGGGG</td>
                        <td class="L_LLLL67">1,2,3,4,5,6</td>
                        <td class="L_LLLL67 f_tar">1234.56</td>
                    </tr>
                    <tr class="">
                        <td rowspan="1">HHHHHHHHH</td>
                        <td class="L_LLLL67">1,2,3,4,5,6</td>
                        <td class="L_LLLL67 f_tar">1234.56</td>
                    </tr>
                    <tr class="TTTTTTT">
                        <td rowspan="1">IIIIIIIIII</td>
                        <td class="L_LLLL67">1,2,3,4,5,6</td>
                        <td class="L_LLLL67 f_tar">1234.56</td>
                    </tr>
                    <tr class="">
                        <td rowspan="1">JJJJJJJJ</td>
                        <td class="L_LLLL67">1,2,3,4,5,6</td>
                        <td class="L_LLLL67 f_tar">1234.56</td>
                    </tr>
                    <tr class="TTTTT">
                        <td rowspan="2">KKKKKKKK</td>
                        <td class="L_LLLL67">1/2/3/4/5/6</td>
                        <td class="L_LLLL67 f_tar">1234.56</td>
                    </tr>
                        <tr class="TTTTTT">
                            <td class="L_LLLL67">1/2/3/4/5/6</td>
                            <td class="L_LLLL67 f_tar">1234.56</td>
                        </tr>
            </tbody>
        </table>
</body>
</html>"""

Python Code

from bs4 import BeautifulSoup
import pandas as pd

soup = BeautifulSoup(html,'html.parser')
table = soup.find('div', attrs={'id':'MMMMMMMM'})
df_list = pd.read_html(str(table), header=1)
df_list

Execution Result

 [        BBBBBB      CCCCCCC  AAAAAAA
 0       DDDDDD       123456  1234.56
 1    EEEEEEEEE       123456  1234.56
 2    EEEEEEEEE       123456  1234.56
 3    EEEEEEEEE       123456  1234.56
 4    FFFFFFFFF       123456  1234.56
 5    GGGGGGGGG       123456  1234.56
 6    HHHHHHHHH       123456  1234.56
 7   IIIIIIIIII       123456  1234.56
 8     JJJJJJJJ       123456  1234.56
 9     KKKKKKKK  1/2/3/4/5/6  1234.56
 10    KKKKKKKK  1/2/3/4/5/6  1234.56]

Expected Result

 [        BBBBBB      CCCCCCC  AAAAAAA
 0       DDDDDD       1,2,3,4,5,6  1234.56
 1    EEEEEEEEE       1,2,3,4,5,6  1234.56
 2    EEEEEEEEE       1,2,3,4,5,6  1234.56
 3    EEEEEEEEE       1,2,3,4,5,6  1234.56
 4    FFFFFFFFF       1,2,3,4,5,6  1234.56
 5    GGGGGGGGG       1,2,3,4,5,6  1234.56
 6    HHHHHHHHH       1,2,3,4,5,6  1234.56
 7   IIIIIIIIII       1,2,3,4,5,6  1234.56
 8     JJJJJJJJ       1,2,3,4,5,6  1234.56
 9     KKKKKKKK       1/2/3/4/5/6  1234.56
 10    KKKKKKKK       1/2/3/4/5/6  1234.56]
 
1 Answers

You need to add the thousands parameter and set it to None by default it's ','.

from bs4 import BeautifulSoup
import pandas as pd

soup = BeautifulSoup(html,'html.parser')
table = soup.find('div', attrs={'id':'MMMMMMMM'})
df_list = pd.read_html(str(table), header=1, thousands=None)
df_list
OUTPUT:
[        BBBBBB      CCCCCCC  AAAAAAA
 0       DDDDDD  1,2,3,4,5,6  1234.56
 1    EEEEEEEEE  1,2,3,4,5,6  1234.56
 2    EEEEEEEEE  1,2,3,4,5,6  1234.56
 3    EEEEEEEEE  1,2,3,4,5,6  1234.56
 4    FFFFFFFFF  1,2,3,4,5,6  1234.56
 5    GGGGGGGGG  1,2,3,4,5,6  1234.56
 6    HHHHHHHHH  1,2,3,4,5,6  1234.56
 7   IIIIIIIIII  1,2,3,4,5,6  1234.56
 8     JJJJJJJJ  1,2,3,4,5,6  1234.56
 9     KKKKKKKK  1/2/3/4/5/6  1234.56
 10    KKKKKKKK  1/2/3/4/5/6  1234.56]
Related