2253

pandas merging dataframes in a loop

Question:

I created a loop to read sqlite database into a pandas dataframe, and I am trying to merge them together based on "Code"

... df = pandas.Dataframe() # Creating an empty dataframe for merging at the end items = ["tb1", "tb2", "tb3"] for each_item in items: my_value = pandas.read_sql_query('select "Code", "Name", "Value" from {tb_name} where "Value" is not null' .format(tbl_name='"%s"' % each_item), con=engine) print(my_value) # This below code is my attempt to merge the dataframes that was obtained through the for loop merge_value = pandas.merge(my_value, df, on='Code', how='outer')

my_value results:

# tb1 results Code Name Value 0 C01 Name1 0.010000 1 C02 Name2 0.001200 2 C03 Name3 0.000300 3 C04 Name4 0.001700 # tb2 results Code Name Value 0 C03 Name3 0.010000 1 C04 Name4 0.001200 2 C05 Name5 0.000300 3 C06 Name6 0.001700 # tb3 results Code Name Value 0 C01 Name1 0.010000 1 C02 Name2 0.001200 2 C05 Name5 0.000300 3 C06 Name6 0.001700

I am trying to merge them into one table like below:

# desired results Code Name Value_x Value_y Value_Z 0 C01 Name1 0.010000 NULL 0.010000 1 C02 Name2 0.001200 NULL 0.001200 2 C03 Name3 0.000300 0.010000 NULL 3 C04 Name4 0.001700 0.001200 NULL 4 C05 Name5 NULL 0.000300 0.000300 5 C06 Name6 NULL 0.001700 0.001700

How do I merge it? I tried the below, but it produces key error: Code

merge_value = pandas.merge(my_value, df, on='Code', how='outer')

Answer1:

You can use DataFrame.merge instead of pandas.merge

<a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html" rel="nofollow">https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html</a>

<hr />

Create an empty DataFrame with the columns to prevent the "key error: Code"

df = pd.DataFrame(columns=['Code'])

then in the loop, you

df.merge(my_value, on='Code', how='outer')

after my_value is created

Recommend

  • hstack csr matrix with pandas array
  • how to prevent pandas psql.read_sql_query from fetching cache
  • Python Pandas read_sql_query “'NoneType' object is not iterable” error
  • Get specific date range
  • R tbats Model Seasonal Customer Flag No Results
  • Update Problems With Creating Simple Php,MySql Voting System
  • How do i get information about foreign keys in SQLite?
  • How to pass a variable between Flask pages?
  • How to pass a variable between Flask pages?
  • Is it valid to use a C99-style designated initializer list to initialize the members of a bit field
  • Error when submitting checkbox to MySQL
  • Parse JSON value as Enum in Rust
  • Imports in __init__.py and `import as` statement
  • Escaping single quotes in JDBC with MySql
  • Adding Parent and Child Nodes in TreeView from Sql Server 2008
  • Installing PHP 7 on digitalocean
  • ListItem.Attributes.Add not working
  • Error when parsing timestamp with pandas read_csv
  • Highlight one bar in a series in highcharts?
  • Getting last autonumber in access
  • How to check if every primary key value is being referenced as foreign key in another table
  • MySQL WHERE-condition in procedure ignored
  • Display Images one by one with next and previous functionality
  • Font Awesome Showing Box instead of Icons
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • php design question - will a Helper help here?
  • Is there a mandatory requirement to switch app.yaml?
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • How do you join a server to an Active Directory (domain)?
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • How does Linux kernel interrupt the application?
  • LevelDB C iterator
  • Is it possible to post an object from jquery to bottle.py?
  • How can i traverse a binary tree from right to left in java?
  • Python/Django TangoWithDjango Models and Databases
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How do I use LINQ to get all the Items that have a particular SubItem?