CODEWITHSUNDEEP
pythonpython-2.7pandasdataframe
nrs90
nrs90

Reputation: 168

Taking certain column values from one row in a Pandas Dataframe and adding them into another dataframe

I would like to copy certain column values from a specific row in my dataframe df to another dataframe called bestdf

Here I create an empty dataframe (called bestdf): 

new_columns = ['DATE', 'PRICE1', 'PRICE2']
bestdf = pd.DataFrame(columns = new_columns)
bestdf.set_index(['DATE'])

.I've located a certain row out of df and assigned the row to a variable last_time: 

last_time = df.iloc[-1]
print last_time

gives me

                  DATETIME     PRC
2016-10-03 00:07:39.295000  335.82

I then want to take the 2016-10-03 from the DATETIME column and put that into the DATE column of my other dataframe (bestdf). I also want to take the PRC and put it into the PRICE1 column of my empty dataframe. I want bestdf to look like this: 

                      DATE  PRICE1  PRICE2
                2016-10-03  335.82

Here is what I've got so far?

sample_date = str(last_time).split()
best_price = sample_date[2]
sample_date = sample_date[0]

bestdf['DATE'] = sample_date
bestdf['PRICE1'] = best_price

This doesn't seem to work though. FYI I also want to put this into a loop (where last_time will be amended and each time the new values will be written to a new row). I'm just currently trying to get the functionality correct.

Please help!

Thanks

Upvotes: 0

Views: 162

Answers (1)

Vikash Singh
Vikash Singh

Reputation: 14011

There are multiple ways to do what are you are looking to do: Also you can break your problem down into multiple pieces. That way you will be able to apply different steps to solve them.

Here is an example:

import pandas as pd
from datetime import datetime

data = [{'DATETIME': '2016-10-03 00:07:39.295000', 'PRC': 335.29},
        {'DATETIME': '2016-10-03 00:07:39.295000', 'PRC': 33.9},
        {'DATETIME': '2016-10-03 00:07:39.295000', 'PRC': 10.9}]

df = pd.DataFrame.from_dict(data, orient='columns')

df

output:

    DATETIME                    PRC
0   2016-10-03 00:07:39.295000  335.29
1   2016-10-03 00:07:39.295000  33.90
2   2016-10-03 00:07:39.295000  10.90

code continue:

bestdf = df[df['PRC'] > 15].copy()
# we filter data from original df and make a copy
bestdf.columns = ['DATE','PRICE1']
# we change columns as we need
bestdf['PRICE2'] = None
bestdf

output:

    DATE                        PRICE1  PRICE2
0   2016-10-03 00:07:39.295000  335.29  None
1   2016-10-03 00:07:39.295000  33.90   None

code continue:

bestdf['DATE'] = bestdf['DATE'].apply(lambda value: value.split(' ')[0])
# we change column format based on how we need it to be
bestdf

output:

    DATE    PRICE1  PRICE2
0   2016-10-03  335.29  None
1   2016-10-03  33.90   None

We can also do the same thing with datetime objects also. Doesn't have to be string necessarily.

Upvotes: 1

Related Questions