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

Question

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

Could you create a minimal example and show your desired output?! — Cleb
– Cleb, Commented Feb 20, 2017 at 18:04
do you want to create a new dataframe from certain rows and columns of another dataframe ? — Vikash Singh
– Vikash Singh, Commented Feb 20, 2017 at 18:15
@VikashSingh thats what I want to do yes (I've edited the above to make it more informative). — nrs90
– nrs90, Commented Feb 21, 2017 at 8:34
@nrs90 if you can share your source dataFrame structure also it would be of much help. Thanks. — Vikash Singh
– Vikash Singh, Commented Feb 21, 2017 at 10:08

Vikash Singh · Accepted Answer · 2017-02-21 09:55:09Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related