transpose csv based on one column

Question

I saw a lot of examples transposing the data but not something similar. I am trying to transpose my csv data which looks like--

Region,1989,1900,2000
new york,3,4,5
atlanta,7,9,0
chicago,8,9,10

to

Region,Year,value
new york,1989,3
new york,1900,4
new york,2000,5
atlanta,1989,7
atlanta,1900,9
atlanta,2000,0

score 0 · Accepted Answer · answered Nov 13 '18 at 16:36

0

You are trying to convert from wide data format to long data format. This can be done using melt function. If you have some columns that needs to be eliminated, you can slice the dataframe to the columns of interest.

Code:

#creating df
df = pd.DataFrame(data = {'Region':['new york', 'atlanta', 'chicago'], '1989': [3,7,8], '1900': [4,9,9], '2000': [5,0,10]})

#converting to long format
long_df = pd.melt(df, id_vars=['Region'], value_vars=['1989', '1900', '2000'], var_name='year', value_name = 'value')

Output:

answered Nov 13 '18 at 16:36

Naveen

1,190
7
20

In my opinion `value_vars=['1989', '1900', '2000']` should be omit – jezrael Nov 13 '18 at 16:48
I just wanted to show that we can select the columns we want. – Naveen Nov 13 '18 at 16:50
yes, I understand, but if there is more columns and need melt by all columns without `Region` – jezrael Nov 13 '18 at 16:51

transpose csv based on one column

1 Answers1