logo
down
shadow

Merge two columns in Pandas


Merge two columns in Pandas

Content Index :

Merge two columns in Pandas
Tag : python , By : jim
Date : January 11 2021, 03:32 PM

around this issue Use combine_first:
df['merged'] = df['buy_ts'].combine_first(df['sell_ts'])

Comments
No Comments Right Now !

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

Share : facebook icon twitter icon

Python/Pandas: combine columns from 2 dataframes based on match of values between columns, but can't use merge


Tag : python , By : 66.
Date : March 29 2020, 07:55 AM
To fix the issue you can do I have two dataframes and I need to separate rows where a value from pmdf matches one of the codes in jcrdf.All_codes. , One way would be to expand jcrdf All_codes column and then use merge
jcrdf_temp = jcrdf.set_index(['jcode', 'jobtitle', 'location']).All_codes.str.split(',',expand = True)\
.stack().reset_index(3,drop = True).reset_index(name = 'All_codes')

new_df = pd.merge(pmdf, jcrdf_temp, left_on = 'code', right_on = 'All_codes')
    code    count   jcode       jobtitle    location    All_codes
0   0567-8315   6   3333-3333   technician  loc3    0567-8315
1   0567-8315   6   3333-3333   technician  loc3    0567-8315
2   0007-4977   7   2222-2222   noob        loc4    0007-4977
3   0007-4977   7   2222-2222   noob        loc4    0007-4977
4   0096-0225   10  4444-4444   manager     loc1    0096-0225
5   0096-0225   10  4444-4444   manager     loc1    0096-0225
6   1365-2133   2   1111-1111   retiree     loc2    1365-2133
new_df = new_df.drop('All_codes', 1).groupby(['jcode', 'jobtitle', 'count', 'location']).code.apply(','.join).reset_index()

    jcode       jobtitle    count   location    code
0   1111-1111   retiree     2       loc2        1365-2133
1   2222-2222   noob        7       loc4        0007-4977,0007-4977
2   3333-3333   technician  6       loc3        0567-8315,0567-8315
3   4444-4444   manager     10      loc1        0096-0225,0096-0225

TypeError from merge pandas DataFrame on columns when item in columns is list


Tag : python , By : picamiolo
Date : March 29 2020, 07:55 AM
this one helps. I got TypeError: type object argument after * must be an iterable, not itertools.imap for doing pd.merge 2 dataframes df1 and df_idenon the columns 'allmzidx' where data is list , I think need convert lists to tuples:
df1['allmzidx'] = df1['allmzidx'].apply(tuple)

for index, each_iden in enumerate(alliden_tuple2):
    df_iden = pd.DataFrame(each_iden, columns=['int','mztop3','allmzidx'])
    df_iden['allmzidx'] = df_iden['allmzidx'].apply(tuple)
    df_iden = pd.merge(df_iden, df1, how='left', on='allmzidx')

pandas merge: merge two dataframes on same column but keep different columns


Tag : python , By : Hitesh Prajapati
Date : March 29 2020, 07:55 AM
I wish this help you I have two pandas dataFrames that share one common column name. I would like to merge on the common column name but keep all the different columns from the second dataFrame where there's a match on the common column name. Here's an example of the two dataFrames: , If you have
setup
z=io.StringIO("""    A      B      C     D     E      F      G     H
    value2 value2   value2 value2 value2 value2  value2 value2
    value3 value3   value3 value3 value3 value3  value3 value3
    value value   value value value value  value value""")

df = pd.read_table(z, delim_whitespace=True)

z2=io.StringIO("""      A     I     J     K     L
    value value value value value
    value2 value2 value2 value2 value2
    value3 value3 value3 value3 value3""")

df2=pd.read_table(z2,delim_whitespace=True)
pd.merge(df,df2, on="A",right_index=True, left_index=True)


    A       B       C       D       E       F       G       H       I       J       K       L
0   value   value   value   value   value   value   value   value   value   value   value   value
1   value   value   value   value   value   value   value   value   value   value   value   value
2   value   value   value   value   value   value   value   value   value   value   value   value
pd.merge(df.set_index("A"),df2.set_index("A"), right_index=True, left_index=True).reset_index()

    A       B       C       D       E       F       G       H      I        J       K       L
0   value2  value2  value2  value2  value2  value2  value2  value2  value2  value2  value2  value2
1   value3  value3  value3  value3  value3  value3  value3  value3  value3  value3  value3  value3
2   value   value   value   value   value   value   value   value   value   value   value   value

How to merge columns after groupby and selecting first valid value of other columns in a pandas dataframe?


Tag : python , By : Longchao Dong
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further You don't need to loop, but you do need to "melt" your dataframe before your group-by operation.
So starting with:
from io import StringIO
import pandas
f = StringIO("""\
ID,col_1,col_2,col_3,Date
1,,20,40,1/1/2018
1,10,,,1/2/2018
1,50,,60,1/3/2018
3,40,10,90,1/1/2018
4,,80,80,1/1/2018
""")

df = pandas.read_csv(f)
print(
    df.melt(id_vars=['ID', 'Date'], value_vars=['col_1', 'col_2', 'col_3'], value_name='first')
      .groupby(by=['ID', 'variable'])
      .first()
      .unstack(level='variable')
)
              Date                     first            
variable     col_1     col_2     col_3 col_1 col_2 col_3
ID                                                      
1         1/1/2018  1/1/2018  1/1/2018  10.0  20.0  40.0
3         1/1/2018  1/1/2018  1/1/2018  40.0  10.0  90.0
4         1/1/2018  1/1/2018  1/1/2018   NaN  80.0  80.0
def flatten_columns(df, sep='_'):
    newcols = [sep.join(_) for _ in df.columns]
    return df.set_axis(newcols, axis='columns', inplace=False)

print(
    df.melt(id_vars=['ID', 'Date'], value_vars=['col_1', 'col_2', 'col_3'], value_name='first')
      .groupby(by=['ID', 'variable'])
      .first()
      .unstack(level='variable')
      .sort_index(level='variable', axis='columns')
      .pipe(flatten_columns)
)
   Date_col_1  first_col_1 Date_col_2  first_col_2 Date_col_3  first_col_3
ID                                                                        
1    1/1/2018         10.0   1/1/2018         20.0   1/1/2018         40.0
3    1/1/2018         40.0   1/1/2018         10.0   1/1/2018         90.0
4    1/1/2018          NaN   1/1/2018         80.0   1/1/2018         80.0

Merge Pandas DataFrame using apply() to only merge on partial match in two columns


Tag : python , By : Nick Pegg
Date : March 29 2020, 07:55 AM
it helps some times I need to merge two pandas DataFrames but not only on exact column values, but also on approximate ones. , Here is one way from merge_asof
pd.merge_asof(df,df2,left_on='col2',right_on='col2b',tolerance = 1,direction ='nearest').dropna()
Out[7]: 
  col1  col2 col1a  col2b
0    a     3    aa    3.0
1    b     4    bb    4.0
2    c    66    cc   67.0
Related Posts Related QUESTIONS :
  • 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
  • Is it possible to specify starting values for each parameter (instead of bounds) for scipy's differential evolution?
  • why datetime.now() and constructed datetime using all fields(like year,month...) of now has big timedelta?
  • MySQL multiple table UPDATE query using sqlalchemy core?
  • find if a semantic version is superset of of another version python
  • Type checking against dynamically created objects
  • Struggling with simple reverse function
  • Is there a function for finding the midpoint of n points on sklearn.neighbors.NearestNeighbors?
  • How to set max number of tweets to fetch
  • PYTHON 3.7.4 NOT USING SQLITE 3.29.0
  • How to replace Nan value with zeros in a numpy array?
  • How to speed up calculating variance among sparse matrix
  • cupy code is not fast enough compared with numpy
  • How to count frequency of select values in Python pandas dataframe
  • Scrape Span Text from Google
  • Python watchdog, watch a directory and rename file on event.modification
  • Filtering rows in DataFrame with dependent conditions
  • How to check if a character is a not a part of number or URL in string?
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com