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:\1WORKING\FINANCIAL ANALYST\Shawn Schreier\Commodity 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')

Inspect your dataframe, it seems that the separator is not correctly recognized. — Maximilian Peters, Jul 21 '20 at 17:01
The data you are reading is not delimited by tabs, but my a variable number of spaces, so does this answer your question? [How to read file with space separated values in pandas](https://stackoverflow.com/questions/19632075/how-to-read-file-with-space-separated-values-in-pandas) — msanford, Jul 21 '20 at 17:03

score 0 · Accepted Answer · answered Jul 21 '20 at 18:52

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):

Thank you! This gets me to where I can do what I need to do. — Shawn Schreier, Jul 22 '20 at 01:05

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

1 Answers1