How do I update dataframe column values with Pandas?

Question

So say I have a bunch of data that goes like this:

10-12-2014 3.45
10-12-2014 3.67
10-12-2014 4.0
10-12-2014 5.0
10-13-2014 6.0
10-13-2014 8.9

and so on.

I want to put this stuff into a Pandas dataframe in this sort of format:

10-12-2014    3.45    3.57    4.0    5.0
10-13-2014    6.0     8.9     etc   etc

In order to do that, currently I just have something like this:

rows = cursor.fetchall()
df = pd.DataFrame(columns('Date', Val1', 'Val2', 'Val3', 'Val4'))
previous_date = 0
for row in rows:
    if previous_date == 0:
        df.

And that's where I'm stuck - I can't seem to find a method that just adds a date and one of the values to the column. I'm also not sure how to update old columns in future iterations, because I've really only been able to find examples of people just adding entire rows. Or is there a better way to do this?

ZJS · Accepted Answer · 2014-08-01 14:30:11Z

Working from the string to get into that format you can do the following.

1) Get data as df

d = """10-12-2014 3.45
10-12-2014 3.67
10-12-2014 4.0
10-12-2014 5.0
10-13-2014 6.0
10-13-2014 8.9"""

df = pd.read_csv(StringIO.StringIO(d),sep="\s",names=['Date','v'])

2) group it up and apply a function to index your values.

groups = df.groupby('Date')
df =     groups.apply(lambda x: x['v'].reset_index(drop=True))

#line below is equivalent to above but clunkier use if you really want the ValX as columns
#df =     groups.apply(lambda x: pd.Series({"Val{0}".format(1+i):each for i,each in enumerate(x['v'])}))

3) reset indexes: use unstack to pivot your value indexes into columns and reset index to reset your date as a column...

df =     df.unstack(level=1)
df =     df.reset_index()

edit for speed if you don't really care about naming the columns

groups = df.groupby('Date').indices
df = pd.DataFrame(data= groups.values(),index=groups.keys()).reset_index()

output

         Date  Val1  Val2  Val3  Val4
0  10-12-2014  3.45  3.67     4     5
1  10-13-2014  6.00  8.90   NaN   NaN

Collectives™ on Stack Overflow

How do I update dataframe column values with Pandas?

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