Hot to transpose rows to columns in python in a csv file

Question

I have the following script that gives me a csv file containing article id , the html code of the article , the url of the article , the date when the article has been created, when the article was last udpated, and the author of the article,

Instead of getting all the outputs in one column I would like each output to be displayed in their own column (transposed).

  A.     B.      C.  
1| item | body   | url ...
2| 1234.| <html> | https://..

As you can see in my script, I tried to play around with data transposition but unfortunately the result is the same.

import requests
import csv
import unicodedata
import getpass

# Set the request parameters
url = ''

# Credentials
user = ''
pwd = ''

# Path of the outputted csv file
csvfile = 'export.csv'

# This loop cycles through all pages of articles, converts the unicode
# to an integer, and writes the integers to an array
output_1 = []
output_1.append("item")
output_2 = []
output_2.append("body")
output_3 = []
output_3.append("html_url")
output_4 = []
output_4.append("created_at")
output_5 = []
output_5.append("updated_at")
output_6 = []
output_6.append("author")

while url:
    response = requests.get(url, auth=(user, pwd))
    data = response.json()
    for article in data['articles']:
        article_id = article['id']
        decode_1 = int(article_id)
        output_1.append(decode_1)
    for article in data['articles']:
        body = article['body']
        decode_2 = unicodedata.normalize('NFKD', body).encode("utf-8")
        output_2.append(decode_2)
    for article in data['articles']:
        html_url = article['html_url']
        decode_3 =unicodedata.normalize('NFKD', html_url)
        output_3.append(decode_3)
    for article in data['articles']:
        created_at = article['created_at']
        decode_4 = unicodedata.normalize('NFKD', created_at)
        output_4.append(decode_4)
    for article in data['articles']:
        updated_at = article['updated_at']
        decode_5 = unicodedata.normalize('NFKD', updated_at)
        output_5.append(decode_5)
    for article in data['articles']:
            author_id = article['author_id']
            decode_6 = int(author_id)
            output_6.append(decode_6)

    print(data['next_page'])
    url = data['next_page']

print("Number of articles:")
print(len(output_1))

# Data Transposition
#nontransposed_data = [("Article ID","Article Body","URL","Created At","Updated At","Author Id"), [output_1], [output_2], [output_3],[output_4],[output_5],[output_6]]
#transposed_data = zip(*nontransposed_data)

# Write to a csv file
with open(csvfile, 'w') as fp:
    writer = csv.writer(fp, dialect='excel')

    writer.writerow([output_1])
    writer.writerow([output_2])
    writer.writerow([output_3])
    writer.writerow([output_4])
    writer.writerow([output_5])
    writer.writerow([output_6])

Use the `T` attribute or the `transpose()` in pandas library. Then use `to_csv()` function to save into a new car file. — yoonghm, Apr 06 '20 at 10:55
Have you considered using pandas? https://pandas.pydata.org/ — Lior Pollak, Apr 06 '20 at 11:16

score 1 · Answer 1 · answered Apr 06 '20 at 11:48

Please note that I also highly recommend that you use the pandas library.

However, if you want to transpose a list of lists in plain python, this can also be done easily.

nontransposed_data = [('Article ID', 'Article Body', 'URL', 'Created At', 'Updated At', 'Author Id'), ['row 0 col 0', 'row 0 col 1', 'row 0 col 2', 'row 0 col 3', 'row 0 col 4', 'row 0 col 5'], ['row 1 col 0', 'row 1 col 1', 'row 1 col 2', 'row 1 col 3', 'row 1 col 4', 'row 1 col 5'], ['row 2 col 0', 'row 2 col 1', 'row 2 col 2', 'row 2 col 3', 'row 2 col 4', 'row 2 col 5']]


transposed_data = list(map(list, zip(*nontransposed_data)))
transposed_data 

>>> [['Article ID', 'row 0 col 0', 'row 1 col 0', 'row 2 col 0'], ['Article Body', 'row 0 col 1', 'row 1 col 1', 'row 2 col 1'], ['URL', 'row 0 col 2', 'row 1 col 2', 'row 2 col 2'], ['Created At', 'row 0 col 3', 'row 1 col 3', 'row 2 col 3'], ['Updated At', 'row 0 col 4', 'row 1 col 4', 'row 2 col 4'], ['Author Id', 'row 0 col 5', 'row 1 col 5', 'row 2 col 5']]

As demonstrated here: Transpose nested list in python

Hot to transpose rows to columns in python in a csv file

1 Answers1