Python DataFrame Merging

Question

I need to merge two dataframes. I create several of the below dataframe from reading files.

What i need to do is pull the 'Depth' column and insert it into a new dataframe. I will then rename the column 'Depth' in the merged dataframe to the serial number of that part. Then repeat.

sigData example

  Current  Depth  Time Velocity
0     130  11066   0.1    26516
1     150  13716   0.2    24090
2     153  15995   0.3    25052
3     157  19109   0.4    26596
4     160  20298   0.5    19947

The resulting dataframe after looping through all 'sigData' files should look like this:

depthDF example

     Time    Sn1     Sn2      Sn3
0     0.1  11066   00001    00001
1     0.2  13716   00002    00002
2     0.3  15995   00003    00003
3     0.4  19109   00004    00004
4     0.5  20298   00005    00005

I will do the same with 'Current' and 'Velocity'. The result should be three dataframes. One with the 'Depth' of all parts, one with 'Velocity' of all parts, and one with 'Current' of all parts.

velocityDF = pd.DataFrame(columns=['Time'])
velocityDF = velocityDF.join(sigData['Velocity'], on='Time', sort='True')
velocityDF.rename(columns={'Velocity': row['SerialNumber']}, inplace=True)

results in:

Empty DataFrame
Columns: [Time, 400602902, 400621787, 400621434, 400619512]
Index: []

and

depthDF = pd.DataFrame(columns=['Time'])
depthDF = depthDF.merge(sigData['Depth'], on='Time', sort='True')
depthDF.rename(columns={'Depth': row['SerialNumber']}, inplace=True)

results in:

ValueError: can not merge DataFrame with instance of type <class 'pandas.core.series.Series'>

Mohd Bilal · Accepted Answer · 2020-05-13 19:41:05Z

1

Try this:

depthDF = depthDF.merge(sigData[['Depth','Time']], on='Time', sort='True', how='right')

Same do for velocityDF.

Hope it helps and will resolve your error..

edited May 13, 2020 at 19:41

answered May 13, 2020 at 18:19

Mohd Bilal

1018 bronze badges

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

10 Comments

Maxwell Over a year ago

KeyError: 'Time' It looks like it only pulled the 'Depth' column so it did not have a key to use for the merge

Mohd Bilal Over a year ago

Try depthDF = depthDF.merge(sigData[['Depth','Time']], on='Time', sort='True')

Maxwell Over a year ago

TypeError: unhashable type: 'list' i cant seem to find a way to pull two columns from a dataframe by column name and not column number ex(print(sigData[['Depth','Time']].head()))

Mohd Bilal Over a year ago

For that you have to use .loc. Try to pull with print(sigData.loc[ : , 'Depth':'Time'].head()).

Maxwell Over a year ago

Yeah that does work, it returns the two columns and data but depthDF = depthDF.merge(sigData.loc[:,['Depth','Time']], on='Time', sort='True') Still returns: Empty DataFrame Columns: [400602902, 400621787, 400621434, 400619512, Time] Index: []

|

Collectives™ on Stack Overflow

Python DataFrame Merging

1 Answer 1

10 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Related