I have an excel file of three sheets:
Sheet1:
name fav date
blue 5 11/20/2020
orange 5 11/20/2020
red 5 11/20/2020
sheet 2 and 3 follows the same format (same column names as well)
I use pandas to read the excel file and it comes out as orderedDict:
dict = pd.read_excel('Book1.xlsx', sheet_name= None)
output:`
OrderedDict([('Sheet1', name fav date
0 purple 3 2020-04-29
1 tan 2 2020-04-29
2 blue 1 2020-04-29), ('Sheet2', name fav date
0 white 1 2020-04-29
1 black 2 2020-04-29
2 gray 3 2020-04-29), ('Sheet3', name fav date
0 blue 5 2020-11-20
1 orange 5 2020-11-20
2 red 5 2020-11-20)])
If I wanted to do any manipulation to all three sheets with the same column names How would I do that?
like for instance If to add the letter 'g' at the end of each name column value in each sheet
ex:
Sheet1:
name fav date
blueg 5 11/20/2020
orangeg 5 11/20/2020
redg 5 11/20/2020
sheet2:
name fav date
whiteg 1 4/29/2020
blackg 2 4/29/2020
grayg 3 4/29/2020
any suggestions?
When you iterate over the items in a dictionary, use .items() and Python returns them as key, value pairs. In the dictionary returned by read_excel(), the key is the sheet name and the value is the dataframe. To access the dataframe, split the tuple into in the for statement and then manipulate the dataframe in-place:
dict = pd.read_excel('Book1.xlsx', sheet_name= None)
for sheet_name, df in dict.items():
df['name'] = df['name'] + 'g'
Note: Using dict for the name of your dictionary shadows the built-in dict() function for creating dictionaries. You should use a different name.