Renaming column in Python SQL table

Question

I am appending data to a dataframe using the pandas library and am renaming my columns in the code below:

chunksize = 20000
j = 0
index_start = 1

for df in pd.read_csv('C:/Users/xxx/Desktop/jjj.tsv', chunksize=chunksize, header = None,sep='\t', iterator=True, encoding='utf-8'):
    df=df.rename(columns = {0:'screen',1:'user'})
    df.to_sql('data', disk_engine, if_exists='append')
    index_start = df.index[-1] + 1
    j=j+1
    if j >=2:
        break
    else:
        count = j*chunksize
        print(count)
        print(j)

However, when I execute a simple query:

df = pd.read_sql_query('SELECT * FROM data LIMIT 3', disk_engine)

I get:

    index   0   1
0   0   293 3785616
1   1   3250207 5540538
2   2   1000052 5542982

Why are my columns not renamed? Should have df=df.rename(columns = {0:'screen',1:'user'})renamed the columns?

what is the output of df.columns.tolist() after renaming the columns? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Apr 23, 2016 at 21:46
I see ['screen', 'user']. For some reason I am not seeing that in the output I get from the query. When I run df = pd.read_sql_query('SELECT * FROM data WHERE screen = 293 LIMIT 3', disk_engine) I get an error saying screen is not found. — zorny
– zorny, Commented Apr 23, 2016 at 21:47

MaxU - stand with Ukraine · Accepted Answer · 2016-04-23 21:49:33Z

1

you can rename your columns on the fly while reading CSV by specifying the names parameter:

pd.read_csv('C:/Users/xxx/Desktop/jjj.tsv',
            chunksize=chunksize,
            header = None,
            sep='\t',
            iterator=True,
            encoding='utf-8',
            names=['screen','user'])

PS most probably you've created your SQL table first time, not renaming your columns - try to drop and recreate it

answered Apr 23, 2016 at 21:49

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Comments

Mr_Saral · Accepted Answer · 2021-04-19 13:55:55Z

1

If you need to rename columns, the best strategy is to use the inplace flag,

df.rename(columns = {0:'screen',1:'user'}, inplace=True)

answered Apr 19, 2021 at 13:55

Mr_Saral

111 bronze badge

Collectives™ on Stack Overflow

Renaming column in Python SQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related