Ad

Transpose Pipe-separated Csv In Python

- 1 answer

I have a pipe separated csv file that I need to transpose before loading elsewhere. How to I change the row to columns on each pipe "|"?

So the three lines I want to transpose are in csv like below:

CountryCode|AFG| ALB| DZA
CountryISO2|AF| AL| DZ
CountryName|Afghanistan| Albania| Algeria

and I am after:

Country Code|CountryISO2|CountryName
AFG         |AF         |Afghanistan
ALB         |AL         |Albania
DZA         |DZ         |Algeria

I've tried using pandas DataFrame and transposing but this takes all the values and loads into a single cell.

df_csv = pd.DataFrame(data=csv)
transposed_csv = df_csv.T
print(transposed_csv)

ends up like this:

                        0                      1
[CountryCode|AFG| ALB|] [CountryISO2|AF| AL|] [CountryName|Afghanistan| Albania]
Ad

Answer

It seems that your problem is that the dataframe is not properly loaded. You have to read csv with sep='|' and then you can transpose it.

df_csv = pd.read_csv(data=file.csv, sep='|')

df_csv.T


0   1
CountryCode CountryISO2 CountryName
AFG AF  Afghanistan
ALB AL  Albania
DZA DZ  Algeria
Ad
source: stackoverflow.com
Ad