I have a spreadsheet like this:
Locality 2005 2006 2007 2008 2009
ABBOTSFORD 427000 448000 602500 600000 638500
ABERFELDIE 534000 600000 735000 710000 775000
AIREYS INLET459000 440000 430000 517500 512500
I don't want to manually swap the column with the row. Could it be possible to use pandas reading data to a list as this:
data['ABBOTSFORD']=[427000,448000,602500,600000,638500]
data['ABERFELDIE']=[534000,600000,735000,710000,775000]
data['AIREYS INLET']=[459000,440000,430000,517500,512500]
You can change the index as explained already using set_index
.
You don't need to manually swap rows with columns, there is a transpose (data.T
) method in pandas that does it for you:
> df = pd.DataFrame([['ABBOTSFORD', 427000, 448000],
['ABERFELDIE', 534000, 600000]],
columns=['Locality', 2005, 2006])
> newdf = df.set_index('Locality').T
> newdf
Locality ABBOTSFORD ABERFELDIE
2005 427000 534000
2006 448000 600000
then you can fetch the dataframe column values and transform them to a list:
> newdf['ABBOTSFORD'].values.tolist()
[427000, 448000]
You can set the column index using index_col parameter available while reading from spreadsheet in Pandas.
Here is my solution:
Firstly, import pandas as pd:
import pandas as pd
Read in filename using pd.read_excel() (if you have your data in a spreadsheet) and set the index to 'Locality' by specifying the index_col parameter.
df = pd.read_excel('testexcel.xlsx', index_col=0)
At this stage if you get a 'no module named xlrd' error, install it using pip install xlrd
.
For visual inspection, read the dataframe using df.head()
which will print the following output
Now you can fetch the values of the desired columns of the dataframe and print it
Source: Stackoverflow.com