logo
down
shadow

Less Memory-intense way of copying tables & renaming columns in sqlite/pandas


Less Memory-intense way of copying tables & renaming columns in sqlite/pandas

Content Index :

Less Memory-intense way of copying tables & renaming columns in sqlite/pandas
Tag : python , By : IanM
Date : November 29 2020, 04:01 AM

Hope this helps To elaborate on my comments...
If you have a table in foo.db and want to copy that table's data to a new table in bar.db with different column names:
$ sqlite3 foo.db
sqlite> ATTACH 'bar.db' AS bar;
sqlite> CREATE TABLE bar.newtable(newcolumn1, newcolumn2);
sqlite> INSERT INTO bar.newtable SELECT oldcolumn1, oldcolumn2 FROM main.oldtable;

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Renaming SQLite Tables/Columns/Rows after indices have been created


Tag : sql , By : krs
Date : March 29 2020, 07:55 AM
hop of those help? If you're using ALTER TABLE with RENAME TO to rename a table, then as described on this page (from the sqlite docs) the indices will still work:

Workflow for adding new columns from Pandas to SQLite tables


Tag : python , By : can
Date : March 29 2020, 07:55 AM
This might help you There are several approaches, you may select which are better for your particular task:
from sqlalchemy import create_engine
import psycopg2
mydb = create_engine('postgresql://user@host.domain:5432/database')
# lets select some groups of data into first dataframe, 
# you may use school ids instead of my sections
df=pd.read_sql_query('''SELECT sections, count(id) FROM table WHERE created_at <'2016-01-01' GROUP BY sections ORDER BY 2 DESC LIMIT 10''', con=mydb)
print(df)  # don't worry about strange output - sections have type int[] and it's supported well!

   sections     count
0  [121, 227]  104583
1  [296, 227]   48905
2  [121]        43599
3  [302, 227]   29684 
4  [298, 227]   26814
5  [294, 227]   24071
6  [297, 227]   23038
7  [292, 227]   22019
8  [282, 227]   20369
9  [283, 227]   19908

# Now we have some sections and we can select only data related to them
for section in df['sections']:
   df2 = pd.read_sql_query('''SELECT sections, name, created_at, updated_at, status 
                              FROM table 
                              WHERE created_at <'2016-01-01'   
                                  AND sections=%(section)s 
                              ORDER BY created_at''', 
                           con=mydb, params=dict(section=section))
    print(section, df2.std())

[121, 227] status    0.478194
dtype: float64
[296, 227] status    0.544706
dtype: float64
[121] status    0.499901
dtype: float64
[302, 227] status    0.504573
dtype: float64
[298, 227] status    0.518472
dtype: float64
[294, 227] status    0.46254
dtype: float64
[297, 227] status    0.525619
dtype: float64
[292, 227] status    0.627244
dtype: float64
[282, 227] status    0.362891
dtype: float64
[283, 227] status    0.406112
dtype: float64
from sqlalchemy import create_engine
mydb = create_engine('postgresql://user@host.domain:5432/database')
df2.to_sql('tablename', mydb, if_exists='append', chunksize=100)

Renaming columns and tables


Tag : google-cloud-platfor , By : Shitic
Date : March 29 2020, 07:55 AM
hope this fix your issue You cannot rename a column or a table. Other kinds of alterations are possible, but not renaming. A work-around for renaming columns is available in this answer: Is it possible to rename columns?

Parsing Excel data with pandas - why is it skipping columns when renaming columns?


Tag : python , By : Tetting
Date : March 29 2020, 07:55 AM
may help you . I really hope its something simple im missing. I'm reading in excel workbooks using python pandas. When I rename my columns to be numbers 1:len(columns) it skips the first few columns.
#dataframe have default columns names
df = pd.DataFrame({0:list('abcdef'),
                   1:[4,5,4,5,5,4],
                   2:[7,8,9,4,2,3]})

print (df)
   0  1  2
0  a  4  7
1  b  5  8
2  c  4  9
3  d  5  4
4  e  5  2
5  f  4  3

#first column called index
print (df.index)
RangeIndex(start=0, stop=6, step=1)

#check columns names (RangeIndex can be also)
print (df.columns)
Int64Index([0, 1, 2], dtype='int64')

#add 1 to columns anmes and convert to str
df.columns = (df.columns + 1).astype(str)
print (df)
   1  2  3
0  a  4  7
1  b  5  8
2  c  4  9
3  d  5  4
4  e  5  2
5  f  4  3

print (df.columns)
Index(['1', '2', '3'], dtype='object')
df = df.rename(columns = lambda x: str(x + 1))
print (df.columns)
Index(['1', '2', '3'], dtype='object')
df = pd.DataFrame({'a':list('abcdef'),
                   'f':[4,5,4,5,5,4],
                   'm':[7,8,9,4,2,3]})

print (df)
   a  f  m
0  a  4  7
1  b  5  8
2  c  4  9
3  d  5  4
4  e  5  2
5  f  4  3

df.columns = pd.RangeIndex(1, df.shape[1] + 1).astype(str)

print (df.columns)
Index(['1', '2', '3'], dtype='object')

print (df)
   1  2  3
0  a  4  7
1  b  5  8
2  c  4  9
3  d  5  4
4  e  5  2
5  f  4  3

Copying different columns into one single column from different files and renaming them with the same filename


Tag : awk , By : Nick
Date : March 29 2020, 07:55 AM
should help you out If you're using GNU awk, you can edit files in-place with bundled inplace library:
gawk -i inplace '{for(i=1;i<=NF;i++) print $i}' *.data
Related Posts Related QUESTIONS :
  • Is it possible to print from Python using non-ANSI colors?
  • Pandas concat historical data using date minus some number of days
  • CV2: Import Error in Python OpenCV
  • Is it possible to do this loop in a one-liner?
  • invalid literal for int() with base 10: - django
  • Why does my code print a value that I have not assigned as yet?
  • the collatz func in automate boring stuff with python
  • How to find all possible combinations of parameters and funtions
  • about backpropagation deep neural network in tensorflow
  • Sort strings in pandas
  • How do access my flask app hosted in docker?
  • Replace the sentence include some text with Python regex
  • Counting the most common element in a 2D List in Python
  • logout a user from the system using a function in python
  • mp4 metadata not found but exists
  • Django: QuerySet with ExpressionWrapper
  • Pandas string search in list of dicts
  • Decryption from RSA encrypted string from sqlite is not the same
  • need of maximum value in int
  • a list of several tuples, how to extract the same of the first two elements in the small tuple in the large tuple
  • Display image of 2D Sinewaves in 3D
  • how to prevent a for loop from overwriting a dictionary?
  • How To Fix: RuntimeError: size mismatch in pyTorch
  • Concatenating two Pandas DataFrames while maintaining index order
  • Why does this not run into an infinite loop?
  • Python Multithreading no current event loop
  • Element Tree - Seaching for specific element value without looping
  • Ignore Nulls in pandas map dictionary
  • How do I get scrap data from web pages using beautifulsoup in python
  • Variable used, golobal or local?
  • I have a regex statement to pull all numbers out of a text file, but it only finds 77 out of the 81 numbers in the file
  • How do I create a dataframe of jobs and companies that includes hyperlinks?
  • Detect if user has clicked the 'maximized' button
  • Does flask_login automatically set the "next" argument?
  • Indents in python 3
  • How to create a pool of threads
  • Pandas giving IndexError on one dataframe but not on another similar dataframe
  • Django Rest Framework - Testing client.login doesn't login user, ret anonymous user
  • Running dag without dag file in airflow
  • Filling across a specified dimension of a numpy array
  • Python populating dataframe in pandas from text files
  • How to interpolate a single ("non-piecewise") cubic spline from a set of data points?
  • Divide 2 integers (leetcode 29) - recursion issue
  • Can someone explain why do I get this output in Python?
  • How do I scrape pdf and html from search results without obvious url
  • Is there a way to automatically make a "collage" of plots with matplotlib?
  • How to combine multiple rows in pandas with shared column values
  • How do I get LOAD_CLASSDEREF instruction after dis.dis?
  • Django - How to add items to Bootstrap dropdown?
  • Linear Regression - Does the below implementation of ridge regression finding coefficient term using gradient method is
  • How to drop all rows in pandas dataframe with negative values?
  • Most Efficient Way to Find Closest Date Between 2 Dataframes
  • Execution error when Passing arguments to a python script using os.system. The script takes sys.argv arguments
  • Looping through a function
  • Create a plot for each unique ID
  • a thread python with 'while' got another thread never start
  • Solution from SciPy solve_ivp contains oscillations for a system of first-order ODEs
  • trigger python events driven by selenium controlled browser
  • Passing line-edits to a contextmanager to set validators
  • Python: globals().items() iterations try to change a dict
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com