converting column names to integer with read_csv

Question

I have constructed a matrix with integer values for columns and index. The matrix is acutally hierachical for each month. My problem is that the indexing and selecting of data does not work anymore as before when I write the data to csv and then load as pandas dataframe.

Selecting data before writing and reading data to file:

matrix.ix[1][4][3] would for example give 123

In words select, month January and get me the (travel) flow from origin 4 to destination 3.

After writing and reading the data to csv and back into pandas, the original referencing fails but if I convert the column indexing to string it works:

matrix.ix[1]['4'][3]

... the column names have automatically been tranformed from integer into string. But I would prefer the original indexing. Any suggestions?

My current quick fix for handling the data after loading from csv is:

#Writing df to file
mulitindex_df_Travel_monthly.to_csv(r'result/Final_monthly_FlightData_countrylevel_v4.csv')


#Loading df from csv
test_matrix = pd.read_csv(filepath_inputdata+'/Final_monthly_FlightData_countrylevel_v4.csv', 
                                       index_col=[0, 1])


test_matrix.rename(columns = int, inplace = True) #Thx, @ayhan

CSV FILE: https://www.dropbox.com/s/4u2opzh65zwcn81/travel_matrix_SO.csv?dl=0

I added the code I am using to save the data and load it back into pandas. I am only specifiying the index_col. But there is at least a minor issue as well. Once loaded its adds me a empty row with name "Unnamed: 1" — Philipp Schwarz, May 15 '16 at 22:20
@ Parfait, did you test this one the dataset I provided in your environment? It does not work for me. — Philipp Schwarz, May 16 '16 at 12:21

score 2 · Answer 1 · answered Mar 03 '22 at 12:39

2

You could also do

df.columns = df.columns.astype(int)

or

df.columns = df.columns.map(int)

Related: what is difference between .map(str) and .astype(str) in dataframe

answered Mar 03 '22 at 12:39

bers

4,817
2
40
59

score 1 · Answer 2 · answered Sep 13 '17 at 12:51

1

I used something like this:

df = df.rename(columns={str(c): c for c in columns})

where:

df is pandas dataframe and columns are column to change

answered Sep 13 '17 at 12:51

wailord

377
3
5

If you know `columns`, then you can use `pd.read_csv(..., names=columns)`. – bers Mar 03 '22 at 12:38

converting column names to integer with read_csv

2 Answers2

Linked