[Tut] How to Convert a Log to a CSV File in Python? - Printable Version +- Sick Gaming (https://www.sickgaming.net) +-- Forum: Programming (https://www.sickgaming.net/forum-76.html) +--- Forum: Python (https://www.sickgaming.net/forum-83.html) +--- Thread: [Tut] How to Convert a Log to a CSV File in Python? (/thread-99878.html) |
[Tut] How to Convert a Log to a CSV File in Python? - xSicKxBot - 08-30-2022 How to Convert a Log to a CSV File in Python? <div> <div class="kk-star-ratings kksr-auto kksr-align-left kksr-valign-top" data-payload="{"align":"left","id":"618493","slug":"default","valign":"top","ignore":"","reference":"auto","class":"","count":"1","readonly":"","score":"5","best":"5","gap":"5","greet":"Rate this post","legend":"5\/5 - (1 vote)","size":"24","width":"142.5","_legend":"{score}\/{best} - ({count} {votes})","font_factor":"1.25"}"> <div class="kksr-stars"> <div class="kksr-stars-inactive"> <div class="kksr-star" data-star="1" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> <div class="kksr-star" data-star="2" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> <div class="kksr-star" data-star="3" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> <div class="kksr-star" data-star="4" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> <div class="kksr-star" data-star="5" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> </p></div> <div class="kksr-stars-active" style="width: 142.5px;"> <div class="kksr-star" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> <div class="kksr-star" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> <div class="kksr-star" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> <div class="kksr-star" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> <div class="kksr-star" style="padding-right: 5px"> <div class="kksr-icon" style="width: 24px; height: 24px;"></div> </p></div> </p></div> </div> <div class="kksr-legend" style="font-size: 19.2px;"> 5/5 – (1 vote) </div> </div> <p><em><strong>A not-so-fictious problem</strong>: Say, you’ve created a web application that runs on a dedicated Linux server in the <a href="https://blog.finxter.com/top-13-attractive-cloud-developer-job-roles-2023/" data-type="post" data-id="410160" target="_blank" rel="noreferrer noopener">cloud</a>. Thousands of users visit your <a href="https://blog.finxter.com/how-to-create-an-interactive-web-application-using-jupyter-notebook/" data-type="post" data-id="24200" target="_blank" rel="noreferrer noopener">web app</a> and suddenly … it crashes. Your users start complaining, and you lose revenue. More importantly, you bleed credibility by the hour. Your server is down, so what do you do?</em> <img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f92f.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p> <p class="has-base-background-color has-background"><strong>First</strong>, don’t panic. <img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f6f8.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p> <p>Let’s analyze your server logs!</p> <p>This article shows you <strong>how to convert your log file to a CSV file</strong> in Python, that you can use for further processing (e.g., in Pandas or Excel). </p> <h2>Problem Formulation by Example</h2> <p>Given a file <code>my_file.log</code> like this one I pulled from a real <a rel="noreferrer noopener" href="https://www.ibm.com/docs/en/zos/2.1.0?topic=problems-example-log-file" data-type="URL" data-id="https://www.ibm.com/docs/en/zos/2.1.0?topic=problems-example-log-file" target="_blank">IBM server log</a> example:</p> <pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">03/22 08:51:01 INFO :.main: *************** RSVP Agent started *************** 03/22 08:51:01 INFO :...locate_configFile: Specified configuration file: /u/user10/rsvpd1.conf 03/22 08:51:01 INFO :.main: Using log level 511 03/22 08:51:01 INFO :..settcpimage: Get TCP images rc - EDC8112I Operation not supported on socket. 03/22 08:51:01 INFO :..settcpimage: Associate with TCP/IP image name = TCPCS</pre> <p>How to convert this log file to a CSV file of the following standard comma-separated values format:</p> <pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">03/22,08:51:01,INFO,:.main: *************** RSVP Agent started *************** 03/22,08:51:01,INFO,:...locate_configFile: Specified configuration file: /u/user10/rsvpd1.conf 03/22,08:51:01,INFO,:.main: Using log level 511 03/22,08:51:01,INFO,:..settcpimage: Get TCP images rc - EDC8112I Operation not supported on socket. 03/22,08:51:01,INFO,:..settcpimage: Associate with TCP/IP image name = TCPCS </pre> <p>Or, here’s how that would look if you opened it with Excel:</p> <div class="wp-block-image"> <figure class="aligncenter size-large"><img loading="lazy" width="1024" height="158" src="https://blog.finxter.com/wp-content/uploads/2022/08/image-90-1024x158.png" alt="" class="wp-image-618634" srcset="https://blog.finxter.com/wp-content/uploads/2022/08/image-90-1024x158.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/08/image-90-300x46.png 300w, https://blog.finxter.com/wp-content/uploads/2022/08/image-90-768x119.png 768w, https://blog.finxter.com/wp-content/uploads/2022/08/image-90.png 1082w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure> </div> <p>Prettier, isn’t it? Unlike the first representation (log file), this CSV representation is easier to read for (most) human beings. <img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f916.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p> <h2>Convert Server Log to CSV with Pandas</h2> <p><strong>You can convert a <code>.log</code> file to a CSV file in Python in four simple steps: (1) <a rel="noreferrer noopener" href="https://blog.finxter.com/how-to-install-pandas-in-python/" target="_blank">Install the Pandas </a>library, (2) import the Pandas library, (3) <a rel="noreferrer noopener" href="https://blog.finxter.com/read-a-csv-file-to-a-pandas-dataframe/" data-type="post" data-id="440655" target="_blank">read the log file</a> as DataFrame, and (4) <a rel="noreferrer noopener" href="https://blog.finxter.com/pandas-dataframe-to_csv-method/" data-type="post" data-id="344277" target="_blank">write</a> the DataFrame to the CSV file</strong>.</p> <ol class="has-global-color-8-background-color has-background"> <li>(Optional in shell) <code>pip install pandas</code></li> <li><code>import pandas as pd</code></li> <li><code>df = pd.read_csv('my_file.log', sep='\s\s+', engine='python')</code></li> <li><code>df.to_csv('my_file.csv', index=None)</code></li> </ol> <p>Here’s a minimal example:</p> <pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pandas as pd df = pd.read_csv('my_file.log', sep='\s\s+', engine='python') df.to_csv('my_file.csv', index=None)</pre> <p class="has-base-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/2139.png" alt="ℹ" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Note</strong>: The <a rel="noreferrer noopener" href="https://blog.finxter.com/python-regex/" data-type="post" data-id="6210" target="_blank">regular expression</a> <code>sep='\s\s+'</code> specifies more than one single <a rel="noreferrer noopener" href="https://blog.finxter.com/regex-special-characters-examples-in-python-re/" data-type="post" data-id="6421" target="_blank">whitespace</a> as a separator between two CSV values. If you have a different separator string, you can define it here. </p> <p>You specify the <code>engine='python'</code> to tell Pandas that we want the Python regular expression engine to process the separator regular expression.</p> <p>The result of the code is the following CSV file:</p> <div class="wp-block-image"> <figure class="aligncenter size-full"><img loading="lazy" width="721" height="343" src="https://blog.finxter.com/wp-content/uploads/2022/08/image-89.png" alt="" class="wp-image-618613" srcset="https://blog.finxter.com/wp-content/uploads/2022/08/image-89.png 721w, https://blog.finxter.com/wp-content/uploads/2022/08/image-89-300x143.png 300w" sizes="(max-width: 721px) 100vw, 721px" /></figure> </div> <p>You can use this CSV file as input for, say, an Excel sheet or Google Spreadsheet for further processing and analysis.</p> <p>This is what your log file looks converted to a CSV and imported to Excel:</p> <figure class="wp-block-image size-large"><img loading="lazy" width="1024" height="158" src="https://blog.finxter.com/wp-content/uploads/2022/08/image-91-1024x158.png" alt="" class="wp-image-618643" srcset="https://blog.finxter.com/wp-content/uploads/2022/08/image-91-1024x158.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/08/image-91-300x46.png 300w, https://blog.finxter.com/wp-content/uploads/2022/08/image-91-768x119.png 768w, https://blog.finxter.com/wp-content/uploads/2022/08/image-91.png 1082w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure> <p>And this is how your log file looks as a <a href="https://blog.finxter.com/how-to-create-a-dataframe-in-pandas/" data-type="post" data-id="16764" target="_blank" rel="noreferrer noopener">Pandas DataFrame</a>:</p> <pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group=""> 03/22 ... :.main: *************** RSVP Agent started *************** 0 03/22 ... :...locate_configFile: Specified configuration... 1 03/22 ... :.main: Using log level 511 2 03/22 ... :..settcpimage: Get TCP images rc - EDC8112I O... 3 03/22 ... :..settcpimage: Associate with TCP/IP image na... [4 rows x 4 columns]</pre> <p class="has-base-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f30d.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Related Tutorial</strong>: <a href="https://blog.finxter.com/pandas-to_csv/" data-type="post" data-id="8027">Python Pandas DataFrame <code>to_csv()</code></a></p></p> </div> https://www.sickgaming.net/blog/2022/08/27/how-to-convert-a-log-to-a-csv-file-in-python/ |