Convert rows of data to headers in python while keeping the data

Question

I am trying to write a python script to transform some csv data. Didn't get too far using groupby or multiindex. I want to go from this:

A, 5000
B, 6000
C, 8000
D, 6000
A, 3000
B, 2000
C, 6000
D, 1000
A, 4000
B, 1000
C, 1000
D, 3000

To this:

A      B      C      D
5000   6000   8000   6000
3000   2000   6000   1000
4000   1000   1000   3000

https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe (Q10) — Panda Kim, May 12 '23 at 14:07
You changed your input / output data. My answer works for your original question. You have a column header named 'cat' with no data then in the output you have a 'cat' column header with 'animal' in the column. This skews your question. — Captain Caveman, May 12 '23 at 14:12
yeah, sorry about that. Would i need to make a new question? — Nycbros, May 12 '23 at 14:16
Yes, a new question is probably best at this point. Although given my solution you may be able to tweak it for other datasets. — Captain Caveman, May 12 '23 at 14:34

score 1 · Accepted Answer · answered May 12 '23 at 14:08

You can convert the data to a dataframe using df.pivot in the format you requested and output the data to a CSV.

import pandas as pd

data = [
    ['A', 5000],
    ['B', 6000],
    ['C', 8000],
    ['D', 6000],
    ['A', 3000],
    ['B', 2000],
    ['C', 6000],
    ['D', 1000],
    ['A', 4000],
    ['B', 1000],
    ['C', 1000],
    ['D', 3000]
]

# Create a dataframe from the data
df = pd.DataFrame(data, columns=['Letters', 'Data'])

# Pivot the dataframe to make letters as column headers
df_pivot = df.pivot(index=None, columns='Letters', values='Data')

# Save the pivoted dataframe to a CSV file
df_pivot.to_csv('output.csv', index=False)

Output:

A, B, C, D
5000, 6000, 8000, 6000
3000, 2000, 6000, 1000
4000, 1000, 1000, 3000

Convert rows of data to headers in python while keeping the data

1 Answers1