2

I am trying to get it so that as I loop over a list of things I can add into a dataframe the quantity received from each warehouse on a certain date.

When I try the following it doesn't work:

if inv['prod'] not in self.inventory.columns:
    ## add row in
    self.inventory[inv['prod']] = 0
    idx = inv['time_stamp'].split(' ')[0]
    if idx not in self.inventory.index:
        self.inventory[idx, :] = 0

    self.inventory[idx, inv['prod']] += inv['qty']

I basically need it to add a column based on each product and then the date it arrives/sold. I know this is not very pythonic but just take it that I don't know in advance the dates or else the products in advance.

data frame will look like this by the end:

Date         InventoryA       InventoryB
2017-01-01       10              NaN
2017-01-02       NaN             NaN
2017-01-03       NaN              5
2017-01-04       NaN              5
2017-01-05       -5              NaN
2017-01-06       NaN             -10
2017-01-07       15              NaN
2017-01-08       NaN             NaN
2017-01-09      -20              NaN
bpython
  • 241
  • 5
  • 15
  • Have you got some test data, like what `inv` is? I would guess that there is a simpler way to convert to a dataframe than looping. – Ken Syme Sep 21 '17 at 12:22
  • It's very confusing to understand your data structure. – foxyblue Sep 21 '17 at 12:22
  • @ken syme there is not simpler way to convert the dataframe as I dont have the total dataframe I get individual messages as they come; that is why i said in the question take it that we have to loop as such – bpython Sep 21 '17 at 13:04
  • @bpython Can you give an example of one of the `inv` "messages" you get? – Ken Syme Sep 21 '17 at 13:08
  • ['InventoryA', 10] – bpython Sep 21 '17 at 13:23
  • How does that work with the `'prod'`, `'time_stamp'` and `'qty'` keys you seem to be looking up, dictionary style? – Ken Syme Sep 21 '17 at 14:08

1 Answers1

3

Say your initial dataframe looks like so:

data = {'InventoryA': [10, np.nan, -5]}
df = pd.DataFrame(data, index=pd.to_datetime(['2017-01-01', '2017-01-03', '2017-01-05']))

Now you want to add a new value (not sure in what you form you want it, but here it's in a dataframe):

new_vals = pd.DataFrame({'InventoryB': 10}, index=pd.to_datetime(['2017-01-02']))

# Add new column if it doesn't exist.
if new_vals.columns.values not in df.columns.values:
    df[new_vals.columns.values[0]] = np.nan

# Add new row if it doesn't exist or add value if it does.
if new_vals.index not in df.index: 
    df = df.append(new_vals)
else: df.loc[new_vals.index, new_vals.columns] += new_vals

# Sort by date
df = df.sort_index(axis=0)
binjip
  • 534
  • 3
  • 7