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'>

Answer 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..