I have a dataframe
>>> df
name count
0 a 1
1 b 2
2 c 0
I want to update the value using this list
l = ['a','b','d']
So my updated df should look like this
>>> df
name count
0 a 2
1 b 3
2 c 0
3 d 1
Only way I can think of is using loop. Can you people suggest any other way. Thanks
You can create Series from list and get counts by Series.value_counts, then create Series from df by DataFrame.set_index and Series.add together, last for DataFrame use Series.rename_axis
and Series.reset_index:
l = ['a','b','d']
add = pd.Series(l).value_counts()
print (add)
d 1
a 1
b 1
dtype: int64
df1 = (df.set_index('name')['count']
.add(add, fill_value=0)
.astype(int)
.rename_axis('name')
.reset_index(name='count'))
print (df1)
name count
0 a 2
1 b 3
2 c 0
3 d 1
Another way would be to add the values on top of each other and GroupBy.count:
x = sorted(list(set(df['name'].tolist() + l)))
new = pd.concat([df['name'].repeat(df['count']).to_frame()
, pd.DataFrame({'name':l})]).groupby('name')['name'].count()
new = new.reindex(x, fill_value=0).reset_index(name='count')
Output
print(new)
name count
0 a 2
1 b 3
2 c 0
3 d 1
I think using a Counter would be appropriate in these situations. The only downside is conversion from df to dict and vice versa.
from collections import Counter
# initialize your variables
df = pd.DataFrame({'name': ['a', 'b', 'c'],
'count': [1, 2, 0]})
l = ['a', 'b', 'd']
# convert to dict with name - count pairs and update with counter of l
df_as_dict = dict(zip(df['name'].values, df['count'].values))
df_as_dict.update(Counter(df_as_dict) + Counter(l))
# create dataframe with updates values
new_df = pd.DataFrame({'name': list(df_as_dict.keys()),
'count': list(df_as_dict.values())})
# ensure df format
new_df = new_df.sort_values('name').reset_index(drop=True)
new_df
count name
0 2 a
1 3 b
2 0 c
3 1 d
