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
– Philipp Schwarz, Commented May 15, 2016 at 22:20
@ Parfait, did you test this one the dataset I provided in your environment? It does not work for me. — Philipp Schwarz
– Philipp Schwarz, Commented May 16, 2016 at 12:21

bers · Accepted Answer · 2022-03-03 12:39:25Z

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 3, 2022 at 12:39

bers

6,3093 gold badges47 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wjandrea · Accepted Answer · 2024-02-04 16:17:19Z

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

edited Feb 4, 2024 at 16:17

wjandrea

34k10 gold badges69 silver badges105 bronze badges

answered Sep 13, 2017 at 12:51

wailord

4274 silver badges5 bronze badges

3 Comments

bers Over a year ago

If you know columns, then you can use pd.read_csv(..., names=columns).

wjandrea Over a year ago

@bers This code only changes a subset, not necessarily all columns

bers Over a year ago

I suspect you are talking about your code, in which case you are correct. The OP's solution posted before your answer is test_matrix.rename(columns=int, inplace=True), so I suspect we are talking about renaming all columns.

Collectives™ on Stack Overflow

converting column names to integer with read_csv

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related