Read text file into excel without putting all data in first column

Question

I have written some code that requests a text file from the web, reads it, and outputs the necessary data into an excel spreadsheet. However, it is not quite in the format I need as it is writing each row as one item, and putting it all into the first column. My code is below, along with an image of the output as my code currently stands. I'd like a column for the date, and then one for each livestock animal with the amount processed.
import pandas as pd
import os
import urllib.request

filename = "Livestock Slaughter.txt"
os.chdir(r'S:1WORKINGFINANCIAL ANALYSTShawn SchreierCommodity Dashboard')
directory = os.getcwd()
url = 'https://www.ams.usda.gov/mnreports/sj_ls710.txt'
data=urllib.request.urlretrieve(url, "Slaughter Rates.txt")
df = pd.read_csv("Slaughter Rates.txt", sep='t', skiprows=5, nrows=3)
df.to_excel('Slaughter Data.xlsx')

Andrej Kesely · Answer

As in comments, you can use delim_whitespace=True to load the CSV and then do some post-processing to get correct data. You can also put URL to pd.read_csv() directly:
import pandas as pd

df = pd.read_csv('https://www.ams.usda.gov/mnreports/sj_ls710.txt', delim_whitespace=True, skiprows=5, nrows=3).reset_index()
df = pd.concat([df.loc[:, 'level_0':'level_2'].agg(' '.join, axis=1), df.iloc[:, 3:]], axis=1)

print(df)
df.to_csv('data.csv')

Prints:
                         0   CATTLE CALVES     HOGS  SHEEP
0  Tuesday 07/21/2020 (est  118,000  2,000  478,000  7,000
1           Week ago (est)  119,000  2,000  475,000  8,000
2           Year ago (act)  121,000  3,000  476,000  8,000

And saves the data as data.csv (screenshot from LibreOffice):

Read text file into excel without putting all data in first column

One Answer

Add your own answers!

Ask a Question